横扫SQL面试——事件流处理（峰值统计）问题

横扫SQL面试

📌 事件流处理（峰值统计）问题

在这里插入图片描述

“会议室预定冲突怎么查？ 🔍 服务器瞬时负载如何算？🎢 健身房的‘人挤人’高峰究竟出现在几点？🏃‍♂️”

这些看似毫不相干的问题，在SQL面试中却共享着同一套解题框架——事件流处理中的峰值统计问题。

这类问题要求你从时间重叠的事件流中🕐，快速找到某个指标的最大并发数（如同时在线人数、并发请求数、亮灯数量等），是数据岗高频面试题，也是区分SQL“背题党”与“真高手”的试金石。

许多初学者的第一反应是逐行遍历时间点暴力统计，但面对千万级数据时，这种解法会直接让数据库“原地爆炸”。 💻🚀

而真正的优雅解法，只需一行窗口函数+时间轴思维，就能以O(n log n)的时间复杂度瞬间锁定答案。

话不多说——直接上题：🎈🎈🎈🎈🎈🎈🎈

一、泳池人流量峰值统计🏊‍♂️🏊‍♂️🏊‍♂️

根据用户进出记录表，统计游泳池单日内最大同时在泳池人数及对应时刻。（千万数据量级~）

字段名	数据类型	描述
user_id	整数	用户的唯一标识，用于区分不同用户
flag	字符串	标记用户的行为，取值为“enter”表示进入游泳池，“leave”表示离开游泳池
time	日期时间型	记录用户进入或离开游泳池的具体时间，格式为“YYYY-MM-DD HH:MM:SS”，这里所有时间均属于同一天（2024-04-07 ）

user_id	flag	time
1	enter	2024-04-07 08:01:00
2	enter	2024-04-07 08:20:00
1	leave	2024-04-07 09:30:00
3	enter	2024-04-07 10:01:00
4	enter	2024-04-07 10:25:00
2	leave	2024-04-07 11:22:00
4	leave	2024-04-07 12:20:00
5	enter	2024-04-07 12:30:00
6	enter	2024-04-07 13:05:00
3	leave	2024-04-07 14:20:00
5	leave	2024-04-07 14:30:00
7	enter	2024-04-07 14:45:00
8	enter	2024-04-07 15:18:00
7	leave	2024-04-07 15:32:00
6	leave	2024-04-07 15:45:00
9	enter	2024-04-07 16:01:00
10	enter	2024-04-07 16:10:00
9	leave	2024-04-07 16:25:00
8	leave	2024-04-07 16:35:00
10	leave	2024-04-07 16:55:00

核心思路：事件流扫描法

事件标记：将enter标记为+1，leave标记为-1 ——生成事件流

在这里插入图片描述

时间轴扫描：按时间排序后计算累积在线人数

在这里插入图片描述

特殊处理：相同时间点时，优先处理leave事件（避免虚增人数）

在这里插入图片描述

🔍 为什么优先处理离开事件？

在事件流处理中，同一时间点的进入（+1）和离开（-1）事件的顺序会直接影响峰值计算结果。

假设泳池在 08:00:00 同时发生两个事件：

用户A离开（flag='leave'）
用户B进入（flag='enter'）

若处理顺序不同，计算结果会如何变化？

情况1：优先处理进入事件（错误示例）

事件时间	事件类型	当前人数变化	实时累积人数
08:00:00	+1	+1	10 → 11
08:00:00	-1	-1	11 → 10

问题：在 08:00:00 时刻，系统会记录到瞬时人数为 11（虚增峰值），但实际上用户A离开和用户B进入是同时发生，泳池人数应保持 10 不变🤣🤣。

情况2：优先处理离开事件（正确做法）

事件时间	事件类型	当前人数变化	实时累积人数
08:00:00	-1	-1	10 → 9
08:00:00	+1	+1	9 → 10

结果：在 08:00:00 时刻，实时人数正确地从 10 → 9 → 10，未产生虚增的峰值。

离开事件优先：认为在某一时刻，用户会先离开泳池，再允许新用户进入。这是对现实场景的合理抽象（例如：闸机需先关闭后开启）。
若反序处理，相当于假设“用户在进入的同时还未离开”，导致逻辑矛盾。

SQL实现关键
在排序时，按时间升序，且同时间下事件类型（type）升序（ORDER BY time, type），使 -1（离开）排在 +1（进入）之前：

sql">select time,-- 窗口函数累加变化值，实现实时人数统计sum(change) over (order by time, change) as current_people  /* 排序规则说明：order by time 保证按时间顺序处理order by change 保证同时间时先处理-1（离开），再处理+1（进入）*/from events

如果同一时间点有多个离开和进入事件，如何处理？

优先级顺序：-1（离开）始终先于 +1（进入），无论事件数量多少。
示例：时间点 08:00:00 的事件顺序为 [-1, -1, +1, +1]，计算过程如下：
```
初始值: 10
-1 → 9
-1 → 8
+1 → 9
+1 → 10
```
该时刻的峰值统计值为 8（离开事件的中间状态），而非最终的 10。这确保统计的是时间点内的最低值，避免虚高。

优先处理离开事件，本质是严格区分时间边界，确保计算的峰值不因事件顺序产生误差。这种处理方式符合现实逻辑（物理空间进出顺序），且能避免数据层面的统计失真。🍬🍬

sql">-- 步骤1：将进出记录转换为事件流（+1表示进入，-1表示离开）
with events as (select time, 1 as change from pool where flag = 'enter'  -- 进入事件标记为+1union all  -- 拼接大法！！！qaqselect time, -1 as change from pool where flag = 'leave' -- 离开事件标记为-1
),-- 步骤2：按时间排序（同时间时优先处理离开事件），并计算实时人数
sorted_events as (select time,-- 窗口函数累加变化值，实现实时人数统计sum(change) over (order by time, change) as current_people  /* 排序规则说明：order by time 保证按时间顺序处理order by change 保证同时间时先处理-1（离开），再处理+1（进入）*/from events
)-- 步骤3：找出人数最多的时刻 （需要排序 处理海量数据可能会效率低一些）
select time as peak_time,          -- 峰值时刻current_people as max_people  -- 最大同时在场人数
from sorted_events
order by current_people desc,  -- 按人数降序排列（最大值排第一）time                  -- 相同人数时取最早出现的时间
limit 1;  -- 取第一条记录（即最大同时人数及其对应时刻）

上面最后一步 order by 需要排序处理海量数据可能会效率低一些。

数据量决定优化策略——对于事件流类问题（泳池、灯泡、服务器负载等），当数据规模达到百万级以上时，优先使用分步计算最大值的写法，避免全表排序瓶颈！ 🚀

在这里插入图片描述

sql">-- 步骤3：单独获取最大人数（避免全表排序）
max_people as (select max(current_people) as max_people  -- 直接计算最大值，时间复杂度 O(n)from sorted_events
)-- 步骤4：关联结果并取最早峰值时刻
select time as peak_time,max_people as max_people
from sorted_events, max_people
where sorted_events.current_people = max_people.max_people  -- 仅筛选等于最大值的行
order by time  -- 只需对少量数据排序（假设峰值时刻较少）
limit 1;

优化点	性能提升原因
分离最大值计算	`max(current_people)` 只需一次全表扫描（O(n)），远快于全表排序（O(n log n)）
减少排序数据量	仅对 `current_people = max_people` 的行按时间排序，数据量通常极低（如单条）
索引加速（可选）	若 `sorted_events` 的 `current_people` 有索引，`where` 条件可快速定位

方法	时间复杂度	适用场景
直接排序取第一条	O(n log n)	小数据量（千级以下）
分步计算最大值	O(n) + O(k)	大数据量（百万级以上，k为峰值时刻数量）

小数据量（<10万行）
直接使用 order by current_people desc, time limit 1，代码更简洁。
大数据量（≥100万行）或实时计算
使用分步计算最大值的方法，性能提升显著。若 current_people 字段有索引，效果更佳。
极端数据（如全天峰值仅一个时刻）
两种方法性能差异可忽略，优先保证代码可读性。

❤tips：个人习惯用 with公共表达式和关键字小写🤣~ 面试只要思路对语法细节什么的面试官不会太过深究的哈~

二、千万级灯泡亮灯峰值统计

工厂灯泡亮灭记录中，找到同时亮灯数量最多的时刻及数量。（千万数据量级~）

字段名	描述	数据类型
id	灯泡唯一标识	INT
亮灯时间	亮灯起始时间	DATETIME
灭灯时间	亮灯结束时间	DATETIME

id	亮灯时间	灭灯时间
1	2024-01-01 08:00:00	2024-01-01 09:00:00
2	2024-01-01 08:30:00	2024-01-01 09:30:00

看到这里想必大家对事件流套路已经很清晰了🤣🤣🤣

1：生成事件流

在这里插入图片描述

2：时空扫描计算

在这里插入图片描述

3：获取峰值时刻
在这里插入图片描述

sql">-- 步骤1：生成亮灭事件流（+1表示亮灯，-1表示灭灯）
with events as (select 亮灯时间 as event_time, 1 as type   -- 亮灯事件标记为+1from 灯泡信息表union all                                   -- 合并亮灭事件select 灭灯时间 as event_time, -1 as type  -- 灭灯事件标记为-1from 灯泡信息表
),-- 步骤2：按时间排序（同时间时优先处理灭灯事件），并计算实时亮灯数
sorted_events as (select event_time,-- 窗口函数累加变化值，统计实时亮灯数量sum(type) over (order by event_time, type) as current_count/* 排序规则说明：order by event_time 保证按时间顺序处理order by type       保证同时间时先处理-1（灭灯），再处理+1（亮灯）避免虚增同时亮灯数量 */from events
),-- 步骤3：获取最大亮灯数量（可能多个时间点达到相同最大值）
max_count as (select max(current_count) as max_count  -- 找出最大的亮灯数量from sorted_events
)-- 步骤4：筛选并输出结果（取第一个达到最大值的时间点）
select event_time as `同时亮灯数量最多的时刻`,max_count as `同时亮灯数量`
from sorted_events
join max_count on sorted_events.current_count = max_count.max_count  -- 关联最大值
order by event_time  -- 多个峰值时间时取最早出现的
limit 1;  -- 保证输出唯一结果

🔍 事件流处理本质

操作	泳池问题	灯泡问题	核心价值
事件类型	enter(+1)/leave(-1)	亮灯(+1)/灭灯(-1)	将连续行为转化为离散事件
排序规则	时间升序，leave优先	时间升序，灭灯优先	避免同一时刻的虚增/虚减
计算逻辑	累积和(current_people)	累积和(current_count)	实时统计并发状态
性能表现	O(n log n)	O(n log n)	支持千万级数据的高效计算

Tips：

异常数据处理
- 未离开记录：COALESCE(leave_time, '23:59:59') 设置默认值
- 时间重叠校验：亮灯时间 < 灭灯时间

分布式优化

sql">/* 使用Hive分桶计算 */
SET hive.enforce.bucketing = true;
CREATE TABLE events_bucketed 
CLUSTERED BY (event_time) INTO 24 BUCKETS 
AS SELECT * FROM events;

实时监控改造

sql">/* Flink SQL流式处理 */
SELECT  TUMBLE_END(event_time, INTERVAL '1' SECOND) AS window_end,SUM(type) OVER (ORDER BY event_time) AS current_count
FROM events
GROUP BY TUMBLE(event_time, INTERVAL '1' SECOND);