题解 | #国庆期间每类视频点赞量和转发量#附解析

SELECT sub2.*
FROM
#内层子查询 sub：
# 从tb_user_video_log表和tb_video_info表进行交叉连接（cross join）。
# 选择视频的类别tag和观看的日期start_time，格式化日期为'%Y-%m-%d'。
# 计算每个类别每天的点赞总数SUM(if_like)和转发总数SUM(if_retweet)。
# 对每个类别的标签和每天的日期进行分组，以便进行聚合计算。
(   
    SELECT
        sub.tag,
        sub.dt,
        SUM(sub.sum_if_like) OVER (PARTITION BY sub.tag ORDER BY sub.dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS sum_like_cnt_7d,
        MAX(sub.sum_if_retweet) OVER (PARTITION BY sub.tag ORDER BY sub.dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS max_retweet_cnt_7d
    FROM 
#内层子查询 sub：
# 从`tb_user_video_log`表和`tb_video_info`表进行交叉连接（cross join）。
# 选择视频的类别tag和观看的日期start_time，格式化日期为'%Y-%m-%d'。
# 计算每个类别每天的点赞总数SUM(if_like)和转发总数SUM(if_retweet)。
# 对每个类别的标签和每天的日期进行分组，以便进行聚合计算。
        (
            SELECT
                vi.tag,
                DATE_FORMAT(start_time,'%Y-%m-%d') AS dt,
                SUM(if_like) AS sum_if_like,
                SUM(if_retweet) AS sum_if_retweet
            FROM tb_user_video_log l ,tb_video_info vi  
            WHERE l.video_id = vi.video_id
            GROUP BY tag,dt
        )sub
) sub2
#最外层查询：
# 在sub2的结果上进行筛选，只取日期在2021-10-01至2021-10-03之间的记录。
# 结果按照视频类别降序，日期升序排序。
WHERE sub2.dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY sub2.tag DESC, sub2.dt ASC;

# 数据库有足够的数据，因为使用了`ROWS BETWEEN 6 PRECEDING AND CURRENT ROW`，这要求每个tag在这个时间范围内每天都有记录。
# 数据库配置要支持这种类型的窗口函数查询。