题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
【本题难点在于窗口函数的应用】！！！！！
【窗口函数 】基本格式
函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows between 起始位置 and 结束位置)
- 能够做为窗口函数的聚合函数sum，avg，count，max，min
- rank()，dense_rank()，row_number()等窗口函数
- rows 2 preceding 截止到之前2行，也就是最靠近的的3行；following为截止到之后N行
- rows 2 preceding and 7 following
所以，
<每天的最近一周>为截止到当天之前的6天，rows 6 preceding
<每类视频每天的近一周总点赞量>则为↓↓↓
sum(like_cnt) over (PARTITION by tag order by dt rows 6 preceding)
【解题思路】
- 先表连接。计算出每类视频各天的点赞量和转发量。使用join、group by完成
- 再计算出每类视频各天之前7天的总点赞量和转发量。使用窗口函数完成（rows 6 preceding）
- 最后数据筛选，筛选出国庆前3日数据
1、先按标签tag计算出各天的点赞数like_cnt及转发数retweet_cnt
SELECT i.tag,DATE_FORMAT(l.start_time,'%Y-%m-%d') dt, sum(l.if_like) like_cnt, sum(if_retweet) retweet_cnt from tb_user_video_log l LEFT JOIN tb_video_info i on l.video_id=i.video_id GROUP BY i.tag,DATE_FORMAT(l.start_time,'%Y-%m-%d'))t2、使用窗口函数计算出当天dt前一周的点赞数总和sum以及最大转发数max
select t.tag,t.dt, sum(t.like_cnt) over (PARTITION by t.tag order by t.dt rows 6 preceding) sum_like_cnt_7d, max(t.retweet_cnt) over (PARTITION by t.tag order by t.dt rows 6 preceding) max_retweet_cnt_7d from (SELECT i.tag,DATE_FORMAT(l.start_time,'%Y-%m-%d') dt, sum(l.if_like) like_cnt, sum(if_retweet) retweet_cnt from tb_user_video_log l LEFT JOIN tb_video_info i on l.video_id=i.video_id GROUP BY i.tag,DATE_FORMAT(l.start_time,'%Y-%m-%d'))t3、将数据筛选，选出国庆前三天的数据（WHERE dt BETWEEN '2021-10-01' and '2021-10-03'），并排序
SELECT * from (select t.tag,t.dt, sum(t.like_cnt) over (PARTITION by t.tag order by t.dt rows 6 preceding) sum_like_cnt_7d, max(t.retweet_cnt) over (PARTITION by t.tag order by t.dt rows 6 preceding) max_retweet_cnt_7d from (SELECT i.tag,DATE_FORMAT(l.start_time,'%Y-%m-%d') dt, sum(l.if_like) like_cnt, sum(if_retweet) retweet_cnt from tb_user_video_log l LEFT JOIN tb_video_info i on l.video_id=i.video_id GROUP BY i.tag,DATE_FORMAT(l.start_time,'%Y-%m-%d'))t)t1 WHERE t1.dt BETWEEN '2021-10-01' and '2021-10-03' ORDER BY t1.tag DESC,t1.dt ;
