京东大数据面试SQL-仓库库存预警

数据仓库晨曦

发布于 2026-06-23 17:29:13

870

文章被收录于专栏：数据仓库技术数据仓库技术

一、题目背景

这道题来自京东物流仓储部门的数据分析岗面试。京东在全国有数百个仓库，每个 SKU 在不同仓库的库存水平不同。仓储运营人员每天需要一份"库存预警报表"，快速定位哪些商品需要从其他仓库调拨或紧急补货。

业务场景：当 当前库存小于安全库存 时，系统自动触发补货流程。但不同紧缺程度对应不同的响应级别——断货需要紧急采购，严重不足需要调拨，普通补货走常规流程。

二、题目

现有一张商品库存表 t2_jd_warehouse_stock，记录了各仓库中商品的当前库存量和安全库存阈值。请查询出所有需要补货的商品，并按照库存紧张程度进行分类。

t2_jd_warehouse_stock 表

+-------------+---------------+----------------+---------------+
| product_id  | warehouse_id  | current_stock  | safety_stock  |
+-------------+---------------+----------------+---------------+
| P001        | WH001         | 50             | 100           |
| P001        | WH002         | 120            | 100           |
| P002        | WH001         | 30             | 80            |
| P002        | WH002         | 0              | 50            |
| P003        | WH001         | 200            | 150           |
| P003        | WH002         | 45             | 60            |
| P004        | WH001         | 10             | 20            |
| P004        | WH003         | 95             | 80            |
+-------------+---------------+----------------+---------------+

分类规则：

当前库存等于 0 → 断货
当前库存小于安全库存的 50% → 严重不足
当前库存小于安全库存（且不符合以上） → 需要补货

期望输出：product_id, warehouse_id, current_stock, safety_stock, 库存状态，按紧急程度从高到低排序。

三、思路分析

本题核心是两步：筛选 + 分类。

筛选：WHERE current_stock < safety_stock 过滤出需要补货的记录。注意 P001-WH002（库存 120 大于安全 100）和 P003-WH001（库存 200 大于安全 150）这些库存充足的不在结果中
分类：CASE WHEN 按优先级从高到低判断——先判断是否断货（等于0），再判断严重不足（低于50%），其余归为普通补货
排序：同样用 CASE WHEN 在 ORDER BY 中赋值权重，断货=1、严重不足=2、需要补货=3

维度	评分
题目难度	⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

四、逐步推导

步骤1：过滤需要补货的记录

先从全表中筛选出库存低于安全库存的记录。

SELECT
    product_id,
    warehouse_id,
    current_stock,
    safety_stock
FROM t2_jd_warehouse_stock
WHERE current_stock < safety_stock;

执行结果（5 条记录需要补货，P001-WH002 和 P003-WH001 被过滤掉）：

+-------------+---------------+----------------+---------------+
| product_id  | warehouse_id  | current_stock  | safety_stock  |
+-------------+---------------+----------------+---------------+
| P001        | WH001         | 50             | 100           |
| P002        | WH001         | 30             | 80            |
| P002        | WH002         | 0              | 50            |
| P003        | WH002         | 45             | 60            |
| P004        | WH001         | 10             | 20            |
+-------------+---------------+----------------+---------------+
5 rows selected (7.038 seconds)

步骤2：用 CASE WHEN 添加库存状态列

在筛选结果上增加分类逻辑。关键点：CASE WHEN 按顺序判断，一旦满足条件就不再往下走，所以要把最严格的条件放最前面。

SELECT
    product_id,
    warehouse_id,
    current_stock,
    safety_stock,
    CASE
        WHEN current_stock = 0THEN'断货'
        WHEN current_stock < safety_stock * 0.5THEN'严重不足'
        ELSE'需要补货'
    ENDAS stock_status
FROM t2_jd_warehouse_stock
WHERE current_stock < safety_stock;

执行结果：

+-------------+---------------+----------------+---------------+---------------+
| product_id  | warehouse_id  | current_stock  | safety_stock  | stock_status  |
+-------------+---------------+----------------+---------------+---------------+
| P001        | WH001         | 50             | 100           | 需要补货          |
| P002        | WH001         | 30             | 80            | 严重不足          |
| P002        | WH002         | 0              | 50            | 断货            |
| P003        | WH002         | 45             | 60            | 需要补货          |
| P004        | WH001         | 10             | 20            | 需要补货          |
+-------------+---------------+----------------+---------------+---------------+
5 rows selected (0.326 seconds)(dwsql.com)

验证分类逻辑：

P002-WH002：current_stock=0 → CASE 第一个条件命中 → 断货 ✅
P001-WH001：50 小于 100×0.5=50？不满足（50 不小于 50），跳过；第二个条件 50 小于 50？否 → 走了 ELSE → 等等，这里有问题！

⚠️ 边界问题：P001-WH001 的当前库存 50 刚好等于安全库存 100 的 50%，不满足"小于 50%"，被归为"需要补货"而非"严重不足"。这取决于业务定义——如果你认为"等于 50% 也算严重不足"，应用 <= 替换 <：WHEN current_stock <= safety_stock * 0.5 THEN '严重不足'。

步骤3：按紧急程度排序

直接用 CASE WHEN 给每种状态赋数值权重，ORDER BY 按权重升序。

SELECT
    product_id,
    warehouse_id,
    current_stock,
    safety_stock,
    CASE
        WHEN current_stock = 0THEN'断货'
        WHEN current_stock < safety_stock * 0.5THEN'严重不足'
        ELSE'需要补货'
    ENDAS stock_status
FROM t2_jd_warehouse_stock
WHERE current_stock < safety_stock
ORDERBY
    CASE
        WHEN current_stock = 0THEN1
        WHEN current_stock < safety_stock * 0.5THEN2
        ELSE3
    END;

最终结果：

+-------------+---------------+----------------+---------------+---------------+
| product_id  | warehouse_id  | current_stock  | safety_stock  | stock_status  |
+-------------+---------------+----------------+---------------+---------------+
| P002        | WH002         | 0              | 50            | 断货            |
| P002        | WH001         | 30             | 80            | 严重不足          |
| P001        | WH001         | 50             | 100           | 需要补货          |
| P003        | WH002         | 45             | 60            | 需要补货          |
| P004        | WH001         | 10             | 20            | 需要补货          |
+-------------+---------------+----------------+---------------+---------------+
5 rows selected (0.964 seconds)(dwsql.com)

断货排第一，严重不足次之，普通补货最后。运营人员从上往下处理即可。

4 严格的case when 写法

⚠️ 因为case when 是短路求值，通常我的写法是 case when 中所有条件不重不漏，严格唯一，这样能保证逻辑严密和防止语法漏洞

SELECT
    product_id,
    warehouse_id,
    current_stock,
    safety_stock,
    CASE
        WHEN current_stock > 0AND current_stock < safety_stock * 0.5THEN'严重不足'--注意这里current_stock > 0，避免把current_stock=0的记录也捕获到
        WHEN current_stock = 0THEN'断货'
        ELSE'需要补货'
    ENDAS stock_status
FROM t2_jd_warehouse_stock
WHERE current_stock < safety_stock
ORDERBY
    CASE
        WHEN current_stock > 0AND current_stock < safety_stock * 0.5THEN2
        WHEN current_stock = 0THEN1
        ELSE3
    END;

最终结果：

+-------------+---------------+----------------+---------------+---------------+
| product_id  | warehouse_id  | current_stock  | safety_stock  | stock_status  |
+-------------+---------------+----------------+---------------+---------------+
| P002        | WH002         | 0              | 50            | 断货            |
| P002        | WH001         | 30             | 80            | 严重不足          |
| P001        | WH001         | 50             | 100           | 需要补货          |
| P003        | WH002         | 45             | 60            | 需要补货          |
| P004        | WH001         | 10             | 20            | 需要补货          |
+-------------+---------------+----------------+---------------+---------------+
5 rows selected (0.482 seconds)(dwsql.com)

五、常见坑点

坑1：CASE WHEN 的执行顺序

CASE WHEN 是短路求值——从上到下依次判断，命中即停止。所以条件顺序至关重要！必须把最严格的条件（断货 = 0）放最前面。

如果写反了：WHEN current_stock < safety_stock * 0.5 THEN '严重不足' 会把 current_stock=0 的记录也捕获（因为 0 小于任何正数），导致后面的 WHEN current_stock = 0 永远不会执行。最终断货被错误归类为严重不足。

坑2：50% 的边界

< 0.5 和 <= 0.5 在边界值上结果不同。面试时要和面试官确认清楚业务口径。

六、举一反三

增加"库存充足"分类：如果要求输出所有商品（包括库存充足的），可以用 CASE WHEN current_stock >= safety_stock THEN '库存充足' 作为第一个条件，同时去掉 WHERE 子句
按商品维度聚合：如果同一商品在多个仓库都需要补货，只显示最紧急的那个仓库——用 ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY ...) 取排名第一
动态安全库存：实际业务中安全库存 = 日均销量 × 补货周期。可以 JOIN 一张销量统计表，在 CASE WHEN 中使用动态计算的安全库存

七、知识点总结

条件分类 + 排序的标准写法：

最严格的条件放 CASE WHEN 最前面
ORDER BY 用同样的 CASE WHEN 赋数值权重
百分比计算记得 * 100.0 触发浮点除法

过滤不需要的数据，减少中间结果集

八、建表语句和数据插入

CREATE TABLEIFNOTEXISTS t2_jd_warehouse_stock (
    product_id STRING,
    warehouse_id STRING,
    current_stock INT,
    safety_stock INT
);

INSERTINTO t2_jd_warehouse_stock VALUES
('P001', 'WH001', 50, 100),
('P001', 'WH002', 120, 100),
('P002', 'WH001', 30, 80),
('P002', 'WH002', 0, 50),
('P003', 'WH001', 200, 150),
('P003', 'WH002', 45, 60),
('P004', 'WH001', 10, 20),
('P004', 'WH003', 95, 80);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2026-06-07，如有侵权请联系 cloudcommunity@tencent.com 删除

安全