题目分析

  • 限定：条件最近一个月发布
  • 维度：视频
  • 指标：热度最高的 top3
  • 统计口径：热度 = （a * 视频完播率 + b * 点赞数 + c * 评论数 + d * 转发数） * 新鲜度
    • 视频完播率 = 播放时长 / 视频时长 【用户维度】
    • 新鲜度 = 1 / (最近无播放天数 + 1) 【视频维度】
    • 最近无播放天数 = 整体最近播放【用户维度】 - 视频最近一次播放的日期 【视频维度】
  • 返回：video_id, hot_index

题解思路

  • 一张用户播放记录，一张视频信息表，显然统计的内容是要依赖用户播放记录信息，因此比较适合的方式是将tb_user_video_log作为主表左连接tb_video_info。
  • 由表结构和统计内容可知，最细维度为用户，所以需要先用户的完播情况、是否点赞等指标。
  • 在计算用户的指标同时可以使用OLAP函数（窗口函数）计算整体最近播放日期，视频最近播放日期。
  • 用户维度指标计算完后汇总到视频维度，按公式计算即可。
with tmp as (
    select
        t1.video_id,
        timestampdiff(second, t2.start_time, t2.end_time) as dt_diff,
        t1.duration,
        t2.if_like,
        t2.if_retweet,
        if(t2.comment_id is null, 0, 1) as if_comment,
        date(max(t2.end_time) over()) as recent_dt,
        date(max(t2.end_time) over(partition by t1.video_id)) as recent_v_dt,
        date(t1.release_time) as release_dt
    from tb_video_info as t1
        right join tb_user_video_log as t2 on t1.video_id = t2.video_id
)

select
    video_id,
    round((100 * cpr + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt) / fresh, 0) as hot_index
from (
    select
        video_id,
        avg(if(dt_diff >= duration, 1, 0)) as cpr,
        sum(if_like) as like_cnt,
        sum(if_retweet) as retweet_cnt,
        sum(if_comment) as comment_cnt,
        datediff(max(recent_dt), max(recent_v_dt)) + 1 as fresh
    from tmp
    where datediff(recent_dt, release_dt) between 0 and 29
    group by video_id
) as t
order by hot_index desc
limit 3;