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 case when risk_level = '低风险' then 1 when risk_level = '中风险' then 2 else 3 end
执行结果
+------------+----------+
| 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 取值比硬编码更灵活。
五、知识点总结
| 考点 | 说明 |
|---|---|
| 加权公式计算 | 多字段线性加权，注意权重的精度和总和约束 |
| 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真题
