题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
一、先按天进行聚合统计
因为原数据是以天为单位的统计数据,每一天都会有多条if_like和if_retweet记录,所以先要按照tag,date进行统计,得到每天的总点赞量sum_like_cnt,和总转发量max_retweet_cnt
二、滑动窗口的设置(ROWS 6 PRECEDING)
思路:在09.25-10.03这个区间内,按tag聚合,dt排序,统计得到前6行及CURRENT ROW的点赞量统计sum_like_cnt_7d,和转发量max_retweet_cnt_7d
三、记录的筛选
到这一步,答案要求的数据基本已经统计出来了。
最后,在外面再套一层SELECT,取出所有字段,按照tag, dt聚合,HAVING限定日期为10月1号到3号,按照题目要求排序就大功告成啦。
代码如下:
SELECT * FROM
(SELECT tag,dt,
sum(sum_like_cnt) over(partition by tag ORDER BY dt ROWS 6 PRECEDING) as sum_like_cnt_7d,
max(max_retweet_cnt) over(partition by tag ORDER BY dt ROWS 6 PRECEDING) as max_retweet_cnt_7d
FROM
(SELECT tag, date(start_time) as dt,
sum(if_like) as sum_like_cnt,
sum(if_retweet) as max_retweet_cnt
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE date(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
GROUP BY tag,dt)a
)b
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY tag desc,dt