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

select*

from

(

select tag, date_format(start_time,'%Y-%m-%d') as dt,

sum(sum(case when if_like=1 then 1 else 0 end))over(partition by tag order by date_format(start_time,'%Y-%m-%d')rows 6 preceding) sum_like_cnt_7d,

max(sum(case when if_retweet=1 then 1 else 0 end))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 on tb_user_video_log.video_id=tb_video_info.video_id

where date_format(start_time,'%Y-%m-%d') between '2021-09-25' and '2021-10-03'

group by tag, date_format(start_time,'%Y-%m-%d')

) as b

where dt between '2021-10-01' and '2021-10-03'

order by tag desc, dt asc

1.窗口函数的应用，利用rows n preceding 选择前n条数据 https://blog.csdn.net/qq_42374697/article/details/115109386