小米大数据面试SQL-MIUI系统版本分布统计

⚠️ 待修正

一、题目背景

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

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

一、题目

统计各MIUI系统版本在最近30天内的活跃用户数及占比，用于评估各版本的覆盖率和使用情况。

假设有设备活跃日志表 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  |
+-----------+------------+-------------------+

三、思路分析

1. 筛选最近30天的数据（使用where条件过滤 active_time）；
2. 按 miui_ver 分组，使用 COUNT DISTINCT 统计活跃设备数；
3. 使用窗口函数 SUM() OVER() 计算总活跃设备数，进而算出占比。

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

四、逐步推导

1.统计各版本的活跃设备数

执行SQL

select miui_ver,
       count(distinct device_id) as device_cnt
from miui_active_log
where active_time >= '2025-06-01'
  and active_time < '2025-07-01'
group by miui_ver

查询结果

+------------+------------+
| miui_ver   | device_cnt |
+------------+------------+
| MIUI 13    | 2          |
| MIUI 14    | 3          |
| MIUI 15    | 4          |
+------------+------------+

2.计算各版本占比

执行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 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 |
+------------+------------+--------+

五、常见坑点

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

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

六、举一反三

1. 动态分段：阈值存在配置表中用JOIN取值，灵活调整分段策略

2. 累计分布CDF：SUM(cnt) OVER (ORDER BY bucket) 算累计占比，画帕累托图

3. 按时间维度对比：按天/周/月分区，观察分布的时序变化

七、知识点总结

考点	说明
SUM() OVER 累积求和	窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
COUNT DISTINCT 去重	统计唯一用户/事件数，避免重复记录干扰聚合结果
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

--建表语句
CREATE TABLE miui_active_log (
    device_id   string COMMENT '设备ID',
    miui_ver    string COMMENT 'MIUI系统版本',
    active_time string COMMENT '活跃时间'
) COMMENT 'MIUI设备活跃日志表';

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