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

# 粒度-视频

# 筛选符合条件的视频,再进行计算

with tmp1 as

(

    select

    max(date(end_time)) max_date

    from

    tb_user_video_log

)

,

tmp2 as

(

    select

    tuvl.video_id

    ,date(end_time) end_date

    ,timestampdiff(second, start_time, end_time) time_diff

    ,if_like

    ,if_retweet

    ,duration

    ,(case when comment_id is not null then 1

        else 0 end) if_comment

    from

    tb_user_video_log tuvl

    join

    tb_video_info tvi

    on tuvl.video_id = tvi.video_id

    where date(release_time) <= (select * from tmp1)

    and date(release_time) >= date_sub((select * from tmp1), interval 29 day)

)

# 筛选出符合条件的视频：对应播放记录,视频时长

# 使用发布时间筛选，可把播放记录的时间也同时筛选

select

video_id

,round((100*play_rate + 5*like_cnt + 3*comment_cnt + 2*retweet_cnt) / (datediff(now_date, re_date) + 1), 0) hot_index

from

(

    select

    video_id

    ,max(end_date) re_date

    ,avg((case when time_diff >= duration then 1

        else 0 end)) play_rate

    ,sum(if_like) like_cnt

    ,sum(if_retweet) retweet_cnt

    ,sum(if_comment) comment_cnt

    ,(select * from tmp1) now_date

    from tmp2

    group by

    video_id

) A # 统计出每个视频计算热度需要的字段

order by hot_index desc

limit 3;