如何在BigQuery中获取包含套餐表内完全匹配商品组合的交易？

表结构说明 #

套餐表（Bundle Table） #

交易表（Transaction Table） #

需求：筛选出包含任意套餐完整商品组合的交易，例如交易1完全匹配Bundle1，交易2包含Bundle2的完整组合，这两笔交易需要被选出；交易3仅包含Bundle2的单个商品，不应被纳入。

解决方案 #

核心思路是先对套餐和交易的商品进行聚合，通过商品集合的交集匹配来判断交易是否包含完整套餐组合，避免简单JOIN导致的误判。

BigQuery SQL实现 #

WITH bundle_agg AS (
  -- 聚合每个套餐的商品列表及商品数量
  SELECT
    `Bundle Name` AS bundle_name,
    ARRAY_AGG(`Product Name`) AS bundle_items,
    COUNT(*) AS item_count
  FROM `your-project.your-dataset.bundle_table`
  GROUP BY `Bundle Name`
),
transaction_agg AS (
  -- 聚合每个交易的商品列表
  SELECT
    `Transactions ID` AS transaction_id,
    ARRAY_AGG(`Product Name`) AS transaction_items
  FROM `your-project.your-dataset.transaction_table`
  GROUP BY `Transactions ID`
)
-- 匹配包含完整套餐组合的交易
SELECT DISTINCT
  ta.transaction_id,
  ba.bundle_name
FROM transaction_agg ta
JOIN bundle_agg ba
ON ARRAY_LENGTH(ARRAY_INTERSECT(ba.bundle_items, ta.transaction_items)) = ba.item_count

逻辑说明 #

1. 聚合预处理：
   • 对套餐表按套餐名称分组，生成每个套餐的商品数组和对应的商品总数
   • 对交易表按交易ID分组，生成每个交易的商品数组
2. 匹配判断：
   使用ARRAY_INTERSECT函数计算套餐商品和交易商品的交集，若交集的长度等于套餐的商品总数，说明该交易包含了该套餐的所有商品，符合筛选条件。

结果验证 #

• 交易1的商品数组与Bundle1的交集长度为2（等于Bundle1的商品数），会被匹配
• 交易2的商品数组与Bundle2的交集长度为2（等于Bundle2的商品数），会被匹配
• 交易3的商品数组与Bundle2的交集长度为1（小于Bundle2的商品数），不会被匹配

内容的提问来源于stack exchange，提问作者Ervandio Irzky Ardyanta