本文详细讲解了如何计算视频的最大并发播放量并获取TOP3视频。核心思路是将观看记录拆分为开始(1)和结束(-1)事件通过窗口函数累加计算各时刻的并发人数再取最大值。文章提供了两种SQL实现方案重点优化了边界情况处理如时间相同时先加后减并解释了差分数组算法的数学原理。同时讨论了理论模型与工程实践的时间精度差异建议在笔试中保持严谨处理边界条件而实际开发中可根据时间精度适当简化。最终方案能正确处理各种边界情况确保结果准确可靠。SQL示例SQL88 输出并发播放量最高的视频描述我们有一个记录表play_record_tb记录了用户观看记录字段的意义如下uid用户idcid视频idstart_timeend_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结束时间记为-1使用窗口函数累计分组求最大值对每个视频求出最大并发数排序取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)sqlSELECT 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)sqlSUM(delta) OVER (PARTITION BY cid ORDER BY event_time) AS concurrent_num这是窗口函数累加求和PARTITION BY cid按视频分组ORDER BY event_time按时间排序SUM(delta) OVER (...)计算到当前时间点的累计和累加过程演示时间delta累计和含义08:3011第1个人开始看并发108:3012第2个人开始看并发209:00-11第1个人结束并发109:00-10第2个人结束并发009:3011第3个人开始看并发110:20-10第3个人结束并发04. 可视化理解text并发数 2 | **** | * * 1 | *** * *** | * * * * * 0 |** * * * * * ** ----------------- 时间 08:30 09:00 09:30 10:2008:30-09:002人同时观看峰值09:00-09:300人观看09:30-10:201人观看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 -- 最大并发数 27. 注意事项区间定义通常[start_time, end_time)左闭右开同一时刻开始和结束的处理顺序会影响结果排序稳定性当时间相同时1和-1的顺序需要明确定义通常先加后减精度要求如果需要精确到毫秒时间字段需要足够精度这个算法的本质是扫描线算法Sweep Line Algorithm广泛应用于区间重叠、会议室安排等问题中。还是不理解好的让我用最简单直观的方式重新解释这个并发数计算原理不再用复杂的术语。一、问题是什么我们要找的是同一时刻最多有多少人同时在观看同一个视频比如下图竖线代表某个时刻数一下有几条横线穿过它text时间轴 → 人A: || 人B: || 人C: || ↑ 这个时刻有A和B两个人 → 并发数2二、传统思路 vs 聪明思路笨办法不用代码用脑子想要找出最大并发数你需要列出所有可能的时刻比如每一秒数每个时刻有多少人在看找出最大值问题时间点是无穷多的不可能真的每一秒都去数。聪明办法差分法核心想法人数只在两个时间点会变化有人开始看 → 人数 1有人结束看 → 人数 -1其他时间人数保持不变。所以我们只需要关注这些变化的时间点就够了三、手把手举例假设只有1个视频有3条观看记录记录开始时间结束时间小王8:309:00小李8:309:00小张9:3010:00步骤1标记变化点时间事件人数变化8:30小王开始看18:30小李开始看19:00小王结束看-19:00小李结束看-19:30小张开始看110:00小张结束看-1步骤2按时间顺序累加从左到右拿着一个计数器遇到1就加遇到-1就减时间顺序事件操作当前人数8:30小王开始118:30小李开始12← 最大值9:00小王结束-119:00小李结束-109:30小张开始1110:00小张结束-10步骤3找出最大值从上面可以看出人数最多的时候是28: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“结束”当作-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 DESC2. 处理跨天/跨年的情况如果时间字段包含日期确保使用DATETIME类型排序会自动正确处理。3. 处理NULL值如果start_time或end_time可能为NULLsqlWHERE start_time IS NOT NULL AND end_time IS NOT NULL更简洁的替代方案使用UNION ALL ROW_NUMBERWITH 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); -- 恰好前一个结束的时刻开始预期结果cidmax_concurrent90023.00090012.00090031.000性能优化建议如果数据量很大百万级以上可以考虑-- 添加索引 ALTER TABLE play_record_tb ADD INDEX idx_cid_time (cid, start_time, end_time); -- 或者使用物化视图MySQL 8.0 -- 或者定期预计算并缓存结果总结优化后的SQL能正确处理✅ 时间相同先加后减✅ NULL值✅ 跨天时间✅ 大量数据性能最关键的就是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实际上是一个在任何精度下都能保证正确的通用写法它有能力处理“时间相同”这个理论上的边界情况。
SQL示例（使用差分数组 + 窗口函数）统计并发数量问题（处理边界：当开始时间和结束时间相同时，应该先+1再-1，才能正确统计峰值）
发布时间：2026/5/27 0:18:17
本文详细讲解了如何计算视频的最大并发播放量并获取TOP3视频。核心思路是将观看记录拆分为开始(1)和结束(-1)事件通过窗口函数累加计算各时刻的并发人数再取最大值。文章提供了两种SQL实现方案重点优化了边界情况处理如时间相同时先加后减并解释了差分数组算法的数学原理。同时讨论了理论模型与工程实践的时间精度差异建议在笔试中保持严谨处理边界条件而实际开发中可根据时间精度适当简化。最终方案能正确处理各种边界情况确保结果准确可靠。SQL示例SQL88 输出并发播放量最高的视频描述我们有一个记录表play_record_tb记录了用户观看记录字段的意义如下uid用户idcid视频idstart_timeend_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结束时间记为-1使用窗口函数累计分组求最大值对每个视频求出最大并发数排序取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)sqlSELECT 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)sqlSUM(delta) OVER (PARTITION BY cid ORDER BY event_time) AS concurrent_num这是窗口函数累加求和PARTITION BY cid按视频分组ORDER BY event_time按时间排序SUM(delta) OVER (...)计算到当前时间点的累计和累加过程演示时间delta累计和含义08:3011第1个人开始看并发108:3012第2个人开始看并发209:00-11第1个人结束并发109:00-10第2个人结束并发009:3011第3个人开始看并发110:20-10第3个人结束并发04. 可视化理解text并发数 2 | **** | * * 1 | *** * *** | * * * * * 0 |** * * * * * ** ----------------- 时间 08:30 09:00 09:30 10:2008:30-09:002人同时观看峰值09:00-09:300人观看09:30-10:201人观看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 -- 最大并发数 27. 注意事项区间定义通常[start_time, end_time)左闭右开同一时刻开始和结束的处理顺序会影响结果排序稳定性当时间相同时1和-1的顺序需要明确定义通常先加后减精度要求如果需要精确到毫秒时间字段需要足够精度这个算法的本质是扫描线算法Sweep Line Algorithm广泛应用于区间重叠、会议室安排等问题中。还是不理解好的让我用最简单直观的方式重新解释这个并发数计算原理不再用复杂的术语。一、问题是什么我们要找的是同一时刻最多有多少人同时在观看同一个视频比如下图竖线代表某个时刻数一下有几条横线穿过它text时间轴 → 人A: || 人B: || 人C: || ↑ 这个时刻有A和B两个人 → 并发数2二、传统思路 vs 聪明思路笨办法不用代码用脑子想要找出最大并发数你需要列出所有可能的时刻比如每一秒数每个时刻有多少人在看找出最大值问题时间点是无穷多的不可能真的每一秒都去数。聪明办法差分法核心想法人数只在两个时间点会变化有人开始看 → 人数 1有人结束看 → 人数 -1其他时间人数保持不变。所以我们只需要关注这些变化的时间点就够了三、手把手举例假设只有1个视频有3条观看记录记录开始时间结束时间小王8:309:00小李8:309:00小张9:3010:00步骤1标记变化点时间事件人数变化8:30小王开始看18:30小李开始看19:00小王结束看-19:00小李结束看-19:30小张开始看110:00小张结束看-1步骤2按时间顺序累加从左到右拿着一个计数器遇到1就加遇到-1就减时间顺序事件操作当前人数8:30小王开始118:30小李开始12← 最大值9:00小王结束-119:00小李结束-109:30小张开始1110:00小张结束-10步骤3找出最大值从上面可以看出人数最多的时候是28: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“结束”当作-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 DESC2. 处理跨天/跨年的情况如果时间字段包含日期确保使用DATETIME类型排序会自动正确处理。3. 处理NULL值如果start_time或end_time可能为NULLsqlWHERE start_time IS NOT NULL AND end_time IS NOT NULL更简洁的替代方案使用UNION ALL ROW_NUMBERWITH 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); -- 恰好前一个结束的时刻开始预期结果cidmax_concurrent90023.00090012.00090031.000性能优化建议如果数据量很大百万级以上可以考虑-- 添加索引 ALTER TABLE play_record_tb ADD INDEX idx_cid_time (cid, start_time, end_time); -- 或者使用物化视图MySQL 8.0 -- 或者定期预计算并缓存结果总结优化后的SQL能正确处理✅ 时间相同先加后减✅ NULL值✅ 跨天时间✅ 大量数据性能最关键的就是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实际上是一个在任何精度下都能保证正确的通用写法它有能力处理“时间相同”这个理论上的边界情况。
