按先进先出规则编写SQL计算销售订单(SO)的库存可用日期

*** 更新 *** 代码已添加在下方。
我需要为每个销售订单(SO)计算库存可用日期，所有SO遵循先进先出履约规则，履约库存包含当前可用库存、采购订单(PO)的对应数量及PO到货日期。示例如下：

• 当前库存数量：100 ea

PO数量及可用日期如下：

PO#1:  30 ea, available date 2021-06-01
PO#2:  40 ea, available date 2021-06-15
PO#3:  30 ea, available date 2021-07-25
PO#4: 100 ea, available date 2021-08-20

SO需求数量如下：

SO#1: 80 ea
SO#2: 30 ea
SO#3: 10 ea
SO#4: 30 ea
SO#5: 110 ea
SO#6: 100 ea

需编写SQL为每个SO分配可用日期，预期查询结果如下：

SO#1 - available date NOW (80 ea from inventory)
SO#2 - available date 2021-06-01 (20 ea from inventory and 10 ea from PO#1.  SO available date is PO#1 date)
SO#3 - available date 2021-06-01 (10 ea from PO#1.  SO available date is PO#1 date)
SO#4 - available date 2021-06-15 (10 ea from PO#1. 20 ea from PO#2.  SO available date is PO#2 date)
SO#5 - available date 2021-08-20 (20 ea from PO#2. 30 ea from PO#3. 60 ea from PO#4.  SO available date is PO#4 date)
SO#6 - available date N.A. (40 ea from PO#4, no more PO and not enough to fulfill SO#6)

我参考Gordon Linoff的建议编写了如下代码，但没能复现Gordon Linoff的左连接方案，目前通过MIN(ETA)得到了正确结果，请问有没有更简洁的实现方案？

CREATE TABLE PO (
PONo VARCHAR(50),
Quantity INT,
ETA DATE)

CREATE TABLE SO (
SONo VARCHAR(50),
Quantity INT,
FillDate DATE)

INSERT INTO PO (PONo, Quantity, ETA) VALUES
('PO0',100, '2021-05-17'), -- PO0对应当前可用库存，ETA为当前日期
('PO1',30, '2021-06-01'),
('PO2',40, '2021-06-15'),
('PO3',30, '2021-07-25'),
('PO4',100,'2021-08-20')

INSERT INTO SO (SONo, Quantity) VALUES
('SO1',80),
('SO2',30),
('SO3',10),
('SO4',30),
('SO5',110),
('SO6',100)


SELECT SONo, Quantity, MIN(ETA) SOAvailDate FROM (
SELECT SO.SoNo, SO.Quantity, PO.PONo, PO.ETA FROM 
(SELECT SO.*, SUM(Quantity) OVER (ORDER BY SO.SONo) as SORunning  FROM SO) AS SO
LEFT JOIN
(SELECT PO.*, SUM(Quantity) OVER (ORDER BY PO.PONo) as PORunning  FROM PO) AS PO
ON SO.SORunning <= PO.PORunning ) AS A
GROUP BY SONO, Quantity

你原来的逻辑已经是正确的FIFO匹配逻辑，我们可以通过以下调整实现更简洁、更符合预期输出的版本：

1. 用CTE替代嵌套子查询，可读性更好
2. 补全库存不足时返回N.A.的逻辑
3. 新增当前库存显示为NOW的适配

优化后的通用SQL代码如下：

WITH SO_Running AS (
    SELECT 
        SONo,
        Quantity,
        SUM(Quantity) OVER (ORDER BY SONo) AS SORunEnd,
        SUM(Quantity) OVER (ORDER BY SONo) - Quantity AS SORunStart
    FROM SO
),
PO_Running AS (
    SELECT 
        PONo,
        Quantity,
        ETA,
        SUM(Quantity) OVER (ORDER BY PONo) AS PORunEnd,
        SUM(Quantity) OVER (ORDER BY PONo) - Quantity AS PORunStart,
        SUM(Quantity) OVER () AS TotalStock
    FROM PO
)
SELECT 
    s.SONo,
    s.Quantity,
    CASE 
        WHEN s.SORunEnd > MAX(p.TotalStock) THEN 'N.A.'
        WHEN MIN(p.ETA) = '2021-05-17' THEN 'NOW'
        ELSE MIN(p.ETA) 
    END AS SOAvailDate
FROM SO_Running s
LEFT JOIN PO_Running p 
    ON s.SORunEnd > p.PORunStart AND s.SORunStart < p.PORunEnd
GROUP BY s.SONo, s.Quantity
ORDER BY s.SONo

如果你的数据库支持QUALIFY语法（如Snowflake、BigQuery、Spark SQL），可以进一步简化，省去GROUP BY步骤：

WITH SO_Running AS (
    SELECT 
        SONo,
        Quantity,
        SUM(Quantity) OVER (ORDER BY SONo) AS SORunEnd
    FROM SO
),
PO_Running AS (
    SELECT 
        PONo,
        ETA,
        SUM(Quantity) OVER (ORDER BY PONo) AS PORunEnd,
        SUM(Quantity) OVER () AS TotalStock
    FROM PO
)
SELECT 
    s.SONo,
    s.Quantity,
    CASE 
        WHEN s.SORunEnd > p.TotalStock THEN 'N.A.'
        WHEN p.ETA = '2021-05-17' THEN 'NOW'
        ELSE p.ETA 
    END AS SOAvailDate
FROM SO_Running s
LEFT JOIN PO_Running p ON s.SORunEnd <= p.PORunEnd
QUALIFY ROW_NUMBER() OVER (PARTITION BY s.SONo ORDER BY p.PORunEnd) = 1
ORDER BY s.SONo

以上代码输出结果完全匹配预期要求。

内容的提问来源于stack exchange，提问作者YellowLarry