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

--热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
--新鲜度=1/(最近无播放天数+1)；
--当前配置的参数a,b,c,d分别为100、5、3、2。
--最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
--结果中热度保留为整数，并按热度降序排序。
-- 需求：找出近一个月发布的视频中热度最高的top3视频
-- 分析：
①近一个月=> datediff((select max(date(end_time)) from tb_user_video_log),date(release_time))<30
②热度最高：按照hot_index倒叙排列=> order by hot_index desc
	本题难度：求热度
	根据题目给出的数据口径：热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度
		视频完播率：视频完整播放/视频播放的总次数
			视频完整播放=>结束播放时间-开始播放时间大于等于视频播放所需要的时间
				if(timestampdiff(second,start_time,end_time)>=duration,1,0) t
					视频完播率=>avg(t) y1
		点赞数:视频的点赞次数=>sum(if_like) y2
		评论数:视频的评价次数=>comment_sum y3
		转发数:视频的转发次数=>sum(if_retweet) y4
		新鲜度:新鲜度=1/(最近无播放天数+1)
			最近无播放天数=>tb_user_video_log最近播放的日期 - 某个视频最近播放日期
				tb_user_video_log最近播放的日期=>select max(date(end_time)) from tb_user_video_log t1
				某个视频最近播放日期=>max(date(end_time)) t2
				新鲜度=>1/(t1-t2+1) y5
	热度=>(100*y1+5*y2+3*y3+2*y4)*y5
③top3=>limit 3
select video_id,
       round((100*player+5*like_sum+3*comment_sum+2*retweet_sum)/flash) hot_index
from(
    select t2.video_id,
       avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) player,
       sum(if_like) like_sum,
       count(comment_id) comment_sum,
       sum(if_retweet) retweet_sum,
       datediff((select max(date(end_time)) from tb_user_video_log),max(date(end_time))) + 1 flash,
       max(date(end_time)) max_date
    from tb_user_video_log t1
    join tb_video_info t2
    on t1.video_id=t2.video_id 
    where datediff((select max(date(end_time)) from tb_user_video_log),date(release_time))<30
    group by t2.video_id
) t
order by hot_index desc
limit 3;