文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >专栏 >拼多多大数据面试SQL-篮球比赛得分分析

拼多多大数据面试SQL-篮球比赛得分分析

作者头像
数据仓库晨曦
发布于 2026-06-23 17:26:43
发布于 2026-06-23 17:26:43
860
举报
文章被收录于专栏：数据仓库技术数据仓库技术

一、题目

两支篮球队（A、B）进行了激烈的比赛，比分交替上升。现有一张得分明细表，记录了每次得分的球队、球员号码、球员姓名、得分分数及得分时间。

样例数据

代码语言：javascript
复制
+-------+---------+----------------------+--------+-------+
| team  | number  |      score_time      | score  | name  |
+-------+---------+----------------------+--------+-------+
| A     | 1       | 2026-03-10 14:02:15  | 1      | A1    |
| A     | 5       | 2026-03-10 14:03:47  | 1      | A5    |
| B     | 4       | 2026-03-10 14:05:22  | 3      | B4    |
| A     | 4       | 2026-03-10 14:07:08  | 3      | A4    |
| B     | 1       | 2026-03-10 14:09:33  | 3      | B1    |
| A     | 3       | 2026-03-10 14:11:19  | 3      | A3    |
| A     | 4       | 2026-03-10 14:13:45  | 3      | A4    |
| B     | 1       | 2026-03-10 14:15:02  | 2      | B1    |
| B     | 2       | 2026-03-10 14:17:36  | 2      | B2    |
| B     | 4       | 2026-03-10 14:19:51  | 1      | B4    |
| A     | 1       | 2026-03-10 14:22:14  | 2      | A1    |
| A     | 1       | 2026-03-10 14:24:39  | 1      | A1    |
| A     | 4       | 2026-03-10 14:26:55  | 1      | A4    |
| B     | 3       | 2026-03-10 14:29:07  | 3      | B3    |
| B     | 2       | 2026-03-10 14:31:28  | 3      | B2    |
| A     | 2       | 2026-03-10 14:33:42  | 3      | A2    |
| A     | 1       | 2026-03-10 14:35:16  | 1      | A1    |
| B     | 3       | 2026-03-10 14:37:53  | 2      | B3    |
| B     | 3       | 2026-03-10 14:39:41  | 3      | B3    |
| A     | 5       | 2026-03-10 14:42:05  | 2      | A5    |
| B     | 1       | 2026-03-10 14:44:28  | 3      | B1    |
| B     | 2       | 2026-03-10 14:46:17  | 1      | B2    |
| A     | 3       | 2026-03-10 14:48:33  | 1      | A3    |
| B     | 4       | 2026-03-10 14:50:49  | 1      | B4    |
| A     | 1       | 2026-03-10 14:53:11  | 3      | A1    |
| B     | 1       | 2026-03-10 14:55:26  | 1      | B1    |
| A     | 4       | 2026-03-10 14:57:38  | 2      | A4    |
| B     | 1       | 2026-03-10 14:59:54  | 1      | B1    |
| B     | 5       | 2026-03-10 15:02:09  | 2      | B5    |
| A     | 1       | 2026-03-10 15:04:33  | 1      | A1    |
| B     | 1       | 2026-03-10 15:06:47  | 3      | B1    |
| A     | 1       | 2026-03-10 15:09:12  | 3      | A1    |
| B     | 2       | 2026-03-10 15:11:35  | 3      | B2    |
| A     | 3       | 2026-03-10 15:13:48  | 3      | A3    |
| A     | 1       | 2026-03-10 15:16:02  | 2      | A1    |
| B     | 3       | 2026-03-10 15:18:29  | 3      | B3    |
| A     | 5       | 2026-03-10 15:20:44  | 2      | A5    |
| B     | 5       | 2026-03-10 15:23:06  | 3      | B5    |
+-------+---------+----------------------+--------+-------+
38 rows selected (6.924 seconds)

需统计：

  1. 连续三次（及以上）为球队得分的球员名单
  2. 帮助各自球队反超比分的球员姓名及对应时间（即该球员得分后，其所在球队的累计得分从落后/平局变为领先）

二、分析

题目一是连续问题，题目二累计求和，整体难度居中

维度

评分

题目难度

⭐️⭐️⭐️⭐️

题目清晰度

⭐️⭐️⭐️⭐️

业务常见度

⭐️⭐️⭐️

三、SQL

问题一：连续三次得分

核心思路：按时间排序，对同一球队内的得分记录编号，用"差值法"识别连续相同球员的分组。

代码语言：javascript
复制
WITH grouped AS (select *,
                        rn - rn_name AS grp
                 from (SELECT *,
                              ROW_NUMBER() OVER (ORDER BY score_time)                         AS rn,
                              ROW_NUMBER() OVER (PARTITION BY team, name ORDER BY score_time) AS rn_name
                       FROM t4_basketball_game_score_detail) ordered)
SELECT DISTINCT name
FROM grouped
GROUP BY team, name, grp
HAVING COUNT(*) >= 3;

查询结果

代码语言：javascript
复制
+-------+
| name  |
+-------+
+-------+
No rows selected (8.832 seconds)

问题二：反超比分

核心思路：用累计求和计算每次得分后两队的实时比分，判断得分前后领先关系是否发生反转。

  1. 计算截止到当前A队、B队得分
代码语言：javascript
复制
SELECT
        *,
        SUM(CASE WHEN team = 'A' THEN score ELSE 0 END) OVER (ORDER BY score_time) AS total_a,
        SUM(CASE WHEN team = 'B' THEN score ELSE 0 END) OVER (ORDER BY score_time) AS total_b
    FROM t4_basketball_game_score_detail

执行结果

代码语言：javascript
复制
+-------+---------+----------------------+--------+-------+----------+----------+
| team  | number  |      score_time      | score  | name  | total_a  | total_b  |
+-------+---------+----------------------+--------+-------+----------+----------+
| A     | 1       | 2026-03-10 14:02:15  | 1      | A1    | 1        | 0        |
| A     | 5       | 2026-03-10 14:03:47  | 1      | A5    | 2        | 0        |
| B     | 4       | 2026-03-10 14:05:22  | 3      | B4    | 2        | 3        |
| A     | 4       | 2026-03-10 14:07:08  | 3      | A4    | 5        | 3        |
| B     | 1       | 2026-03-10 14:09:33  | 3      | B1    | 5        | 6        |
| A     | 3       | 2026-03-10 14:11:19  | 3      | A3    | 8        | 6        |
| A     | 4       | 2026-03-10 14:13:45  | 3      | A4    | 11       | 6        |
| B     | 1       | 2026-03-10 14:15:02  | 2      | B1    | 11       | 8        |
| B     | 2       | 2026-03-10 14:17:36  | 2      | B2    | 11       | 10       |
| B     | 4       | 2026-03-10 14:19:51  | 1      | B4    | 11       | 11       |
| A     | 1       | 2026-03-10 14:22:14  | 2      | A1    | 13       | 11       |
| A     | 1       | 2026-03-10 14:24:39  | 1      | A1    | 14       | 11       |
| A     | 4       | 2026-03-10 14:26:55  | 1      | A4    | 15       | 11       |
| B     | 3       | 2026-03-10 14:29:07  | 3      | B3    | 15       | 14       |
| B     | 2       | 2026-03-10 14:31:28  | 3      | B2    | 15       | 17       |
| A     | 2       | 2026-03-10 14:33:42  | 3      | A2    | 18       | 17       |
| A     | 1       | 2026-03-10 14:35:16  | 1      | A1    | 19       | 17       |
| B     | 3       | 2026-03-10 14:37:53  | 2      | B3    | 19       | 19       |
| B     | 3       | 2026-03-10 14:39:41  | 3      | B3    | 19       | 22       |
| A     | 5       | 2026-03-10 14:42:05  | 2      | A5    | 21       | 22       |
| B     | 1       | 2026-03-10 14:44:28  | 3      | B1    | 21       | 25       |
| B     | 2       | 2026-03-10 14:46:17  | 1      | B2    | 21       | 26       |
| A     | 3       | 2026-03-10 14:48:33  | 1      | A3    | 22       | 26       |
| B     | 4       | 2026-03-10 14:50:49  | 1      | B4    | 22       | 27       |
| A     | 1       | 2026-03-10 14:53:11  | 3      | A1    | 25       | 27       |
| B     | 1       | 2026-03-10 14:55:26  | 1      | B1    | 25       | 28       |
| A     | 4       | 2026-03-10 14:57:38  | 2      | A4    | 27       | 28       |
| B     | 1       | 2026-03-10 14:59:54  | 1      | B1    | 27       | 29       |
| B     | 5       | 2026-03-10 15:02:09  | 2      | B5    | 27       | 31       |
| A     | 1       | 2026-03-10 15:04:33  | 1      | A1    | 28       | 31       |
| B     | 1       | 2026-03-10 15:06:47  | 3      | B1    | 28       | 34       |
| A     | 1       | 2026-03-10 15:09:12  | 3      | A1    | 31       | 34       |
| B     | 2       | 2026-03-10 15:11:35  | 3      | B2    | 31       | 37       |
| A     | 3       | 2026-03-10 15:13:48  | 3      | A3    | 34       | 37       |
| A     | 1       | 2026-03-10 15:16:02  | 2      | A1    | 36       | 37       |
| B     | 3       | 2026-03-10 15:18:29  | 3      | B3    | 36       | 40       |
| A     | 5       | 2026-03-10 15:20:44  | 2      | A5    | 38       | 40       |
| B     | 5       | 2026-03-10 15:23:06  | 3      | B5    | 38       | 43       |
+-------+---------+----------------------+--------+-------+----------+----------+
38 rows selected (1.024 seconds)
  1. 计算前一行的两队得分
代码语言：javascript
复制
select team,score_time,score,total_a,total_b,
       lag(total_a) over (order by score_time) as pre_total_a,
       lag(total_b) over (order by score_time) as pre_total_b
from (SELECT *,
             SUM(CASE WHEN team = 'A' THEN score ELSE 0 END) OVER (ORDER BY score_time) AS total_a,
             SUM(CASE WHEN team = 'B' THEN score ELSE 0 END) OVER (ORDER BY score_time) AS total_b
      FROM t4_basketball_game_score_detail)

执行结果

代码语言：javascript
复制
+-------+----------------------+--------+----------+----------+--------------+--------------+
| team  |      score_time      | score  | total_a  | total_b  | pre_total_a  | pre_total_b  |
+-------+----------------------+--------+----------+----------+--------------+--------------+
| A     | 2026-03-10 14:02:15  | 1      | 1        | 0        | NULL         | NULL         |
| A     | 2026-03-10 14:03:47  | 1      | 2        | 0        | 1            | 0            |
| B     | 2026-03-10 14:05:22  | 3      | 2        | 3        | 2            | 0            |
| A     | 2026-03-10 14:07:08  | 3      | 5        | 3        | 2            | 3            |
| B     | 2026-03-10 14:09:33  | 3      | 5        | 6        | 5            | 3            |
| A     | 2026-03-10 14:11:19  | 3      | 8        | 6        | 5            | 6            |
| A     | 2026-03-10 14:13:45  | 3      | 11       | 6        | 8            | 6            |
| B     | 2026-03-10 14:15:02  | 2      | 11       | 8        | 11           | 6            |
| B     | 2026-03-10 14:17:36  | 2      | 11       | 10       | 11           | 8            |
| B     | 2026-03-10 14:19:51  | 1      | 11       | 11       | 11           | 10           |
| A     | 2026-03-10 14:22:14  | 2      | 13       | 11       | 11           | 11           |
| A     | 2026-03-10 14:24:39  | 1      | 14       | 11       | 13           | 11           |
| A     | 2026-03-10 14:26:55  | 1      | 15       | 11       | 14           | 11           |
| B     | 2026-03-10 14:29:07  | 3      | 15       | 14       | 15           | 11           |
| B     | 2026-03-10 14:31:28  | 3      | 15       | 17       | 15           | 14           |
| A     | 2026-03-10 14:33:42  | 3      | 18       | 17       | 15           | 17           |
| A     | 2026-03-10 14:35:16  | 1      | 19       | 17       | 18           | 17           |
| B     | 2026-03-10 14:37:53  | 2      | 19       | 19       | 19           | 17           |
| B     | 2026-03-10 14:39:41  | 3      | 19       | 22       | 19           | 19           |
| A     | 2026-03-10 14:42:05  | 2      | 21       | 22       | 19           | 22           |
| B     | 2026-03-10 14:44:28  | 3      | 21       | 25       | 21           | 22           |
| B     | 2026-03-10 14:46:17  | 1      | 21       | 26       | 21           | 25           |
| A     | 2026-03-10 14:48:33  | 1      | 22       | 26       | 21           | 26           |
| B     | 2026-03-10 14:50:49  | 1      | 22       | 27       | 22           | 26           |
| A     | 2026-03-10 14:53:11  | 3      | 25       | 27       | 22           | 27           |
| B     | 2026-03-10 14:55:26  | 1      | 25       | 28       | 25           | 27           |
| A     | 2026-03-10 14:57:38  | 2      | 27       | 28       | 25           | 28           |
| B     | 2026-03-10 14:59:54  | 1      | 27       | 29       | 27           | 28           |
| B     | 2026-03-10 15:02:09  | 2      | 27       | 31       | 27           | 29           |
| A     | 2026-03-10 15:04:33  | 1      | 28       | 31       | 27           | 31           |
| B     | 2026-03-10 15:06:47  | 3      | 28       | 34       | 28           | 31           |
| A     | 2026-03-10 15:09:12  | 3      | 31       | 34       | 28           | 34           |
| B     | 2026-03-10 15:11:35  | 3      | 31       | 37       | 31           | 34           |
| A     | 2026-03-10 15:13:48  | 3      | 34       | 37       | 31           | 37           |
| A     | 2026-03-10 15:16:02  | 2      | 36       | 37       | 34           | 37           |
| B     | 2026-03-10 15:18:29  | 3      | 36       | 40       | 36           | 37           |
| A     | 2026-03-10 15:20:44  | 2      | 38       | 40       | 36           | 40           |
| B     | 2026-03-10 15:23:06  | 3      | 38       | 43       | 38           | 40           |
+-------+----------------------+--------+----------+----------+--------------+--------------+
38 rows selected (0.387 seconds)
  1. 对当前得分队伍及前后分值进行判断，A 队球员得分后 A 反超或者 B 队球员得分后 B 反超
代码语言：javascript
复制
select *
from (select *,
             lag(total_a) over (order by score_time) as pre_total_a,
             lag(total_b) over (order by score_time) as pre_total_b
      from (SELECT *,
                   SUM(CASE WHEN team = 'A' THEN score ELSE 0 END) OVER (ORDER BY score_time) AS total_a,
                   SUM(CASE WHEN team = 'B' THEN score ELSE 0 END) OVER (ORDER BY score_time) AS total_b
            FROM t4_basketball_game_score_detail) t) tt
where -- A 队球员得分后 A 反超（之前 A <= B，现在 A > B）
    (tt.team = 'A' AND tt.pre_total_a <= tt.pre_total_b AND tt.total_a > tt.total_b)
   OR
   -- B 队球员得分后 B 反超（之前 B <= A，现在 B > A）
    (tt.team = 'B' AND tt.pre_total_b <= tt.pre_total_a AND tt.total_b > tt.total_a);

执行结果

代码语言：javascript
复制
+-------+---------+----------------------+--------+-------+----------+----------+--------------+--------------+
| team  | number  |      score_time      | score  | name  | total_a  | total_b  | pre_total_a  | pre_total_b  |
+-------+---------+----------------------+--------+-------+----------+----------+--------------+--------------+
| B     | 4       | 2026-03-10 14:05:22  | 3      | B4    | 2        | 3        | 2            | 0            |
| A     | 4       | 2026-03-10 14:07:08  | 3      | A4    | 5        | 3        | 2            | 3            |
| B     | 1       | 2026-03-10 14:09:33  | 3      | B1    | 5        | 6        | 5            | 3            |
| A     | 3       | 2026-03-10 14:11:19  | 3      | A3    | 8        | 6        | 5            | 6            |
| A     | 1       | 2026-03-10 14:22:14  | 2      | A1    | 13       | 11       | 11           | 11           |
| B     | 2       | 2026-03-10 14:31:28  | 3      | B2    | 15       | 17       | 15           | 14           |
| A     | 2       | 2026-03-10 14:33:42  | 3      | A2    | 18       | 17       | 15           | 17           |
| B     | 3       | 2026-03-10 14:39:41  | 3      | B3    | 19       | 22       | 19           | 19           |
+-------+---------+----------------------+--------+-------+----------+----------+--------------+--------------+
8 rows selected (0.354 seconds)

四、建表语句

代码语言：javascript
复制
CREATE TABLE t4_basketball_game_score_detail (
    team string,
    number bigint,
    score_time string,
    score bigint,
    name string
);

INSERT INTO t4_basketball_game_score_detail VALUES
('A', 1, '2026-03-10 14:02:15', 1, 'A1'),
('A', 5, '2026-03-10 14:03:47', 1, 'A5'),
('B', 4, '2026-03-10 14:05:22', 3, 'B4'),
('A', 4, '2026-03-10 14:07:08', 3, 'A4'),
('B', 1, '2026-03-10 14:09:33', 3, 'B1'),
('A', 3, '2026-03-10 14:11:19', 3, 'A3'),
('A', 4, '2026-03-10 14:13:45', 3, 'A4'),
('B', 1, '2026-03-10 14:15:02', 2, 'B1'),
('B', 2, '2026-03-10 14:17:36', 2, 'B2'),
('B', 4, '2026-03-10 14:19:51', 1, 'B4'),
('A', 1, '2026-03-10 14:22:14', 2, 'A1'),
('A', 1, '2026-03-10 14:24:39', 1, 'A1'),
('A', 4, '2026-03-10 14:26:55', 1, 'A4'),
('B', 3, '2026-03-10 14:29:07', 3, 'B3'),
('B', 2, '2026-03-10 14:31:28', 3, 'B2'),
('A', 2, '2026-03-10 14:33:42', 3, 'A2'),
('A', 1, '2026-03-10 14:35:16', 1, 'A1'),
('B', 3, '2026-03-10 14:37:53', 2, 'B3'),
('B', 3, '2026-03-10 14:39:41', 3, 'B3'),
('A', 5, '2026-03-10 14:42:05', 2, 'A5'),
('B', 1, '2026-03-10 14:44:28', 3, 'B1'),
('B', 2, '2026-03-10 14:46:17', 1, 'B2'),
('A', 3, '2026-03-10 14:48:33', 1, 'A3'),
('B', 4, '2026-03-10 14:50:49', 1, 'B4'),
('A', 1, '2026-03-10 14:53:11', 3, 'A1'),
('B', 1, '2026-03-10 14:55:26', 1, 'B1'),
('A', 4, '2026-03-10 14:57:38', 2, 'A4'),
('B', 1, '2026-03-10 14:59:54', 1, 'B1'),
('B', 5, '2026-03-10 15:02:09', 2, 'B5'),
('A', 1, '2026-03-10 15:04:33', 1, 'A1'),
('B', 1, '2026-03-10 15:06:47', 3, 'B1'),
('A', 1, '2026-03-10 15:09:12', 3, 'A1'),
('B', 2, '2026-03-10 15:11:35', 3, 'B2'),
('A', 3, '2026-03-10 15:13:48', 3, 'A3'),
('A', 1, '2026-03-10 15:16:02', 2, 'A1'),
('B', 3, '2026-03-10 15:18:29', 3, 'B3'),
('A', 5, '2026-03-10 15:20:44', 2, 'A5'),
('B', 5, '2026-03-10 15:23:06', 3, 'B5');
本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2026-05-27，如有侵权请联系 cloudcommunity@tencent.com 删除
sql
大数据
time
面试

本文分享自 数据仓库技术 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ，欢迎热爱写作的你一起参与！

sql
大数据
time
面试
评论
登录后参与评论
0 条评论
热度
最新
登录 后参与评论
推荐阅读
目录
  • 一、题目
  • 二、分析
  • 三、SQL
    • 问题一：连续三次得分
    • 问题二：反超比分
  • 四、建表语句
领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档

Copyright © 2013 - 2026 Tencent Cloud.

All Rights Reserved. 腾讯云 版权所有

登录 后参与评论