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

#用户-视频互动表 uid+video_id+start_time
#短视频信息表 video_id

#热度
#top3 desc limit 3
#近一个月发布 max(end_time) release_time

#热度= (a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度
#视频完播率 = 完整播放视频数/视频播放总数= （结束时间-开始时间）vs 视频时长
#点赞数 = sum(if_like)
#评论数 = sum(comment_id)
#转发数 = sum(if_retweet)
#新鲜度=1/(最近无播放天数+1)=max(end_time)+vider id
select
video_id
,round((100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度) as 热度
from 
    (select
    t1.video_id
    ,视频完播率
    ,点赞数
    ,评论数
    ,转发数
    ,1/(abs(datediff((select max(end_time) from tb_user_video_log ),video_time))+1) as 新鲜度
    from 
        (select
        video_id
        ,sum(if(time>=duration,1,0))/count(1) as 视频完播率
        ,sum(if_like) as 点赞数
        ,count(comment_id) as 评论数
        ,sum(if_retweet) as 转发数
        from
            (select
            a.video_id
            ,timestampdiff(second,start_time,end_time) as time
            ,if_like
            ,if_retweet
            ,comment_id
            ,duration
            ,release_time
            from tb_user_video_log a  -- 视频互动表处理
            left join 
            tb_video_info b
            on a.video_id = b.video_id) c
        where abs(datediff((select max(end_time) from tb_user_video_log ),release_time))<=29  -- 视频发布时间限制
        group by 1) t1
        inner join 
            (select 
            video_id
            ,max(end_time) as video_time
            from tb_user_video_log
            group by 1)t2 -- 视频最后播放时间表
        on t1.video_id = t2.video_id
     ) as subquery_alias  -- 宽表输出
order by 热度 desc
limit 3;