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

1、首先查看over窗口函数的用法

select
tag,
date(start_time) as dt,
sum(if_like) over(partition by tag order by date(start_time)) as sum_like_cnt_7d,
max(if_retweet) over(partition by tag order by date(start_time)) as max_retweet_cnt_7d
from tb_user_video_log left join tb_video_info using(video_id)

返回结果：

在上述表格中，第一行是表头，分别对应原数据中的各列含义，后续每行对应原数据中的每一条记录。 2、此题解答

with r1 as(
select
tag,
dt,
sum(like_count) over(partition by tag order by dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as sum_like_cnt_7d,
max(retweet_count) over(partition by tag order by dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
as max_retweet_cnt_7d
from(
select 
tag,
date(start_time) as dt,
sum(if_like) as like_count,
sum(if_retweet) as retweet_count
from tb_user_video_log left join tb_video_info using(video_id)
group by tag,dt
) t1
where dt>='2021-09-25' and dt<='2021-10-03'
)
select tag,dt,sum_like_cnt_7d,max_retweet_cnt_7d
from r1
where dt>='2021-10-01' and dt<='2021-10-03'
order by tag desc, dt asc

学习参考链接： 1、SQL160 国庆期间每类视频点赞量和转发量https://zhuanlan.zhihu.com/p/328504991

2、SQL160 国庆期间每类视频点赞量和转发量