SQL示例（使用差分数组 + 窗口函数）统计并发数量问题（处理边界：当开始时间和结束时间相同时，应该先+1再-1，才能正确统计峰值）

本文详细讲解了如何计算视频的最大并发播放量并获取TOP3视频。

---

核心思路是将观看记录拆分为开始(+1)和结束(-1)事件，通过窗口函数累加计算各时刻的并发人数，再取最大值。

---

文章提供了两种SQL实现方案，重点优化了边界情况处理（如时间相同时先加后减），并解释了差分数组算法的数学原理。

---

同时讨论了理论模型与工程实践的时间精度差异，建议在笔试中保持严谨处理边界条件，而实际开发中可根据时间精度适当简化。

---

最终方案能正确处理各种边界情况，确保结果准确可靠。

SQL示例

SQL88 输出并发播放量最高的视频

---

描述

我们有一个记录表play_record_tb记录了用户观看记录，字段的意义如下

uid：用户id

cid：视频id

start_time，end_time：用户开始和结束的观看时间

score：用户打分

请统计视频的播放量最大值，输出最大值top3高的视频信息，结果按照最大值由高到低排序，保留3位小数。

视频9001在2022-01-01 08:31:00这个时刻有序号1和4的记录正在播放，最大值为2；

视频9002在2022-01-05 08:31:00这个时刻有序号7和8的记录正在播放，最大为2；

视频9003在2022-01-04 08:31:00这个时刻有序号5的记录正在播放，最大为1。

解法

这道题需要计算每个视频的最大并发播放量（同一时刻最多有多少用户在同时观看），然后取TOP3。

解题思路

1. 并发计算：将每条观看记录看作一个时间区间，统计每个时间点同时观看的人数

2. 常用技巧：将开始时间记为+1，结束时间记为-1，使用窗口函数累计

3. 分组求最大值：对每个视频求出最大并发数

4. 排序取TOP3：按最大值降序排列

SQL实现

WITH time_points AS ( -- 将每个观看记录拆分成开始(+1)和结束(-1)两个事件 SELECT cid, start_time AS event_time, 1 AS delta FROM play_record_tb UNION ALL SELECT cid, end_time AS event_time, -1 AS delta FROM play_record_tb ), concurrent_count AS ( -- 按时间排序，计算累计并发数 SELECT cid, event_time, SUM(delta) OVER (PARTITION BY cid ORDER BY event_time) AS concurrent_num FROM time_points ), max_concurrent AS ( -- 对每个视频取最大并发数 SELECT cid, MAX(concurrent_num) AS max_concurrent FROM concurrent_count GROUP BY cid ) -- 取TOP3 SELECT cid, ROUND(CAST(max_concurrent AS DECIMAL(10,3)), 3) AS max_concurrent FROM max_concurrent ORDER BY max_concurrent DESC, cid LIMIT 3;

另一种更简洁的实现方式（使用LATERAL或自关联）

WITH RECURSIVE time_points AS ( -- 收集所有可能的时间点（开始和结束时间） SELECT cid, start_time AS time_pt FROM play_record_tb UNION SELECT cid, end_time FROM play_record_tb ), concurrent_at_time AS ( -- 每个时间点的并发数 SELECT tp.cid, tp.time_pt, COUNT(*) AS concurrent_num FROM time_points tp JOIN play_record_tb pr ON pr.cid = tp.cid AND pr.start_time <= tp.time_pt AND pr.end_time > tp.time_pt GROUP BY tp.cid, tp.time_pt ), max_concurrent AS ( SELECT cid, MAX(concurrent_num) AS max_concurrent FROM concurrent_at_time GROUP BY cid ) SELECT cid, ROUND(CAST(max_concurrent AS DECIMAL(10,3)), 3) AS max_concurrent FROM max_concurrent ORDER BY max_concurrent DESC, cid LIMIT 3;

基于您提供的示例数据验证

-- 视频9001： -- 记录1: 08:30-09:00, 记录4: 08:30-09:00 → 08:30-09:00期间2人在看 → 最大值=2 -- 视频9002： -- 记录7: 08:30-08:54, 记录8: 08:30-08:51 → 08:30-08:51期间2人在看 → 最大值=2 -- 视频9003： -- 记录5: 08:30:10-08:51:09 → 只有1人 → 最大值=1 -- 输出： -- 9001|2.000 -- 9002|2.000 -- 9003|1.000

注意：如果多个视频的最大并发数相同，按cid升序排列（保证结果确定性）。

并发数计算原理详解

这段SQL的核心思想是使用差分数组 + 窗口函数来计算每个时刻的并发观看人数。让我逐步拆解：

1. 核心数学模型

想象一条时间轴，每个观看记录是一个时间段[start_time, end_time)：

text

用户A: |=======| 用户B: |=======| 用户C: |===|

关键技巧：将时间段转换为事件点

• 在开始时刻：人数 +1（有人开始看）

• 在结束时刻：人数 -1（有人停止看）

2. 第一步：拆分为事件点 (time_points)

sql

SELECT cid, start_time AS event_time, 1 AS delta FROM play_record_tb UNION ALL SELECT cid, end_time AS event_time, -1 AS delta FROM play_record_tb

示例数据转换：

假设有以下观看记录：

text

记录1: 9001, 08:30:00 → 09:00:00 记录2: 9001, 08:30:00 → 09:00:00 记录3: 9001, 09:30:00 → 10:20:00

转换为事件点：

text

(9001, 08:30:00, +1) -- 记录1开始 (9001, 08:30:00, +1) -- 记录2开始 (9001, 09:00:00, -1) -- 记录1结束 (9001, 09:00:00, -1) -- 记录2结束 (9001, 09:30:00, +1) -- 记录3开始 (9001, 10:20:00, -1) -- 记录3结束

3. 第二步：计算累计并发数 (concurrent_count)

sql

SUM(delta) OVER (PARTITION BY cid ORDER BY event_time) AS concurrent_num

这是窗口函数累加求和：

• PARTITION BY cid：按视频分组

• ORDER BY event_time：按时间排序

• SUM(delta) OVER (...)：计算到当前时间点的累计和

累加过程演示：

4. 可视化理解

text

并发数 2 | **** | * * 1 | *** * *** | * * * * * 0 |** * * * * * ** +-----------------> 时间 08:30 09:00 09:30 10:20

• 08:30-09:00：2人同时观看（峰值）

• 09:00-09:30：0人观看

• 09:30-10:20：1人观看

5. 为什么这样设计？

传统方法（效率低）：

sql

-- 对每个时间点都要查询一次 SELECT COUNT(*) FROM records WHERE start_time <= '08:31:00' AND end_time > '08:31:00'

差分数组法（效率高）：

• 只需要 O(n) 次操作

• 一次性计算所有时间点的并发数

• 利用窗口函数在数据库内部高效计算

6. 完整示例

基于题目的数据验证：

sql

-- 视频9001的记录 记录1: 08:30-09:00 (uid1001) 记录4: 08:30-09:00 (uid1002) 记录3: 09:30-10:20 (uid1001) -- 事件点累加过程 08:30: +1 → 并发=1 08:30: +1 → 并发=2 ← 最大值 09:00: -1 → 并发=1 09:00: -1 → 并发=0 09:30: +1 → 并发=1 10:20: -1 → 并发=0 -- 最大并发数 = 2

7. 注意事项

1. 区间定义：通常[start_time, end_time)左闭右开，同一时刻开始和结束的处理顺序会影响结果

2. 排序稳定性：当时间相同时，+1和-1的顺序需要明确定义（通常先加后减）

3. 精度要求：如果需要精确到毫秒，时间字段需要足够精度

这个算法的本质是扫描线算法（Sweep Line Algorithm），广泛应用于区间重叠、会议室安排等问题中。

还是不理解

好的，让我用最简单直观的方式重新解释这个并发数计算原理，不再用复杂的术语。

一、问题是什么？

我们要找的是：同一时刻，最多有多少人同时在观看同一个视频？

比如下图，竖线代表某个时刻，数一下有几条横线穿过它：

text

时间轴 → 人A: |=======| 人B: |=======| 人C: |===| ↑ 这个时刻有A和B两个人 → 并发数=2

二、传统思路 vs 聪明思路

笨办法（不用代码，用脑子想）

要找出最大并发数，你需要：

1. 列出所有可能的时刻（比如每一秒）

2. 数每个时刻有多少人在看

3. 找出最大值

问题：时间点是无穷多的，不可能真的每一秒都去数。

聪明办法（差分法）

核心想法：人数只在两个时间点会变化：

• 有人开始看 → 人数 +1

• 有人结束看 → 人数 -1

其他时间，人数保持不变。

所以我们只需要关注这些变化的时间点就够了！

三、手把手举例

假设只有1个视频，有3条观看记录：

步骤1：标记变化点

步骤2：按时间顺序累加

从左到右，拿着一个计数器，遇到+1就加，遇到-1就减：

步骤3：找出最大值

从上面可以看出，人数最多的时候是2（8:30-9:00期间）。

四、回到SQL代码

现在回头看那段SQL，就很好理解了：

sql

-- 第1步：把每一条观看记录拆成两个事件（开始+1，结束-1） WITH time_points AS ( SELECT cid, start_time, 1 AS delta FROM play_record_tb -- 开始事件：+1 UNION ALL SELECT cid, end_time, -1 AS delta FROM play_record_tb -- 结束事件：-1 ), -- 第2步：按时间排序，把delta累加起来 concurrent_count AS ( SELECT cid, event_time, SUM(delta) OVER (PARTITION BY cid ORDER BY event_time) AS concurrent_num FROM time_points ) -- 第3步：找出每个视频的最大值 SELECT cid, MAX(concurrent_num) FROM concurrent_count GROUP BY cid

五、用图来理解

想象你在爬楼梯：

• 有人开始看 → 上1个台阶（+1）

• 有人结束看 → 下1个台阶（-1）

楼梯的高度 = 当前人数

text

高度 2 | ⬆️ | | 1 | ⬆️ | ⬆️ | | | | 0 |⬇️ | ⬇️ | ⬇️ +----+---+---+---+--→ 时间 8:30 9:00 9:30

最高点就是最大并发数！

六、一句话总结

不需要检查每一秒，只需要在有人开始或结束时检查人数变化，记录下最高峰就行了。

就像统计一个房间最多同时有多少人：

• 你只需要在有人进门和有人出门时数一下人数

• 不需要每秒钟都盯着看

这种方式我能理解

你发的这个解释确实非常清晰，它把“画时间线”和“开始+1、结束-1”这两种方法讲透了。

我之前可能讲得太绕，这个材料的核心其实就是两点：

1. 画时间轴：把每个人的观看时间画成横条，肉眼看哪个时间段叠在一起的条最多。这是最直观的理解方式。

2. 事件拆解法：把“开始”当作+1，“结束”当作-1，按时间顺序做加减，过程中出现的最大值就是答案。这就是SQL代码背后的原理。

也就是说，那段SQL里的SUM(delta) OVER (...)，本质上就是电脑在自动做这个“按时间顺序累加”的步骤，完全不需要我们手动去数。

既然这个图解逻辑你已经理解了，那我们回到真实面试或笔试的场景。

问题的关键往往不是理解原理，而是：

• 边界情况怎么处理：比如一个视频同时有上百条记录，手动画线不现实。

• SQL怎么写才能保证满分：比如时间相同（8:30有人开始，也有人结束）时，应该先+1还是先-1？这会影响峰值结果。

优化那段SQL代码，确保它能正确处理所有边界情况

主要需要处理的问题是：当开始时间和结束时间相同时，应该先+1再-1，才能正确统计峰值。

优化后的SQL（处理所有边界情况）

WITH time_points AS ( -- 开始事件：+1 SELECT cid, start_time AS event_time, 1 AS delta, 1 AS event_order -- 开始事件优先级高（先加） FROM play_record_tb UNION ALL -- 结束事件：-1 SELECT cid, end_time AS event_time, -1 AS delta, 0 AS event_order -- 结束事件优先级低（后减） FROM play_record_tb ), concurrent_count AS ( SELECT cid, event_time, -- 按时间排序，时间相同时按event_order排序（先加后减） SUM(delta) OVER ( PARTITION BY cid ORDER BY event_time, event_order DESC -- DESC确保+1先于-1 ) AS concurrent_num FROM time_points ), max_concurrent AS ( SELECT cid, MAX(concurrent_num) AS max_concurrent FROM concurrent_count GROUP BY cid ) SELECT cid, ROUND(CAST(max_concurrent AS DECIMAL(10,3)), 3) AS max_concurrent FROM max_concurrent ORDER BY max_concurrent DESC, cid LIMIT 3;

关键优化点说明

1. 处理时间相同的情况

问题场景：用户A在8:30结束，用户B在8:30开始

• 如果先减后加：8:30时刻并发数可能偏低

• 如果先加后减：8:30时刻并发数正确（包含两人）

sql

-- 添加 event_order 字段 -- 开始事件: event_order = 1 (优先级高，先执行) -- 结束事件: event_order = 0 (优先级低，后执行) ORDER BY event_time, event_order DESC

2. 处理跨天/跨年的情况

如果时间字段包含日期，确保使用DATETIME类型，排序会自动正确处理。

3. 处理NULL值

如果start_time或end_time可能为NULL：

sql

WHERE start_time IS NOT NULL AND end_time IS NOT NULL

更简洁的替代方案（使用UNION ALL + ROW_NUMBER）

WITH all_events AS ( SELECT cid, start_time AS event_time, 1 AS delta FROM play_record_tb WHERE start_time IS NOT NULL UNION ALL SELECT cid, end_time AS event_time, -1 AS delta FROM play_record_tb WHERE end_time IS NOT NULL ), concurrent_count AS ( SELECT cid, event_time, SUM(delta) OVER ( PARTITION BY cid ORDER BY event_time, delta DESC -- delta DESC: +1(1) 排在 -1(-1) 前面 ) AS concurrent_num FROM all_events ) SELECT cid, ROUND(MAX(concurrent_num), 3) AS max_concurrent FROM concurrent_count GROUP BY cid ORDER BY max_concurrent DESC, cid LIMIT 3;

边界情况测试用例

-- 测试数据 INSERT INTO play_record_tb (uid, cid, start_time, end_time) VALUES -- 情况1：同一时刻开始和结束 (1001, 9001, '2022-01-01 08:30:00', '2022-01-01 08:30:00'), -- 瞬间观看 (1002, 9001, '2022-01-01 08:30:00', '2022-01-01 09:00:00'), -- 情况2：多个同时开始 (1001, 9002, '2022-01-01 08:30:00', '2022-01-01 09:00:00'), (1002, 9002, '2022-01-01 08:30:00', '2022-01-01 09:00:00'), (1003, 9002, '2022-01-01 08:30:00', '2022-01-01 09:00:00'), -- 情况3：无缝衔接 (1001, 9003, '2022-01-01 08:30:00', '2022-01-01 09:00:00'), (1002, 9003, '2022-01-01 09:00:00', '2022-01-01 09:30:00'); -- 恰好前一个结束的时刻开始

预期结果

性能优化建议

如果数据量很大（百万级以上），可以考虑：

-- 添加索引 ALTER TABLE play_record_tb ADD INDEX idx_cid_time (cid, start_time, end_time); -- 或者使用物化视图（MySQL 8.0+） -- 或者定期预计算并缓存结果

总结

优化后的SQL能正确处理：

1. ✅ 时间相同（先加后减）

2. ✅ NULL值

3. ✅ 跨天时间

4. ✅ 大量数据性能

最关键的就是ORDER BY event_time, delta DESC或显式的event_order字段，确保时间相同时开始事件先于结束事件处理。

现实里，不会有“问题场景：用户A在8:30结束，用户B在8:30开始”这种时间完全一致的情况，因为时间单位还有毫秒、微秒等。那种高并发场景，同一秒内的上万订单，也会区分先后，是这样吗

你说得完全正确，你提的这个问题非常关键，它指出了一个理论模型和工程实践之间的重要区别。

简单来说：理论上，时间相同是可能的，我们必须处理；现实中，它几乎不会发生。

让我们把这个看似矛盾的结论拆开来看。

1. 为什么理论上必须处理？

因为SQL中的时间精度是可以设置的，最低精度通常是秒。

假设系统记录时间只到秒。那么，用户A在08:30:00结束，用户B也在08:30:00开始，这在数据里就是完全相同的两个时间点。对于数据库来说，它无法知道谁先谁后。

所以，一个严谨的SQL算法必须考虑这种情况，并通过我们之前提到的ORDER BY event_time, event_order来人为规定一个顺序（比如先加后减），否则结果就是不确定的。

2. 为什么现实中几乎不会发生？

你说的完全对。在真实的高并发生产环境中，情况是这样的：

• 精度极高：时间记录通常会精确到毫秒（3位）、微秒（6位），甚至纳秒（9位）。例如2024-05-26 08:30:00.123456。

• 有序保证：在高并发下，即使是同一秒内，系统的处理引擎（如消息队列、数据库）也会为事件分配一个严格递增的、唯一的时间戳或序列号。两个不同的事件，理论上不可能获得一个完全一样的、精确到微秒的时间戳。

3. 两种场景的核心区别

• 理论模型 / 笔试面试：简化了问题，时间精度往往是“秒”。此时，必须考虑边界情况，考察的是思维的严谨性。

• 工程实践 / 线上代码：时间精度极高。此时，时间完全相同的情况趋近于零。通常不需要特意处理这种顺序，代码可以简化。但一个优秀的工程师依然会保留这种“以防万一”的逻辑，因为它几乎不增加成本。

总结一下

• 你的敏锐是对的：能发现这个模型与现实精度的差异，说明你思考得很深入。

• 你的说法符合工程事实：在真实海量数据和高精度时间下，时间点重复是小概率事件。

• 最优解是什么？：在笔试或面试中，加上处理逻辑（ORDER BY event_time, delta DESC）是更优的选择，这体现了你考虑问题的周全性。在实际开发中，如果确认时间精度足够（如微秒级），可以简化；如果精度低（如秒级），则必须加上。

所以，我优化的那段SQL实际上是一个在任何精度下都能保证正确的通用写法，它有能力处理“时间相同”这个理论上的边界情况。