Azure Synapse中计算日期范围内每日库存的SQL查询问题

需求：在Azure Synapse环境中编写SQL查询，计算指定日期范围内每个商品的每日库存数量。起始日期的库存需包含该商品在起始日期及之前所有条目的数量总和，后续日期的库存基于前一日的库存加上当日的变动（无变动则保持前一日数值）。

示例Inventory表 #

期望查询结果 #

现有尝试及问题 #

尝试1：起始库存计算正确，但后续日期结果有误且无每日记录 #

WITH Inventory AS (
    SELECT ItemId, Date, SUM(Qty) AS TotalQuantity
    FROM Inventory
    WHERE Date <= '2023-01-07'
    GROUP BY ItemId, Date
), StartingInventory AS (
    SELECT ItemId, SUM(Qty) AS StartingQuantity
    FROM Inventory
    WHERE DatePhysical < '2023-01-01'
    GROUP BY ItemId
)
SELECT i.ItemId, i.Date, SUM(i.TotalQuantity) + 
COALESCE(s.StartingQuantity, 0) AS DailyTotalInventory
FROM Inventory i
LEFT JOIN StartingInventory s ON i.ItemId = s.ItemId
WHERE i.DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'
GROUP BY i.ItemId, i.Date, s.StartingQuantity
ORDER BY i.ItemId, i.Date

尝试2：起始日期计算正确，但后续日期结果错误且未生成每日记录 #

WITH AllDates AS (
    SELECT DISTINCT d.DatePhysical, i.ItemId
    FROM Inventrans i
    CROSS JOIN (
        SELECT DISTINCT DatePhysical
        FROM InvenTrans
        WHERE DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'
    ) d
), Inventory AS (
    SELECT ItemId, DatePhysical, SUM(Qty) AS TotalQuantity
    FROM InvenTrans
    WHERE DatePhysical <= '2023-01-07'
    GROUP BY ItemId, DatePhysical
), StartingInventory AS (
    SELECT ItemId, SUM(Qty) AS StartingQuantity
    FROM Inventrans
    WHERE DatePhysical < '2023-01-01'
    GROUP BY ItemId
)
SELECT d.DatePhysical, d.ItemId, COALESCE(SUM(i.TotalQuantity), 0) + COALESCE(s.StartingQuantity, 0) AS DailyTotalInventory
FROM AllDates d
LEFT JOIN Inventory i ON d.ItemId = i.ItemId AND d.DatePhysical = i.DatePhysical
LEFT JOIN StartingInventory s ON d.ItemId = s.ItemId
WHERE d.DatePhysical BETWEEN '2023-01-01' AND '2023-01-07'
GROUP BY d.DatePhysical, d.ItemId, s.StartingQuantity
ORDER BY d.ItemId, d.DatePhysical 

正确解决方案 #

要实现需求，需要完成以下核心步骤：

1. 生成指定日期范围内的所有连续日期
2. 获取所有需要统计的商品列表
3. 计算每个商品在每个日期（含起始日前）的累计库存变动
4. 关联日期序列与商品列表，填充每日库存数值

完整SQL查询如下：

-- 定义查询的日期范围
DECLARE @StartDate DATE = '2023-01-01';
DECLARE @EndDate DATE = '2023-01-06';

WITH DateSequence AS (
    -- 生成指定范围内的所有连续日期
    SELECT @StartDate AS Date
    UNION ALL
    SELECT DATEADD(DAY, 1, Date)
    FROM DateSequence
    WHERE Date < @EndDate
),
AllItems AS (
    -- 获取所有唯一商品
    SELECT DISTINCT ItemId
    FROM Inventory
),
InventoryChanges AS (
    -- 按商品和日期汇总库存变动，包含起始日前的记录
    SELECT 
        ItemId,
        Date,
        SUM(Qty) AS DailyChange
    FROM Inventory
    WHERE Date <= @EndDate
    GROUP BY ItemId, Date
),
CumulativeInventory AS (
    -- 计算每个商品从最早日期到每个日期的累计库存
    SELECT
        ai.ItemId,
        ds.Date,
        SUM(ic.DailyChange) OVER (PARTITION BY ai.ItemId ORDER BY ic.Date) AS CumulativeQty
    FROM DateSequence ds
    CROSS JOIN AllItems ai
    LEFT JOIN InventoryChanges ic ON ai.ItemId = ic.ItemId AND ic.Date <= ds.Date
)
-- 填充每日库存，无变动时继承前一日数值
SELECT
    ItemId,
    Date,
    LAST_VALUE(CumulativeQty IGNORE NULLS) OVER (PARTITION BY ItemId ORDER BY Date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Qty
FROM CumulativeInventory
ORDER BY ItemId, Date;

代码说明 #

• DateSequence：递归生成指定起始到结束日期的所有连续日期，确保每个日期都有记录
• AllItems：提取所有商品，避免遗漏无变动的商品
• InventoryChanges：按商品和日期汇总每日库存变动，包含起始日前的所有记录
• CumulativeInventory：计算每个商品到每个日期的累计库存总和
• 最后一步使用LAST_VALUE填充空值，确保无变动日期继承前一日的库存数值

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