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

/*统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量，
结果按视频类别降序、日期升序排序。
假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。
解题： 时间区间：国庆头三天，一周内累和
滑动窗口函数滚动求和
rows between current row and n following 从当前行到后面n行
rows between current row and n preceding 从前面n行到当前行
rows between current row and unbounded preceding 前面所有行到当前行的累计
rows between current row and unbounded following*当前行到后面所有行*/

select tag,dt,like_cnt,retweet_cnt
from 
    (select tag,
        date1 dt,
        sum(likecnt) over(partition by tag order by date1  ROWS between 6 preceding and current row) like_cnt,
        max(retcnt) over(partition by tag order by date1  ROWS between 6 preceding and current row) retweet_cnt
    from(
         select tag
            ,date_format(start_time,'%Y-%m-%d') as date1
            ,sum(if_like) as likecnt
            ,sum(if_retweet) as retcnt
         from tb_user_video_log u
         left join tb_video_info i on u.video_id = i.video_id
         where year(u.start_time) = 2021
         group by tag,date1
        )a
    group by tag,dt
    )b 
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt