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

select * from (
select tag,dt,sum(num_like) over(partition by tag order by dt rows between 6 preceding and current row) num,
max(num_retweet) over(partition by tag order by dt rows between 6 preceding and current row) from(
    select tag,date_format(start_time,'%Y-%m-%d') dt,sum(if_like) num_like,sum(if_retweet) num_retweet
    from tb_user_video_log t
    left join tb_video_info v
    on t.video_id	=v.video_id	
    group by tag,date_format(start_time,'%Y-%m-%d')
) t
) l
where dt between '2021-10-01' and '2021-10-03'
order by tag desc ,dt

窗口函数还可以这样灵活使用！动态求和&求最大值