题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
一、滑动窗口知识点补充
| 取值 | 说明 | 备注 |
|
CURRENT ROW |
当前行 |
|
| N PRECEDING | 前N行 | 看了几个帖子说不包含本行，但根据另一位解题的博主说的以及自己试验应该是包含本行的，即前N+1行 |
| UNBOUNDED PRECEDING | 一直到第一行 |
|
| N FOLLOWING | 后N行 |
|
|
UNBOUNDED FOLLOWING |
一直到最后一行 |
|
SUM(字段) OVER(PARTITION BY 字段 ORDER BY 字段 ROWS M PRECEDING AND N FOLLOWING)
二、解题步骤
（一）查询出每类视频每天的点赞量和转发量，作为一个新表t1
WITH t1 AS (SELECT tag, date_format(start_time, '%Y-%m-%d') dt, SUM(if_like) like_cnt, SUM(if_retweet) retweet_cnt FROM tb_user_video_log tu JOIN tb_video_info tv ON tu.video_id = tv.video_id GROUP BY tag, dt ORDER BY tag, dt)
就是一个简单的分组函数及函数的使用，此时表t1的结构如下：
（二）使用滑动窗口，得到每类视频每天的近一周总点赞量和一周内最大单天转发量
SELECT tag, dt , SUM(like_cnt) OVER(partition by tag order by dt rows 6 preceding) sum_like_cnt_7d , MAX(retweet_cnt) OVER(partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d FROM t1
滑动窗口的用法第一部分已经讲了，得到的下表即为每类视频每天的近一周总点赞量和一周内最大单天转发量
（三）将（二）得到的作为表t2，取出国庆前三天的结果并进行排序就得到最终的结果啦
SELECT * FROM (SELECT tag, dt , SUM(like_cnt) OVER(partition by tag order by dt rows 6 preceding) sum_like_cnt_7d , MAX(retweet_cnt) OVER(partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d FROM t1) t2 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC, dt
三、代码汇总
WITH t1 AS (SELECT tag, date_format(start_time, '%Y-%m-%d') dt, SUM(if_like) like_cnt, SUM(if_retweet) retweet_cnt FROM tb_user_video_log tu JOIN tb_video_info tv ON tu.video_id = tv.video_id GROUP BY tag, dt ORDER BY tag, dt) SELECT * FROM (SELECT tag, dt , SUM(like_cnt) OVER(partition by tag order by dt rows 6 preceding) sum_like_cnt_7d , MAX(retweet_cnt) OVER(partition by tag order by dt rows 6 preceding) max_retweet_cnt_7d FROM t1) t2 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC, dt
