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

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

http://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11

题目：

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

思路：

1、统计2021年国庆头3天：where between and（左闭右闭）

2、每类视频每天：group by tag,date(start_time)

3、近一周总点赞量：滑动窗口（rows between 6 preceding and current row）可以省略and current row：近一周总点赞量=sum(每天点赞总量)=sum(sum(if_like))

4、一周内最大单天转发量：=max(每天转发总量)=sum(sum(if_retweet))

错误写法：

select tag, date(start_time) dt,
    sum(sum(if_like)) over(partition by tag
                           order by date(start_time)
                           rows between 6 preceding and current row) sum_like_cnt_7d,
    max(sum(if_retweet)) over(partition by tag
                              order by date(start_time)
                              rows between 6 preceding and current row) max_retweet_cnt_7d
from tb_user_video_log left join tb_video_info using(video_id)
where date(start_time) between '2021-10-01' and '2021-10-03'
group by tag,date(start_time)
order by tag desc, dt

alt

错误原因：

SELECT * FROM a的原因把where放到a外面才是10.1-10.3的前一周的数据相加。即算出9.24与其前6天点赞数相加和7天内最大点赞数、9.25与其前6天点赞数相加和7天内最大点赞数、9.26与其前6天点赞数相加和7天内最大点赞数…10.1与其前6天点赞数相加和7天内最大点赞数、10.2与其前6天点赞数相加和7天内最大点赞数、10.3与其前6天点赞数相加和7天内最大点赞数。

如果没有a，那么ROWS 6 PRECEDING的范围将仅限于10.1-10.3，而非10.1-10.3的前一周，结果将会变成：

10.1的点赞数相加和单天最大点赞数、10.2与10.1点赞数相加和两天最大点赞数、10.3与10.1、10.2点赞数相加和单天最大点赞数

正确写法：

select * from(
select tag, date(start_time) dt,
    sum(sum(if_like)) over(partition by tag
                           order by date(start_time)
                           rows between 6 preceding and current row) sum_like_cnt_7d,
    max(sum(if_retweet)) over(partition by tag
                              order by date(start_time)
                              rows between 6 preceding and current row) max_retweet_cnt_7d
from tb_user_video_log left join tb_video_info using(video_id)
group by tag,date(start_time)
) a
where dt between '2021-10-01' and '2021-10-03'
order by tag desc, dt