小米大数据面试SQL-智能家居设备联动场景分析
⚠️ 待修正
一、题目背景
这道题来自小米的数据分析岗面试。小米是消费电子和IoT，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：设备监控相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
分析小米智能家居设备的联动场景：当用户在5分钟内先后触发了两个不同品类的设备时，统计最常见的设备联动组合Top5。例如用户先开了智能门锁，5分钟内又开了客厅灯，这算一次"门锁→智能灯"的联动。
假设有设备触发日志表 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 |
+---------+-----------+----------+-------------------+
三、思路分析
- 自连接同一用户的设备触发记录，条件：
a.category < b.category且时间戳在5分钟（300秒）内； - 使用时间差的绝对值为
unix_timestamp(b.trigger_time) - unix_timestamp(a.trigger_time) between 0 and 300； - 统计每种联动组合的频次，取Top5；
- 注意去重：同一次联动只计一次。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
1.自连接找到5分钟内的跨品类触发对
执行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,
unix_timestamp(b.trigger_time) - unix_timestamp(a.trigger_time) as diff_seconds
from device_trigger_log a
join device_trigger_log b
on a.user_id = b.user_id
and a.category < b.category
and 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:00 | 2025-06-01 18:03 | 180 |
| u01 | 门锁 | 空调 | 2025-06-01 18:00 | 2025-06-01 18:06 | 360 |
| u01 | 智能灯 | 空调 | 2025-06-01 18:03 | 2025-06-01 18:06 | 180 |
| u02 | 门锁 | 智能灯 | 2025-06-01 19:00 | 2025-06-01 19:01 | 60 |
| u02 | 门锁 | 音箱 | 2025-06-01 19:00 | 2025-06-01 19:02 | 120 |
| u02 | 智能灯 | 音箱 | 2025-06-01 19:01 | 2025-06-01 19:02 | 60 |
| u03 | 门锁 | 空调 | 2025-06-01 20:00 | 2025-06-01 20:04 | 240 |
| u01 | 门锁 | 智能灯 | 2025-06-02 08:00 | 2025-06-02 08:02 | 120 |
+---------+------------+------------+-------------------+-------------------+--------------+
2.统计联动组合频次，取Top5
执行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 device_trigger_log a
join device_trigger_log b
on a.user_id = b.user_id
and a.category < b.category
and 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 |
+--------------+--------------+
| 门锁→智能灯 | 2 |
| 门锁→空调 | 1 |
| 智能灯→空调 | 1 |
| 门锁→音箱 | 1 |
| 智能灯→音箱 | 1 |
+--------------+--------------+
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE device_trigger_log (
user_id string COMMENT '用户ID',
device_id string COMMENT '设备ID',
category string COMMENT '设备品类',
trigger_time string COMMENT '触发时间'
) COMMENT '设备触发日志表';
-- 插入数据
insert into 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真题
