SQL 卖家信用分计算：多维度评分加权汇总（得物面试题）
一、题目
得物平台需要为每个卖家计算信用评分，用于卖家分级管理。给定三张表：t5_seller_info（卖家基础信息）、t5_order_record（订单记录）、t5_return_record（退货记录）。请计算每个卖家的各维度得分和最终信用分，并按分数划分等级。
信用分由以下四个维度加权求和，满分 100 分：
+----------+------+---------------------------------------------------------------------+
| 评分维度 | 权重 | 计分规则 |
+----------+------+---------------------------------------------------------------------+
| 成交能力 | 40% | 已完成订单数 ≥ 5 得满分 40 分，否则按比例：订单数 / 5 × 40 |
| 退货控制 | 30% | 退货率 ≤ 10% 得满分 30 分，每超过 1 个百分点扣 3 分，最低 0 分 |
| 店铺资质 | 20% | 已认证（verified='yes'）得 20 分，未认证得 10 分 |
| 客单价水平 | 10% | 卖家平均客单价 ≥ 全平台平均客单价得满分 10 分，否则按比例：卖家均价 / 平台均价 × 10 |
+----------+------+---------------------------------------------------------------------+
卖家等级划分：S 级（≥90）、A 级（80-89）、B 级（70-79）、C 级（60-69）、D 级（<60）。
t5_seller_info 卖家基础信息表：
+------------+---------------+----------------+-----------+
| seller_id | seller_name | register_date | verified |
+------------+---------------+----------------+-----------+
| S001 | 潮流买手店A | 2024-01-15 | yes |
| S002 | 球鞋之家 | 2024-03-20 | yes |
| S003 | 街头潮流馆 | 2024-06-10 | no |
| S004 | SneakerWorld | 2024-09-05 | yes |
| S005 | 潮品汇 | 2024-11-01 | yes |
+------------+---------------+----------------+-----------+
t5_order_record 订单记录表：
+-----------+------------+-----------+-------------+---------+------------+
| order_id | seller_id | buyer_id | order_time | amount | status |
+-----------+------------+-----------+-------------+---------+------------+
| ORD101 | S001 | U501 | 2025-03-01 | 3500 | completed |
| ORD102 | S001 | U502 | 2025-03-05 | 2200 | completed |
| ORD103 | S001 | U503 | 2025-03-10 | 5800 | completed |
| ORD104 | S001 | U504 | 2025-03-15 | 1500 | cancelled |
| ORD105 | S002 | U505 | 2025-03-02 | 4200 | completed |
| ORD106 | S002 | U506 | 2025-03-08 | 3100 | completed |
| ORD107 | S003 | U507 | 2025-03-03 | 1800 | completed |
| ORD108 | S003 | U508 | 2025-03-12 | 2500 | completed |
| ORD109 | S004 | U509 | 2025-03-06 | 6800 | completed |
| ORD110 | S004 | U510 | 2025-03-14 | 7200 | completed |
| ORD111 | S004 | U511 | 2025-03-20 | 4300 | completed |
| ORD112 | S005 | U512 | 2025-03-11 | 2100 | completed |
+-----------+------------+-----------+-------------+---------+------------+
t5_return_record 退货记录表：
+------------+-----------+------------+--------------+---------+----------------+
| return_id | order_id | seller_id | return_time | reason | return_status |
+------------+-----------+------------+--------------+---------+----------------+
| RT001 | ORD103 | S001 | 2025-03-18 | 尺码不符 | completed |
| RT002 | ORD110 | S004 | 2025-03-22 | 商品瑕疵 | completed |
| RT003 | ORD111 | S004 | 2025-03-25 | 假货质疑 | pending |
+------------+-----------+------------+--------------+---------+----------------+
二、思路分析
本题是多维度评分加权的综合题，核心考察多表关联、聚合统计、条件评分逻辑和加权汇总。各维度计算规则不同，需分别处理后统一汇总。
+----------+------+
| 维度 | 评分 |
+----------+------+
| 题目难度 | ⭐⭐⭐⭐ |
| 题目清晰度 | ⭐⭐⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐⭐ |
+----------+------+
- 成交能力：从
t5_order_record统计各卖家 completed 订单数，得分 =least(completed_orders / 5, 1) × 40 - 退货控制：关联
t5_return_record（仅统计 completed 退货），退货率 = 退货数 / 已完成订单数，得分 =greatest(0, 30 - greatest(0, 退货率% - 10) × 3) - 店铺资质：从
t5_seller_info的 verified 字段判断，yes=20，no=10 - 客单价水平：计算各卖家 avg(amount) 和全平台 avg(amount)，得分 =
least(卖家均价 / 平台均价, 1) × 10 - 四个维度求和得最终信用分，再用 case when 分级
三、逐步推导
1.计算各维度得分
统计订单和退货数据，计算四个维度得分。
执行SQL
with order_stats as (
select
seller_id,
count(case when status = 'completed' then 1 end) as completed_orders,
avg(case when status = 'completed' then amount end) as avg_amount
from t5_order_record
group by seller_id
),
return_stats as (
select
seller_id,
count(*) as return_cnt
from t5_return_record
where return_status = 'completed'
group by seller_id
),
platform_avg as (
select avg(amount) as platform_avg_amount
from t5_order_record
where status = 'completed'
)
select
s.seller_id,
s.seller_name,
s.verified,
coalesce(o.completed_orders, 0) as completed_orders,
round(coalesce(o.avg_amount, 0), 2) as avg_amount,
round(coalesce(r.return_cnt, 0) * 100.0 / nullif(o.completed_orders, 0), 2) as return_rate_pct,
-- 成交能力（40分）：上限40
round(least(coalesce(o.completed_orders, 0) / 5.0, 1) * 40, 2) as score_transaction,
-- 退货控制（30分）：下限0，退货率每超10%一个百分点扣3分
round(greatest(0, 30 - greatest(0,
coalesce(r.return_cnt, 0) * 100.0 / nullif(o.completed_orders, 0) - 10
) * 3), 2) as score_return,
-- 店铺资质（20分）：认证20，未认证10
case when s.verified = 'yes' then 20 else 10 end as score_qualification,
-- 客单价水平（10分）：上限10
round(least(coalesce(o.avg_amount, 0) / nullif(pa.platform_avg_amount, 0), 1) * 10, 2) as score_avg_price
from t5_seller_info s
left join order_stats o on s.seller_id = o.seller_id
left join return_stats r on s.seller_id = r.seller_id
cross join platform_avg pa
order by s.seller_id;
执行结果
+------------+---------------+-----------+-------------------+-------------+------------------+--------------------+---------------+----------------------+------------------+
| seller_id | seller_name | verified | completed_orders | avg_amount | return_rate_pct | score_transaction | score_return | score_qualification | score_avg_price |
+------------+---------------+-----------+-------------------+-------------+------------------+--------------------+---------------+----------------------+------------------+
| S001 | 潮流买手店A | yes | 3 | 3833.33 | 33.33 | 24.00 | 0.00 | 20 | 9.69 |
| S002 | 球鞋之家 | yes | 2 | 3650.0 | 0.00 | 16.00 | 30.00 | 20 | 9.23 |
| S003 | 街头潮流馆 | no | 2 | 2150.0 | 0.00 | 16.00 | 30.00 | 10 | 5.44 |
| S004 | SneakerWorld | yes | 3 | 6100.0 | 33.33 | 24.00 | 0.00 | 20 | 10.0 |
| S005 | 潮品汇 | yes | 1 | 2100.0 | 0.00 | 8.00 | 30.00 | 20 | 5.31 |
+------------+---------------+-----------+-------------------+-------------+------------------+--------------------+---------------+----------------------+------------------+
5 rows selected (2.835 seconds)(https://www.dwsql.com)
2.计算最终信用分并分级
汇总得分并划分卖家等级。
执行SQL
with order_stats as (
select
seller_id,
count(case when status = 'completed' then 1 end) as completed_orders,
avg(case when status = 'completed' then amount end) as avg_amount
from t5_order_record
group by seller_id
),
return_stats as (
select
seller_id,
count(*) as return_cnt
from t5_return_record
where return_status = 'completed'
group by seller_id
),
platform_avg as (
select avg(amount) as platform_avg_amount
from t5_order_record
where status = 'completed'
),
score_detail as (
select
s.seller_id,
s.seller_name,
coalesce(o.completed_orders, 0) as completed_orders,
round(coalesce(r.return_cnt, 0) * 100.0 / nullif(o.completed_orders, 0), 1) as return_rate,
round(least(coalesce(o.completed_orders, 0) / 5.0, 1) * 40, 2) as score_transaction,
round(greatest(0, 30 - greatest(0,
coalesce(r.return_cnt, 0) * 100.0 / nullif(o.completed_orders, 0) - 10
) * 3), 2) as score_return,
case when s.verified = 'yes' then 20 else 10 end as score_qualification,
round(least(coalesce(o.avg_amount, 0) / nullif(pa.platform_avg_amount, 0), 1) * 10, 2) as score_avg_price
from t5_seller_info s
left join order_stats o on s.seller_id = o.seller_id
left join return_stats r on s.seller_id = r.seller_id
cross join platform_avg pa
)
select
seller_id,
seller_name,
completed_orders,
return_rate,
score_transaction,
score_return,
score_qualification,
score_avg_price,
round(score_transaction + score_return + score_qualification + score_avg_price, 2) as total_score,
case
when round(score_transaction + score_return + score_qualification + score_avg_price, 2) >= 90 then 'S级'
when round(score_transaction + score_return + score_qualification + score_avg_price, 2) >= 80 then 'A级'
when round(score_transaction + score_return + score_qualification + score_avg_price, 2) >= 70 then 'B级'
when round(score_transaction + score_return + score_qualification + score_avg_price, 2) >= 60 then 'C级'
else 'D级'
end as seller_grade
from score_detail
order by total_score desc;
执行结果
+------------+---------------+-------------------+--------------+--------------------+---------------+----------------------+------------------+--------------+---------------+
| seller_id | seller_name | completed_orders | return_rate | score_transaction | score_return | score_qualification | score_avg_price | total_score | seller_grade |
+------------+---------------+-------------------+--------------+--------------------+---------------+----------------------+------------------+--------------+---------------+
| S002 | 球鞋之家 | 2 | 0.0 | 16.00 | 30.00 | 20 | 9.23 | 75.23 | B级 |
| S005 | 潮品汇 | 1 | 0.0 | 8.00 | 30.00 | 20 | 5.31 | 63.31 | C级 |
| S003 | 街头潮流馆 | 2 | 0.0 | 16.00 | 30.00 | 10 | 5.44 | 61.44 | C级 |
| S004 | SneakerWorld | 3 | 33.3 | 24.00 | 0.00 | 20 | 10.0 | 54.0 | D级 |
| S001 | 潮流买手店A | 3 | 33.3 | 24.00 | 0.00 | 20 | 9.69 | 53.69 | D级 |
+------------+---------------+-------------------+--------------+--------------------+---------------+----------------------+------------------+--------------+---------------+
5 rows selected (1.214 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：least / greatest 控制得分上下限 — 成交能力和客单价维度的得分需要 least(..., 1) × 满分 来确保不超过满分；退货控制维度需要 greatest(0, ...) 确保得分不为负数。遗漏这些边界限制会导致得分溢出，最终信用分超过 100 或为负数。
坑2：cross join platform_avg 确保每行都能拿到平台均值 — platform_avg 是一个单行单列的子查询结果，使用 cross join 将其附加到主查询的每一行上，从而在计算客单价得分时每行都能引用 platform_avg_amount。如果改用子查询直接写在计算公式里，虽然结果相同但可读性变差，且部分 SQL 引擎会因相关子查询导致性能问题。
坑3：nullif 防除零 — 退货率计算的分母 completed_orders 可能为 0（新卖家尚无已完成订单），客单价得分的分母 platform_avg_amount 在极端情况下也可能为 0。必须用 nullif(分母, 0) 将除零转换为 NULL，外围的 coalesce / round 自然处理 NULL，避免直接报错。
五、知识点总结
+-----------------------------+---------------------------------------------------------------------+
| 考点 | 说明 |
+-----------------------------+---------------------------------------------------------------------+
| `case when` 条件聚合 | 按订单状态筛选统计，`count(case when ...)` 等价于条件计数 |
| `least` / `greatest` 边界函数 | 控制得分的上下限，替代多层 `case when` 边界判断 |
| `cross join` 单行维度表 | 将全局统计值附加到每行，便于统一计算公式 |
| `nullif` + `coalesce` 防除零 | `nullif(x, 0)` 返回 NULL 避免除法报错，`coalesce` 兜底 NULL |
| 多表 `left join` | 保留主表全部卖家，未匹配的用 `coalesce` 补 0 |
+-----------------------------+---------------------------------------------------------------------+
六、建表语句和数据插入
点击展开 DDL & DML
create table t5_seller_info (
seller_id string,
seller_name string,
register_date string,
verified string
);
insert into t5_seller_info values
('S001', '潮流买手店A', '2024-01-15', 'yes'),
('S002', '球鞋之家', '2024-03-20', 'yes'),
('S003', '街头潮流馆', '2024-06-10', 'no'),
('S004', 'SneakerWorld', '2024-09-05', 'yes'),
('S005', '潮品汇', '2024-11-01', 'yes');
create table t5_order_record (
order_id string,
seller_id string,
buyer_id string,
order_time string,
amount int,
status string
);
insert into t5_order_record values
('ORD101', 'S001', 'U501', '2025-03-01', 3500, 'completed'),
('ORD102', 'S001', 'U502', '2025-03-05', 2200, 'completed'),
('ORD103', 'S001', 'U503', '2025-03-10', 5800, 'completed'),
('ORD104', 'S001', 'U504', '2025-03-15', 1500, 'cancelled'),
('ORD105', 'S002', 'U505', '2025-03-02', 4200, 'completed'),
('ORD106', 'S002', 'U506', '2025-03-08', 3100, 'completed'),
('ORD107', 'S003', 'U507', '2025-03-03', 1800, 'completed'),
('ORD108', 'S003', 'U508', '2025-03-12', 2500, 'completed'),
('ORD109', 'S004', 'U509', '2025-03-06', 6800, 'completed'),
('ORD110', 'S004', 'U510', '2025-03-14', 7200, 'completed'),
('ORD111', 'S004', 'U511', '2025-03-20', 4300, 'completed'),
('ORD112', 'S005', 'U512', '2025-03-11', 2100, 'completed');
create table t5_return_record (
return_id string,
order_id string,
seller_id string,
return_time string,
reason string,
return_status string
);
insert into t5_return_record values
('RT001', 'ORD103', 'S001', '2025-03-18', '尺码不符', 'completed'),
('RT002', 'ORD110', 'S004', '2025-03-22', '商品瑕疵', 'completed'),
('RT003', 'ORD111', 'S004', '2025-03-25', '假货质疑', 'pending');
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
