如何基于昨日数据在SQL中补全缺失的库存记录？

核心思路 #

通过全量产品清单（或昨日全量库存数据）左连接今日上报的变动数据，用COALESCE函数优先取今日上报的库存值，无上报数据则沿用昨日库存，最后统一日期为今日。

情况1：已有独立的全量产品目录表 #

假设产品目录表名为product_catalog，历史库存表为inventory_history，今日上报的临时表为today_report，执行以下SQL：

SELECT 
    p.Product,
    COALESCE(tr.Inventory, ih.Inventory) AS Inventory,
    '2022-12-08' AS Date
FROM product_catalog p
-- 关联今日上报的变动数据
LEFT JOIN today_report tr ON p.Product = tr.Product
-- 关联昨日的全量库存数据
LEFT JOIN (
    SELECT Product, Inventory 
    FROM inventory_history 
    WHERE Date = '2022-12-07'
) ih ON p.Product = ih.Product;

情况2：无独立产品目录，用昨日库存表获取全量产品 #

如果没有单独的产品目录，直接从昨日的库存表中提取所有产品：

SELECT 
    ih_yesterday.Product,
    COALESCE(tr.Inventory, ih_yesterday.Inventory) AS Inventory,
    '2022-12-08' AS Date
FROM (
    -- 取昨日全量库存数据
    SELECT Product, Inventory 
    FROM inventory_history 
    WHERE Date = '2022-12-07'
) ih_yesterday
-- 关联今日上报的变动数据
LEFT JOIN today_report tr ON ih_yesterday.Product = tr.Product;

逻辑说明 #

• COALESCE(a, b)：如果a（今日上报的库存）不为空则取a，否则取b（昨日库存）
• 左连接确保所有产品都会被保留，不会因为今日无上报而丢失
• 手动指定Date为今日，统一输出日期

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