关于mysql国庆头三天近一周的数据查询题目

提示:文章写完后,目录可以自动生成,如何生成可参考右边的帮助文档

前言

在牛客网刷大厂sql题目的时候,发现“国庆头三天近一周的数据查询题目”有出现不止一次,感觉思维还是很重要,在周末就打算总结下牛客网里类似的两道题目,写一篇文章以便自己复习。


一、SQL177 国庆期间近7日日均取消订单量

1.数据源和场景说明

 场景逻辑说明: 

  • 用户提交打车请求后,在用户打车记录表生成一条打车记录,order_id-订单号设为null; 

  • 当有司机接单时,在打车订单表生成一条订单,填充order_time-接单时间及其左边的字段,start_time-开始计费的上车时间及其右边的字段全部为null,并把order_id-订单号order_time-接单时间end_time-打车结束时间)写入打车记录表;若一直无司机接单,超时或中途用户主动取消打车,则记录end_time-打车结束时间

  • 若乘客上车前,乘客或司机点击取消订单,会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间,其余字段设为null。 

  • 当司机接上乘客时,填充订单表中该start_time-开始计费的上车时间。 

  • 当订单完成时填充订单完成时间、里程数、费用;评分设为null,在用户给司机打1~5星评价后填充。 

问题:请统计国庆头3天里,每天的近7日日均订单完成量和日均订单取消量,按日期升序排序。结果保留2位小数。 

2.滑动窗口解题

思路:滑动窗口函数提取国庆三天中每一天假期的前六天数据累计求和但数据源里面的数据是每天每个时间的,所以每一天的数据有好几条,所以先要聚合然后利用聚合后的子查询进行滑动窗口函数累计求和。注:此处只需计算司机取消订单的数量,所以只需要用order这个表就可以。

select dt,
round(cnt4/7,2) finish_num_7d,
round((cnt3-cnt4)/7,2) cancel_num_7d
from
(
select dt,
sum(cnt1) over(order by dt rows between 6 preceding and current row) cnt3,
sum(cnt2) over(order by dt rows between 6 preceding and current row) cnt4
from
(
select date(order_time) dt,count(order_id) cnt1,count(start_time) cnt2 
#cnt1各天总订单,cnt2各天完成订单
from tb_get_car_order
group by date(order_time)
) t1
) t2
where datediff('2021-10-3',dt) between 0 and 2

知识点:

1.提取国庆三天可以用datediff('2021-10-3',dt) between 0 and 2,用 date_formate(dt,'%Y-%m-%d') between '2021-10-1' and '2021-10-3' 会报错, 直接dt between '2021-10-1' and '2021-10-3'就可以,不需要套一层格式化。

2.滑动窗口逻辑,此处不用分区。
 

3.笛卡尔积解题

思路:先查询出国庆三天的日期数据与原数据链接

select t1.dt,
round(count(od.start_time)/7,2) finish_num_7d,
round((count(od.order_time)-count(od.start_time))/7,2) cancel_num_7d
from
(
select distinct date(order_time) dt
from tb_get_car_order
where datediff('2021-10-3',date(order_time)) between 0 and 2
) t1
join tb_get_car_order od
on datediff(t1.dt,od.order_time) between 0 and 6
group by t1.dt
order by t1.dt

知识点:

1.链接条件就只有datediff(t1.dt,od.order_time) between 0 and 6,即使order_time包括时间,也是可以用datediff。

2.用timestampdiff(day,od.order_time,t1.dt) between 0 and 6会计算的不准确,timestampdiff(day,od.order_time,t1.dt) between 0 and 5结果就和datediff(t1.dt,od.order_time) between 0 and 6。

因为timestampdiff

3.制造日期列和原表链接,国庆三天每天都会对应好多条数据。

二、SQL160 国庆期间每类视频点赞量和转发量

1.数据源和场景说明

问题:统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量,结果按视频类别降序、日期升序排序。假设数据库中数据足够多,至少每个类别下国庆头3天及之前一周的每天都有播放记录。

2.滑动窗口函数解题

select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from
(
select tag,dt,
max(retweet)over(partition by tag order by dt rows between 6 preceding and current row) max_retweet_cnt_7d,
sum(likes)over(partition by tag order by dt rows between 6 preceding and current row) sum_like_cnt_7d
from
(
select info.tag,date(log.start_time) dt,sum(if_retweet) retweet,sum(log.if_like) likes
from tb_user_video_log log
join tb_video_info info
on log.video_id=info.video_id
group by date(log.start_time),info.tag
) t2
) t3
where dt between '2021-10-01' and '2021-10-03'

跟上一题的共同点就是都是在聚合后的情况下进行滑动窗口。

3.笛卡尔积解题

select t2.tag,t1.dt,sum(likes) sum_like_cnt_7d,
max(retweet) max_retweet_cnt_7d
from
(
select distinct date(start_time) dt
from tb_user_video_log
where datediff('2021-10-3',date(start_time)) between 0 and 2
) t1
left join
(
select info.tag,date(log.start_time) dt,sum(if_retweet) retweet,sum(log.if_like) likes
from tb_user_video_log log
join tb_video_info info
on log.video_id=info.video_id
group by date(log.start_time),info.tag
) t2
on datediff(t1.dt,t2.dt) between 0 and 6
group by t1.dt,t2.tag
order by t2.tag desc ,t1.dt

知识点:

1.datediff('2021-10-3',date(start_time))这个地方,如果有一个参数是具体日期,一定要加英文单引号‘’!

2.因为求“每天的近一周总点赞量”和“一周内最大单天转发量”。这两个的聚合条件不一样。“一周总点赞量”的聚合条件是国庆三天(t1.dt)。“一周内最大单天转发量”的聚合条件是每一天(t2.dt),所以需要对聚合条件是每一天进行子查询再链表再聚合条件是国庆三天。

3.制造日期列的表一定要distinct去重日期!!


 总结

这类题目就两个方法,一个是滑动窗口函数进行累计,一个是笛卡尔积然后聚合运算。

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值