互联网大厂SQL真题（三）

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

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

注：

• 热度=(a*视频完播率+b*点赞数+c*评论数+d*转发数)*新鲜度；
• 新鲜度=1/(最近无播放天数+1)；
• 当前配置的参数a,b,c,d分别为100、5、3、2。
• 最近播放日期以end_time-结束观看时间为准，假设为T，则最近一个月按[T-29, T]闭区间统计。
• 结果中热度保留为整数，并按热度降序排序。

 

 

 Solution

本题虽然最终只需要求热度这一个指标，但其中热度是一个计算公式，其涉及到了相当多的指标，所以需要分开来一个一个看。根据热度计算公式和给定的表，不难得出最简单的几个指标就是点赞数，评论数和转发数，这三个指标通过简单group by加聚合函数就可以得出。

现在来关注其它指标，首先是视频完播率，通过观察表和给定输出示例可以发现，video_info里的duration单位应该是秒，因此我们可以使用timediff(second, date1, date2)这个函数来判断一个视频是否完播，如果end_time和start_time的差大于等于对应的duration，那么就代表完播。而因为是求完播率，再加上count(video_id)作为分母就行，实际上也就是这里使用avg()就行。该指标代码如下：

select
    avg(if(timediff(SECOND, start_time, end_time)>=duration, 
    1, 0)) as finish_rate
from
    tb_user_video_log
    left join
    tb_video_info
    on tb_user_video_log.video_id = tb_video_info.video_id

接下来是新鲜度指标，由于新鲜度涉及到了无播放天数，所以我们首先得知道一个视频的最近播放日期是多少，也就是max(date(end_time))，这部分代码如下：

select
    max(date(end_time)) as cur_dt
from
    tb_user_video_log

为了求最近无播放天数，我们需要求video_info里的release_time和cur_dt的天数差，此时我们可以将上述代码封装成一个子表，用left join on 1 = 1的方式将该表与其它表连接，此时就可计算出无播放天数。再补充上其它指标，我们可以得出下述代码：

SELECT
            video_id,
            AVG(
                IF(
                    TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration,
                    1,
                    0
                )
            ) as finish_rate,
            SUM(if_like) as like_cnt,
            COUNT(comment_id) as comment_cnt,
            SUM(if_retweet) as retweet_cnt,
            MAX(DATE(end_time)) as recent_end_dt,
            MAX(DATE(release_time)) as release_dt,
             --由于使用了group by,这里需要避免语法错误所以加max
            MAX(cur_dt) as cur_dt 
        FROM
            tb_user_video_log
            JOIN tb_video_info 
            on tb_user_video_log.video_id = tb_video_info.video_id
            LEFT JOIN (
                SELECT
                    MAX(DATE(end_time)) as cur_dt
                FROM
                    tb_user_video_log
            ) as tb_max_dt ON 1 = 1
        GROUP BY
            video_id
        HAVING
            TIMESTAMPDIFF(DAY, release_dt, cur_dt) <= 29

由于题目限定时间为近一个月，所以在group by之后加了一个having条件用于限定时间。

最后，根据热度计算公式，可以得出最终代码如下：

SELECT
    video_id,
    ROUND(
        (
            100 * finish_rate + 5 * like_cnt + 3 * comment_cnt + 2 * retweet_cnt
        ) / (TIMESTAMPDIFF(DAY, recent_end_dt, cur_dt) + 1),
        0
    ) as hot_index
FROM
    (
        SELECT
            video_id,
            AVG(
                IF(
                    TIMESTAMPDIFF(SECOND, start_time, end_time) >= duration,
                    1,
                    0
                )
            ) as finish_rate,
            SUM(if_like) as like_cnt,
            COUNT(comment_id) as comment_cnt,
            SUM(if_retweet) as retweet_cnt,
            MAX(DATE(end_time)) as recent_end_dt,
            MAX(DATE(release_time)) as release_dt,
            MAX(cur_dt) as cur_dt 
        FROM
            tb_user_video_log
            JOIN tb_video_info 
            on tb_user_video_log.video_id = tb_video_info.video_id
            LEFT JOIN (
                SELECT
                    MAX(DATE(end_time)) as cur_dt
                FROM
                    tb_user_video_log
            ) as tb_max_dt ON 1 = 1
        GROUP BY
            video_id
        HAVING
            TIMESTAMPDIFF(DAY, release_dt, cur_dt) <= 29
    ) as t_video_info
ORDER BY
    hot_index DESC
LIMIT
    3;