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

select * from (
select tag,dt1 dt,
sum(daily_like_cnt) over(partition by tag order by date(dt1) range between interval 6 day preceding and current row) sum_like_cnt_7d,
max(daily_ret_cnt) over(partition by tag order by date(dt1) range between interval 6 day preceding and current row) max_retweet_cnt_7d
from(
select tag,date_format(start_time,'%Y-%m-%d') dt1,sum(if_like) daily_like_cnt,sum(if_retweet) daily_ret_cnt
from tb_user_video_log l
join tb_video_info i
on l.video_id=i.video_id
group by tag,dt1) t
group by tag,dt1) t1
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt

首先对视频信息表和用户行为表进行连接，按时间和类别分组，计算出每个类别每天的点赞数和转发数。再从这张表中，利用窗口函数计算每天的七日内点赞数之和以及七日内单日最大转发数。最后选择国庆节头三天的数据输出。