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

# 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；

# 新鲜度=1/(最近无播放天数+1)；

#关于最近无播放天数（指的不是这一个月有多少天无播放，而是距离当前有多少天没有播放量，所以是每个视频最后一次播放日期，到当前也就是整个所有视频最后播放日期，之间的天数差）
# 看到评论区不错的解释是：SELECT MAX(end_time) FROM tb_user_video_log) 是整个表内最大的日期，相当于当前日期，而MAX（date(end_time)）是聚合后每个视频的最后播放日期。当播放次数为0时，最近无播放天数=当前日期-发布日期； 当播放次数不为0时，最近无播放天数=当前日期-最近一次播放日期

# 关于完播率
#avg(if(timestampdiff(second, start_time, end_time)>=duration,1,0)) 完播的记为1，求平均

#用join，如果是left join没有播放记录的也会出现,需要where end_time is not null

#完整代码
select
video_id, 
round((100*finished_rate + 
5*like_cnt + 
3*comment_cnt + 
2*retweet_cnt)/(no_play_day+1),0) as hot_index
from
(select vi.video_id, 
avg(if(timestampdiff(second, start_time, end_time)>=duration,1,0)) finished_rate,
sum(if_like) as like_cnt, 
sum(if_retweet) as retweet_cnt,
count(comment_id) as comment_cnt,
datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) as no_play_day
from tb_video_info vi
join tb_user_video_log uv using(video_id) 
where datediff(date((select max(end_time) from tb_user_video_log)),date(release_time))<=29
group by vi.video_id) a
order by 2 desc
limit 3