MySQL如何关联日期表补全商品变更表缺失的日期及对应数值

核心逻辑 #

你需要先构造所有「商品+门店」对应的全量连续日期骨架，再用窗口函数填充缺失的数值，具体分4步走：

• 第一步：提取所有商品和门店的唯一组合，避免后续补全日期时漏了某个门店的某个商品维度
• 第二步：生成全量日期骨架，把第一步得到的唯一组合和日期维度表做笛卡尔关联，得到每一个「商品+门店」对应的所有连续日期
• 第三步：左关联原始变更表，把全量日期骨架和原商品变更表按name、shop、date三个字段左关联，有变更记录的日期会带出对应amount，无变更的日期amount为null
• 第四步：填充空值的数量，用支持忽略空值的窗口函数，取每个「商品+门店」分区下，当前日期之前最近一次非空的amount值填充即可

WITH distinct_sku_shop AS (
    -- 提取所有商品+门店的唯一组合
    SELECT DISTINCT name, shop FROM 商品变更表
),
full_date_frame AS (
    -- 生成商品+门店+全量日期的骨架
    SELECT a.name, a.shop, b.date
    FROM distinct_sku_shop a
    CROSS JOIN 日期维度表 b
    -- 可在此处加日期范围过滤，比如b.date between '2021-01-01' and '2023-12-31'
),
join_original AS (
    -- 左关联原始变更表
    SELECT 
        f.name,
        f.shop,
        f.date,
        t.amount
    FROM full_date_frame f
    LEFT JOIN 商品变更表 t 
        ON f.name = t.name 
        AND f.shop = t.shop 
        AND f.date = t.date
)
-- 填充空值，得到最终结果
SELECT 
    name,
    shop,
    date,
    LAST_VALUE(amount IGNORE NULLS) OVER(
        PARTITION BY name, shop 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS amount
FROM join_original
ORDER BY name, shop, date;

如果你的数据库不支持LAST_VALUE的IGNORE NULLS参数（比如低版本MySQL），可以用分组标记法替代：

-- 前面的CTE逻辑和上面一致，仅修改最后一步的空值填充逻辑
SELECT 
    name,
    shop,
    date,
    MAX(amount) OVER(PARTITION BY name, shop, group_flag) AS amount
FROM (
    SELECT 
        *,
        SUM(CASE WHEN amount IS NOT NULL THEN 1 ELSE 0 END) OVER(
            PARTITION BY name, shop 
            ORDER BY date
        ) AS group_flag
    FROM join_original
) t

• 日期维度表的日期范围需要覆盖你所需的业务查询范围，避免出现日期缺失
• 如果需要补全的日期早于该商品在对应门店的第一条变更记录，可在最后一步用COALESCE函数把空值设置为默认值（比如0）
• 若你的商品变更表中amount存储的是变动增量而非当前总存量，需要先计算累计存量再做填充

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