题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
Select * from(
Select tag,dt,
sum(like_count) over (PARTITION BY tag ORDER BY dt asc rows 6 preceding) like_cnt,
max(retweet_cnt) over (PARTITION BY tag Order by dt asc rows 6 preceding) retweet_cnt
from (
Select tag,date(start_time) as dt,
sum(if_like) like_count,
sum(if_retweet) retweet_cnt
from tb_video_info a
left join tb_user_video_log b
on a.video_id=b.video_id
group by tag,dt) c
group by tag,dt
order by tag desc, dt asc) d
where d.dt between '2021-10-01' and '2021-10-03'
Select tag,dt,
sum(like_count) over (PARTITION BY tag ORDER BY dt asc rows 6 preceding) like_cnt,
max(retweet_cnt) over (PARTITION BY tag Order by dt asc rows 6 preceding) retweet_cnt
from (
Select tag,date(start_time) as dt,
sum(if_like) like_count,
sum(if_retweet) retweet_cnt
from tb_video_info a
left join tb_user_video_log b
on a.video_id=b.video_id
group by tag,dt) c
group by tag,dt
order by tag desc, dt asc) d
where d.dt between '2021-10-01' and '2021-10-03'
最外层套一个select是为了最后对时间进行筛选，如果在上一层select中筛选会影响开窗函数的计算
