如何为库存数据的各类日期缺口生成零库存记录

问题背景 #

inventory_table中，特定地点的产品库存为0时无对应记录，每日更新前一日数据。需要按product_id和inventory_location_id分组，补全缺失日期的0库存记录，同时保留原有数据。

示例场景 #

场景1：存在日期缺口 #

原始表有日期中断（如2023-07-10到2023-06-27之间无记录），需要补全中间日期的0库存。

场景2：开放式日期缺口 #

原始表最新记录到2023-07-03，需要补全到当前日期的0库存。

解决方案 #

核心思路是先生成所有需要覆盖的日期范围，再获取所有唯一的(product_id, inventory_location_id)组合，交叉连接得到所有可能的条目后，左连原表补全库存值：

WITH date_range AS (
  -- 生成需要覆盖的日期范围，可根据需求调整起始日期
  SELECT day
  FROM UNNEST(GENERATE_DATE_ARRAY('2020-01-01', CURRENT_DATE(), INTERVAL 1 DAY)) AS day
),
product_location_pairs AS (
  -- 获取所有存在记录的产品-地点组合
  SELECT DISTINCT product_id, inventory_location_id
  FROM inventory_table
),
all_possible_entries AS (
  -- 交叉连接日期和产品-地点组合，得到所有需要的条目
  SELECT dr.day, plp.product_id, plp.inventory_location_id
  FROM date_range dr
  CROSS JOIN product_location_pairs plp
)
-- 左连原表，用COALESCE将NULL库存转为0
SELECT
  ape.day AS date,
  ape.product_id,
  ape.inventory_location_id,
  COALESCE(it.inventory, 0) AS inventory
FROM all_possible_entries ape
LEFT JOIN inventory_table it
  ON ape.day = it.date
  AND ape.product_id = it.product_id
  AND ape.inventory_location_id = it.inventory_location_id
-- 按需排序
ORDER BY ape.product_id, ape.inventory_location_id, ape.day DESC;

关键说明 #

1. 日期范围控制：GENERATE_DATE_ARRAY的起始和结束日期可根据业务调整，比如只取最近90天，减少不必要的数据量避免超时。
2. 产品-地点组合：用DISTINCT确保只获取有过记录的组合，避免生成大量无意义的条目。
3. 左连接逻辑：必须同时匹配date、product_id、inventory_location_id三个字段，确保每个组合的日期都能正确关联到原表数据。

为什么之前的查询失败 #

• 第一个查询仅将日期表和原表左连，没有把每个产品-地点组合和所有日期绑定，导致缺失的组合不会出现在结果中。
• 第二个查询用LEAD获取下一条记录的日期，只能定位到缺口的起始点，无法生成中间所有日期的条目。

性能优化建议 #

如果交叉连接出现超时：

• 缩小日期范围，比如从CURRENT_DATE() - INTERVAL 180 DAY开始。
• 过滤产品-地点组合，比如只保留最近有记录的组合：

  SELECT DISTINCT product_id, inventory_location_id
  FROM inventory_table
  WHERE date >= CURRENT_DATE() - INTERVAL 90 DAY
  

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