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

• 能够做为窗口函数的聚合函数sum，avg，count，max，min
• rank()，dense_rank()，row_number()等窗口函数
• rows 2 preceding 截止到之前2行，也就是最靠近的的3行；following为截止到之后N行
• rows 2 preceding and 7 following

sum(like_cnt) over (PARTITION by tag order by dt rows 6 preceding)

【解题思路】

1. 先表连接。计算出每类视频各天的点赞量和转发量。使用join、group by完成
2. 再计算出每类视频各天之前7天的总点赞量和转发量。使用窗口函数完成（rows 6 preceding）
3. 最后数据筛选，筛选出国庆前3日数据

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

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

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
;