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

• 难点
  • 七天内的累计值与最大值计算，使用滑动窗口，一般是窗口函数中over(xx rows n preceding)，包含本身对前n项进行实时计算。

select *
from (
    select
        t.tag,
        t.dt,
        sum(t.sum_like_cnt_d) over(partition by t.tag order by t.dt rows 6 preceding) as sum_like_cnt_7d,
        max(t.sum_retweet_cnt_d) over(partition by t.tag order by t.dt rows 6 preceding) as max_retweet_cnt_d
    from (
        select
            tvi.tag,
            date_format(tuvl.end_time, '%Y-%m-%d') as dt,
            sum(tuvl.if_like) as sum_like_cnt_d,
            sum(tuvl.if_retweet) as sum_retweet_cnt_d
        from tb_user_video_log as tuvl
            inner join tb_video_info as tvi on tuvl.video_id = tvi.video_id
        group by tvi.tag, date_format(tuvl.end_time, '%Y-%m-%d')
    ) as t
    order by t.tag desc, t.dt
) as t2
where t2.dt between '2021-10-01' and '2021-10-03';