蚂蚁集团大数据面试SQL-芝麻信用分人群分布
⚠️ 待修正
一、题目背景
这道题来自蚂蚁集团芝麻信用事业部的数据分析岗面试。芝麻信用分是支付宝生态的核心风控和用户分层工具，涵盖350-950分共五个等级。数据分析师需要通过信用分分布了解用户质量结构，为信贷额度、免押金服务等业务决策提供数据支持。
业务场景：运营团队每月要出一份"信用分人群分布报告"，看各等级用户占比的变化趋势。如果"较差"人群占比突然上升，需要联动风控团队排查是否有刷分行为。
二、题目
现有一张用户芝麻信用分表 t1_zhima_score，包含用户ID和芝麻信用分。请按以下区间统计各人群的用户数和占比：
| 区间 | 人群标签 |
|---|---|
| 350-500 | 较差 |
| 501-600 | 中等 |
| 601-700 | 良好 |
| 701-800 | 优秀 |
| 801-950 | 极好 |
芝麻信用分表 t1_zhima_score：
+----------+--------+
| user_id | score |
+----------+--------+
| u01 | 680 |
| u02 | 720 |
| u03 | 550 |
| u04 | 810 |
| u05 | 420 |
| u06 | 760 |
| u07 | 630 |
| u08 | 580 |
| u09 | 880 |
| u10 | 490 |
+----------+--------+
三、思路分析
本题考察 CASE WHEN 分段统计和占比计算：
- 分段映射：用 CASE WHEN 将信用分映射到对应的区间标签
- 分组统计：按区间标签 GROUP BY 统计每组的用户数
- 占比计算：用
SUM(COUNT(*)) OVER()窗口函数获取总人数，各组人数/总人数得占比
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：使用 CASE WHEN 将信用分映射到区间标签
select user_id,
score,
case
when score between 350 and 500 then '350-500（较差）'
when score between 501 and 600 then '501-600（中等）'
when score between 601 and 700 then '601-700（良好）'
when score between 701 and 800 then '701-800（优秀）'
when score between 801 and 950 then '801-950（极好）'
end as score_range
from t1_zhima_score
执行结果
+----------+--------+--------------------+
| user_id | score | score_range |
+----------+--------+--------------------+
| u01 | 680 | 601-700（良好） |
| u02 | 720 | 701-800（优秀） |
| u03 | 550 | 501-600（中等） |
| u04 | 810 | 801-950（极好） |
| u05 | 420 | 350-500（较差） |
| u06 | 760 | 701-800（优秀） |
| u07 | 630 | 601-700（良好） |
| u08 | 580 | 501-600（中等） |
| u09 | 880 | 801-950（极好） |
| u10 | 490 | 350-500（较差） |
+----------+--------+--------------------+
步骤2：统计各区间人数及占比
select score_range,
count(1) as user_cnt,
round(count(1) / sum(count(1)) over (), 4) as user_rate
from (
select case
when score between 350 and 500 then '350-500（较差）'
when score between 501 and 600 then '501-600（中等）'
when score between 601 and 700 then '601-700（良好）'
when score between 701 and 800 then '701-800（优秀）'
when score between 801 and 950 then '801-950（极好）'
end as score_range
from t1_zhima_score
) t
group by score_range
order by score_range
执行结果
+--------------------+----------+-----------+
| score_range | user_cnt | user_rate |
+--------------------+----------+-----------+
| 350-500（较差） | 2 | 0.2000 |
| 501-600（中等） | 2 | 0.2000 |
| 601-700（良好） | 2 | 0.2000 |
| 701-800（优秀） | 2 | 0.2000 |
| 801-950（极好） | 2 | 0.2000 |
+--------------------+----------+-----------+
各信用分区间分布均匀，各占20%。实际业务中通常呈正态分布，"良好"和"中等"区间人数最多。
五、常见坑点
坑1：CASE WHEN 条件的判断顺序
CASE WHEN 按从上到下的顺序匹配，第一个满足的条件生效。如果先写 WHEN score >= 350 再写 WHEN score >= 801，那 801分以上的用户会被第一个条件捕获，不会进入"极好"区间。正确做法是从小到大或从大到小写区间，确保不重叠。
坑2：BETWEEN 的边界是闭区间
BETWEEN 350 AND 500 包含 350 和 500 两个边界值。如果实际分数刚好落在边界（如 500分），需确认业务定义是"350-500"还是"350-499, 500-600"。
坑3：占比用 SUM OVER() 比子查询更简洁
老式写法是在子查询中 SELECT COUNT(*) FROM table 拿到总数再做除法，两次扫描。SUM(COUNT(*)) OVER() 一次扫描搞定，且窗口函数的占比计算不受 GROUP BY 影响。
六、举一反三
- 动态分段阈值：如果分段值存在配置表中，用 JOIN 替代硬编码 CASE WHEN，阈值变更时只需改配置表
- 累计分布（CDF）：
SUM(user_cnt) OVER (ORDER BY score_range)计算按分数从低到高的累计占比 - 按月趋势：加上月份维度
GROUP BY month, score_range，观察各等级人群占比的月度变化 - 交叉分析：关联用户交易表，统计各信用等级用户的平均客单价、逾期率等，验证信用分的区分度
七、知识点总结
| 考点 | 说明 |
|---|---|
| CASE WHEN 分段 | 将连续值映射为离散标签，注意条件顺序 |
| GROUP BY + COUNT | 按标签分组统计用户数 |
| SUM() OVER() 算占比 | 窗口函数一次扫描获取全局总数 |
| ROUND 格式化 | 占比保留4位小数便于展示 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t1_zhima_score (
user_id string COMMENT '用户ID',
score int COMMENT '芝麻信用分'
) COMMENT '用户芝麻信用分表';
INSERT INTO t1_zhima_score VALUES
('u01', 680), ('u02', 720), ('u03', 550),
('u04', 810), ('u05', 420), ('u06', 760),
('u07', 630), ('u08', 580), ('u09', 880),
('u10', 490);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
