题解 | #国庆期间每类视频点赞量和转发量#

第一次写题解不太会写，见谅！ 1、第一步：先统计每类视频每天的点赞量和转发量 select tv.tag,date(tu.start_time) dt,sum(tu.if_like) cnt_like,sum(tu.if_retweet) cnt_re from tb_user_video_log tu join tb_video_info tv using(video_id) where date(tu.start_time) BETWEEN '2021-09-25' and '2021-10-03' group by tv.tag,date(tu.start_time) 注释：对date进行过滤可以加快查询速度

2、第二步：再统计每类视频近一周总点赞量和一周内最大单天转发量 SELECT tag,dt,sum(cnt_like) over(PARTITION by tag order by dt range between INTERVAL 6 day preceding and current row) sum_like_cnt_7d, max(cnt_re) over(PARTITION by tag order by dt range between INTERVAL 6 day preceding and current row) sum_retweet_cnt_7d from ( select tv.tag,date(tu.start_time) dt,sum(tu.if_like) cnt_like,sum(tu.if_retweet) cnt_re from tb_user_video_log tu join tb_video_info tv using(video_id) where date(tu.start_time) BETWEEN '2021-09-25' and '2021-10-03' group by tv.tag,date(tu.start_time) )a 注释：1.窗口函数求和是累加；2.由于是查询近一周，所以使用滑动窗口；3.range和rows的区别：rows返回指定范围行，range指定范围值，由于本题指定日期的前一周的每天数据都存在，所以二者没差别。但是如果指定日期的前一周的每天数据不是都存在，应该用 range才可以，其实除了指定了范围行数的情况，根据指的大小的返回行范围的都应该用range，具体用法不再细说。

3、第三步：最后再筛选出国庆前三天 SELECT * FROM ( SELECT tag,dt,sum(cnt_like) over(PARTITION by tag order by dt range between INTERVAL 6 day preceding and current row) sum_like_cnt_7d, max(cnt_re) over(PARTITION by tag order by dt range between INTERVAL 6 day preceding and current row) sum_retweet_cnt_7d from ( select tv.tag,date(tu.start_time) dt,sum(tu.if_like) cnt_like,sum(tu.if_retweet) cnt_re from tb_user_video_log tu join tb_video_info tv using(video_id) where date(tu.start_time) BETWEEN '2021-09-25' and '2021-10-03' group by tv.tag,date(tu.start_time) )a )b where dt BETWEEN '2021-10-01' and '2021-10-03'

ps：刚学SQL没多久，个人愚见，错了请大佬轻喷，不吝指教，谢谢！