每月库存running total计算：补全无订单月份数据及SQL优化求助

需求：统计仓库中各产品每月的入库库存累计值（running total）。现有SQL查询存在以下问题：无该产品订单的月份，work_area、product_id、quantity、running_total字段均为NULL；期望优化后达到：无订单月份的quantity显示为0，work_area、product_id保持对应固定值，running_total延续上月的累计数值。

当前查询结果 #

期望结果 #

原查询SQL #

;WITH year_order_received_cte AS (
    SELECT Distinct DATEPART(YEAR, date_receive) AS year_order_receive
    FROM  Inv.Ordering_Receive
), all_month_in_year_received_cte AS (
    SELECT DISTINCT MonthNumber, [Year]
    FROM dbO.Dim_Date
    WHERE [Year] in (SELECT year_order_receive FROM year_order_received_cte)
), product_arrived_each_month_cte AS (
    SELECT work_area_id, t1.ordering_id, t2.product_id , date_receive, DATEPART(Month, date_receive) AS month_received, DATEPART(YEAR, date_receive) AS year_received
    , t2.quantity
    FROM Inv.Ordering t1
    INNER JOIN Inv.Ordering_Item t2 ON t2.ordering_id = t1.ordering_id 
    INNER JOIN Inv.Ordering_Receive t3 ON t3.ordering_id=t2.ordering_id
    WHERE received=1            
), inventory_each_month_cte AS (
    SELECT MonthNumber, [Year], work_area_id, product_id, quantity
    , (SELECT SUM(COALESCE(quantity,0)) FROM product_arrived_each_month_cte t2 WHERE t2.month_received <= t1.month_received) AS running_total
    FROM all_month_in_year_received_cte t0 
    LEFT OUTER JOIN product_arrived_each_month_cte t1 ON t1.month_received = t0.MonthNumber AND t1.year_received = t0.[Year]
)
SELECT *
FROM inventory_each_month_cte
ORDER BY MonthNumber

优化后的SQL #

;WITH year_order_received_cte AS (
    SELECT DISTINCT DATEPART(YEAR, date_receive) AS year_order_receive
    FROM Inv.Ordering_Receive
), all_month_in_year_received_cte AS (
    SELECT DISTINCT MonthNumber, [Year]
    FROM dbO.Dim_Date
    WHERE [Year] IN (SELECT year_order_receive FROM year_order_received_cte)
), -- 生成所有需要统计的工作区-产品组合
product_workarea_combo AS (
    SELECT DISTINCT work_area_id, product_id
    FROM Inv.Ordering t1
    INNER JOIN Inv.Ordering_Item t2 ON t2.ordering_id = t1.ordering_id
    INNER JOIN Inv.Ordering_Receive t3 ON t3.ordering_id = t2.ordering_id
    WHERE received = 1
), -- 生成所有月份与产品-工作区的笛卡尔积，确保每个组合都有12个月的记录
all_month_product_combo AS (
    SELECT 
        amiy.MonthNumber,
        amiy.[Year],
        pwc.work_area_id,
        pwc.product_id
    FROM all_month_in_year_received_cte amiy
    CROSS JOIN product_workarea_combo pwc
), -- 计算每月实际入库数量
monthly_quantity AS (
    SELECT
        DATEPART(Month, date_receive) AS month_received,
        DATEPART(YEAR, date_receive) AS year_received,
        work_area_id,
        product_id,
        SUM(quantity) AS monthly_qty
    FROM Inv.Ordering t1
    INNER JOIN Inv.Ordering_Item t2 ON t2.ordering_id = t1.ordering_id
    INNER JOIN Inv.Ordering_Receive t3 ON t3.ordering_id = t2.ordering_id
    WHERE received = 1
    GROUP BY DATEPART(Month, date_receive), DATEPART(YEAR, date_receive), work_area_id, product_id
)
SELECT
    amp.MonthNumber,
    amp.[Year],
    amp.work_area_id AS work_area,
    amp.product_id,
    COALESCE(mq.monthly_qty, 0) AS quantity,
    -- 使用窗口函数计算累计值，自动延续上月数值
    SUM(COALESCE(mq.monthly_qty, 0)) OVER (
        PARTITION BY amp.work_area_id, amp.product_id, amp.[Year]
        ORDER BY amp.MonthNumber
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM all_month_product_combo amp
LEFT JOIN monthly_quantity mq 
    ON amp.MonthNumber = mq.month_received 
    AND amp.[Year] = mq.year_received
    AND amp.work_area_id = mq.work_area_id
    AND amp.product_id = mq.product_id
ORDER BY amp.[Year], amp.work_area_id, amp.product_id, amp.MonthNumber

优化说明 #

1. 生成产品-工作区组合：新增product_workarea_combo CTE，提取所有有入库记录的work_area_id和product_id组合，确保每个组合都能覆盖全年12个月。
2. 笛卡尔积生成全量月份记录：通过all_month_product_combo将全年所有月份与每个产品-工作区组合进行关联，保证无订单月份也有对应记录。
3. 按月聚合入库数量：monthly_quantity CTE按月、年、工作区、产品聚合实际入库数量，避免重复计算。
4. 窗口函数计算累计值：使用SUM() OVER()窗口函数，按工作区、产品、年份分区，按月排序，自动计算累计值，无订单月份会延续上月的累计结果。
5. NULL值处理：用COALESCE将无订单月份的quantity转为0。

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