# 1. 热度表 # 筛选出最近一个月发布的 # 2. 新鲜度表 # 最近无播放日期:现在的日期-该视频最后播放的日期。 # 3. 左联结上面两个表进行计算,选择前3 select video_id, round((complete_rate*100+like_cnt*5+comment_cnt*3+retweet_cnt*2)*(1/(no_day+1))) as hot_index from( -- 1. 热度表 -- 每类视频的视频完播率,点赞数,评论数,转发数 select video_id, sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(start_time) as complete_rate, sum(if_like) as like_cnt, count(comment_id) as comment_cnt, sum(if_retweet) as retweet_cnt from tb_user_video_log left join tb_video_info using(video_id) where datediff((select max(end_time) from tb_user_video_log),release_time)<=29 # 筛选最近一个月发布的视频,当前日期-发布日期<=29天 group by video_id ) h_t left join ( -- 2. 新鲜度表 -- 每类视频最近无播放天数 select video_id, datediff((select max(end_time) from tb_user_video_log),last_date) as no_day -- 最近无播放的天数 from( select video_id, max(end_time) as last_date from tb_user_video_log group by video_id ) f_t ) t using(video_id) order by hot_index desc limit 3