基于SCD Type 2表的SQL Server近12个月末库存查询求助

问题背景 #

现有一张库存快照表（SCD Type 2结构），需要提取过去12个月每个月末的库存数据，部分月份无快照记录时需按规则填充（如无数据则取0）。

表结构 #

预期输出 #

解决方案 #

核心思路：先生成过去12个月的月末日期列表，再为每个月末日期匹配该日期之前最新的库存记录，无匹配记录时填充0。

通用SQL实现 #

WITH 
-- 1. 生成过去12个月的月末日期序列
month_end_dates AS (
    SELECT 
        LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL n MONTH)) AS month_end_date
    FROM 
        UNNEST(GENERATE_ARRAY(0, 11)) n
),
-- 2. 为每条库存记录标记所属的产品-供应商组合，并按日期排序
ranked_inventory AS (
    SELECT 
        Date,
        Product,
        Supplier,
        `Stock on Hand`,
        ROW_NUMBER() OVER (PARTITION BY Product, Supplier ORDER BY Date DESC) AS rn
    FROM 
        inventory_table
)
-- 3. 关联日期序列与库存数据，取每个月末之前的最新库存
SELECT 
    FORMAT_DATE('%Y%m%d', med.month_end_date) AS Date,
    ri.Product,
    ri.Supplier,
    COALESCE(ri.`Stock on Hand`, 0) AS `Stock on Hand`
FROM 
    month_end_dates med
LEFT JOIN LATERAL (
    SELECT 
        Product, Supplier, `Stock on Hand`
    FROM 
        ranked_inventory ri
    WHERE 
        PARSE_DATE('%Y%m%d', ri.Date) <= med.month_end_date
        AND ri.Product = '19789063' -- 若需所有产品，可去掉此条件并关联产品维度表
        AND ri.Supplier = '152254' -- 若需所有供应商，可去掉此条件并关联供应商维度表
    ORDER BY 
        ri.Date DESC
    LIMIT 1
) ri ON TRUE
ORDER BY 
    med.month_end_date DESC;

代码说明 #

1. 生成月末日期序列：用GENERATE_ARRAY生成0到11的数字，对应过去12个月，通过LAST_DAY获取每个月的最后一天。
2. 排序库存记录：用ROW_NUMBER()按产品-供应商分组，按日期倒序排序，方便后续取最新记录。
3. 关联匹配：通过LATERAL JOIN为每个月末日期找到最近的库存记录，用COALESCE处理无记录的情况，填充0。
4. 格式转换：将日期格式转为YYYYMMDD格式，与原表保持一致。

适配不同数据库的调整 #

• 若使用MySQL：替换GENERATE_ARRAY为递归CTE生成日期序列，PARSE_DATE替换为STR_TO_DATE。
• 若使用SQL Server：GENERATE_ARRAY替换为递归CTE，LAST_DAY替换为EOMONTH，FORMAT_DATE替换为FORMAT。

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