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

明确题意：

统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量

问题分解：

关联用户-视频互动表和短视频信息表：left join tb_user_video_log tuvl on tvi.video_id = tuvl.video_id

计算单天点赞量：sum(if_like)

计算单天转发量：sum(if_retweet)

按视频tag和日期分组：group by tag,dt

计算每天近一周总点赞量：sum(like_cnt) over w

计算每天近一周最大转发量：max(retweet_cnt) over w

筛选国庆头三天的记录：where dt between '2021-10-01' and '2021-10-03'

细节问题：

按视频类别降序、日期升序排序：order by tag desc,dt asc

select *
from(
select tag,
    dt,
    sum(like_cnt) over(partition by tag order by dt desc rows between current row and 6 following) sum_like_cnt_7d,
    max(retweet_cnt) over(partition by tag order by dt desc rows between current row and 6 following) max_retweet_cnt_7d
  # 开窗必须在筛选国庆3天记录前开，否则窗口只有三天记录
from(
select tag,
    date(start_time) dt,
    sum(if_like) like_cnt,
    sum(if_retweet) retweet_cnt
from tb_video_info tvi
left join tb_user_video_log tuvl on tvi.video_id = tuvl.video_id 
group by tag,dt
    ) t1
) t2
where dt between '2021-10-01' and '2021-10-03'
order by tag desc,dt asc;