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

select *
from
(select tag,
date_format(start_time,'%Y-%m-%d') dt,
sum(sum(if_like)) over(partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding)sum_like_cnt_7d,
max(sum(if_retweet)) over(partition by tag order by date_format(start_time,'%Y-%m-%d') rows 6 preceding)max_retweet_cnt_7d
from tb_user_video_log tl
left join tb_video_info ti
using(video_id)
where datediff('2021-10-03',date_format(start_time,'%Y-%m-%d'))<9
group by tag,dt)t1
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt asc
;

框架语句分为以下几种：

CURRENT ROW — 当前行

UNBOUNDED PRECEDING —当前行上侧所有行

UNBOUNDED FOLLOWING —当前行下侧所有行

expr PRECEDING—当前行上侧expr行(expr可以是数字，也可以是表达式)

expr FOLLOWING—当前行下侧expr行(expr可以是数字，也可以是表达式)