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

select *
from
(
select  tag,dt,
        sum(sum_like_cnt) over w,
        max(retweet_cnt) over w
from(
select tag,date(end_time) dt,sum(if_like) sum_like_cnt,sum(if_retweet) retweet_cnt
from tb_user_video_log uvl
inner join tb_video_info vi on uvl.video_id=vi.video_id
where date(end_time) between '2021-9-25' and '2021-10-3'
group by tag,dt
)t1
#select是将表格列出来后是从最后一行往后算的，从最后面往前滑动窗口
#window w as(partition by tag  order by dt  rows 6 preceding) 
#这里限定为第一行开始往后滑动窗口
window w as(partition by tag  order by dt desc rows between current row and  6 following)
)t2
where dt between '2021-10-1' and '2021-10-3'
order by tag desc,dt asc