SQL MIUI系统版本分布：按版本号GROUP BY统计（小米面试题）
一、题目
统计各MIUI系统版本在最近30天内（active_time 落在最近30天范围内）的活跃用户数及占比。活跃用户定义为去重设备数（count(distinct device_id)），占比 = 各版本设备数 / 总设备数。用于评估各版本的覆盖率和使用情况。
假设有设备活跃日志表 t2_miui_active_log：
+------------+-----------+-------------------+
| device_id | miui_ver | active_time |
+------------+-----------+-------------------+
| DV001 | MIUI 14 | 2025-06-01 08:00 |
| DV002 | MIUI 15 | 2025-06-01 09:00 |
| DV003 | MIUI 14 | 2025-06-02 10:00 |
| DV004 | MIUI 15 | 2025-06-02 11:00 |
| DV005 | MIUI 15 | 2025-06-03 08:00 |
| DV001 | MIUI 14 | 2025-06-03 09:00 |
| DV006 | MIUI 13 | 2025-06-04 10:00 |
| DV007 | MIUI 15 | 2025-06-04 11:00 |
| DV008 | MIUI 14 | 2025-06-05 08:00 |
| DV009 | MIUI 13 | 2025-06-05 09:00 |
+------------+-----------+-------------------+
二、思路分析
- 筛选最近30天的数据（使用
where条件过滤active_time）； - 按
miui_ver分组，使用count(distinct device_id)统计活跃设备数； - 使用窗口函数
sum(device_cnt) over()计算总活跃设备数，进而算出占比。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、逐步推导
1.统计各版本的活跃设备数
按 MIUI 版本分组，统计最近30天内各版本的活跃设备数（去重）。
执行SQL
select miui_ver,
count(distinct device_id) as device_cnt
from t2_miui_active_log
where active_time >= '2025-06-01'
and active_time < '2025-07-01'
group by miui_ver
查询结果
+-----------+-------------+
| miui_ver | device_cnt |
+-----------+-------------+
| MIUI 15 | 4 |
| MIUI 13 | 2 |
| MIUI 14 | 3 |
+-----------+-------------+
3 rows selected (1.317 seconds)(https://www.dwsql.com)
2.计算各版本占比
用窗口函数 sum() over() 计算总设备数，再算各版本占比。
执行SQL
select miui_ver,
device_cnt,
round(device_cnt / sum(device_cnt) over(), 4) as ratio
from (
select miui_ver,
count(distinct device_id) as device_cnt
from t2_miui_active_log
where active_time >= '2025-06-01'
and active_time < '2025-07-01'
group by miui_ver
) t
order by device_cnt desc
查询结果
+-----------+-------------+---------+
| miui_ver | device_cnt | ratio |
+-----------+-------------+---------+
| MIUI 15 | 4 | 0.4444 |
| MIUI 14 | 3 | 0.3333 |
| MIUI 13 | 2 | 0.2222 |
+-----------+-------------+---------+
3 rows selected (1.344 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：count(distinct device_id) vs count(*) — 活跃日志表中同一设备可能在30天内有多天活跃记录，若使用 count(*) 会把同一设备重复计数。必须使用 count(distinct device_id) 确保每个设备只计一次。
坑2：时间范围硬编码 — 示例中 where active_time >= '2025-06-01' and active_time < '2025-07-01' 是硬编码的固定日期。生产环境中应改为动态计算：where active_time >= date_sub(current_date(), 30)，保证每次查询都覆盖真实的"最近30天"。
坑3：sum(device_cnt) over() 作为分母 — 窗口函数 sum() over() 不带 partition by 计算的是全局总量，比子查询 (select sum(device_cnt) from ...) 方式更优雅，一次扫描即可同时得到组内值和全局总量。
五、知识点总结
| 考点 | 说明 |
|---|---|
| COUNT(DISTINCT) 去重 | 同一设备多天活跃产生多条记录，必须去重统计唯一设备数 |
| SUM() OVER() 窗口函数 | 不带 partition by 时计算全局总量，用于计算占比的分母 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，按版本维度汇总活跃设备数 |
六、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
create table t2_miui_active_log (
device_id string comment '设备id',
miui_ver string comment 'miui系统版本，如miui 13/miui 14/miui 15',
active_time string comment '活跃时间，格式yyyy-mm-dd hh:mm'
) comment 'miui设备活跃日志表';
-- 插入数据
insert into t2_miui_active_log(device_id, miui_ver, active_time) values
('DV001','MIUI 14','2025-06-01 08:00'),
('DV002','MIUI 15','2025-06-01 09:00'),
('DV003','MIUI 14','2025-06-02 10:00'),
('DV004','MIUI 15','2025-06-02 11:00'),
('DV005','MIUI 15','2025-06-03 08:00'),
('DV001','MIUI 14','2025-06-03 09:00'),
('DV006','MIUI 13','2025-06-04 10:00'),
('DV007','MIUI 15','2025-06-04 11:00'),
('DV008','MIUI 14','2025-06-05 08:00'),
('DV009','MIUI 13','2025-06-05 09:00');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
