阿里巴巴大数据面试SQL-按GMV/DSR/退货率将商家分为四个层级
一、题目背景
淘宝/天猫有上千万活跃商家，平台需要对商家进行精细化运营。商家分层是运营策略的基础——优质商家给予流量扶持，潜力商家提供培训指导，风险商家采取限流甚至清退。这道题是阿里BI数据分析岗的高频考题，考察多指标综合评分和CASE WHEN分层能力。
业务场景：商家运营团队每月初跑一次"商家分层报表"，根据GMV（交易额）、DSR（店铺动态评分）和退货率三个维度，将商家划分为优质、潜力、风险和一般四个等级，为后续的流量分配和运营策略提供数据支撑。
二、题目
现有商家店铺表 t3_seller_info 和交易表 t3_order_info，请按 GMV、DSR评分、退货率三个维度，将商家分为四个层级，并统计各层级的商家数量、平均GMV、平均DSR和平均退货率。
| 层级 | 条件 |
|---|---|
| 优质商家 | GMV ≥ 10000元 且 DSR ≥ 4.7 且 退货率 < 5% |
| 潜力商家 | GMV ≥ 10000元 且（DSR < 4.7 或 退货率 ≥ 5%） |
| 风险商家 | DSR < 4.3 或 退货率 ≥ 10% |
| 一般商家 | 不满足以上条件的其余商家 |
商家店铺表 t3_seller_info：
+------------+--------------+-----------+------------+
| seller_id | seller_name | category | dsr_score |
+------------+--------------+-----------+------------+
| S001 | 潮流运动鞋店 | 运动户外 | 4.85 |
| S002 | 品质家居馆 | 家居用品 | 4.62 |
| S003 | 数码先锋店 | 数码电子 | 4.50 |
| S004 | 美妆精选店 | 美妆个护 | 4.72 |
| S005 | 零食大本营 | 食品饮料 | 4.15 |
| S006 | 母婴乐园 | 母婴用品 | 4.91 |
+------------+--------------+-----------+------------+
交易表 t3_order_info：
+-----------+------------+----------+-----------+--------------+
| order_id | seller_id | user_id | amount | is_returned |
+-----------+------------+----------+-----------+--------------+
| O001 | S001 | u01 | 2500.00 | 0 |
| O002 | S001 | u02 | 1800.00 | 0 |
| O003 | S001 | u03 | 3200.00 | 1 |
| O004 | S002 | u01 | 500.00 | 0 |
| O005 | S002 | u04 | 800.00 | 1 |
| O006 | S003 | u02 | 15000.00 | 0 |
| O007 | S003 | u05 | 12000.00 | 0 |
| O008 | S004 | u03 | 6000.00 | 0 |
| O009 | S004 | u06 | 8800.00 | 0 |
| O010 | S005 | u07 | 300.00 | 1 |
| O011 | S005 | u08 | 450.00 | 1 |
| O012 | S006 | u09 | 9000.00 | 0 |
+-----------+------------+----------+-----------+--------------+
is_returned: 1-已退货, 0-未退货 DSR评分满分为5.0
三、思路分析
核心是多表JOIN + 三维指标聚合 + CASE WHEN 四层分级 + 层汇总：
- 关联两表：LEFT JOIN 商家表和交易表，确保没有交易的商家也出现在统计中
- 聚合指标：按 seller_id GROUP BY，汇总GMV、DSR评分、退货率（退货订单数/总订单数）
- 四层分级：CASE WHEN 嵌套判断，先判断GMV ≥ 10000元的优质/潜力（用DSR ≥ 4.7和退货率 < 5%做细分），再判断风险条件（DSR < 4.3 或退货率 ≥ 10%）
- 层汇总：按层级 GROUP BY，统计商家数、平均值
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：关联两表，按商家聚合GMV、DSR和退货率
Spark SQL
select
s.seller_id,
s.seller_name,
s.category,
s.dsr_score,
coalesce(sum(o.amount), 0) as gmv,
round(sum(case when o.is_returned = 1 then 1 else 0 end) * 1.0 /
nullif(count(o.order_id), 0), 4) as return_rate
from
t3_seller_info s
left join t3_order_info o on
s.seller_id = o.seller_id
group by
s.seller_id,
s.seller_name,
s.category,
s.dsr_score
执行结果
+------------+--------------+-----------+------------+-----------+--------------+
| seller_id | seller_name | category | dsr_score | gmv | return_rate |
+------------+--------------+-----------+------------+-----------+--------------+
| S003 | 数码先锋店 | 数码电子 | 4.50 | 27000.00 | 0.0000 |
| S002 | 品质家居馆 | 家居用品 | 4.62 | 1300.00 | 0.5000 |
| S001 | 潮流运动鞋店 | 运动户外 | 4.85 | 7500.00 | 0.3333 |
| S004 | 美妆精选店 | 美妆个护 | 4.72 | 14800.00 | 0.0000 |
| S006 | 母婴乐园 | 母婴用品 | 4.91 | 9000.00 | 0.0000 |
| S005 | 零食大本营 | 食品饮料 | 4.15 | 750.00 | 1.0000 |
+------------+--------------+-----------+------------+-----------+--------------+
6 rows selected (1.35 seconds)(https://www.dwsql.com)
S005退货率100%但只有2笔订单，样本太小可能导致指标失真——实际业务中会有"订单数≥N"的最小样本量过滤。
步骤2：CASE WHEN 三维分层 + 按层汇总
执行SQL
select
seller_id,
seller_name,
category,
gmv,
dsr_score,
return_rate,
case when gmv >= 10000 and dsr_score >=4.7 and return_rate < 0.05 then '优质商家'
when gmv >= 10000 and (dsr_score < 4.7 or return_rate >= 0.05) then '潜力商家'
when dsr_score < 4.3 or return_rate >0.1 then '风险商家'
else '一般商家' end as s_type
from (
select
s.seller_id,
s.seller_name,
s.category,
s.dsr_score,
coalesce(sum(o.amount), 0) as gmv,
round(sum(case when o.is_returned = 1 then 1 else 0 end) * 1.0 /
nullif(count(o.order_id), 0), 4) as return_rate
from
t3_seller_info s
left join t3_order_info o on
s.seller_id = o.seller_id
group by
s.seller_id,
s.seller_name,
s.category,
s.dsr_score
) t ;
执行结果
+------------+--------------+-----------+-----------+------------+--------------+---------+
| seller_id | seller_name | category | gmv | dsr_score | return_rate | s_type |
+------------+--------------+-----------+-----------+------------+--------------+---------+
| S003 | 数码先锋店 | 数码电子 | 27000.00 | 4.50 | 0.0000 | 潜力商家 |
| S002 | 品质家居馆 | 家居用品 | 1300.00 | 4.62 | 0.5000 | 风险商家 |
| S001 | 潮流运动鞋店 | 运动户外 | 7500.00 | 4.85 | 0.3333 | 风险商家 |
| S004 | 美妆精选店 | 美妆个护 | 14800.00 | 4.72 | 0.0000 | 优质商家 |
| S006 | 母婴乐园 | 母婴用品 | 9000.00 | 4.91 | 0.0000 | 一般商家 |
| S005 | 零食大本营 | 食品饮料 | 750.00 | 4.15 | 1.0000 | 风险商家 |
+------------+--------------+-----------+-----------+------------+--------------+---------+
6 rows selected (0.564 seconds)(https://www.dwsql.com)
五、常见坑点
坑1：分层条件的判断顺序
CASE WHEN 从上到下匹配，必须把条件最严格的"优质商家"（三维同时满足）放在最前面。如果把"潜力商家"（GMV ≥ 10000元即可，DSR和退货率宽松）放前面，会吞掉本应判为优质的商家，导致优质商家数量为0。
坑2：DSR 阈值的业务合理性
DSR ≥ 4.7 作为优质门槛需要结合平台数据分布。如果平台整体DSR均值在4.6左右，4.7能有效筛选出表现优异的商家；如果均值偏低（如4.2），应下调阈值。同理，DSR < 4.3 作为风险信号是因为在淘宝体系中4.3以下是"飘绿"状态，消费者信任度显著下降。
坑3：没有交易的商家（GMV=0）
LEFT JOIN 确保0交易商家也出现，但 nullif(count(order_id), 0) 让退货率 = 0 而非 NULL。如果不加 NULLIF，0/0 在某些SQL方言中返回NULL而非0。0交易商家的DSR来自店铺表，会正常参与判断。
坑4：退货率阈值应基于数据分布
面试中如果回答"阈值5%/10%是拍脑袋定的"会扣分。正确的回答是："基于平台整体退货率的P25/P50/P75分位数设定阈值，或使用评分卡模型（逻辑回归）通过历史数据训练得到最优阈值"。
六、举一反三
- 可变阈值配置：将分层阈值（GMV门槛、DSR线、退货率线）存入配置表，用 JOIN 替代硬编码，运营可自助调参无需改SQL
- 综合评分模型：加权公式
score = GMV_zscore × 0.4 + DSR_zscore × 0.3 + (1-退货率)_zscore × 0.3，比简单硬阈值更科学，避免边界值商家被"一刀切" - 层级迁移监控：LAG(tier) OVER (PARTITION BY seller_id ORDER BY month) 看商家每月层级变化，标记"降级商家"做预警
- 按品类独立分层：加PARTITION BY category，不同品类的GMV基准和DSR分布不同（家电vs零食），品类内排名更公平
七、知识点总结
| 考点 | 说明 |
|---|---|
| LEFT JOIN + COALESCE | 保留无交易商家，NULL转0 |
| CASE WHEN 多条件分层 | 按GMV+DSR+退货率三维指标四层分级，注意判断顺序 |
| 三维条件组合 | 优质需三维同时满足，风险为"或"关系（任一触发即标记） |
| NULLIF 防除零 | 除数为0时返回NULL而非报错 |
| 层汇总 GROUP BY tier | 统计各层商家数和平均值 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t3_seller_info (
seller_id string COMMENT '商家ID',
seller_name string COMMENT '商家名称',
category string COMMENT '主营类目',
dsr_score decimal(3,2) COMMENT 'DSR动态评分(满分5.0)'
) COMMENT '商家店铺表';
CREATE TABLE t3_order_info (
order_id string COMMENT '订单ID',
seller_id string COMMENT '商家ID',
user_id string COMMENT '用户ID',
amount decimal(10,2) COMMENT '订单金额',
is_returned int COMMENT '是否退货: 1-是, 0-否'
) COMMENT '交易表';
INSERT INTO t3_seller_info VALUES
('S001', '潮流运动鞋店', '运动户外', 4.85),
('S002', '品质家居馆', '家居用品', 4.62),
('S003', '数码先锋店', '数码电子', 4.50),
('S004', '美妆精选店', '美妆个护', 4.72),
('S005', '零食大本营', '食品饮料', 4.15),
('S006', '母婴乐园', '母婴用品', 4.91);
INSERT INTO t3_order_info VALUES
('O001', 'S001', 'u01', 2500.00, 0),
('O002', 'S001', 'u02', 1800.00, 0),
('O003', 'S001', 'u03', 3200.00, 1),
('O004', 'S002', 'u01', 500.00, 0),
('O005', 'S002', 'u04', 800.00, 1),
('O006', 'S003', 'u02', 15000.00, 0),
('O007', 'S003', 'u05', 12000.00, 0),
('O008', 'S004', 'u03', 6000.00, 0),
('O009', 'S004', 'u06', 8800.00, 0),
('O010', 'S005', 'u07', 300.00, 1),
('O011', 'S005', 'u08', 450.00, 1),
('O012', 'S006', 'u09', 9000.00, 0);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
