问题切分

筛选条件：近一个月发布的视频

要求的是：热度最高的top3视频

隐藏条件：结果中热度保留为整数，并按热度降序排序

重点问题

什么是近一个月发布的视频？

最近播放日期为2021-10-03，记作当天日期，即max(date(end_time))

近一个月播放就是datediff(date(release_time),select max(date(end_time) from tb_user_video_log) <= 29

热度怎么计算？

热度=(100*视频完播率+5*点赞数+3*评论数+2*转发数)*新鲜度

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

视频完播率=视频完播数/视频播放数=sum(if(timestampdiff(second,start_time,end_time)) >= duration,1,0)/count(start_time)

点赞数=sum(if_like)

评论数=sum(if(comment_id !='NULL',1,0))

转发数=sum(if_retweet)

最近无播放天数=当天日期-每个视频最近播放日期=datediff((select max(date(end_time)) from tb_user_video_log),(max(date(end_time)))

怎么求TOP3？

因为是所以视频中的TOP3，所以直接按照热度降序排列，然后limit3即可

select

    video_id,

    round((100*视频完播率+5*点赞数+3*评论数+2*转发数)*(1/(最近无播放天数+1)),0) hot_index

from

    (select

        tu.video_id,

        sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0))/count(start_time) 视频完播率,

        sum(if_like) 点赞数,

        sum(if(comment_id !='NULL',1,0)) 评论数,

        sum(if_retweet) 转发数,

        datediff((select max(date(end_time)) from tb_user_video_log),(max(date(end_time)))) 最近无播放天数

    from

        tb_user_video_log tu left join tb_video_info tv

        on tu.video_id = tv.video_id

    where

        datediff((select max(date(end_time)) from tb_user_video_log),date(release_time)) <= 29

    group by 1) a

order by 2 desc

limit 3