BigQuery大表场景下窗口函数资源超限问题求解

原方案用左连接+ROW_NUMBER()会产生大量中间笛卡尔积，在千万级数据下极易触发资源限制。以下是针对BigQuery优化的三种可行方案：

方案一：预聚合支付日期的有效区间（推荐全量计算场景） #

思路 #

先对支付记录表做预聚合，为每个供应商的每笔支付记录生成生效时间区间：该支付日期作为最新支付日期的生效范围是「上一笔支付日期的次日」到「下一笔支付日期的前日」，最后一笔支付的生效区间则延续到每日供应商表的最大日期。之后只需将每日供应商表与这个预聚合的区间表做范围连接，避免全量笛卡尔积。

代码示例 #

WITH payment_ranges AS (
  SELECT
    supplier_id,
    payment_date AS latest_payment_date,
    -- 当前支付日期的生效起始：上一笔支付的次日
    DATE_ADD(LAG(payment_date) OVER(PARTITION BY supplier_id ORDER BY payment_date), INTERVAL 1 DAY) AS start_date,
    -- 当前支付日期的生效结束：下一笔支付的前日；若为最后一笔，则用每日表的最大日期
    COALESCE(
      DATE_SUB(LEAD(payment_date) OVER(PARTITION BY supplier_id ORDER BY payment_date), INTERVAL 1 DAY),
      (SELECT MAX(report_date) FROM `project.dataset.supplier_daily`)
    ) AS end_date
  FROM `project.dataset.supplier_payments`
  -- 去重同一供应商同一天的重复支付记录，避免无效区间
  GROUP BY supplier_id, payment_date
),
no_payment_suppliers AS (
  -- 补充无支付记录的供应商，其最新支付日期为NULL，生效区间覆盖全量日期范围
  SELECT
    DISTINCT supplier_id,
    NULL AS latest_payment_date,
    (SELECT MIN(report_date) FROM `project.dataset.supplier_daily`) AS start_date,
    (SELECT MAX(report_date) FROM `project.dataset.supplier_daily`) AS end_date
  FROM `project.dataset.supplier_daily`
  WHERE supplier_id NOT IN (SELECT DISTINCT supplier_id FROM `project.dataset.supplier_payments`)
)
SELECT
  d.supplier_id,
  d.report_date,
  COALESCE(r.latest_payment_date, np.latest_payment_date) AS latest_payment_date
FROM `project.dataset.supplier_daily` d
LEFT JOIN payment_ranges r
  ON d.supplier_id = r.supplier_id
  AND d.report_date BETWEEN r.start_date AND r.end_date
LEFT JOIN no_payment_suppliers np
  ON d.supplier_id = np.supplier_id
ORDER BY d.supplier_id, d.report_date;

优势 #

将支付记录从N条压缩为M个区间（M远小于每日表的总行数），范围连接的中间数据量大幅降低，BigQuery对区间匹配的底层优化能进一步提升性能。

方案二：数组聚合+子查询匹配日期 #

思路 #

将每个供应商的所有支付日期按时间排序后聚合成数组，之后对每日供应商表的每条数据，从对应供应商的支付数组中筛选出小于等于当日日期的最大值，即为最新支付日期。BigQuery对数组操作的内存和计算优化能有效减少资源消耗。

代码示例 #

WITH supplier_payment_arrays AS (
  SELECT
    supplier_id,
    -- 按支付日期升序聚合去重后的日期数组
    ARRAY_AGG(DISTINCT payment_date ORDER BY payment_date) AS payment_dates
  FROM `project.dataset.supplier_payments`
  GROUP BY supplier_id
),
supplier_all AS (
  -- 整合所有供应商，无支付记录的供应商数组为空
  SELECT
    DISTINCT d.supplier_id,
    COALESCE(sp.payment_dates, []) AS payment_dates
  FROM `project.dataset.supplier_daily` d
  LEFT JOIN supplier_payment_arrays sp
    ON d.supplier_id = sp.supplier_id
)
SELECT
  d.supplier_id,
  d.report_date,
  -- 从数组中筛选出<=当日日期的最大值
  (SELECT MAX(pd) FROM UNNEST(sa.payment_dates) pd WHERE pd <= d.report_date) AS latest_payment_date
FROM `project.dataset.supplier_daily` d
JOIN supplier_all sa
  ON d.supplier_id = sa.supplier_id
ORDER BY d.supplier_id, d.report_date;

优势 #

避免了大表之间的直接连接，将每个供应商的支付数据压缩为单条数组记录，查询时的子筛选操作在BigQuery中执行效率极高。

方案三：增量更新（适合每日新增数据场景） #

思路 #

如果每日供应商表是按日增量生成的（仅新增当日数据），无需全量计算所有历史数据。只需基于前一日的最新支付日期，结合当日新增的支付记录，更新当日的最新支付日期即可。

代码示例 #

-- 假设已存在增强表`supplier_daily_enhanced`，包含supplier_id、report_date、latest_payment_date字段
MERGE INTO `project.dataset.supplier_daily_enhanced` target
USING (
  SELECT
    d.supplier_id,
    d.report_date,
    -- 取前一日的最新支付日期与当日新增支付日期的最大值
    GREATEST(
      COALESCE(
        (SELECT latest_payment_date FROM `project.dataset.supplier_daily_enhanced` 
         WHERE supplier_id = d.supplier_id AND report_date = DATE_SUB(d.report_date, INTERVAL 1 DAY)),
        NULL
      ),
      COALESCE(
        (SELECT MAX(payment_date) FROM `project.dataset.supplier_payments` 
         WHERE supplier_id = d.supplier_id AND payment_date = d.report_date),
        NULL
      )
    ) AS latest_payment_date
  FROM `project.dataset.supplier_daily` d
  WHERE d.report_date = CURRENT_DATE() -- 替换为当日实际日期
) source
ON target.supplier_id = source.supplier_id AND target.report_date = source.report_date
WHEN NOT MATCHED THEN
  INSERT (supplier_id, report_date, latest_payment_date)
  VALUES (source.supplier_id, source.report_date, source.latest_payment_date)
WHEN MATCHED THEN
  UPDATE SET latest_payment_date = source.latest_payment_date;

优势 #

仅处理当日新增数据，计算量和资源消耗降至最低，适合长期持续维护的场景。

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