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

# 先找到2021/9/25到2021/10/03的数据
with t1 as (
    select b.tag, a.if_like, a.if_retweet, a.end_time
    from tb_user_video_log as a
    left join tb_video_info as b
    on a.video_id = b.video_id
    where a.end_time between '2021-09-25 00:00:00' and '2021-10-03 23:59:59'
),
t2 as (
    # 计算每一个类别每一天的点赞量以及转发量
    select date_format(end_time, '%Y-%m-%d') as day, tag, sum(if_like) as like_cnt, sum(if_retweet) as retweet_cnt
    from t1
    group by date_format(end_time, '%Y-%m-%d'), tag
),# 找到前一个周的总点赞量
t3 as (
    select tag, day as dt, sum(like_cnt) over(partition by tag rows between 6 preceding and current row) as sum_like_cnt_7d
    from t2
),
t4 as (
    select *
    from t3
    where dt >= '2021-10-01'
),
t5 as (
    # 计算单天最大转发量
    (select tag, '2021-10-01' as dt, max(retweet_cnt) as max_retweet_cnt_7d
    from (
        select *
        from t2
        where day <= '2021-10-01'
    ) as e
    group by tag)
    union all
    (select tag, '2021-10-02' as dt, max(retweet_cnt) as max_retweet_cnt_7d
    from (
        select *
        from t2
        where day <= '2021-10-02'
    ) as e
    group by tag)
    union all
    (select tag, '2021-10-03' as dt, max(retweet_cnt) as max_retweet_cnt_7d
    from (
        select *
        from t2
        where day <= '2021-10-03'
    ) as e
    group by tag)
)
select t4.tag, t4.dt, t4.sum_like_cnt_7d, t5.max_retweet_cnt_7d
from t4
left join t5
on t4.tag = t5.tag and t4.dt = t5.dt
order by t4.tag desc, t4.dt;