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

with record as (
    select tag,date_format(start_time,'%Y-%m-%d') as dt,sum(if_like) as sum_like_cnt,sum(if_retweet) as sum_retweet_cnt
    from tb_user_video_log logs
    left join tb_video_info info
    on logs.video_id=info.video_id
    group by tag,date_format(start_time,'%Y-%m-%d')
)

select * from (
select tag,dt,
sum(sum_like_cnt) over (partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
max(sum_retweet_cnt) over (partition by tag order by dt rows 6 preceding) as
max_retweet_cnt_7d
from record
group by tag,dt)a
where dt between '2021-10-01' and '2021-10-03' 
order by tag desc ,dt

救命，数不对的原因：

• dt的限制不要放在里面，放在里面会导致窗口函数计算错，要先生成分组累积之后，再对dt筛选