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

QL161 近一个月发布的视频中热度最高的top3视频 

select 
#video_id
t_wbl.video_id
#视频完播率
-- ,t_wbl.ratio as wbl
#视频点赞/转发/关注/评论数
-- ,IFNULL(sum(if_like),0) as if_like  
-- ,IFNULL(sum(if_retweet),0) as if_retweet
-- ,IFNULL(sum(if_follow),0) as if_follow
-- ,count(comment_id) as if_comment
#最近无播放天数
-- ,IFNULL(DATEDIFF((select DATE_FORMAT((SELECT MAX(end_time) FROM `tb_user_video_log`),'%Y-%m-%d')),max(tu.END_time)),30) as wbfts
#热度
, ROUND((100*t_wbl.ratio + 5*IFNULL(sum(if_like),0) + 3*count(comment_id) + 2*IFNULL(sum(if_retweet),0) ) / 
(IFNULL(DATEDIFF((select DATE_FORMAT((SELECT MAX(end_time) FROM `tb_user_video_log`),'%Y-%m-%d')),max(tu.END_time)),30) + 1),0)
as hot_index
from 
(select 
IFNULL(round( fhbsf / zbsf , 1) ,0) as `ratio`,zbs.video_id from
(select count(1) as fhbsf,tv.video_id  from `tb_user_video_log` tu  join `tb_video_info` tv on tu.video_id = tv.video_id
where TIMESTAMPDIFF(SECOND, start_time,end_time) >= tv.duration 
and tv.release_time > date_add((select DATE_FORMAT((SELECT MAX(end_time) FROM `tb_user_video_log`),'%Y-%m-%d')),interval -29 day) 
group by video_id ) fhbs
 right JOIN
(select count(tu.id) as zbsf ,tv.video_id  from `tb_user_video_log` tu right join `tb_video_info` tv on tu.video_id = tv.video_id
where  tv.release_time > date_add((select DATE_FORMAT((SELECT MAX(end_time) FROM `tb_user_video_log`),'%Y-%m-%d')),interval -29 day)
group by video_id ) zbs 
on fhbs.video_id = zbs.video_id) t_wbl
left join  `tb_user_video_log` tu  
on t_wbl.video_id = tu.video_id 
group by tu.video_id,t_wbl.video_id having hot_index<>0
order by  hot_index desc limit 0,3

最后的 having hot_index<>0 理论上应该不用加,不过不加过不了

补充:最后的 having hot_index<>0 是为了 过滤没有播放记录的视频