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

# 思路：得到每一天的点赞和转发,使用窗口函数计算前6天的数据，取国庆前3天
select *
from(
    select tag,dt,
    sum(l) over (partition by tag order by dt rows  between 6 preceding and current row ) like_cnt,
    max(retweet) over (partition by tag order by dt rows  6 preceding ) retweet_cnt #默认到当前行
    from(
    select tag,date(start_time) dt,sum(if_like) l ,sum(if_retweet) retweet
    from tb_user_video_log
    join tb_video_info
    on tb_user_video_log.video_id = tb_video_info.video_id
    where year(start_time)=2021
    group by tag,date(start_time)) t1
    ) t2
where dt in ('2021-10-01','2021-10-02','2021-10-03')
order by tag desc,dt