补全缺失日期值：按Item ID统计缺货天数及具体日期

嘿，这个库存日期补全的需求我太熟了！之前帮电商团队处理过类似的统计，咱们一步步来搞定它～

首先得明确：咱们需要先找出每个Item ID的完整缺货时间段（从缺货起始日，到下一次库存恢复的前一天；如果到现在还缺货，就到你指定的截止日），然后把这个时间段里的每一天都补全出来，之后统计天数和日期就简单了。

下面分两种常用场景给你方案：

方案1：用SQL批量处理（适合数据库存储的情况） #

假设你的库存表叫inventory_changes，字段是item_id（商品ID）、change_date（库存变化日期）、stock_qty（库存数量，0代表缺货）。我们可以用窗口函数+递归CTE来生成完整日期：

步骤1：标记每个缺货记录的结束日期 #

先通过LEAD()窗口函数，拿到同一个商品下的下一次库存变化日期，这样就能算出当前缺货时间段的结束日：

WITH item_stock_changes AS (
    SELECT 
        item_id,
        change_date,
        stock_qty,
        -- 拿到同商品下的下一条库存变化日期
        LEAD(change_date) OVER (PARTITION BY item_id ORDER BY change_date) AS next_change_date
    FROM inventory_changes
),
outage_periods AS (
    SELECT 
        item_id,
        change_date AS outage_start,
        -- 如果是最后一条缺货记录（没有下一次变化），用当前日期；否则用下一次变化日减1天
        COALESCE(DATE_SUB(next_change_date, INTERVAL 1 DAY), CURDATE()) AS outage_end
    FROM item_stock_changes
    WHERE stock_qty = 0 -- 筛选出缺货的记录
)

步骤2：生成日期序列并补全缺货日期 #

接下来用递归CTE生成覆盖所有缺货时间段的日期序列，再和缺货时间段关联，把每一天都列出来：

-- 接上面的CTE，继续写
SELECT 
    op.item_id,
    date_generator.date AS outage_date
FROM outage_periods op
JOIN (
    -- 生成日期序列（MySQL 8.0+支持递归CTE，其他数据库写法类似，比如PostgreSQL用generate_series）
    WITH RECURSIVE date_seq AS (
        SELECT MIN(outage_start) AS date FROM outage_periods
        UNION ALL
        SELECT DATE_ADD(date, INTERVAL 1 DAY) FROM date_seq
        WHERE date < (SELECT MAX(outage_end) FROM outage_periods)
    )
    SELECT date FROM date_seq
) date_generator ON date_generator.date BETWEEN op.outage_start AND op.outage_end
ORDER BY op.item_id, date_generator.date;

统计缺货天数和日期列表 #

有了补全后的日期表，统计就很简单了：

-- 统计每个商品的缺货天数和具体日期
SELECT 
    item_id,
    COUNT(outage_date) AS total_outage_days,
    GROUP_CONCAT(outage_date ORDER BY outage_date SEPARATOR ', ') AS outage_dates
FROM (
    -- 这里放上面补全日期的查询语句
) full_outage_dates
GROUP BY item_id;

方案2：用Excel手动/半自动化处理（适合小数据集） #

如果你的数据在Excel里，可以这么操作：

• 整理缺货时间段：把每个商品的缺货起始日列出来，然后找到同商品下的下一条库存记录日期，减1天作为缺货结束日（如果是最后一条缺货记录，就填当前日期）。
• 填充日期序列：在缺货起始日的单元格下方，右键选择「序列」，设置终止值为缺货结束日，就能自动生成这段时间的所有日期。
• 统计：用COUNTIF统计每个商品的缺货天数，用TEXTJOIN函数把同商品的缺货日期拼接成列表（需要Excel 2019及以上版本）。

注意事项 #

• 如果你的库存表中，缺货的定义不是stock_qty=0，记得修改筛选条件（比如stock_qty < 安全库存阈值）。
• 对于最后一条仍处于缺货状态的记录，结束日可以根据实际需求调整（比如用当月最后一天，而不是当前日期）。

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