题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
啊又是一道废掉脑细胞的题，总算做出来了
- 首先先筛选出时间在9.25到10.3的行，然后group by tag和date（start_time）来计算每天的总点赞量和总转发量，再按tag降序，日期升序排好表格，形成表格t1
- 重点来了，在表格t1的基础上利用**窗口函数（rows n preceding）**来计算近七天的总点赞数和最大转发量，形成表格t2
- 最后在表格t2的基础上筛选出时间在10.1到10.3号之间的，按照要求排序即可
select *
from
(select
tag,
dt,
sum(if_like_sum) over (partition by tag order by dt rows 6 preceding) as sum_like_cnt_7d,
max(if_retweet_sum) over (partition by tag order by dt rows 6 preceding) as max_retweet_cnt_7d
from(
select
tag,
date(start_time) as dt,
sum(if_like) as if_like_sum,
sum(if_retweet) as if_retweet_sum
from tb_video_info tvi
join tb_user_video_log tuvl
on tvi.video_id=tuvl.video_id
where date(start_time) >='2021-09-25' and date(start_time) <='2021-10-03'
group by 1,2
order by 1 desc,2)t1)t2
where dt>='2021-10-01' and dt<='2021-10-03'
order by 1 desc ,2
