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

SELECT *
FROM(
SELECT 
    tag,
    DATE_FORMAT(end_time,'%Y-%m-%d') AS dt,
    SUM(SUM(if_like)) OVER(partition by tag order by DATE_FORMAT(end_time,'%Y-%m-%d') rows 6 preceding) AS sum_like_cnt_7d,
    MAX(SUM(if_retweet)) OVER(partition by tag order by DATE_FORMAT(end_time,'%Y-%m-%d') rows 6 preceding) AS max_retweet_cnt_7d
FROM tb_video_info info
JOIN tb_user_video_log log USING(video_id)
WHERE DATEDIFF('2021-10-03',SUBSTR(end_time,1,10))<9
GROUP BY tag,dt
ORDER BY tag DESC,dt) a
WHERE a.dt BETWEEN '2021-10-01' AND '2021-10-03'

#----需要注意SQL的执行顺序，就能理解使用FROM子查询的原因了