题解 | #近一个月发布的视频中热度最高的top3视频#

# 最近无播放天数=最近播放日期-视频的最近播放日期
# 先近一个月获取每个视频的视频完播率、点赞数、评论数、转发数、视频最近播放日期、最大播放日期等信息作为临时表A
WITH A AS(
select 
a.video_id 
,avg(if(timestampdiff(second,start_time	,end_time) >= duration ,1,0)) as all_rate  -- 完播率
,sum(if_like) as like_cnt -- 点赞数
,count(comment_id) as  comment_cnt -- 评论数
,sum(if_retweet) as retweet_cnt -- 转发数
,datediff((select max(end_time) from tb_user_video_log),max(end_time)) as f  -- 最近无播放天数
from tb_user_video_log a 
left join tb_video_info b 
on a.video_id = b.video_id 
where datediff((select max(end_time) from tb_user_video_log),release_time) <= 29
group by a.video_id) 

-- 计算各类视频的热度
select 
video_id 
,round((100*all_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)*(1/(f+1)),0) as hot_index -- 热度 
from A  
order by hot_index desc 
limit 3