题解 | #国庆期间每类视频点赞量和转发量#附解析
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
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在这个时间范围内每天都有记录。 # 数据库配置要支持这种类型的窗口函数查询。