如何实现含无交易日期的多维度每日库存累计余额SQL查询

解决思路 #

• 生成覆盖查询时间区间的完整日期序列，确保无交易日期也能被纳入统计
• 提取所有需要统计的维度组合（仓库、产品编码、状态）
• 将日期序列与维度组合做笛卡尔积，左连接每日交易数据，无交易日期的变动量设为0
• 用窗口函数计算累计库存，自动延续无交易日期的余额值

完整SQL代码 #

WITH DateRange AS (
    -- 生成从最早交易日期到当前日期的所有日期
    SELECT CAST(MIN(IPTDAT_0) AS DATE) AS Date_1
    FROM STOJOU
    WHERE IPTDAT_0 > DATEADD(YEAR, -1, GETDATE()) AND ITMREF_0 IN ('10010261','10030333')
    UNION ALL
    SELECT DATEADD(DAY, 1, Date_1)
    FROM DateRange
    WHERE Date_1 < CAST(GETDATE() AS DATE)
),
DimensionCombos AS (
    -- 获取所有需要统计的仓库、产品、状态组合
    SELECT DISTINCT
        STOFCY_0 AS Site_0,
        ITMREF_0 AS Item,
        STA_0 AS Status_0
    FROM STOJOU
    WHERE IPTDAT_0 > DATEADD(YEAR, -1, GETDATE()) AND ITMREF_0 IN ('10010261','10030333')
),
DailyTransactions AS (
    -- 关联日期与维度组合，左连接交易数据补全无交易日期
    SELECT
        dc.Site_0,
        dc.Item,
        dc.Status_0,
        dr.Date_1,
        ISNULL(t.DailyMVT, 0) AS DailyMVT
    FROM DateRange dr
    CROSS JOIN DimensionCombos dc
    LEFT JOIN (
        -- 按日期、维度汇总每日交易变动
        SELECT
            STOFCY_0 AS Site_0,
            ITMREF_0 AS Item,
            STA_0 AS Status_0,
            CAST(IPTDAT_0 AS DATE) AS Date_1,
            SUM(QTYPCU_0) AS DailyMVT
        FROM STOJOU
        WHERE IPTDAT_0 > DATEADD(YEAR, -1, GETDATE()) AND ITMREF_0 IN ('10010261','10030333')
        GROUP BY STOFCY_0, ITMREF_0, STA_0, CAST(IPTDAT_0 AS DATE)
    ) t ON dc.Site_0 = t.Site_0
        AND dc.Item = t.Item
        AND dc.Status_0 = t.Status_0
        AND dr.Date_1 = t.Date_1
)
-- 计算每日累计库存余额
SELECT
    Site_0,
    Item,
    Status_0,
    Date_1,
    DailyMVT,
    SUM(DailyMVT) OVER (PARTITION BY Site_0, Item, Status_0 ORDER BY Date_1) AS RunningTotal
FROM DailyTransactions
ORDER BY Site_0, Item, Status_0, Date_1
OPTION (MAXRECURSION 0);

自定义日期范围说明 #

如果需要指定固定日期范围（而非从最早交易日期到今日），可修改DateRange部分的起始和结束日期，例如：

DateRange AS (
    SELECT CAST('2022-07-01' AS DATE) AS Date_1
    UNION ALL
    SELECT DATEADD(DAY, 1, Date_1)
    FROM DateRange
    WHERE Date_1 < CAST('2022-08-31' AS DATE)
)

内容的提问来源于stack exchange，提问作者Henk Du Toit