SQL IoT设备在线率：在线时长/总时长（小米面试题）

一、题目

统计小米IoT平台上各类设备的在线率。当日在线定义为当天有心跳上报（heartbeat_time落在目标日期），总激活设备定义为 status = 'active'。设备在线率 = 当日在线设备数 / 该品类总激活设备数。计算2025年6月1日各设备品类的在线率。

假设有两张表：

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

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

-- t1_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 t1_device_info d
left join t1_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             | 1           |
| 智能灯       | 2             | 2           |
| 音箱        | 3             | 3           |
+-----------+---------------+-------------+
3 rows selected (1.923 seconds)(https://www.dwsql.com)

2.计算在线率

在步骤1基础上计算在线率并降序排列。

执行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 t1_device_info d
    left join t1_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.0          |
| 音箱        | 3             | 3           | 1.0          |
| 摄像头       | 2             | 1           | 0.5          |
+-----------+---------------+-------------+--------------+
3 rows selected (0.977 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：left join条件中日期过滤的位置 — 写在 on 还是 where？on 控制 join 的匹配范围，where 过滤最终结果。将日期条件写在 on 子句中，确保未上线设备不会因为 where 过滤被错误排除，从而无法参与在线率计算。

坑2：count(distinct h.device_id) 对无心跳设备的行为 — count(distinct h.device_id) 对于没有匹配到心跳记录的设备，h.device_id 为 NULL，count(distinct ...) 不会将 NULL 计入，因此无心跳设备的 online_cnt 计为 0，确保在线率计算正确。

坑3：心跳数据幂等性 — 同一天同一设备可能上报多条心跳记录，必须使用 count(distinct h.device_id) 去重，确保每台设备每天只算一次在线。

五、知识点总结

考点	说明
LEFT JOIN + 条件位置	on 中的条件影响 join 匹配范围，where 过滤最终结果集，需根据业务语义选择位置
COUNT(DISTINCT) 去重	心跳表中同设备同天多条记录需去重，确保每设备每天只计一次在线
GROUP BY + 聚合函数	分组聚合是数据分析的基础，按品类维度汇总设备数和在线数

六、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t1_device_info (
    device_id string comment '设备id',
    category  string comment '设备品类：智能灯/摄像头/音箱',
    status    string comment '设备状态：active-激活, offline-离线'
) comment '设备基础信息表';

create table t1_device_heartbeat (
    device_id      string comment '设备id',
    heartbeat_time string comment '心跳上报时间，格式yyyy-mm-dd hh:mm'
) comment '设备心跳上报表';

-- 插入数据
insert into t1_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 t1_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真题