SQL大神带你飞 | 24春招京东SQL真题解析-最受欢迎的top3课程

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

题目分析

在这道题目中，我们需要从两个表中提取数据：course_info_tb 和 play_record_tb。以下是每个表的结构及字段的汉语意思：

1. course_info_tb（课程信息表）

   • cid：课程ID
   • release_date：课程发布日期

2. play_record_tb（播放记录表）

   • cid：课程ID
   • start_time：播放开始时间
   • end_time：播放结束时间
   • score：评分

目标：找出发布后一周内播放次数多且平均评分不低于3的课程，按播放时长降序排列，输出前三名。

输出要求：输出课程ID、播放次数、播放总时长，按播放总时长降序排列，限制输出前三名。

知识点关键词：SQL连接、时间差计算、分组、聚合函数、排序、限制输出

解答步骤

1. 连接表

   我们需要将 course_info_tb 表和 play_record_tb 表连接在一起，以便获取每个播放记录的课程信息。使用 JOIN 语句通过 cid 字段连接两个表。

   from course_info_tb c 
   join play_record_tb p on p.cid = c.cid
   

2. 过滤条件

   使用 WHERE 子句过滤出在课程发布后一周内的播放记录。通过 TIMESTAMPDIFF 函数计算 release_date 和 start_time 之间的天数差。

   where 
   timestampdiff(day, c.release_date, date_format(p.start_time, '%Y-%m-%d')) <= 7
   

3. 计算播放次数和总时长

   使用 COUNT(*) 计算播放次数，使用 SUM 和 TIMESTAMPDIFF 计算播放总时长（以分钟为单位）。

   count(*) as pv,
   sum(timestampdiff(minute, p.start_time, p.end_time)) as time_len
   

4. 分组和过滤

   使用 GROUP BY 语句按课程ID分组，并使用 HAVING 子句过滤出平均评分不低于3的课程。

   group by p.cid
   having avg(p.score) >= 3
   

5. 排序和限制输出

   使用 ORDER BY 语句按播放总时长降序排列结果，并使用 LIMIT 限制输出前三名。

   order by time_len desc
   limit 3
   

完整代码

select p.cid,
count(*) as pv,
sum(timestampdiff(minute, p.start_time, p.end_time)) as time_len
from course_info_tb c 
join play_record_tb p on p.cid = c.cid
where 
    timestampdiff(day, c.release_date, date_format(p.start_time, '%Y-%m-%d')) <= 7
group by p.cid
having avg(p.score) >= 3
order by time_len desc
limit 3

近似题目练习推荐

每个月Top3的周杰伦歌曲

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

最长连续登录天数

• 知识点：时间差计算、窗口函数、分组、排序

获取指定客户每月的消费额

• 知识点：SQL连接、聚合函数、分组、日期函数