如何按仓库-商品维度限制日期并优化CROSS JOIN计算7天平均库存？

核心需求拆解 #

需要计算各仓库的7天平均库存，规则为：

• 每个商品仅从其在对应仓库的首次入库日期开始计入库存
• 库存为0的日期需保留至当前日期
• 替代性能低下的CROSS JOIN方法

问题1：按仓库-商品维度限制起始日期 #

通过两步实现日期范围过滤：

1. 先聚合得到每个仓库-商品的首次入库日期
2. 将日期表与聚合结果做条件JOIN，仅保留首次入库日及之后的日期

示例代码片段 #

-- 步骤1：计算每个仓库-商品的首次入库日期
WITH product_warehouse_first_date AS (
  SELECT
    warehouse_id,
    product_id,
    MIN(inventory_date) AS first_receive_date
  FROM inventory
  GROUP BY warehouse_id, product_id
),
-- 步骤2：生成每个仓库-商品的有效日期范围
warehouse_product_dates AS (
  SELECT
    pw.warehouse_id,
    pw.product_id,
    d.calendar_date
  FROM product_warehouse_first_date pw
  JOIN datelist d
    ON d.calendar_date >= pw.first_receive_date
    AND d.calendar_date <= CURRENT_DATE -- 限定到当前日期
)

问题2：替代CROSS JOIN的高效方法 #

CROSS JOIN会生成全量的仓库-商品-日期交叉数据，数据量爆炸时性能极差。推荐以下两种优化方案：

方案1：条件JOIN + 窗口函数计算移动平均 #

结合日期范围过滤、库存填充、窗口函数三步实现，全程避免全量交叉：

WITH product_warehouse_first_date AS (
  SELECT
    warehouse_id,
    product_id,
    MIN(inventory_date) AS first_receive_date
  FROM inventory
  GROUP BY warehouse_id, product_id
),
warehouse_product_dates AS (
  SELECT
    pw.warehouse_id,
    pw.product_id,
    d.calendar_date
  FROM product_warehouse_first_date pw
  JOIN datelist d
    ON d.calendar_date >= pw.first_receive_date
    AND d.calendar_date <= CURRENT_DATE
),
-- 填充库存：无记录的日期补0
filled_inventory AS (
  SELECT
    wpd.warehouse_id,
    wpd.product_id,
    wpd.calendar_date,
    COALESCE(i.stock_quantity, 0) AS stock_quantity
  FROM warehouse_product_dates wpd
  LEFT JOIN inventory i
    USING (warehouse_id, product_id, calendar_date)
),
-- 按仓库计算每日总库存
warehouse_daily_total AS (
  SELECT
    warehouse_id,
    calendar_date,
    SUM(stock_quantity) AS total_stock
  FROM filled_inventory
  GROUP BY warehouse_id, calendar_date
),
-- 窗口函数计算7天移动平均（含当天及前6天）
warehouse_daily_avg AS (
  SELECT
    warehouse_id,
    calendar_date,
    AVG(total_stock) OVER (
      PARTITION BY warehouse_id
      ORDER BY calendar_date
      RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW
    ) AS 7_day_avg_stock
  FROM warehouse_daily_total
)
-- 最终输出
SELECT * FROM warehouse_daily_avg
ORDER BY warehouse_id, calendar_date;

方案2：用GENERATE_SERIES替代日期表 #

如果没有维护datelist表，可动态生成日期序列，进一步简化流程：

WITH product_warehouse_first_date AS (
  SELECT
    warehouse_id,
    product_id,
    MIN(inventory_date) AS first_receive_date
  FROM inventory
  GROUP BY warehouse_id, product_id
),
-- 动态生成日期范围（从最早入库日到当前）
all_dates AS (
  SELECT
    date_trunc('day', generate_series(
      (SELECT MIN(first_receive_date) FROM product_warehouse_first_date),
      CURRENT_DATE,
      INTERVAL '1' DAY
    ))::DATE AS calendar_date
),
warehouse_product_dates AS (
  SELECT
    pw.warehouse_id,
    pw.product_id,
    ad.calendar_date
  FROM product_warehouse_first_date pw
  JOIN all_dates ad
    ON ad.calendar_date >= pw.first_receive_date
)
-- 后续库存填充、平均计算同方案1

性能优势说明 #

• 避免全量CROSS JOIN：仅生成仓库-商品的有效日期范围，减少不必要的计算行数
• 窗口函数优化：Starburst对窗口函数的执行效率远高于先聚合再关联的逻辑
• 动态日期生成：无需维护静态日期表，适配日期范围变化场景

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