开窗函数滑动窗口统计

时间：37ms 内存：6388KB

【需求解析】

原文："统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，结果按视频类别降序、日期升序排序。假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。"

也就是---

统计每类视频，2021年10月1号到3号，这三天，每天往前7天的总点赞量，以及7天内【单天转发量】的最大值

【难点解析】

一、先按天进行聚合统计

因为原数据是以天为单位的统计数据，每一天都会有多条if_like和if_retweet记录，所以先要按照tag，date进行统计，得到每天的总点赞量like_cnt，和总转发量retweet_cnt

二、滑动窗口的设置(ROWS BETWEEN CURRENT ROW AND 6 PRECEDING)

思路：在09.25-10.03这个区间内，按tag聚合，dt逆序，统计得到CURRENT ROW及后6行的点赞量统计sum_like_cnt_7d，和转发量sum_retweet_cnt_7d

注意点：不可以按dt升序，ROW 6 PRECEDING输出，会报错。

SQL_ERROR_INFO: "Window 'w': frame start or end is negative, NULL or of non-integral type"

理由就像Error_Info所说的那样，【开窗函数进行滑动统计时，起点或终点必须非空】。在本题中，如果按照dt升序，第一行数据前面根本不存在前6行，所以会报错

三、记录的筛选

到这一步，答案要求的数据基本已经统计出来了。

最后，在外面再套一层SELECT，取出所有字段，按照tag, dt聚合，HAVING限定日期为10月1号到3号，按照题目要求排序就大功告成啦。

代码如下：

SELECT
  *
FROM (
  SELECT
    tag,
    dt,
    SUM(like_cnt) OVER w sum_like_cnt_7d,
    MAX(retweet_cnt) OVER w sum_retweet_cnt_7d
  FROM (
    SELECT
      tag,
      DATE(start_time) dt,
      SUM(if_like) like_cnt,
      SUM(if_retweet) retweet_cnt
    FROM tb_video_info
    LEFT JOIN tb_user_video_log USING(video_id)
    WHERE DATE(start_time) BETWEEN '2021-09-25' AND '2021-10-03'
    group by 1,2) t1
  WINDOW w AS (PARTITION BY tag ORDER BY dt DESC ROWS BETWEEN CURRENT ROW AND 6 FOLLOWING)
) t2
GROUP BY 1, 2
HAVING dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY 1 DESC, 2