假设现有多种商品的订单需求表 DEMO_REQUIREMENT，以及商品的到货队列表 DEMO_ARR_QUEUE，要求按照业务需要，设计一个报表，展示出每种商品的到货队列，并给出每次到货应直接出货多少数量，出货后剩余多少数量，直到某批到货满足订单需求则不再统计该商品的出货计划。

-- 创建商品需求表并插入示例数据
CREATE TABLE demo_requirement (
    item_id INT,
    req_qty INT
);


INSERT INTO demo_requirement VALUES-- 创建商品需求表并插入示例数据
CREATE TABLE demo_requirement (
    item_id INT,
    req_qty INT
);


INSERT INTO demo_requirement VALUES
(1, 60),
(2, 100),
(3, 80),
(4, 90);


-- 创建商品到货队列表并插入示例数据
CREATE TABLE demo_arr_queue (
    arr_id INT,
    item_id INT,
    arr_qty INT
);


INSERT INTO demo_arr_queue VALUES
(10, 1, 60),
(11, 1, 50),
(12, 2, 40),
(13, 2, 60),
(14, 3, 20),
(15, 3, 50),
(16, 3, 50),
(17, 4, 40),
(18, 4, 40);

-- 计算每个商品每次到货时累计到货数量以及对应需求数量（通过连接获取），作为中间结果展示
SELECT
    daq.item_id,
    daq.arr_id,
    daq.arr_qty,
    SUM(daq.arr_qty) OVER (PARTITION BY daq.item_id ORDER BY daq.arr_id) AS cumulative_arrival_qty,
    dr.req_qty
FROM
    demo_arr_queue daq
-- 左连接商品需求表，以便获取每个商品的需求数量
LEFT JOIN demo_requirement dr ON daq.item_id = dr.item_id
ORDER BY
    daq.item_id,
    daq.arr_id;

这个中间结果展示了每个商品每次到货的基本信息（到货批次 ID、到货数量），以及通过窗口函数计算出的累计到货数量，还有通过左连接获取到的对应商品需求数量，方便后续基于这些数据去进一步计算出货量和剩余数量。

步骤2：基于中间结果计算出货量和剩余量

-- 基于前面的中间结果进一步计算出货量和剩余量，展示最终结果
SELECT
    sub.item_id,
    sub.arr_id,
    sub.arr_qty,
    -- 计算出货数量
    CASE
        WHEN sub.cumulative_arrival_qty <= sub.req_qty
        THEN LEAST(sub.arr_qty, sub.req_qty - (sub.cumulative_arrival_qty - sub.arr_qty))
        ELSE 0
    END AS shipment_quantity,
    -- 计算剩余数量
    CASE
        WHEN sub.cumulative_arrival_qty < sub.req_qty
        THEN sub.req_qty - sub.cumulative_arrival_qty
        WHEN sub.cumulative_arrival_qty = sub.req_qty
        THEN 0
        ELSE sub.cumulative_arrival_qty - sub.req_qty
    END AS remaining_quantity
FROM (
    -- 这里是前面计算累计到货数量和获取需求数量的中间结果
    SELECT
        daq.item_id,
        daq.arr_id,
        daq.arr_qty,
        SUM(daq.arr_qty) OVER (PARTITION BY daq.item_id ORDER BY daq.arr_id) AS cumulative_arrival_qty,
        dr.req_qty
    FROM
        demo_arr_queue daq
    LEFT JOIN demo_requirement dr ON daq.item_id = dr.item_id
    ORDER BY
        daq.item_id,
        daq.arr_id
) sub
ORDER BY
    sub.item_id,
    sub.arr_id;

累计到货数量计算

使用窗口函数

运用 SUM 函数结合 OVER 子句来计算每个商品的累计到货数量。具体而言，在对 DEMO_ARR_QUEUE 表进行查询时，通过 PARTITION BY 按照商品 ID（item_id）对数据进行分区，这样就可以针对每个商品独立地进行计算。然后使用 ORDER BY 按照到货批次 ID（arr_id）进行排序，确保累计计算是按照到货的先后顺序进行的。例如：

SUM(arr_qty) OVER (PARTITION BY item_id ORDER BY arr_id) AS cumulative_arrival_qty

• 这个表达式会为每个商品的每一行数据计算出从第一行到当前行的到货数量总和，也就是累计到货数量。

出货数量计算

条件判断逻辑

首先进行一个主要的条件判断，即比较当前商品的累计到货数量（cumulative_arrival_qty
）与订单需求数量（req_qty
）的大小关系。使用 CASE WHEN
 语句来实现：

CASE
    WHEN cumulative_arrival_qty <= req_qty

剩余数量计算

分情况处理

同样使用 CASE WHEN
 语句来处理不同情况。

当累计到货数量小于订单需求数量时，剩余数量就是订单需求数量减去累计到货数量：

CASE
    WHEN cumulative_arrival_qty < req_qty
    THEN req_qty - cumulative_arrival_qty

• 当累计到货数量等于订单需求数量时，剩余数量为 0：

    WHEN cumulative_arrival_qty = req_qty
    THEN 0

• 当累计到货数量大于订单需求数量时，剩余数量为累计到货数量减去订单需求数量：

    ELSE cumulative_arrival_qty - req_qty
END AS remaining_quantity

往期精彩

指标体系建设：什么是虚荣指标？

SQL进阶技巧：如何分析双重职务问题？

其实数据分析，只是在筛选没有准备的人，写给正在求职的你们。。。

SQL进阶技巧：如何求解直接线上最多的点数？

数据科学与SQL：如何利用本福特法则识别财务数据造假？

解锁SQL无限可能：如何利用HiveSQL实现0-1背包问题？