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

with wanbo_rate as (
    select u.video_id,
        sum(if(timestampdiff(second, u.start_time, u.end_time) >= v.duration, 1, 0)) / count(u.start_time) as wanbo_rate
    from tb_user_video_log u
    join tb_video_info v
    on u.video_id = v.video_id
    group by u.video_id
),
final_day as (
    select date(max(end_time)) as final_day
    from tb_user_video_log
),
xinxian_level as (
    select video_id,
        1 / (datediff(max(f.final_day), max(end_time)) + 1) as xinxian_level
    from tb_user_video_log
    cross join final_day f
    group by video_id
)

select u.video_id,
    round((100 * w.wanbo_rate + 5 * sum(u.if_like) + 3 * count(u.comment_id) + 2 * sum(u.if_retweet)) * x.xinxian_level) hot_index
from tb_user_video_log u
join tb_video_info v on u.video_id = v.video_id
join wanbo_rate w on u.video_id = w.video_id
join xinxian_level x on u.video_id = x.video_id
cross join final_day f
where datediff(f.final_day, v.release_time) < 30
group by u.video_id
order by hot_index desc
limit 3

喜欢拆开来写，缝缝补补，休休改改又一年，不过怎么才能提高速度呢QAQ