SQL 商品真伪鉴定通过率：按品类/品牌统计（得物面试题）
一、题目
得物平台的核心特色是"先鉴定，后发货"的购物模式。卖家发货到得物平台后，需要经过专业鉴定师的检验。现在需要统计不同品类的商品鉴定通过率。给定 t1_authenticate_record 表。
t1_authenticate_record 鉴定记录表：
+----------+-------------+-----------+--------------+---------+----------------------+-------------------+
| auth_id | product_id | category | brand | result | auth_time | authenticator_id |
+----------+-------------+-----------+--------------+---------+----------------------+-------------------+
| A001 | P1001 | 运动鞋 | Nike | pass | 2025-01-10 09:00:00 | AU001 |
| A002 | P1002 | 运动鞋 | Adidas | pass | 2025-01-10 10:30:00 | AU002 |
| A003 | P1003 | 运动鞋 | Nike | fail | 2025-01-11 14:00:00 | AU001 |
| A004 | P1004 | 运动鞋 | New Balance | pass | 2025-01-12 09:15:00 | AU003 |
| A005 | P1005 | 服装 | Supreme | pass | 2025-01-12 11:00:00 | AU002 |
| A006 | P1006 | 运动鞋 | Li-Ning | pass | 2025-01-13 08:30:00 | AU001 |
| A007 | P1007 | 服装 | Off-White | fail | 2025-01-13 10:00:00 | AU003 |
| A008 | P1008 | 服装 | Supreme | pass | 2025-01-14 14:20:00 | AU002 |
| A009 | P1009 | 配饰 | G-Shock | pass | 2025-01-14 16:00:00 | AU001 |
| A010 | P1010 | 运动鞋 | Air Jordan | fail | 2025-01-15 09:45:00 | AU003 |
| A011 | P1011 | 服装 | Fear of God | pass | 2025-01-15 11:30:00 | AU002 |
| A012 | P1012 | 配饰 | G-Shock | fail | 2025-01-16 10:00:00 | AU001 |
+----------+-------------+-----------+--------------+---------+----------------------+-------------------+
要求：
- 按品类（category）统计鉴定总数、通过数、不通过数
- 计算各品类的鉴定通过率（pass数量 / 总数）
- 按通过率降序排列
- 额外统计每个鉴定师的鉴定总数和通过率
二、思路分析
本题是分组聚合的经典应用，考察 COUNT、SUM + CASE WHEN 条件计数、以及按多维度分组的统计能力。核心在于理解 CASE WHEN 与聚合函数的配合使用。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐ |
| 题目清晰度 | ⭐⭐⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐⭐ |
- 按
category分组，使用count(*)统计总数，count(case when result = 'pass' then 1 end)统计通过数； - 通过率 = through / 总数，用
round()保留精度； - 鉴定师维度同理，按
authenticator_id分组即可。
三、逐步推导
1.按品类统计鉴定通过率
按 category 分组，使用 count(case when) 统计通过/不通过数，计算通过率。
执行SQL
select category,
count(*) as total_cnt,
count(case when result = 'pass' then 1 end) as pass_cnt,
count(case when result = 'fail' then 1 end) as fail_cnt,
round(count(case when result = 'pass' then 1 end) * 1.0 / count(*), 4) as pass_rate
from t1_authenticate_record
group by category
order by pass_rate desc;
执行结果
+-----------+------------+-----------+-----------+------------+
| category | total_cnt | pass_cnt | fail_cnt | pass_rate |
+-----------+------------+-----------+-----------+------------+
| 服装 | 4 | 3 | 1 | 0.7500 |
| 运动鞋 | 6 | 4 | 2 | 0.6667 |
| 配饰 | 2 | 1 | 1 | 0.5000 |
+-----------+------------+-----------+-----------+------------+
3 rows selected (0.64 seconds)(https://www.dwsql.com)
2.按鉴定师统计鉴定通过率
按 authenticator_id 分组，同样使用条件计数统计通过率。
执行SQL
select authenticator_id,
count(*) as total_auth_cnt,
count(case when result = 'pass' then 1 end) as pass_cnt,
count(case when result = 'fail' then 1 end) as fail_cnt,
round(count(case when result = 'pass' then 1 end) * 1.0 / count(*), 4) as pass_rate
from t1_authenticate_record
group by authenticator_id
order by total_auth_cnt desc;
执行结果
+-------------------+-----------------+-----------+-----------+------------+
| authenticator_id | total_auth_cnt | pass_cnt | fail_cnt | pass_rate |
+-------------------+-----------------+-----------+-----------+------------+
| AU001 | 5 | 3 | 2 | 0.6000 |
| AU002 | 4 | 4 | 0 | 1.0000 |
| AU003 | 3 | 1 | 2 | 0.3333 |
+-------------------+-----------------+-----------+-----------+------------+
3 rows selected (0.587 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：count(case when) vs sum(case when) — count(case when result = 'pass' then 1 end) 中未命中返回 null，count 自动跳过；等价于 sum(case when result = 'pass' then 1 else 0 end)。两种写法结果相同，按习惯选择。
坑2：整数除法导致通过率为 0 — count(...) / count(*) 中两者都是整数，在 Hive/Spark 中整数相除结果截断为 0。必须用 * 1.0 或 cast(... as double) 确保浮点运算。
五、知识点总结
| 考点 | 说明 |
|---|---|
| count(case when) 条件计数 | 按条件分类统计行数，未命中返回 null 被 count 自动跳过 |
| group by 分组聚合 | 按品类/鉴定师维度汇总，配合 count(*) 和条件计数 |
| round 保留精度 | 控制通过率小数位数，避免浮点数过长 |
| 整数除法陷阱 | 整数/整数 结果截断，需 *1.0 或 cast 转浮点 |
六、建表语句和数据插入
点击展开 DDL & DML
create table t1_authenticate_record (
auth_id string comment '鉴定记录id',
product_id string comment '商品id',
category string comment '商品品类',
brand string comment '品牌',
result string comment '鉴定结果：pass-通过, fail-不通过',
auth_time string comment '鉴定时间',
authenticator_id string comment '鉴定师id'
) comment '商品鉴定记录表';
insert into t1_authenticate_record values
('A001', 'P1001', '运动鞋', 'Nike', 'pass', '2025-01-10 09:00:00', 'AU001'),
('A002', 'P1002', '运动鞋', 'Adidas', 'pass', '2025-01-10 10:30:00', 'AU002'),
('A003', 'P1003', '运动鞋', 'Nike', 'fail', '2025-01-11 14:00:00', 'AU001'),
('A004', 'P1004', '运动鞋', 'New Balance', 'pass', '2025-01-12 09:15:00', 'AU003'),
('A005', 'P1005', '服装', 'Supreme', 'pass', '2025-01-12 11:00:00', 'AU002'),
('A006', 'P1006', '运动鞋', 'Li-Ning', 'pass', '2025-01-13 08:30:00', 'AU001'),
('A007', 'P1007', '服装', 'Off-White', 'fail', '2025-01-13 10:00:00', 'AU003'),
('A008', 'P1008', '服装', 'Supreme', 'pass', '2025-01-14 14:20:00', 'AU002'),
('A009', 'P1009', '配饰', 'G-Shock', 'pass', '2025-01-14 16:00:00', 'AU001'),
('A010', 'P1010', '运动鞋', 'Air Jordan', 'fail', '2025-01-15 09:45:00', 'AU003'),
('A011', 'P1011', '服装', 'Fear of God', 'pass', '2025-01-15 11:30:00', 'AU002'),
('A012', 'P1012', '配饰', 'G-Shock', 'fail', '2025-01-16 10:00:00', 'AU001');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
