视频热度计算

• 简要思路：
  1. 合并表，使用inner join即可。目标是提取需要的字段，并加工出部分需求字段，如：if_finish表示是否完播，if_comment表示是否评论，recent_play表示最近一天（用于筛选近一个月的数据）
  2. 筛选出近一个月，并按video_id分组汇总，计算子指标。如：播放量、点赞量、转发量、评论数、近期未观看天数
  3. 计算最终指标hot_index并按其大小倒序选出top3
• 难点：
  • 对最近一个月的理解，这里修饰的是发布，而非热度的，因此筛选出最近一个月的数据即可，同时这里的最近日期来自于所有数据的max(end_time)
  • 对最近无播放天数的理解，这里指的是上述的最近日期与每个视频的最近日期的相差天数

select t2.video_id,
       round((100 * t2.play_cnt + 5 * t2.like_cnt + 3 * t2.comment_cnt + 2 * t2.retweet_cnt) / (fresh + 1),
             0) as hot_index
from (
         select t.video_id,
                sum(t.if_finish) / count(t.uid)               as play_cnt,
                sum(t.if_like)                                as like_cnt,
                sum(t.if_retweet)                             as retweet_cnt,
                sum(t.if_comment)                             as comment_cnt,
                datediff(max(t.recent_play), max(t.end_time)) as fresh
         from (
                  select tuvl.uid,
                         tuvl.video_id,
                         tuvl.end_time,
                         if(timestampdiff(second, tuvl.start_time, tuvl.end_time) >= tvi.duration, 1, 0) as if_finish,
                         if(tuvl.comment_id, 1, 0)                                                       as if_comment,
                         tuvl.if_like,
                         tuvl.if_retweet,
                         tvi.duration,
                         tvi.release_time,
                         max(tuvl.end_time) over ()                                                      as recent_play
                  from tb_user_video_log as tuvl
                           inner join tb_video_info as tvi on tuvl.video_id = tvi.video_id
              ) as t
         where t.release_time between date_sub(t.recent_play, interval 29 day) and t.recent_play
         group by t.video_id
     ) as t2
order by hot_index desc
limit 3;