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

with t1 as (
    select
        t1.video_id,
        avg(if(timestampdiff(second, start_time, end_time)>=duration, 1, 0)) as finish_rate,
        sum(if_like) as like_index,
        count(comment_id) as comment_index,
        sum(if_retweet) as retweet_index,
        datediff(date((select max(end_time) from tb_user_video_log)),max(date(end_time))) as fresh_index
    from 
        tb_user_video_log t1
        join tb_video_info t2 on t1.video_id = t2.video_id
    where release_time between date_sub((select max(end_time) from tb_user_video_log), interval 29 day) and (select max(end_time) from tb_user_video_log)
    group by t1.video_id
)
select
    video_id,
    round((100*finish_rate + 5*like_index + 3*comment_index + 2*retweet_index) * (1/(fresh_index+1)), 0) as hot_index
from t1
order by hot_index desc
limit 3

注意：

在 SQL 中，如果要计算两个时间之间的天数差，推荐使用 DATEDIFF 而不是 TIMESTAMPDIFF(day, ...)，因为 DATEDIFF 只比较日期部分，能避免因时间精度（小时、分钟、秒）带来的误差，更适合用于分析如“新鲜度指数”“最近观看天数”等业务场景；而 TIMESTAMPDIFF 更适合用于计算精确的时间差（如秒、分钟、小时），用于场景如“观看时长”“响应延迟”等。简单来说，按天用 DATEDIFF 更稳定，按秒用 TIMESTAMPDIFF 更精准。