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

select
video_id
,round(((100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度),0) as hot_index
from(
    select
    a.video_id
    ,avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0)) as 视频完播率
    ,sum(if_like) as 点赞数
    ,sum(if_retweet) as 转发数
    ,count(comment_id) as 评论数
    ,1/(datediff((select date(max(end_time)) from tb_user_video_log),date(max(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 date(max(end_time)) from tb_user_video_log),date(release_time))<=29
    group by 1) c
order by hot_index desc
limit 3;

#视频完播率
avg(if(timestampdiff(second,start_time,end_time)>=duration,1,0))
#点赞数
sum(if_like)
#评论数
count(comment_id)
#转发数
sum(if_retweet)
#时间区间
datediff((select date(max(end_time)) from tb_user_video_log),date(release_time))<=29
#新鲜度
1/(datediff((select date(max(end_time)) from tb_user_video_log)
,date(max(end_time)))+1)
#所有视频中最长的结束时间-当前视频的最大结束时间=未播放天数
#热度
round(((100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度),0)