一、知识点汇总与拓展

先看这个图

开窗函数的框架限定算数我的知识盲区吧，这里正好用到了，顺便完成了一项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;

搞定~