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

# 最近无播放天数=最近播放日期-视频的最近播放日期
# 先获取每个视频的视频完播率、点赞数、评论数、转发数、视频最近播放日期、整体最近播放日期等基础信息
select 
video_id
,round((100*finish_rate+5*like_cnt+3*comment_cnt+2*retweet_cnt)*(1/(days_cnt+1)),0) hot_index 
from (
select 
a.video_id 
,avg(if(timestampdiff(second,start_time,end_time) >= duration ,1,0)) as finish_rate 
,sum(if_like) as like_cnt 
,count(comment_id) as comment_cnt 
,sum(if_retweet) as retweet_cnt 
# 需要注意date本身就有括号，内部使用子查询时，需要用括号将子查询包含起来
,datediff(date((select max(end_time) from tb_user_video_log)),date(max(end_time))) as days_cnt 
from tb_user_video_log a 
inner join tb_video_info b 
on a.video_id = b.video_id 
# 需要注意发布时间和结束观看时间的区别。很可能有的视频0901日发布，但是0906日才播放
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time)) <= 29 
group by a.video_id  ) a 
order by hot_index desc 
limit 3