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

with t1 as
    ( select substr(t1.start_time,1,10) as dt,
                    t2.tag,
                    sum(if_like) as like_cnt,
                    sum(if_retweet) as retweet_cnt
             from
                 tb_user_video_log t1 join tb_video_info t2
                                           on
                                                   t1.video_id = t2.video_id
             where datediff('2021-10-01 00:00:00',start_time) < 7
             group by
                 dt,tag
    ),
t2 as( -- 本币方法，顺着思路就想到一个个union   o.0
    select tag,dt,
           sum(like_cnt) over(partition by tag order by dt, like_cnt) as sum_like_cnt_7d,
           max(retweet_cnt) over(partition by dt,  retweet_cnt ) as max_retweet_cnt_7d
    from t1 where dt between date_sub('2021-10-01',INTERVAL 6 DAY) and '2021-10-01'
            group by tag,dt
    ),
t3 as(
    select tag,dt,
           sum(like_cnt) over(partition by tag order by dt, like_cnt) as sum_like_cnt_7d,
           max(retweet_cnt) over(partition by dt,  retweet_cnt ) as max_retweet_cnt_7d
    from t1 where dt between date_sub('2021-10-02',INTERVAL 6 DAY) and '2021-10-02'
            group by tag,dt

    ),
t4 as(
    select tag,dt,
           sum(like_cnt) over(partition by tag order by dt, like_cnt) as sum_like_cnt_7d,
           max(retweet_cnt) over(partition by dt,  retweet_cnt ) as max_retweet_cnt_7d
    from t1 where dt between date_sub('2021-10-03',INTERVAL 6 DAY) and '2021-10-03'
            group by tag, dt
),
t5 as(
    select tag,max(dt) as dt,max(sum_like_cnt_7d) as sum_like_cnt_7d,max(max_retweet_cnt_7d) as max_retweet_cnt_7d from t2 group by tag
      union all
    select tag,max(dt) as dt,max(sum_like_cnt_7d) as sum_like_cnt_7d,max(max_retweet_cnt_7d) as max_retweet_cnt_7d from t3 group by tag
      union all
    select tag,max(dt) as dt,max(sum_like_cnt_7d) as sum_like_cnt_7d,max(max_retweet_cnt_7d) as max_retweet_cnt_7d from t4 group by tag
)select * from t5 order by tag desc ,dt ;