跳到主要内容

SQL 智能家居设备联动：跨设备事件关联分析（小米面试题）

一、题目​

请统计所有设备联动组合的出现次数，输出 Top5。

联动定义为：同一用户在 5 分钟内（0~300秒）先后触发了两个 不同品类、不同设备 的事件，计为该组合发生 1 次。注意联动是无向的——"门锁+智能灯"和"智能灯+门锁"是同一个组合，不重复计数。

假设有设备触发日志表 t5_device_trigger_log：

+---------+-----------+----------+-------------------+
| user_id | device_id | category | trigger_time      |
+---------+-----------+----------+-------------------+
| u01     | D001      | 门锁     | 2025-06-01 18:00  |
| u01     | D002      | 智能灯   | 2025-06-01 18:03  |
| u01     | D003      | 空调     | 2025-06-01 18:06  |
| u02     | D004      | 门锁     | 2025-06-01 19:00  |
| u02     | D005      | 智能灯   | 2025-06-01 19:01  |
| u02     | D006      | 音箱     | 2025-06-01 19:02  |
| u03     | D007      | 门锁     | 2025-06-01 20:00  |
| u03     | D008      | 空调     | 2025-06-01 20:04  |
| u03     | D009      | 智能灯   | 2025-06-01 20:10  |
| u01     | D001      | 门锁     | 2025-06-02 08:00  |
| u01     | D002      | 智能灯   | 2025-06-02 08:02  |
+---------+-----------+----------+-------------------+

二、思路分析​

  1. 自连接同一用户的设备触发记录，条件：a.category < b.category 且时间戳在 5 分钟（300 秒）内；
  2. 使用时间差的绝对值为 unix_timestamp(b.trigger_time) - unix_timestamp(a.trigger_time) between 0 and 300；
  3. 统计每种联动组合的频次，取 Top5；
  4. 注意去重：同一次联动只计一次。
维度评分
题目难度⭐️⭐️⭐️⭐️
题目清晰度⭐️⭐️⭐️
业务常见度⭐️⭐️⭐️⭐️

三、逐步推导​

1. 自连接找到 5 分钟内的跨品类触发对​

自连接同一用户的触发记录，限定不同品类且时间间隔在 0~300 秒内。

执行 SQL

select a.user_id,
       a.category as category_a,
       b.category as category_b,
       a.trigger_time as time_a,
       b.trigger_time as time_b,
       abs(unix_timestamp(b.trigger_time) - unix_timestamp(a.trigger_time)) as diff_seconds
from t5_device_trigger_log a
join t5_device_trigger_log b
  on a.user_id = b.user_id
 and a.category < b.category
 and abs(unix_timestamp(b.trigger_time) - unix_timestamp(a.trigger_time))  between 0 and 300

查询结果

+----------+-------------+-------------+----------------------+----------------------+---------------+
| user_id  | category_a  | category_b  |        time_a        |        time_b        | diff_seconds  |
+----------+-------------+-------------+----------------------+----------------------+---------------+
| u01      | 智能灯         | 空调          | 2025-06-01 18:03:00  | 2025-06-01 18:06:00  | 180           |
| u01      | 智能灯         | 门锁          | 2025-06-01 18:03:00  | 2025-06-01 18:00:00  | 180           |
| u02      | 门锁          | 音箱          | 2025-06-01 19:00:00  | 2025-06-01 19:02:00  | 120           |
| u02      | 智能灯         | 音箱          | 2025-06-01 19:01:00  | 2025-06-01 19:02:00  | 60            |
| u02      | 智能灯         | 门锁          | 2025-06-01 19:01:00  | 2025-06-01 19:00:00  | 60            |
| u03      | 空调          | 门锁          | 2025-06-01 20:04:00  | 2025-06-01 20:00:00  | 240           |
| u01      | 智能灯         | 门锁          | 2025-06-02 08:02:00  | 2025-06-02 08:00:00  | 120           |
+----------+-------------+-------------+----------------------+----------------------+---------------+
7 rows selected (0.452 seconds)(https://www.dwsql.com)

2. 统计联动组合频次，取 Top5​

按联动组合分组统计频次，降序取 Top 5。

执行 SQL

select concat(category_a, '→', category_b) as linkage_pair,
       count(1) as linkage_cnt
from (
    select a.user_id,
           a.category as category_a,
           b.category as category_b
    from t5_device_trigger_log a
    join t5_device_trigger_log b
      on a.user_id = b.user_id
     and a.category < b.category
     and abs(unix_timestamp(b.trigger_time) - unix_timestamp(a.trigger_time))  between 0 and 300
) t
group by category_a, category_b
order by linkage_cnt desc
limit 5

查询结果

+---------------+--------------+
| linkage_pair  | linkage_cnt  |
+---------------+--------------+
| 智能灯→门锁        | 3            |
| 门锁→音箱         | 1            |
| 智能灯→音箱        | 1            |
| 智能灯→空调        | 1            |
| 空调→门锁         | 1            |
+---------------+--------------+
5 rows selected (0.652 seconds)

四、常见坑点​

坑 1：unix_timestamp 在 Spark 与 Hive 中的行为差异 — Spark SQL 的 unix_timestamp 默认解析 yyyy-MM-dd HH:mm:ss 格式，而 Hive 需要显式传入格式串 unix_timestamp(trigger_time, 'yyyy-MM-dd HH:mm:ss')。如果表里存的是 2025-06-01 18:00（缺秒），Spark 可能返回 NULL，需统一补齐秒或用 to_timestamp 替代。

坑 2：between 0 and 300 包含 0 秒边界 — 0 秒意味着两个设备同时触发。面试中需明确"同时触发"算不算联动：若同一用户的传感器上报可能被打包在同一秒内上报，0 秒差可能代表真正的联动也可能是数据采集的巧合，应与面试官讨论业务口径。

坑 3：自连接 + 时间窗口在百亿级日志上的性能爆炸 — 每天百亿条触发日志，自连接按 user_id 聚合后仍可能是天文数字。面试中应主动讨论优化方案：先按 user_id + 日期 分桶预聚合，将每天的日志拆分为独立分区，再在每个分区内做自连接，避免全局 shuffle；或者用窗口函数 lead() over (partition by user_id order by trigger_time) 避免全量自连接。

坑 4：同一设备自身触发产生噪音 — 联动定义要求两个不同设备，若只限制 a.category < b.category 而不同时过滤 a.device_id != b.device_id，可能出现同一设备多次触发被误判为联动（同一品类下不同设备是合理的联动，如同一用户有多个智能灯）。

坑 5：abs() 计算绝对值 — 联动定义要求时间间隔在 0~300 秒内，若只计算 diff_seconds 而不取绝对值，可能会出现负数差值，导致联动统计错误。

五、知识点总结​

考点说明
自连接 (self join)同一张表按不同条件关联自身，常用于时序事件配对分析
时间窗口过滤unix_timestamp 计算时间差 + between 约束窗口范围，注意 Spark/Hive 语法差异
a.category < b.category 去重无向组合去重的经典技巧，避免 (A,B) 和 (B,A) 重复计数
GROUP BY + 聚合函数分组聚合是数据分析的基础，配合 order by + limit 取 TopN
大数据量自连接优化百亿级数据需考虑分区裁剪、预聚合、窗口函数替代方案

六、建表语句和数据插入​

点击展开 DDL & DML
-- 建表语句
create table t5_device_trigger_log (
    user_id      string comment '用户id',
    device_id    string comment '设备id',
    category     string comment '设备品类',
    trigger_time string comment '触发时间'
) comment '设备触发日志表';

-- 插入数据
insert into t5_device_trigger_log(user_id, device_id, category, trigger_time) values
('u01','D001','门锁','2025-06-01 18:00:00'),
('u01','D002','智能灯','2025-06-01 18:03:00'),
('u01','D003','空调','2025-06-01 18:06:00'),
('u02','D004','门锁','2025-06-01 19:00:00'),
('u02','D005','智能灯','2025-06-01 19:01:00'),
('u02','D006','音箱','2025-06-01 19:02:00'),
('u03','D007','门锁','2025-06-01 20:00:00'),
('u03','D008','空调','2025-06-01 20:04:00'),
('u03','D009','智能灯','2025-06-01 20:10:00'),
('u01','D001','门锁','2025-06-02 08:00:00'),
('u01','D002','智能灯','2025-06-02 08:02:00');
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

相关题目
01. IoT设备在线率02. MIUI系统版本分布03. DAU/WAU/MAU计算