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

1问题：

统计2021年国庆头3天每类视频每天的近一周总点赞量和一周内最大单天转发量， 结果按视频类别降序、日期升序排序。 假设数据库中数据足够多，至少每个类别下国庆头3天及之前一周的每天都有播放记录。

2.思路：

（1）每天的总点赞量和总转发量；

粒度：标签+每天日期

总点赞量：sum(if(if_like=1,1,0))

最大单天转发量：sum(if_retweet)

时间范围：where year(xx)= 2021

（2）近一周的总点赞量和每天最大点赞量

粒度：标签+每天日期

近一周的总点赞量：sum(like1) over(partition by tag order by date1 ROWS between 6 preceding and current row)

近一周的每天最大转发量：max(retweet1) over(partition by tag order by date1 ROWS between 6 preceding and current row) retweet_cnt

（3）国庆前三天的每类视频

国庆前三天：between '2021-10-01' and '2021-10-03'

每类视频：group by

结果按视频类别降序、日期升序排序:order tag desc,dt

3.解题代码

select tag,dt,like_cnt,retweet_cnt
FROM
(select tag,date1 dt,
    sum(like1) over(partition by tag order by date1  ROWS between 6 preceding and current row) like_cnt,
    max(retweet1) over(partition by tag order by date1  ROWS between 6 preceding and current row) retweet_cnt
from(
    SELECT b.tag,
        date_format(a.start_time,'%Y-%m-%d') date1,
        sum(if(if_like=1,1,0)) like1,
        sum(a.if_retweet) retweet1
    FROM tb_user_video_log a
        left join tb_video_info b
        on a.video_id=b.video_id
    where year(a.start_time)=2021
    GROUP BY b.tag,date1) lchid
group BY tag,date1) child
where dt between '2021-10-01' and '2021-10-03'
group BY tag,dt
order by tag desc,dt