SQL 多仓发货最优仓库选择：距离+库存多因素排序（SHEIN面试题）
一、题目
SHEIN在全球拥有多个仓库，每个订单需要从有库存的仓库中选择距离收货地址最近的仓库发货。请编写SQL，计算每个订单的最优发货仓库及预计送达时间。
预计送达时间计算公式：距离(km) × 1.5小时 + 拣货1小时
假设有三张表：
t5_orders：订单表，记录订单ID、SKU编码、购买数量和收货国家t5_warehouse_stock：仓库库存表，记录各仓库各SKU的库存数量t5_warehouse_distance：仓库距离表，记录各仓库到各国的距离（公里）
-- t5_orders 订单表
+-----------+---------+------+----------+
| order_id | sku_id | qty | country |
+-----------+---------+------+----------+
| 1001 | SKU001 | 2 | US |
| 1002 | SKU002 | 1 | UK |
| 1003 | SKU003 | 3 | FR |
+-----------+---------+------+----------+
-- t5_warehouse_stock 仓库库存表
+------------+---------+--------+
| warehouse | sku_id | stock |
+------------+---------+--------+
| WH_US | SKU001 | 10 |
| WH_US | SKU002 | 0 |
| WH_UK | SKU001 | 5 |
| WH_UK | SKU002 | 8 |
| WH_UK | SKU003 | 2 |
| WH_FR | SKU003 | 10 |
| WH_FR | SKU001 | 3 |
+------------+---------+--------+
-- t5_warehouse_distance 仓库距离表(km)
+------------+----------+-----------+
| warehouse | country | distance |
+------------+----------+-----------+
| WH_US | US | 200 |
| WH_UK | US | 5500 |
| WH_US | UK | 5800 |
| WH_UK | UK | 150 |
| WH_FR | UK | 400 |
| WH_UK | FR | 500 |
| WH_FR | FR | 100 |
| WH_US | FR | 6200 |
+------------+----------+-----------+
二、思路分析
- 关联三张表：
t5_orders（订单） →t5_warehouse_stock（库存） →t5_warehouse_distance（距离） - 关键过滤：
t5_warehouse_stock.stock >= t5_orders.qty，必须在JOIN时就过滤掉库存不足的仓库 - 使用
ROW_NUMBER()按订单分组、按距离升序排序，取排名为1的记录即为最优仓库 - 计算预计送达时间 =
distance * 1.5 + 1（小时）
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、逐步推导
1. 关联三表，筛选有库存的仓库并按距离排序
-- 将订单与库存表、距离表关联，过滤库存不足的仓库，按订单分组距离升序排名
select o.order_id,
o.sku_id,
o.country,
ws.warehouse,
ws.stock,
wd.distance,
row_number() over (partition by o.order_id order by wd.distance asc) as rn
from t5_orders o
join t5_warehouse_stock ws
on o.sku_id = ws.sku_id
and ws.stock >= o.qty
join t5_warehouse_distance wd
on ws.warehouse = wd.warehouse
and o.country = wd.country
查询结果
+-----------+---------+----------+------------+--------+-----------+-----+
| order_id | sku_id | country | warehouse | stock | distance | rn |
+-----------+---------+----------+------------+--------+-----------+-----+
| 1001 | SKU001 | US | WH_US | 10 | 200 | 1 |
| 1001 | SKU001 | US | WH_UK | 5 | 5500 | 2 |
| 1002 | SKU002 | UK | WH_UK | 8 | 150 | 1 |
| 1003 | SKU003 | FR | WH_FR | 10 | 100 | 1 |
+-----------+---------+----------+------------+--------+-----------+-----+
4 rows selected (0.713 seconds)(https://www.dwsql.com)
说明：订单1001在WH_US和WH_UK都有库存，按距离升序后WH_US排第1；订单1002只有WH_UK满足库存条件（WH_US的SKU002库存为0被过滤）；订单1003只有WH_FR满足库存条件（WH_UK的SKU003库存2 < 需求量3被过滤）。
2. 取最优仓库并计算送达时间
-- 筛选排名为1的记录，计算预计送达时间
select order_id,
sku_id,
country,
warehouse as best_warehouse,
distance as min_distance_km,
round(distance * 1.5 + 1, 1) as estimated_delivery_hours
from (
select o.order_id,
o.sku_id,
o.country,
ws.warehouse,
wd.distance,
row_number() over (partition by o.order_id order by wd.distance asc) as rn
from t5_orders o
join t5_warehouse_stock ws
on o.sku_id = ws.sku_id
and ws.stock >= o.qty
join t5_warehouse_distance wd
on ws.warehouse = wd.warehouse
and o.country = wd.country
) t
where rn = 1
查询结果
+-----------+---------+----------+-----------------+------------------+---------------------------+
| order_id | sku_id | country | best_warehouse | min_distance_km | estimated_delivery_hours |
+-----------+---------+----------+-----------------+------------------+---------------------------+
| 1001 | SKU001 | US | WH_US | 200 | 301.0 |
| 1002 | SKU002 | UK | WH_UK | 150 | 226.0 |
| 1003 | SKU003 | FR | WH_FR | 100 | 151.0 |
+-----------+---------+----------+-----------------+------------------+---------------------------+
3 rows selected (0.715 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：库存过滤的时机不对
必须在与t5_warehouse_stock JOIN时就用 ws.stock >= o.qty 过滤，而非先JOIN再在WHERE中过滤。如果把过滤条件写到外层WHERE，会导致先匹配到库存不足的仓库，再被过滤掉，中间结果膨胀且逻辑不清晰。更重要的是，如果先JOIN距离表再过滤库存，可能会匹配到"有距离数据但库存不足"的仓库，影响ROW_NUMBER的排序结果。
坑2：并列距离时的随机选择
ROW_NUMBER() ... ORDER BY distance ASC 在多个仓库距离相同时，只会随机选取其中一条作为rn=1。如果业务要求并列时需全部保留或按其他规则（如库存量、仓库优先级）进一步排序，应在ORDER BY中增加第二个排序字段，例如 ORDER BY wd.distance ASC, ws.stock DESC。
坑3：库存不足的订单会"消失"
由于使用了INNER JOIN，如果某个订单的SKU在所有仓库都没有足够库存（stock >= qty），该订单不会出现在最终结果中。面试中应主动讨论这一边界情况：如果业务要求即使无库存也要展示订单（标注为"无可发货仓库"），需将t5_orders作为主表，改用LEFT JOIN + COALESCE处理NULL值。
坑4：距离数据缺失导致仓库被排除
t5_warehouse_distance 表不一定覆盖所有"仓库-国家"组合。某个仓库虽然有库存，但如果没有录入该仓库到目标国家的距离，INNER JOIN会将该仓库排除。这实际上是一个隐式的数据完整性检查，面试中可以提出通过LEFT JOIN + 默认距离兜底策略来处理。
五、知识点总结
| 考点 | 说明 |
|---|---|
| ROW_NUMBER() 窗口函数 | 按分区(订单)排序(距离)取Top1，是求"每组最优"的标准解法 |
| JOIN条件的过滤时机 | stock >= qty写在ON子句中，在JOIN阶段就排除无效行，比WHERE更高效且语义更清晰 |
| INNER JOIN vs LEFT JOIN | 库存不足时订单是否保留？INNER JOIN会丢弃无匹配订单，LEFT JOIN保留但需COALESCE处理NULL |
| 多表关联的顺序与优化 | 三表JOIN时先过滤再关联，减少中间结果集大小；距离表应最后JOIN，因为距离数据可能稀疏 |
| 业务公式的表达 | distance * 1.5 + 1 将距离转换为时间，ROUND保留小数位数，体现业务计算能力 |
六、建表语句
点击展开 DDL & DML
-- 订单表：记录每个订单的SKU、数量和收货国家
create table t5_orders (
order_id bigint comment '订单id',
sku_id string comment 'sku编码',
qty int comment '购买数量',
country string comment '收货国家'
) comment '订单表';
-- 仓库库存表：记录各仓库各sku的库存数量
create table t5_warehouse_stock (
warehouse string comment '仓库编码',
sku_id string comment 'sku编码',
stock int comment '库存数量'
) comment '仓库库存表';
-- 仓库距离表：记录各仓库到各国的距离（公里）
create table t5_warehouse_distance (
warehouse string comment '仓库编码',
country string comment '国家',
distance int comment '距离（公里）'
) comment '仓库距离表';
-- 插入订单数据
insert into t5_orders(order_id, sku_id, qty, country) values
(1001, 'SKU001', 2, 'US'),
(1002, 'SKU002', 1, 'UK'),
(1003, 'SKU003', 3, 'FR');
-- 插入仓库库存数据
insert into t5_warehouse_stock(warehouse, sku_id, stock) values
('WH_US','SKU001',10),
('WH_US','SKU002',0),
('WH_UK','SKU001',5),
('WH_UK','SKU002',8),
('WH_UK','SKU003',2),
('WH_FR','SKU003',10),
('WH_FR','SKU001',3);
-- 插入仓库距离数据
insert into t5_warehouse_distance(warehouse, country, distance) values
('WH_US','US',200),
('WH_UK','US',5500),
('WH_US','UK',5800),
('WH_UK','UK',150),
('WH_FR','UK',400),
('WH_UK','FR',500),
('WH_FR','FR',100),
('WH_US','FR',6200);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
