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

select
video_id
,CONVERT(int,(avg(if_comp*1.0) * 100 + 5 * sum(if_like*1.0) + 3 * sum(is_comment*1.0) + 2 * sum(if_retweet*1.0))
     * (1.0 / (1 + min(diff_time))),0) hot_index
from
(
SELECT a.video_id
,CASE WHEN DATEDIFF(second,a.start_time,a.end_time) >= b.duration THEN 1 ELSE 0 END AS if_comp
,if_like
,CASE WHEN a.comment_id is null THEN 0 ELSE 1 END as is_comment
,if_retweet
,abs(datediff(day,(select max(end_time) from tb_user_video_log),end_time)) diff_time
from tb_user_video_log a
left join tb_video_info b
on a.video_id = b.video_id
where ABS(DATEDIFF(DAY,(select max(end_time) from tb_user_video_log),release_time)) < 30
) t
group by t.video_id
order by CONVERT(int,(avg(if_comp*1.0) * 100 + 5 * sum(if_like*1.0) + 3 * sum(is_comment*1.0) + 2 * sum(if_retweet*1.0))
     * (1.0 / (1 + min(diff_time))),0) desc 

select 
video_id
,round((avg(if_comp) * 100 + 5 * sum(if_like) + 3 * sum(is_comment) + 2 * sum(if_retweet))
     * (1 / (1 + min(diff_time))))  hot_index
from (
    select 
    log.video_id video_id
    ,if(timestampdiff(second,start_time,end_time) >= duration, 1, 0) as if_comp
    ,if_like
    ,if(comment_id is null, 0 ,1) as is_comment
    ,if_retweet
    ,abs(datediff(end_time, (select max(end_time) from tb_user_video_log))) diff_time
    from tb_user_video_log log
    left join tb_video_info info
    on log.video_id = info.video_id
    where abs(datediff(release_time, (select max(end_time) from tb_user_video_log))) < 30
) a
group by 1
order by 2 desc 
limit 3;