小米大数据面试SQL-IoT设备在线率统计

⚠️ 待修正

一、题目背景

这道题来自小米的数据分析岗面试。小米是消费电子和IoT，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：设备监控相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

统计小米IoT平台上各类设备的在线率。设备在线率 = 当日在线设备数 / 该品类总激活设备数。计算2025年6月1日各设备品类的在线率。

假设有两张表：

• device_info：设备基础信息表（含激活状态）
• device_heartbeat：设备心跳上报表（在线记录）

-- device_info 设备信息表
+-----------+----------+----------+
| device_id | category | status   |
+-----------+----------+----------+
| D001      | 智能灯   | active   |
| D002      | 智能灯   | active   |
| D003      | 智能灯   | offline  |
| D004      | 摄像头   | active   |
| D005      | 摄像头   | active   |
| D006      | 音箱     | active   |
| D007      | 音箱     | active   |
| D008      | 音箱     | active   |
+-----------+----------+----------+

-- device_heartbeat 心跳表
+-----------+-------------------+
| device_id | heartbeat_time    |
+-----------+-------------------+
| D001      | 2025-06-01 08:00  |
| D002      | 2025-06-01 08:05  |
| D004      | 2025-06-01 08:10  |
| D006      | 2025-06-01 08:15  |
| D007      | 2025-06-01 08:20  |
| D008      | 2025-06-01 08:25  |
+-----------+-------------------+

三、思路分析

1. 总激活设备数 = 设备信息表中 status = 'active' 的设备数；
2. 当日在线设备 = 心跳表中有当天心跳记录的设备数；
3. 使用 LEFT JOIN 关联两表，按品类分组统计，计算在线率。

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

四、逐步推导

1.统计各品类激活设备总数和在线设备数

执行SQL

select d.category,
       count(distinct d.device_id) as total_active,
       count(distinct h.device_id) as online_cnt
from device_info d
left join device_heartbeat h
  on d.device_id = h.device_id
 and substr(h.heartbeat_time, 1, 10) = '2025-06-01'
where d.status = 'active'
group by d.category

查询结果

+----------+--------------+------------+
| category | total_active | online_cnt |
+----------+--------------+------------+
| 智能灯   | 2            | 2          |
| 摄像头   | 2            | 1          |
| 音箱     | 3            | 3          |
+----------+--------------+------------+

2.计算在线率

执行SQL

select category,
       total_active,
       online_cnt,
       round(online_cnt / total_active, 4) as online_rate
from (
    select d.category,
           count(distinct d.device_id) as total_active,
           count(distinct h.device_id) as online_cnt
    from device_info d
    left join device_heartbeat h
      on d.device_id = h.device_id
     and substr(h.heartbeat_time, 1, 10) = '2025-06-01'
    where d.status = 'active'
    group by d.category
) t
order by online_rate desc

查询结果

+----------+--------------+------------+-------------+
| category | total_active | online_cnt | online_rate |
+----------+--------------+------------+-------------+
| 智能灯   | 2            | 2          | 1.0000      |
| 音箱     | 3            | 3          | 1.0000      |
| 摄像头   | 2            | 1          | 0.5000      |
+----------+--------------+------------+-------------+

五、常见坑点

坑1：CASE WHEN条件的顺序 — 从上到下匹配，先命中先生效。写区间时从低到高或从高到低，避免条件重叠。

坑2：占比计算的分母 — SUM() OVER() 不带 PARTITION BY 才是全局总量，加了PARTITION BY分母变成组内总量。

六、举一反三

1. 按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高

2. 按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比

3. 时段分析：按小时统计，识别午晚高峰时段的配送压力变化

七、知识点总结

考点	说明
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE device_info (
    device_id string COMMENT '设备ID',
    category  string COMMENT '设备品类',
    status    string COMMENT '设备状态：active-激活,offline-离线'
) COMMENT '设备基础信息表';

CREATE TABLE device_heartbeat (
    device_id      string COMMENT '设备ID',
    heartbeat_time string COMMENT '心跳上报时间'
) COMMENT '设备心跳上报表';

-- 插入数据
insert into device_info(device_id, category, status) values
('D001','智能灯','active'),
('D002','智能灯','active'),
('D003','智能灯','offline'),
('D004','摄像头','active'),
('D005','摄像头','active'),
('D006','音箱','active'),
('D007','音箱','active'),
('D008','音箱','active');

insert into device_heartbeat(device_id, heartbeat_time) values
('D001','2025-06-01 08:00'),
('D002','2025-06-01 08:05'),
('D004','2025-06-01 08:10'),
('D006','2025-06-01 08:15'),
('D007','2025-06-01 08:20'),
('D008','2025-06-01 08:25');

📱关注公众号

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

💬加群交流

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