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

select
tag,
dt,
like_cnt,
retweet_cnt
from
(
select 
tag,
date(start_time) as dt,
sum(if_like) as like_perday,
sum(if_retweet) as follow_perday,
sum(sum(if_like) ) over(partition by tag order by date(start_time) rows 6 preceding) as like_cnt,
max(sum(if_retweet) ) over(partition by tag order by date(start_time) rows 6 preceding) as retweet_cnt
from tb_user_video_log a left join tb_video_info b using(video_id)
group by tag,date(start_time)
order by tag desc,date(start_time) ) t1
where dt between "2021-10-01" and "2021-10-03"
order by tag desc,dt

窗口函数的使用方法教程.....思路很简单