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

方法一：
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;

方法二：
select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from
(select tag,dt,
       sum(like_cnt) over (partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
       max(retweet_cnt) over (partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from (
     select b.tag,DATE_FORMAT(a.start_time,'%Y-%m-%d') as dt,
     sum(a.if_like) as like_cnt,
     sum(a.if_retweet) as retweet_cnt
     from tb_user_video_log a
     join tb_video_info b
     on a.video_id=b.video_id
     WHERE DATEDIFF('2021-10-03',DATE_FORMAT(start_time,'%Y-%m-%d'))<9
     group by tag,dt
     order by tag,dt
) tt
order by tag desc,dt asc)aa
where dt between '2021-10-01' and '2021-10-03'