基于FIFO模型从采购价计算销货成本的SQL实现问题

已知条件 #

存在两张业务表：

• sales（销售表）：字段包括 product_id（产品ID）、count（销售数量）、date（销售日期）
• purchases（采购表）：字段包括 product_id（产品ID）、purchase number（采购编号）、count of items in the purchase（采购数量）、cost per unit（单位成本）

任务要求 #

使用*FIFO（先进先出）*模型计算销货成本：采购批次按采购编号升序排序（编号越小批次越早），销售优先从最早批次扣减库存，当前批次库存耗尽后再依次扣减后续批次的库存。

已尝试方案及问题 #

1. 按销售日期和产品ID排序销售数据，计算销售数量的累计窗口总和，但无法实现与具体采购批次的关联，也不清楚批次耗尽后切换至下一批次的逻辑，对应SQL代码如下：

SELECT
    "Product ID" AS product_id,
    "Date" AS date,
    "Sales QTY" AS sales_qty,
    SUM("Sales QTY") OVER (PARTITION BY "Product ID"
        ORDER BY "Date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cummulative_sale_sum
FROM "SALES"
ORDER BY product_id, date

2. 尝试通过采购累计数量与减法逻辑计算，但从第二个月开始计算结果出现错误，对应SQL代码如下：

WITH sales_grouped AS (
    SELECT
        "Product ID",
        SUM("Sales QTY") AS total_sales_qty,
        to_char("Date", 'YYYY-MM') AS year_month
    FROM "SALES"
    GROUP BY "Product ID", to_char("Date", 'YYYY-MM')
),
supply_cumulative AS (
    SELECT
        "Product ID",
        "Supply QTY",
        "#Supply",
        "Costs Per PCS",
        SUM("Supply QTY") OVER (PARTITION BY "Product ID" ORDER BY "#Supply") AS cumulative_qty
    FROM "SUPPLY"
),
matched_sales AS (
    SELECT
        s."Product ID",
        s.year_month,
        s.total_sales_qty,
        c."Supply QTY",
        c."Costs Per PCS",
        c.cumulative_qty - c."Supply QTY" AS prev_cumulative_qty
    FROM sales_grouped s
    JOIN supply_cumulative c
    ON s."Product ID" = c."Product ID"
),
fifo_calculation AS (
    SELECT
        "Product ID",
        year_month,
        "Costs Per PCS",
        CASE
            WHEN total_sales_qty > prev_cumulative_qty THEN
                LEAST(total_sales_qty - prev_cumulative_qty, "Supply QTY")
            ELSE 0
        END AS used_qty
    FROM matched_sales
)
SELECT
    "Product ID",
    year_month,
    SUM(used_qty * "Costs Per PCS") AS total_cost
FROM fifo_calculation
GROUP BY "Product ID", year_month
ORDER BY "Product ID", year_month;

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