得物大数据面试SQL-商品真伪鉴定通过率

⚠️ 待修正

一、题目背景

这道题来自得物的数据分析岗面试。得物是潮流电商平台，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：用户消费行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

得物平台的核心特色是"先鉴定，后发货"的购物模式。卖家发货到得物平台后，需要经过专业鉴定师的检验。现在需要统计不同品类的商品鉴定通过率。给定 authenticate_record 表。

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(*) 统计总数
使用 SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) 统计通过数，同理统计不通过数
通过率 = 通过数 / 总数 × 100
鉴定师维度同理，按 authenticator_id 分组即可
可以合并为两段 UNION ALL 或分别输出

四、逐步推导

步骤1：按品类统计鉴定通过率

SELECT
    category,
    COUNT(*) AS total_cnt,
    SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) AS pass_cnt,
    SUM(CASE WHEN result = 'fail' THEN 1 ELSE 0 END) AS fail_cnt,
    CONCAT(
        ROUND(
            SUM(CASE WHEN result = 'pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
        ), '%'
    ) AS pass_rate
FROM authenticate_record
GROUP BY category
ORDER BY pass_rate DESC;

执行结果：

category	total_cnt	pass_cnt	fail_cnt	pass_rate
服装	4	3	1	75.00%
配饰	2	1	1	50.00%
运动鞋	6	4	2	66.67%

（注：按通过率应该更精确地看到服装75% > 运动鞋66.67% > 配饰50%）

步骤2：按鉴定师统计鉴定数量和通过率

SELECT
    a.authenticator_id,
    COUNT(*) AS total_auth_cnt,
    SUM(CASE WHEN a.result = 'pass' THEN 1 ELSE 0 END) AS pass_cnt,
    SUM(CASE WHEN a.result = 'fail' THEN 1 ELSE 0 END) AS fail_cnt,
    CONCAT(
        ROUND(
            SUM(CASE WHEN a.result = 'pass' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2
        ), '%'
    ) AS pass_rate,
    -- 鉴定师最常鉴定的品类
    MAX(c.category) AS most_common_category
FROM authenticate_record a
JOIN (
    SELECT
        authenticator_id,
        category,
        ROW_NUMBER() OVER (
            PARTITION BY authenticator_id
            ORDER BY COUNT(*) DESC
        ) AS rn
    FROM authenticate_record
    GROUP BY authenticator_id, category
) c ON a.authenticator_id = c.authenticator_id AND c.rn = 1
GROUP BY a.authenticator_id
ORDER BY total_auth_cnt DESC;

执行结果：

authenticator_id	total_auth_cnt	pass_cnt	fail_cnt	pass_rate	most_common_category
AU001	5	3	2	60.00%	运动鞋
AU002	4	4	0	100.00%	服装
AU003	3	1	2	33.33%	运动鞋

五、常见坑点

坑1：分子分母的业务定义 — 明确分子是"延迟订单数"还是"延迟用户数"，分母对应"总订单数"还是"总用户数"，口径不同结果差异大。

坑2：时间差计算的单位 — unix_timestamp 返回秒，除以60才是分钟。忘记单位转换会导致判断完全错误。

六、举一反三

按骑手/站点/城市维度下钻：定位问题来源，是某个骑手还是某个区域的延迟率偏高
按延迟程度分级：CASE WHEN 分为轻微/中等/严重三级，关注严重延迟的占比
时段分析：按小时统计，识别午晚高峰时段的配送压力变化

七、知识点总结

考点	说明
ROW_NUMBER + 差值法	连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
SUM() OVER 累积求和	窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE authenticate_record (
    auth_id VARCHAR(10) PRIMARY KEY,
    product_id VARCHAR(10),
    category VARCHAR(20),
    brand VARCHAR(30),
    result VARCHAR(10),
    auth_time DATETIME,
    authenticator_id VARCHAR(10)
);

INSERT INTO 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真题

一、题目背景​

一、题目​

三、思路分析​

四、逐步推导​

五、常见坑点​

六、举一反三​

七、知识点总结​

八、建表语句和数据插入​

你可能还想看