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

横扫SQL面试

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

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

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

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

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

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

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

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

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

核心思路：事件流扫描法

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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。这确保统计的是时间点内的最低值，避免虚高。

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

-- 步骤1：将进出记录转换为事件流（+1表示进入，-1表示离开）
with events as (
    select time, 1 as change from pool 
    where flag = 'enter'  -- 进入事件标记为+1
    
    union all  -- 拼接大法！！！qaq
    
    select 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 需要排序 处理海量数据可能会效率低一些。

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

-- 步骤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;

1. 小数据量（<10万行）
   直接使用 order by current_people desc, time limit 1，代码更简洁。

2. 大数据量（≥100万行）或实时计算
   使用分步计算最大值的方法，性能提升显著。若 current_people 字段有索引，效果更佳。

3. 极端数据（如全天峰值仅一个时刻）
   两种方法性能差异可忽略，优先保证代码可读性。

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

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

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

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

1：生成事件流

2：时空扫描计算

3：获取峰值时刻

-- 步骤1：生成亮灭事件流（+1表示亮灯，-1表示灭灯）
with events as (
    select 亮灯时间 as event_time, 1 as type   -- 亮灯事件标记为+1
    from 灯泡信息表
    union all                                   -- 合并亮灭事件
    select 灭灯时间 as event_time, -1 as type  -- 灭灯事件标记为-1
    from 灯泡信息表
),

-- 步骤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;  -- 保证输出唯一结果

🔍 事件流处理本质

Tips：

1. 异常数据处理

   • 未离开记录：COALESCE(leave_time, '23:59:59') 设置默认值
   • 时间重叠校验：亮灯时间 < 灭灯时间

2. 分布式优化

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

3. 实时监控改造

   /* 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);
   

不同业务场景共享同一套事件流处理范式，后续遇到类似事件流处理的问题

本专栏会持续更新~ 欢迎大家 订阅 关注 ~😊😊😊