华为大数据面试SQL-设备告警日志分析

一、题目

已知有表t1_device_alarm记录了物联网设备的告警日志，包含设备ID、告警时间。请找出在任意连续的5分钟内，告警次数超过3次的设备，并输出每个设备的故障时间窗口（告警最多的那个5分钟窗口的起止时间及告警数）。

注意：连续5分钟指从第一次告警开始往后的5分钟内，若该窗口内告警数>3，则视为该设备在该时段处于故障状态。

样例数据

+-----------+----------------------+
| device_id |     alarm_time       |
+-----------+----------------------+
| A001      | 2024-06-01 08:00:00  |
| A001      | 2024-06-01 08:01:00  |
| A001      | 2024-06-01 08:02:00  |
| A001      | 2024-06-01 08:04:00  |
| A001      | 2024-06-01 08:10:00  |
| A002      | 2024-06-01 09:00:00  |
| A002      | 2024-06-01 09:01:00  |
| A002      | 2024-06-01 09:10:00  |
| A002      | 2024-06-01 09:11:00  |
| A002      | 2024-06-01 09:12:00  |
| A002      | 2024-06-01 09:13:00  |
+-----------+----------------------+

三、思路分析

本题是IoT设备告警分析中的经典场景——滑动时间窗口统计。核心思路有几种：

• 自连接法：对每个告警记录，找到该设备在同一时间窗口（当前告警时间+5分钟内）的所有告警记录，统计数量。最直观的做法是自连接 + GROUP BY。

• 窗口函数法：使用COUNT + RANGE窗口，range between current row and interval 5 minute following。但需要注意不同SQL引擎对时间区间窗口的支持差异。

• 本题采用自连接法：以每个告警作为窗口起点，JOIN该设备在 [t, t+5min] 内的所有告警，然后按设备+窗口起点GROUP BY计数，筛选告警>3的窗口，最后按设备取告警最多的窗口。

滑动窗口在华为通信设备监控、基站告警分析等场景中极为常见。

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

1.自连接统计每个窗口起点对应的5分钟内告警数

将t1_device_alarm自连接，条件为同一设备，且b.alarm_time在a.alarm_time到a.alarm_time+5分钟之间。这样每个a记录作为窗口起点，关联到的b记录数即为该窗口内的告警数。

执行SQL

select
    a.device_id,
    a.alarm_time as window_start,
    max(b.alarm_time) as window_end,
    count(*) as alarm_cnt
from t1_device_alarm a
inner join t1_device_alarm b
    on a.device_id = b.device_id
    and b.alarm_time >= a.alarm_time
    and b.alarm_time <= date_add(a.alarm_time, interval 5 minute)
group by a.device_id, a.alarm_time

执行结果

+-----------+----------------------+----------------------+------------+
| device_id |     window_start     |     window_end       | alarm_cnt  |
+-----------+----------------------+----------------------+------------+
| A001      | 2024-06-01 08:00:00  | 2024-06-01 08:04:00  | 4          |
| A001      | 2024-06-01 08:01:00  | 2024-06-01 08:04:00  | 3          |
| A001      | 2024-06-01 08:02:00  | 2024-06-01 08:04:00  | 2          |
| A001      | 2024-06-01 08:04:00  | 2024-06-01 08:04:00  | 1          |
| A001      | 2024-06-01 08:10:00  | 2024-06-01 08:10:00  | 1          |
| A002      | 2024-06-01 09:00:00  | 2024-06-01 09:01:00  | 2          |
| A002      | 2024-06-01 09:01:00  | 2024-06-01 09:01:00  | 1          |
| A002      | 2024-06-01 09:10:00  | 2024-06-01 09:13:00  | 4          |
| A002      | 2024-06-01 09:11:00  | 2024-06-01 09:13:00  | 3          |
| A002      | 2024-06-01 09:12:00  | 2024-06-01 09:13:00  | 2          |
| A002      | 2024-06-01 09:13:00  | 2024-06-01 09:13:00  | 1          |
+-----------+----------------------+----------------------+------------+

2.筛选故障设备并输出告警最多的窗口

在上一步基础上，用HAVING筛选告警数>3的窗口，然后用ROW_NUMBER对每个设备按告警数降序排名，取排名第一的窗口作为该设备的"故障窗口"。

执行SQL

with window_alarms as (
    select
        a.device_id,
        a.alarm_time as window_start,
        max(b.alarm_time) as window_end,
        count(*) as alarm_cnt
    from t1_device_alarm a
    inner join t1_device_alarm b
        on a.device_id = b.device_id
        and b.alarm_time >= a.alarm_time
        and b.alarm_time <= date_add(a.alarm_time, interval 5 minute)
    group by a.device_id, a.alarm_time
    having count(*) > 3
)
select
    device_id,
    window_start,
    window_end,
    alarm_cnt
from (
    select
        device_id,
        window_start,
        window_end,
        alarm_cnt,
        row_number() over (partition by device_id order by alarm_cnt desc) as rn
    from window_alarms
) t
where rn = 1

执行结果

+-----------+----------------------+----------------------+------------+
| device_id |     window_start     |     window_end       | alarm_cnt  |
+-----------+----------------------+----------------------+------------+
| A001      | 2024-06-01 08:00:00  | 2024-06-01 08:04:00  | 4          |
| A002      | 2024-06-01 09:10:00  | 2024-06-01 09:13:00  | 4          |
+-----------+----------------------+----------------------+------------+

结果：设备A001在08:00-08:04期间连续5分钟内有4次告警，设备A002在09:10-09:13期间有4次告警，均被判定为故障状态。

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
ROW_NUMBER + 差值法	连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE IF NOT EXISTS t1_device_alarm (
    device_id STRING COMMENT '设备ID',
    alarm_time STRING COMMENT '告警时间'
);

--数据插入
INSERT INTO t1_device_alarm(device_id, alarm_time) VALUES
('A001', '2024-06-01 08:00:00'),
('A001', '2024-06-01 08:01:00'),
('A001', '2024-06-01 08:02:00'),
('A001', '2024-06-01 08:04:00'),
('A001', '2024-06-01 08:10:00'),
('A002', '2024-06-01 09:00:00'),
('A002', '2024-06-01 09:01:00'),
('A002', '2024-06-01 09:10:00'),
('A002', '2024-06-01 09:11:00'),
('A002', '2024-06-01 09:12:00'),
('A002', '2024-06-01 09:13:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题