题目信息

找出近一个月发布的视频中热度最高的top3视频（一定要注意是近一个月发布的视频！！！）

问题拆解

• 热度 = (100*视频完播率+5*点赞数+3*评论数+2*转发数) * 新鲜度
• 视频完播率 = 完成播放次数 / 被播放次数
• 新鲜度 = 1 / (最近无播放天数+1)；
• 最近无播放天数 = 所有视频最近播放日期 - 该视频最近播放日期

1. 最近无播放天数求解

所有视频最近播放日期 select max(date_format(end_time,'%Y-%m-%d')) from tb_user_video_log 该视频最近播放日期 max(date_format(end_time,'%Y-%m-%d')))

利用 datediff() 求解差值即可

通过以下代码得到表 tb_noplayd 其中包括 video_id,noplay_d

		select 
			video_id,
		   datediff(
			 (select max(date_format(end_time,'%Y-%m-%d')) from tb_user_video_log),
			max(date_format(end_time,'%Y-%m-%d'))) noplay_d
		from tb_user_video_log
		group by video_id

2. 将tb_user_video_log, tb_video_info, tb_noplayd 根据video_id连接起来，并对video_id进行分组 group by

分组之前一定要记得先筛选出近一个月发布的视频

where date_format(release_time,'%Y-%m-%d') >= ( select date_sub(max(date_format(end_time,'%Y-%m-%d')),interval 29 day) from tb_user_video_log )

分组后聚合可得到

• 完播率 sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(*) play_time

if(timestampdiff(second,start_time,end_time)>=duration,1,0)) 利用 timestampdiff() 求出每个视频的播放时长，如果播放时长大于或等于该视频的长度，那么就说明该视频播放完成，值为1，为0就是未完成，不计数。

sum() 对完成的累计求和即为每个视频完成播放的总次数

count()* 即为总播放次数

• sum(if_like) like_cnt

• 评论数 count(comment_id) comment_cnt

• 转发数 sum(if_retweet) retweet_cnt

3. 根据第2步得到的play_time,like_cnt,comment_cnt,retweet_cnt,noplay_d进行计算热度即可，然后以hot_index降序排，用limit选出前3个即可

代码中还有许多需要完善的地方，各位大佬可以指点哦，一起学习。

代码

select tb.video_id,
	round((100*play_time+5*like_cnt+3*comment_cnt+2*retweet_cnt)*(1/(noplay_d+1)) ) hot_index
from (
	select 
		uvl.video_id,
		sum(if(timestampdiff(second,start_time,end_time)>=duration,1,0))/count(*) play_time,
		sum(if_like) like_cnt,
		count(comment_id) comment_cnt,
		sum(if_retweet) retweet_cnt,
		noplay_d
	from tb_user_video_log uvl
	left join tb_video_info vi
	on uvl.video_id=vi.video_id
	left join (
			select 
				video_id,
			   datediff(
				 (select max(date_format(end_time,'%Y-%m-%d')) from tb_user_video_log),
				max(date_format(end_time,'%Y-%m-%d'))) noplay_d
			from tb_user_video_log
			group by video_id
	) tb_noplayd
	on uvl.video_id=tb_noplayd.video_id
     where date_format(release_time,'%Y-%m-%d') >= (select date_sub(max(date_format(end_time,'%Y-%m-%d')),interval 29 day) from tb_user_video_log) -- 近一个月发布的视频
	group by uvl.video_id
   
) tb
order by hot_index desc
limit 3