如何在BigQuery中实现购物篮分析的关联规则及支持度计算

核心思路 #

支持度计算的核心是统计每个商品集（单个/多商品组合）在订单中出现的次数，再除以总订单数。关键在于生成订单内所有合法的商品子集，并避免重复的无序组合。

完整SQL实现 #

以下代码基于示例订单数据集，实现从单个商品到多商品组合的全量支持度计算：

WITH sample_orders AS (
  -- 示例订单数据：order_id为订单ID，product为商品名
  SELECT 'order1' AS order_id, 'colgate' AS product UNION ALL
  SELECT 'order1' AS order_id, 'kellogs' AS product UNION ALL
  SELECT 'order1' AS order_id, 'dettol' AS product UNION ALL
  SELECT 'order2' AS order_id, 'colgate' AS product UNION ALL
  SELECT 'order2' AS order_id, 'kellogs' AS product UNION ALL
  SELECT 'order3' AS order_id, 'dettol' AS product UNION ALL
  SELECT 'order4' AS order_id, 'colgate' AS product UNION ALL
  SELECT 'order5' AS order_id, 'kellogs' AS product UNION ALL
  SELECT 'order5' AS order_id, 'dettol' AS product
),
total_orders AS (
  -- 计算总订单数（支持度的分母）
  SELECT COUNT(DISTINCT order_id) AS total_count
  FROM sample_orders
),
order_product_sets AS (
  -- 递归生成每个订单的所有非空商品子集
  -- 第一步：生成单个商品的子集
  SELECT 
    order_id,
    ARRAY[product] AS product_set,
    1 AS set_size
  FROM sample_orders
  
  UNION ALL
  
  -- 第二步：递归生成多商品组合，避免重复无序组合
  SELECT
    ops.order_id,
    ARRAY_CONCAT(ops.product_set, ARRAY[s.product]) AS product_set,
    ops.set_size + 1 AS set_size
  FROM order_product_sets ops
  JOIN sample_orders s 
    ON ops.order_id = s.order_id
    -- 确保商品未在当前子集中
    AND s.product NOT IN UNNEST(ops.product_set)
    -- 按字典序拼接，避免生成colgate+kellogs和kellogs+colgate这类重复组合
    AND s.product > ops.product_set[OFFSET(ops.set_size - 1)]
)
-- 统计每个商品集的出现次数并计算支持度
SELECT
  ARRAY_TO_STRING(product_set, ',') AS product_set,
  set_size AS combination_size,
  COUNT(DISTINCT order_id) AS occurrence_count,
  ROUND(COUNT(DISTINCT order_id) / total_count, 4) AS support
FROM order_product_sets, total_orders
GROUP BY product_set, set_size, total_count
-- 可选：过滤低支持度或指定组合大小
-- HAVING support >= 0.2 AND set_size <= 3
ORDER BY combination_size, support DESC

关键细节说明 #

• 避免重复组合：通过s.product > ops.product_set[OFFSET(ops.set_size - 1)]确保商品按字典序拼接，同一订单内的商品组合只会生成一次（比如不会同时出现colgate,kellogs和kellogs,colgate）。
• 递归限制：BigQuery默认递归深度为100，若商品数量较多，可通过添加WHERE set_size < N限制最大组合数量（比如WHERE set_size < 3只计算最多2个商品的组合）。
• 性能优化：若订单量极大，可先过滤掉出现次数极少的单个商品（比如HAVING occurrence_count > 10），减少后续组合的计算量。

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