BigQuery查询优化：每日关联销售商品去重与内存超限问题

核心问题分析 #

原代码的两个问题根源：

• 重复倒置记录：自连接时用a.product_id != b.product_id会生成A-B和B-A两种完全重复的组合，导致统计结果冗余。
• 内存超限：全表自连接会产生指数级的中间数据（O(n²)量级），加上全局ORDER BY需要加载大量数据到内存排序，直接触发资源限制。

优化方案 #

优化后的SQL代码 #

SELECT
  DAT_VTE AS DATE,
  product_id,
  product_id_bought_with,
  COUNT(*) AS counting
FROM (
  -- 先按日期+交易ID聚合同订单的商品列表
  SELECT
    DAT_VTE,
    ID_TICKET,
    ARRAY_AGG(product_id) AS products
  FROM `project_id.dataset.table_name`
  GROUP BY DAT_VTE, ID_TICKET
  -- 过滤掉只有单个商品的订单，这类订单无关联销售数据
  HAVING ARRAY_LENGTH(products) > 1
),
-- 在同一订单内生成商品组合，用<确保单向唯一
UNNEST(products) AS product_id
UNNEST(products) AS product_id_bought_with
WHERE product_id < product_id_bought_with
GROUP BY DATE, product_id, product_id_bought_with
-- 按日期+计数排序，比全局排序更省内存
ORDER BY DATE, counting DESC

优化点说明 #

1. 消除重复组合：用product_id < product_id_bought_with替代!=，每个商品组合仅保留一次（比如只存324324-876123，不会生成876123-324324）。
2. 减少中间数据量：先聚合订单内的商品列表，再在订单内部生成组合，避免全表自连接带来的数据爆炸，中间数据量从O(n²)降到O(m*k²)（m是订单数，k是单订单平均商品数），内存占用大幅降低。
3. 优化排序逻辑：将全局ORDER BY counting DESC改为ORDER BY DATE, counting DESC，排序粒度缩小到单日数据，减少内存消耗。

额外优化建议 #

• 分区表改造：如果表数据量极大，把表按DAT_VTE设为日期分区表，查询时可指定日期范围（比如WHERE DAT_VTE BETWEEN '2022-01-01' AND '2022-01-31'），进一步减少扫描和处理的数据量。
• 限制排序范围：如果不需要全局所有结果，可加LIMIT（比如ORDER BY counting DESC LIMIT 100），避免加载全量数据排序。
• 过滤无效订单：提前过滤掉只有单个商品的订单，减少后续处理的数据量。

示例数据验证结果 #

基于你提供的示例数据，执行上述SQL会得到以下无重复的结果：

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