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

思路：先生成c表格包含以下字段： video_id、视频完播率、点赞数、评论数、转发数、新鲜度 再从c表格中提取计算热度

select video_id,
    round((100*视频完播率 + 5*点赞数 + 3*评论数 + 2*转发数)*新鲜度,0) as 热度 
    from
		(select a.video_id,
    		sum(case when Timestampdiff(second,a.start_time,a.end_time) >= b.duration then 1 else 0 end)/count(a.video_id) as 视频完播率,
    		sum(if_like) as 点赞数,
    		sum(case when a.comment_id <> 'NULL' then 1 else 0 end) as 评论数,
   			sum(if_retweet) as 转发数,
    		max(1/(datediff((select max(end_time) from tb_user_video_log),a.end_time)+1)) as 新鲜度
    		from 
    		tb_user_video_log a
    		join
    		tb_video_info b
    		on a.video_id = b.video_id
    	where datediff((select max(end_time) from tb_user_video_log),b.release_time) <= 29
    	group by video_id) as c
    group by video_id
    order by 热度 desc
    limit 3;