篮球比赛得分分析（连续得分 + 反超判断）
一、题目
两支篮球队（A、B）进行了激烈的比赛，比分交替上升。现有一张得分明细表，记录了每次得分的球队、球员号码、球员姓名、得分分数及得分时间。
样例数据
+-------+---------+----------------------+--------+-------+
| 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)
需统计：
- 连续三次（及以上）为球队得分的球员名单
- 帮助各自球队反超比分的球员姓名及对应时间（即该球员得分后，其所在球队的累计得分从落后/平局变为领先）
二、分析
题目一是连续问题，题目二累计求和，整体难度居中
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️ |
三、SQL
问题一：连续三次得分
核心思路：按时间排序，对同一球队内的得分记录编号，用"差值法"识别连续相同球员的分组。
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;
查询结果
+-------+
| name |
+-------+
+-------+
No rows selected (8.832 seconds)
问题二：反超比分
核心思路：用累计求和计算每次得分后两队的实时比分，判断得分前后领先关系是否发生反转。
- 计算截止到当前A队、B对得分
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
执行结果
+-------+---------+----------------------+--------+-------+----------+----------+
| 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)
- 计算前一行的两队得分
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)
执行结果
+-------+----------------------+--------+----------+----------+--------------+--------------+
| 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)
- 对当前得分队伍及前后分值进行判断，A 队球员得分后 A 反超或者 B 队球员得分后 B 反超
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);
执行结果
+-------+---------+----------------------+--------+-------+----------+----------+--------------+--------------+
| 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)
四、建表语句
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');