题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
一、知识点汇总与拓展
先看这个图
开窗函数的框架限定算数我的知识盲区吧，这里正好用到了，顺便完成了一项SQL的查缺补漏。
- rows n perceding：从当前行到前n行（一共n+1行）
- rang/rows between 边界规则1 and 边界规则2：rang表示按照值的范围进行定义框架，rows表示按照行的范围进行定义框架
rows between 2 perceding and 2 following #当前行往前2行+当前行+当前行往后2行（一共5行）
rows between 1 following 3 following #当前行的后1——>后3（共3行）
rows between unbounded preceding and current row #从第一行到当前行
二、解题步骤
整体的解题思路，首先查询每个tag每天的点赞量，再使用窗口函数进行框架限定查询。
- 本题的难点主要是查看7天内的累计点赞数，以及对比7天内的转发量最大日。
1）由题意可知，时间范围是9.25-10.1（目的为了缩小查询范围）
- 10月1日近一周的数据为9.25-10.1
- 10月2日近一周的数据为9.26-10.1
- 10月3日近一周的数据为9.27-10.3
DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9 #限定日期
2）查询在规定日期内，每类tag每天的点赞（like_cnt）和转发数据(ret_cnt)
SELECT tag,DATE_FORMAT(start_time,'%Y-%m-%d') dt,
SUM(if_like) like_cnt, #每类tag每天的点赞数据
SUM(if_retweet) ret_cnt #每类tag每天的转发数据
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
GROUP BY dt,tag;
3）接下来统计7天内的累计点赞，以及7天内转发的最大值
- 直接用窗口函数进行限定 rows 6 preceding：从当前行+往前6行
SUM(like_cnt)OVER(PARTITION BY tag ORDER BY dt ROWS 6 PERCEDING) #按照日期顺序，一行一行向下进行每日点赞数累加，每7行为一个窗口框架进行数据累加 SUM(ret_cn)OVER(PARTITION BY tag ORDER BY dt ROWS 6 PERCEDING) #按照日期顺序，进行大小比较，每7行为一个窗口框架，进行数据比较
- 带入 like_cnt，ret_cnt，dt 几个定义
SUM(SUM(if_like))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS sum_like_cnt_7d #得出7天内每类tag的点赞数据 MAX(SUM(if_retweet))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS max_retweet_cnt_7d #得出7天内每类tag转发量最大的日期
#带入完整查询
SELECT tag,DATE_FORMAT(start_time,'%Y-%m-%d') dt,
SUM(SUM(if_like))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS sum_like_cnt_7d,
MAX(SUM(if_retweet))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS max_retweet_cnt_7d
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
GROUP BY dt,tag
4）接下来取出10-1到10-3的数据并按视频类别降序、日期升序排序。
WITH t1 AS (
SELECT tag,DATE_FORMAT(start_time,'%Y-%m-%d') dt,
SUM(SUM(if_like))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS sum_like_cnt_7d ,
MAX(SUM(if_retweet))OVER(PARTITION BY tag ORDER BY DATE_FORMAT(start_time,'%Y-%m-%d') rows 6 preceding) AS max_retweet_cnt_7d
FROM tb_user_video_log JOIN tb_video_info USING(video_id)
WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
GROUP BY dt,tag
)
SELECT * FROM t1 WHERE dt BETWEEN '2021-10-01' AND '2021-10-03' ORDER BY tag DESC,dt ASC;
搞定~
SQL解题集 文章被收录于专栏
这是牛客SQL相关的解题集
虾皮信息公司福利 754人发布
