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

with base_time as (
    #取最近时间作为基准时间
    select
        date(max(end_time)) as T_date
    from
        tb_user_video_log
    limit 1
),
wanbo as (
    select
        uid,
        U.video_id,
        #使用timestampdiff函数计算播放时长并计算完播率
        if(timestampdiff(second, start_time, end_time) >= duration, 1, 0) as if_wanbo
    from
        base_time, tb_user_video_log U left join tb_video_info V
    on
        U.video_id = V.video_id
    where
        #筛选时间，确保发布时间和视频播放结束时间都在最近一个月内
        date(end_time) BETWEEN DATE_SUB(base_time.T_date, INTERVAL 29 DAY) AND base_time.T_date
        AND date(release_time) BETWEEN DATE_SUB(base_time.T_date, INTERVAL 29 DAY) AND base_time.T_date
),
fresh as (
    select
        video_id,
        sum(if_like) as likes,
        count(comment_id) as comments,
        sum(if_retweet) as retweets,
        #这个max(base_time.T_date)很关键，如果不使用max则SQL认不出T_date只有一条记录，会导致这条无法运行
        1/(datediff(max(base_time.T_date), max(end_time)) + 1) as fresh
    from
        tb_user_video_log, base_time
    group by
        video_id
)
select
    F.video_id,
    #计算热度
    round((100*sum(if_wanbo)/count(*)+5*likes+3*comments+2*retweets)*F.fresh, 0) as hot_index  
from
    #使用inner join避免出现一个表有记录而另一个表没有导致产生None结果的情况
    fresh as F inner join wanbo as W
on
    F.video_id = W.video_id
group by
    F.video_id
order by
    hot_index desc
limit 3

只要善用CTE将难计算的base_time和完播率以及新鲜度分别计算，最后再合表计算，就可以解决了