如何用SQL识别销售记录中的套餐组合（含多套场景）

场景与数据说明 #

门店支持销售预定义产品套餐，但网站系统仅记录单独产品编码，无法直接识别套餐形式的销售。现有两张核心数据：

套餐参考表 set_products #

记录各套餐包含的产品编码及对应数量，示例数据如下：

WITH ReferenceTable AS (
  SELECT 'set123' AS set_products, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 'set123' AS set_products, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemZ' AS product_id, 1 AS quantity UNION ALL
  SELECT 'set456' AS set_products, 'itemY' AS product_id, 1 AS quantity
)
SELECT * FROM ReferenceTable

• set123套餐：2个itemA + 1个itemB
• set456套餐：1个itemZ + 1个itemY

销售交易表 #

记录每笔交易的ticket_id、售出产品及数量，示例数据如下：

WITH MyTable AS (
  SELECT 153612 AS ticket_id, 'itemA' AS product_id, 2 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  SELECT 153612 AS ticket_id, 'itemZ' AS product_id, 3 AS quantity UNION ALL

  SELECT 542652 AS ticket_id, 'itemA' AS product_id, 1 AS quantity UNION ALL
  SELECT 542652 AS ticket_id, 'itemB' AS product_id, 1 AS quantity UNION ALL
  
  SELECT 625167 AS ticket_id, 'itemA' AS product_id, 4 AS quantity UNION ALL
  SELECT 625167 AS ticket_id, 'itemB' AS product_id, 2 AS quantity
)
SELECT * FROM MyTable

预期识别结果 #

• 交易153612：售出1套set123，额外单独售出3个itemZ → 前两行标记set123，第三行标记NULL
• 交易542652：产品匹配set123但数量不符 → 全部标记NULL
• 交易625167：售出2套set123 → 两行均标记set123

BigQuery解决方案 #

以下SQL适用于海量数据场景，通过分步计算实现套餐识别：

WITH 
-- 1. 聚合每笔交易中各产品的总销量
ticket_product_totals AS (
  SELECT ticket_id, product_id, SUM(quantity) AS total_quantity
  FROM MyTable
  GROUP BY ticket_id, product_id
),
-- 2. 计算每笔交易中各套餐的最大可匹配套数
ticket_set_candidates AS (
  SELECT 
    t.ticket_id,
    r.set_products,
    MIN(FLOOR(t.total_quantity / r.quantity)) AS max_possible_sets
  FROM ticket_product_totals t
  JOIN ReferenceTable r ON t.product_id = r.product_id
  GROUP BY t.ticket_id, r.set_products
  HAVING max_possible_sets >= 1
),
-- 3. 确定每笔交易最终匹配的套餐（若多套餐冲突，按包含产品数优先选择）
ticket_final_sets AS (
  SELECT 
    ticket_id,
    set_products,
    max_possible_sets
  FROM ticket_set_candidates
  QUALIFY ROW_NUMBER() OVER(PARTITION BY ticket_id ORDER BY (SELECT COUNT(*) FROM ReferenceTable WHERE set_products = r.set_products) DESC) = 1
),
-- 4. 关联销售数据与套餐匹配结果，计算套餐占用的产品数量
ticket_product_set_mapping AS (
  SELECT 
    m.ticket_id,
    m.product_id,
    m.quantity,
    r.set_products,
    s.max_possible_sets * r.quantity AS set_used_quantity
  FROM MyTable m
  LEFT JOIN ticket_final_sets s ON m.ticket_id = s.ticket_id
  LEFT JOIN ReferenceTable r ON s.set_products = r.set_products AND m.product_id = r.product_id
)
-- 5. 最终输出套餐标识
SELECT 
  ticket_id,
  product_id,
  quantity,
  CASE 
    WHEN set_used_quantity >= quantity THEN set_products
    ELSE NULL
  END AS set_identifier
FROM ticket_product_set_mapping
ORDER BY ticket_id, product_id;

方案说明 #

1. ticket_product_totals：先聚合每笔交易的产品总销量，避免重复计算
2. ticket_set_candidates：通过关联套餐表，取套餐内所有产品可整除次数的最小值，得到该套餐的最大可匹配套数
3. ticket_final_sets：处理同一交易匹配多套餐的情况，这里按套餐包含产品数排序优先选择，可根据业务规则调整
4. ticket_product_set_mapping：计算每个产品在套餐中被使用的数量
5. 最终输出：判断当前行销量是否全部属于套餐，是则标记套餐编码，否则标记为单独销售（NULL）

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