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

### 窗口滑动函数的使用。。sum() over(partition by tag order by date_format(tvl.start_time,'%Y-%m-%d')   rows 6 preceding)  

# 国庆期间每类视频点赞量和转发量。
     select A.tag,
            A.dt,
            A.sum_like_cnt_7d,
            A.max_retweet_cnt_7d 
       from (
     select tvi.tag,
            date_format(tvl.start_time,'%Y-%m-%d') dt,
            sum(sum(tvl.if_like)) over(partition by tvi.tag order by date_format(tvl.start_time,'%Y-%m-%d') rows 6 preceding) sum_like_cnt_7d,
            max(sum(tvl.if_retweet)) 
            over(partition by tvi.tag order by date_format(tvl.start_time,'%Y-%m-%d') rows 6 preceding) max_retweet_cnt_7d 
       from tb_user_video_log tvl join tb_video_info tvi using(video_id) 
      where year(tvl.start_time) = 2021 
        and datediff(date('2021-10-03'),tvl.start_time)<=8 
      group by tvi.tag,date_format(tvl.start_time,'%Y-%m-%d') 
      ) A 
      where A.dt in ('2021-10-01','2021-10-02','2021-10-03') 
      order by A.tag desc,dt;