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

with aa as
(
    select 
        a.*,b.duration,b.release_time
    from
        tb_user_video_log a 
    left join
        tb_video_info b
    on a.video_id=b.video_id
)

select
    video_id,
    round((100*wbl+5*dzs+3*pls+2*zfs)/(datediff((select max(end_time) from tb_user_video_log),zjsj)+1),0) as hot_index 
from 
(
    select
    video_id,
    sum(case when timestampdiff(second,start_time,end_time)-duration>=0 then 1 else 0 end)/count(1) as wbl,
    sum(if_like) as dzs,
    count(comment_id) as pls,
    sum(if_retweet) as zfs,
    max(end_time) zjsj
from    
    aa
where
    date(release_time)>date_sub((select max(end_time) from tb_user_video_log),interval 30 day)
group by 
    video_id) b
order by 
    hot_index desc
limit 3