基于StockChanges表统计SQL Server 2014指定日期区间实时库存数

表结构与需求回顾 #

Product表 #

ID     Name
--     --------
1      Product 1
2      Product 2
2      Product 3

StockChanges表 #

ID     ProductID     Date         InStock
---    --------      --------     -----------
1      1             1/1/2020     1
2      1             1/2/2020     0
3      1             1/5/2020     1
5      2             1/1/2020     1
6      2             1/3/2020     0
7      2             1/4/2020     1

需要统计2020-01-01至2020-01-05区间内每日的实时库存总数，规则为：对每个日期，取每个产品在该日期或之前的最新库存变动记录，求和所有产品的InStock值，预期结果：

Day            InStockCount
----------     ------------
1/1/2020       2
1/2/2020       1
1/3/2020       0
1/4/2020       1
1/5/2020       2

SQL实现代码 #

WITH DateRange AS (
    -- 递归生成指定日期区间内的所有日期
    SELECT CAST('2020-01-01' AS DATE) AS Day
    UNION ALL
    SELECT DATEADD(DAY, 1, Day)
    FROM DateRange
    WHERE Day < CAST('2020-01-05' AS DATE)
),
LatestStockPerProductDate AS (
    -- 为每个产品和日期，找到该日期或之前的最新库存变动日期
    SELECT 
        p.ID AS ProductID,
        dr.Day,
        MAX(sc.Date) AS LatestChangeDate
    FROM DateRange dr
    CROSS JOIN Product p
    LEFT JOIN StockChanges sc 
        ON sc.ProductID = p.ID 
        AND sc.Date <= dr.Day
    GROUP BY p.ID, dr.Day
)
-- 关联库存变动表获取对应值，按日期求和
SELECT 
    lspd.Day,
    ISNULL(SUM(sc.InStock), 0) AS InStockCount
FROM LatestStockPerProductDate lspd
LEFT JOIN StockChanges sc 
    ON sc.ProductID = lspd.ProductID 
    AND sc.Date = lspd.LatestChangeDate
GROUP BY lspd.Day
ORDER BY lspd.Day;

逻辑说明 #

1. DateRange：通过递归CTE生成目标日期区间内的所有日期，确保区间内每一天都被统计到。
2. LatestStockPerProductDate：交叉连接Product和DateRange，为每个产品的每一天找到最新的库存变动记录日期（如果存在）。
3. 最后通过LEFT JOIN关联到StockChanges表获取对应的InStock值，使用ISNULL处理无变动记录的产品（默认库存为0），按日期分组求和得到每日库存总数。

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