题解 | #国庆期间每类视频点赞量和转发量#
国庆期间每类视频点赞量和转发量
https://www.nowcoder.com/practice/f90ce4ee521f400db741486209914a11
select *
from
(
select
tag,
date_format(start_time,'%Y-%m-%d') dt,
sum(count(if(if_like=1,log.video_id,null))) over(partition by tag order by date_format(start_time,'%Y-%m-%d') rows between 6 preceding and current row) sum_like_cnt_7d,
max(count(if(if_retweet=1,log.video_id,null))) over(partition by tag order by date_format(start_time,'%Y-%m-%d') rows between 6 preceding and current row) max_retweet_cnt_7d
from tb_user_video_log log
left join tb_video_info info on log.video_id=info.video_id
where year(start_time)=2021
group by tag,date_format(start_time,'%Y-%m-%d')
) f where dt in ('2021-10-01','2021-10-02','2021-10-03')
order by tag desc,dt;
1、首先，求出每种类型每天的点赞数和转发数
2、其次，使用窗口函数的动态窗口 6 preceding and current 进行窗口的限定，近7天的总数用sum() over()
3、最后，筛选 出国庆头3天每种类型每天的的一周内的总点赞量和最大转发量。
投递理想汽车等公司10个岗位 >
查看11道真题和解析