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

select
    *
from
(select 
    tag,
    dt,
    sum(like_cnt)over(partition by tag order by dt rows 6 preceding)
    as sum_like_cnt_7d	,
    max(retweet_cnt)over(partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from(
    select
        b.tag as tag,
        date_format(a.start_time,'%Y-%m-%d') as dt,
        sum(a.if_like) as like_cnt,
        sum(a.if_retweet) as retweet_cnt
    from 
       tb_user_video_log a left join  tb_video_info b
       on a.video_id = b.video_id
    where
        date_format(a.start_time,'%Y-%m-%d') >= '2021-09-25'
        and date_format(a.start_time,'%Y-%m-%d') <= '2021-10-03'
    group by 
        b.tag,
        date_format(a.start_time,'%Y-%m-%d')    
) t1
)t2
where
    dt in ('2021-10-01','2021-10-02','2021-10-03')
order by
    tag desc, dt

注：

sum(like_cnt)over(partition by tag order by dt rows 6 preceding)

日期升序，当前行+向前取6行

笔记：

rows between 2 perceding and 2 following #当前行往前2行+当前行+当前行往后2行（一共5行）

rows between 1 following 3 following #当前行的后1——>后3（共3行）

rows between unbounded preceding and current row #从第一行到当前行