SQL大神带你飞 | 24春招京东SQL真题解析-输出播放量最高的视频

最近做了京东的24年春招题，题目如下：

题目分析

在这道题目中，我们需要从 play_record_tb 表中找出每个 cid 的最大峰值用户数（peak_uv），并按降序排列，最后输出前三个 cid。表结构如下：

• cid：内容ID
• start_time：播放开始时间
• end_time：播放结束时间

目标：找出每个 cid 的最大峰值用户数。

输出要求：输出 cid 和对应的最大峰值用户数，按降序排列，取前3个。

知识点关键词：自连接、条件聚合、分组、排序、限制输出

解答步骤

1. 自连接计算重叠用户数

   首先，我们需要通过自连接来计算每个 cid 在任意时间段内的重叠用户数。通过自连接 play_record_tb 表，使用 p1 和 p2 两个别名，检查 p1.start_time 是否在 p2.start_time 和 p2.end_time 之间。

   表的连接及分组：

from play_record_tb p1
join play_record_tb p2 on p1.cid = p2.cid
group by p1.cid, p1.id

• 代码解释：将表格play_record_tb自连接，来确定每一个视频id是否曾同时播放过，此时注意分组要同时包含cid和id，否则在后续sum计算中把所有视频id的数量都累加一遍，但我们只需要取最大值而不是总和。

round(sum(if(p1.start_time between p2.start_time and p2.end_time, 1, 0)), 3) as peak_uv

• 代码解释：这里使用 if 函数来判断 p1.start_time 是否在 p2 的时间范围内，如果是则计数为1，否则为0。round 函数用于将结果四舍五入到小数点后三位。

2. 计算每个 cid 的最大峰值用户数

   在上一步的基础上，我们需要对每个 cid 计算出最大峰值用户数。

   select cid,
          max(peak_uv) as max_peak_uv
   from (
       -- 上一步的查询结果
   ) p
   group by cid
   

   • 代码解释：使用子查询将上一步的结果作为临时表 p，然后对 cid 进行分组，使用 max 函数找出每个 cid 的最大 peak_uv。

3. 排序并限制输出

   最后，我们需要对结果按 max_peak_uv 降序排列，并限制输出前三个结果。

   order by max_peak_uv desc
   limit 3
   

   • 代码解释：order by 用于对结果进行排序，desc 表示降序排列，limit 3 限制输出前三个结果。

完整代码

select cid,
       max(peak_uv) as max_peak_uv
from (
    select p1.cid,
           round(sum(if(p1.start_time between p2.start_time and p2.end_time, 1, 0)), 3) as peak_uv
    from play_record_tb p1
    join play_record_tb p2 on p1.cid = p2.cid
    group by p1.cid, p1.id
) p
group by cid
order by max_peak_uv desc
limit 3

近似题目练习推荐

获取员工其当前的薪水比其manager当前薪水还高的相关信息

• 知识点：自连接、子查询、条件过滤

异常的邮件概率

• 知识点：聚合函数、分组求和、条件过滤、子查询

牛客每个人最近的登录日期(三)

• 知识点：自连接、窗口函数、分组、排序