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

牛客

SQL161 近一个月发布的视频中热度最高的top3视频

解题思路

盲点 这道题目需要排除没有播放 or 发布时间不在最近一个月的 视频记录

1. 需要按照热度公式来处理每个视频
1. 首先拆除公式有 完播率、点赞数、评论数、转发数、新鲜度
2. 我们已知参数是固定的，所以可以先计算出这几个数值⬆️
2. 需要用where来排除发布时间不在近一个月的视频
1. 如 最近的播放时间是2021-08-01 12:00:00 往前推一个月是 2021-07-01 00:00:00，但理论上2021-07-01不属于最近一个月，所以再往后推1天是2021-07-02 00:00:00
2. t1.release_time > ( select date_add ( concat(substr(date_sub (max(end_time), interval 1 month),1,10),' 00:00:00'), interval 1 day ) as max_time from tb_user_video_log )
3. 可以用join\where的方式排除没有播放量的视频
1. 用视频记录表来做join的主表就可以将没有播放记录的视频筛掉
2. 当然用where end_time is not null也可以达到效果
4. 计算热度时需要考虑点赞、评论、转发为空的数据，以免数据错误
1. 如果有null值可以if(col is null , 0 , 1)
2. 如果都是数据值可以 sum(col)

#
# (a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
# 新鲜度=1/(最近无播放天数+1)；
# 当前配置的参数a,b,c,d分别为100、5、3、2。
#  round(hot_index,0) or cast(hot_index as SIGNED)
select
    video_id,
    round(
        (
            100 * wanbo + 5 * dianzan + 3 * pinglun + 2 * zhuanfa
        ) * (1 / (wubo + 1)),
        0
    ) as hot_index
from
    (
        select
            t1.video_id,
            avg(
                if (
                    unix_timestamp (t2.end_time) - unix_timestamp (t2.start_time) >= duration,
                    1,
                    0
                )
            ) as wanbo,
            sum(if_like) as dianzan,
            sum(if (comment_id is null, 0, 1)) as pinglun,
            sum(if_retweet) as zhuanfa,
            datediff (
                (
                    select
                        max(end_time) as max_time
                    from
                        tb_user_video_log
                ),
                max(t2.end_time)
            ) as wubo
        from
            tb_user_video_log as t2
            left join tb_video_info as t1 on t1.video_id = t2.video_id
        where
            t1.release_time > (
                select
                    date_add (
                        concat(substr(date_sub (max(end_time), interval 1 month),1,10),' 00:00:00'),
                        interval 1 day
                    ) as max_time
                from
                    tb_user_video_log
            )
        group by
            t1.video_id
    ) t
order by
    hot_index desc
limit
    3;