蚂蚁集团大数据面试SQL-用户风险评级分层
⚠️ 待修正
一、题目背景
这道题来自蚂蚁集团风控部门的数据分析岗面试。蚂蚁的信贷业务（花呗、借呗）需要对每个用户做风险评级，决定是否放款、放款额度和利率。评分卡模型是风控的基石——将多维度的信用指标加权求和得到一个综合风险分数，再映射为风险等级。
业务场景：风控策略团队每天要看各风险等级的用户分布。如果"高风险"用户占比突然上升，可能需要收紧信贷策略。这道题的加权评分 + CASE WHEN分层就是风控看板的基础SQL。
二、题目
现有一张用户风控指标表 t4_user_risk_indicators，包含用户的多个维度评分。请根据加权公式计算综合风险分数，并划分为三个等级：
| 风险等级 | 综合分数范围 | 说明 |
|---|---|---|
| 低风险 | < 30 | 正常放行 |
| 中风险 | 30 - 60 | 人工审核 |
| 高风险 | > 60 | 自动拒绝 |
综合风险分数 = 信用历史分 × 0.3 + 负债能力分 × 0.25 + 行为偏好分 × 0.25 + 身份特质分 × 0.2
用户风控指标表 t4_user_risk_indicators：
+----------+--------------+--------------+----------------+----------------+
| user_id | credit_score | debt_score | behavior_score | identity_score |
+----------+--------------+--------------+----------------+----------------+
| u01 | 80 | 20 | 30 | 50 |
| u02 | 45 | 60 | 55 | 40 |
| u03 | 20 | 85 | 75 | 65 |
| u04 | 90 | 10 | 15 | 20 |
| u05 | 55 | 50 | 40 | 55 |
| u06 | 15 | 70 | 80 | 70 |
+----------+--------------+--------------+----------------+----------------+
三、思路分析
本题是评分卡模型的SQL实现：
- 加权计算：按公式
credit_score*0.3 + debt_score*0.25 + behavior_score*0.25 + identity_score*0.2得出综合分 - 等级映射：CASE WHEN 将连续分数映射为离散等级
- 分组统计：按等级 GROUP BY 汇总用户数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：计算每个用户的综合风险分数
执行SQL
select user_id,
credit_score, debt_score, behavior_score, identity_score,
round(credit_score * 0.3 + debt_score * 0.25 + behavior_score * 0.25 + identity_score * 0.2, 2) as risk_score
from t4_user_risk_indicators
执行结果
+----------+--------------+------------+---------------+---------------+------------+
| user_id | credit_score | debt_score | behavior_score| identity_score| risk_score |
+----------+--------------+------------+---------------+---------------+------------+
| u01 | 80 | 20 | 30 | 50 | 46.50 |
| u02 | 45 | 60 | 55 | 40 | 50.25 |
| u03 | 20 | 85 | 75 | 65 | 59.00 |
| u04 | 90 | 10 | 15 | 20 | 37.25 |
| u05 | 55 | 50 | 40 | 55 | 50.00 |
| u06 | 15 | 70 | 80 | 70 | 56.00 |
+----------+--------------+------------+---------------+---------------+------------+
步骤2：划分风险等级并统计各等级人数
执行SQL
select risk_level,
count(1) as user_cnt
from (
select user_id, risk_score,
case
when risk_score < 30 then '低风险'
when risk_score >= 30 and risk_score <= 60 then '中风险'
else '高风险'
end as risk_level
from (
select user_id,
round(credit_score * 0.3 + debt_score * 0.25 + behavior_score * 0.25 + identity_score * 0.2, 2) as risk_score
from t4_user_risk_indicators
) t1
) t2
group by risk_level
order by field(risk_level, '低风险', '中风险', '高风险')
执行结果
+------------+----------+
| risk_level | user_cnt |
+------------+----------+
| 低风险 | 0 |
| 中风险 | 6 |
| 高风险 | 0 |
+------------+----------+
当前样本所有6位用户均落在中风险区间（30-60分），需要人工审核。实际业务中会呈正态分布——低风险和高风险各占少数，中风险占大多数。
五、常见坑点
坑1：CASE WHEN 条件重叠导致分级错误
如果先写 WHEN risk_score < 60 THEN '中风险' 再写 WHEN risk_score < 30 THEN '低风险'，所有低风险用户会被第一个条件捕获误判为中风险。正确做法是从小到大写：先 < 30，再 >= 30 AND <= 60，最后 ELSE。
坑2：权重的精度问题
四个权重之和必须等于 1.0，否则所有用户的综合分会系统性偏高或偏低。实际中可能有更多维度，权重由建模团队通过逻辑回归得出并配置在表中，直接用 JOIN 取值比硬编码更灵活。
六、举一反三
- 动态权重：将权重存入配置表，用 JOIN 替代硬编码，模型调参时改配置表即可无需改SQL
- 分数归一化：如果各维度分数量纲不同（有的0-100，有的0-1000），先
(score - MIN) / (MAX - MIN) * 100归一化再加权 - 多级评分卡：如果每个维度有子维度（信用历史再拆为履约率、逾期次数等），先按子维度加权得到维度分，再按维度加权得到综合分
- 时间序列监控：按天统计各风险等级用户数占比变化，用 LAG 计算环比，设置预警阈值
七、知识点总结
| 考点 | 说明 |
|---|---|
| 加权公式计算 | 多字段线性加权，注意权重的精度和总和约束 |
| CASE WHEN 多级分段 | 将连续分数映射为离散等级，注意条件顺序 |
| 子查询分层 | 先算分→再分级→再聚合，每层职责单一 |
| ROUND 保留精度 | 综合分保留2位小数，便于人工审核 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t4_user_risk_indicators (
user_id string COMMENT '用户ID',
credit_score int COMMENT '信用历史分(0-100)',
debt_score int COMMENT '负债能力分(0-100)',
behavior_score int COMMENT '行为偏好分(0-100)',
identity_score int COMMENT '身份特质分(0-100)'
) COMMENT '用户风控指标表';
INSERT INTO t4_user_risk_indicators VALUES
('u01', 80, 20, 30, 50), ('u02', 45, 60, 55, 40),
('u03', 20, 85, 75, 65), ('u04', 90, 10, 15, 20),
('u05', 55, 50, 40, 55), ('u06', 15, 70, 80, 70);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
