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

select video_id,
round((100*final_video+5*like_video+3*comment_video+2*retweet_video)*(1/(no_video+1)),0) hot_index
from
(select video_id,
#视频完播率是指完成播放次数占总播放次数的比例。简单起见，结束观看时间与开始播放时间的差>=视频时长时，视为完成播放。
sum(case when timestampdiff(second,start_time,end_time) >= duration then 1 else 0 end)/count(uid) final_video,
sum(if_like) like_video,
#评论数使用count函数计数，不能使用sum是因为字段值有null值，累计求和后会返回null，无法计算
count(comment_id) comment_video,
sum(if_retweet) retweet_video,
#最近无播放天数：该视频最后一次播放时间距离整体统计最近时间的时间差
datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) no_video
from tb_user_video_log ul
left join tb_video_info ti
using(video_id)
#近一个月内发布视频中，近按照视频观看end_time最大值计算
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29
group by video_id) t
order by hot_index desc
#top3视频
limit 3
;