Hive 刷题——视频热度问题

需求描述

现有用户视频播放记录表（user_video_log）如下。

视频信息表(video_info) 如下：

找出近一个月发布的视频中热度最高的top3视频。

注：热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；

新鲜度=1/(最近无播放天数+1)；

当前配置的参数a,b,c,d分别为100、5、3、2。

最近播放日期以 end_time-结束观看时间 为准，假设为T，则最近一个月按 [T-29, T] 闭区间统计。

当天日期使用视频中最大的end_time

结果中热度保留为整数，并按热度降序排序。

期望结果如下：

解题思路

1、先计算热度因子

select video_id,
       sum((cast(if_like as int)))       if_like,
       sum((cast(if_retweet as int)))    if_retweet,
       sum(if(comment_id is null, 0, 1)) if_comment
from user_video_log
group by video_id

2、每个视频看完用户

select vi.video_id,
       sum(if(unix_timestamp(end_time) - unix_timestamp(start_time) = duration, 1, 0)) / sum(1) uv_rate
from video_info vi
         join user_video_log uv on vi.video_id = uv.video_id
group by vi.video_id

参考SQL

select video_id,heat
from (select video_id, hot heat, rank() over (partition by video_id order by hot desc ) rn
      from (select t.video_id,
                   (ceil(100 * uv_rate) + 5 * if_like + 3 * if_comment + 2 * if_retweet) / (new_date + 1) hot
            from (select u1.video_id, datediff(this_date, date_format(max(end_time), 'yyyy-MM-dd')) new_date
                  from user_video_log uv
                           join
                       (select video_id, date_format(max(end_time), 'yyyy-MM-dd') this_date
                        from user_video_log
                        group by video_id) u1
                       on uv.video_id = u1.video_id
                  group by u1.video_id, this_date) t
                     join
                 (select video_id,
                         sum((cast(if_like as int)))       if_like,
                         sum((cast(if_retweet as int)))    if_retweet,
                         sum(if(comment_id is null, 0, 1)) if_comment
                  from user_video_log
                  group by video_id) t1 on t.video_id = t1.video_id
                     join
                 (select vi.video_id,
                         sum(if(unix_timestamp(end_time) - unix_timestamp(start_time) = duration, 1, 0)) /
                         sum(1) uv_rate
                  from video_info vi
                           join user_video_log uv on vi.video_id = uv.video_id
                  group by vi.video_id) t2 on t.video_id = t2.video_id) t) i
where rn <= 3;