SQL 花呗分期率计算：CASE WHEN 条件统计（蚂蚁集团面试题）
一、题目
现有一张花呗账单表 t2_huabei_bill，记录了用户每月的账单信息。请统计每月的账单总数、分期账单数以及分期率。
花呗账单表 t2_huabei_bill：
+----------+----------+-------------+--------+----------------+
| bill_id | user_id | bill_month | amount | is_installment |
+----------+----------+-------------+--------+----------------+
| B001 | u01 | 2023-01 | 5000 | 1 |
| B002 | u02 | 2023-01 | 3000 | 0 |
| B003 | u03 | 2023-01 | 8000 | 1 |
| B004 | u01 | 2023-01 | 2000 | 0 |
| B005 | u04 | 2023-02 | 12000 | 1 |
| B006 | u01 | 2023-02 | 6000 | 1 |
| B007 | u02 | 2023-02 | 4500 | 0 |
| B008 | u03 | 2023-02 | 7000 | 0 |
| B009 | u05 | 2023-02 | 3500 | 1 |
| B010 | u02 | 2023-03 | 9000 | 1 |
| B011 | u04 | 2023-03 | 5500 | 0 |
| B012 | u01 | 2023-03 | 4000 | 0 |
+----------+----------+-------------+--------+----------------+
is_installment: 1-已分期, 0-未分期
二、思路分析
本题考察按月聚合统计和条件聚合：
- 按月分组：
GROUP BY bill_month按账单月份聚合 - 条件计数：
SUM(CASE WHEN is_installment = 1 THEN 1 ELSE 0 END)统计分期账单数 - 分期率：分期账单数 / 总账单数
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、逐步推导
步骤1：按月统计总账单数和分期数
执行SQL
select bill_month,
count(1) as total_bill_cnt,
count(case when is_installment = 1 then 1 end) as installment_cnt
from t2_huabei_bill
group by bill_month
order by bill_month
执行结果
+-------------+----------------+------------------+
| bill_month | total_bill_cnt | installment_cnt |
+-------------+----------------+------------------+
| 2023-01 | 4 | 2 |
| 2023-02 | 5 | 3 |
| 2023-03 | 3 | 1 |
+-------------+----------------+------------------+
步骤2：计算分期率
执行SQL
select bill_month,
total_bill_cnt,
installment_cnt,
round(installment_cnt / total_bill_cnt, 4) as installment_rate
from (
select bill_month,
count(1) as total_bill_cnt,
count(case when is_installment = 1 then 1 end) as installment_cnt
from t2_huabei_bill
group by bill_month
) t
order by bill_month
执行结果
+-------------+----------------+------------------+-------------------+
| bill_month | total_bill_cnt | installment_cnt | installment_rate |
+-------------+----------------+------------------+-------------------+
| 2023-01 | 4 | 2 | 0.5000 |
| 2023-02 | 5 | 3 | 0.6000 |
| 2023-03 | 3 | 1 | 0.3333 |
+-------------+----------------+------------------+-------------------+
2月分期率最高（60%），可能与春节消费大额账单集中有关；3月分期率最低（33%）。
四、常见坑点
坑1：SUM + CASE WHEN vs COUNT + WHERE 的区别
SUM(CASE WHEN is_installment = 1 THEN 1 ELSE 0 END) 和 COUNT(CASE WHEN is_installment = 1 THEN 1 END) 结果相同，但前者显式保留0值、后者依赖 NULL 不计数。从可读性角度推荐 SUM+CASE WHEN 方案，意图更明确。
坑2：分期率的分子分母口径
如果一个用户当月有多笔账单，每笔账单独立计算分期。不要用 COUNT(DISTINCT user_id) 做分母——那算的是"分期的用户占比"，不是"分期的账单占比"，两个指标含义不同。
五、知识点总结
| 考点 | 说明 |
|---|---|
| SUM + CASE WHEN 条件聚合 | 一行内完成条件计数，比子查询更高效 |
| GROUP BY 按月汇总 | 按账单月份维度聚合所有账单 |
| ROUND 格式化比率 | 保留4位小数，便于展示百分比 |
| 子查询分层 | 先聚合再计算比率，逻辑清晰 |
六、建表语句和数据插入
点击展开 DDL & DML
create table t2_huabei_bill (
bill_id string comment '账单ID',
user_id string comment '用户ID',
bill_month string comment '账单月份',
amount decimal(10,2) comment '账单金额',
is_installment int comment '是否分期：1-是，0-否'
) comment '花呗账单表';
insert into t2_huabei_bill values
('B001', 'u01', '2023-01', 5000, 1),
('B002', 'u02', '2023-01', 3000, 0),
('B003', 'u03', '2023-01', 8000, 1),
('B004', 'u01', '2023-01', 2000, 0),
('B005', 'u04', '2023-02', 12000, 1),
('B006', 'u01', '2023-02', 6000, 1),
('B007', 'u02', '2023-02', 4500, 0),
('B008', 'u03', '2023-02', 7000, 0),
('B009', 'u05', '2023-02', 3500, 1),
('B010', 'u02', '2023-03', 9000, 1),
('B011', 'u04', '2023-03', 5500, 0),
('B012', 'u01', '2023-03', 4000, 0);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
