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

with t1 as (
    select tag,substr(end_time,1,10) dt,
           sum(if_like) sum_like,
           sum(if_retweet) sum_re
    from tb_user_video_log t1
    join tb_video_info tvi on t1.video_id = tvi.video_id
    group by tag,dt
),
t2 as (
    select *,sum(sum_like) over(partition by tag order by dt rows 6 preceding) sum_like_7,
           max(sum_re) over(partition by tag order by dt rows 6 preceding) sum_re_7
    from t1
    order by tag desc,dt
)
select tag,dt,sum_like_7,sum_re_7 from t2 where dt between '2021-10-01' and '2021-10-03';

# 第一步先求每天的转发量和每天的点赞量
# 第二步求每类视频每天 近一周总点赞量和一周内最大单天转发量(窗口实现)
注意:以后遇到求最近几天指标或者分组TOPN，第一反应就应该想到用窗口实现