Databricks SQL获取热门商品购买组合：现有代码不符预期求修正

问题背景 #

需求为获取最热门的商品购买组合列表，数据表包含itemno（商品编号）、date（交易日期）字段（原代码中用到trans字段，推测表中存在唯一标识每笔交易的trans字段）。当前SQL代码未得到预期输出，示例中商品A的销量应为8，商品组合A,B的销量应为6；同时Databricks SQL暂不支持string_agg函数，当前使用的代码如下：

select itemcombo, count(itemcombo) as sales_ 
from (
  select trans, array_join(array_sort(collect_set(itemno)),',') as itemcombo
  from  a
  group by trans) b
group by itemcombo

问题分析 #

当前代码仅将每笔交易的所有商品合并为完整组合统计，未拆分出交易内所有可能的商品子集（比如一笔交易包含A、B、C，代码仅生成A,B,C这一个组合，但实际需要统计A、B、C、A,B、A,C、B,C这些子组合的出现次数），这是导致单个商品/小组合销量统计错误的核心原因。此外，collect_set会去重同交易内的重复商品，若需求包含统计同交易内重复购买的商品数量，该函数也会造成数据偏差。

解决方案 #

方案1：统计所有商品子集的交易次数（匹配示例需求） #

该方案会生成每笔交易的所有非空商品子集，确保单个商品、两两组合等都被正确统计，同时统一组合的排序格式避免重复计数：

WITH trans_items AS (
  -- 按交易分组，获取该交易的唯一商品列表（若需统计同交易重复购买，改用collect_list）
  SELECT 
    trans,
    array_sort(collect_set(itemno)) AS items
  FROM a
  GROUP BY trans
),
subsets AS (
  -- 递归生成所有非空子集
  SELECT 
    trans,
    array(items[0]) AS subset,
    items,
    1 AS idx
  FROM trans_items
  WHERE size(items) >= 1
  UNION ALL
  SELECT 
    s.trans,
    array_union(s.subset, array(t.items[s.idx])),
    t.items,
    s.idx + 1 AS idx
  FROM subsets s
  JOIN trans_items t ON s.trans = t.trans
  WHERE s.idx < size(t.items)
  UNION ALL
  SELECT 
    trans,
    array(items[idx]) AS subset,
    items,
    idx + 1 AS idx
  FROM trans_items
  WHERE size(items) >= 2
    AND idx BETWEEN 1 AND size(items)-1
),
unique_subsets AS (
  -- 去重同一交易内的重复子集，转换为标准化字符串组合
  SELECT 
    trans,
    array_join(array_sort(subset), ',') AS itemcombo
  FROM subsets
  GROUP BY trans, subset
)
-- 统计每个组合的交易次数并按热度排序
SELECT 
  itemcombo,
  COUNT(DISTINCT trans) AS sales_
FROM unique_subsets
GROUP BY itemcombo
ORDER BY sales_ DESC;

方案2：区分单个商品销量与多商品组合交易次数 #

若需求中商品A的销量8指A的总销售数量，组合A,B的销量6指同时购买A和B的交易次数，可采用以下拆分统计的方式：

-- 统计单个商品的总销售数量
WITH single_item_sales AS (
  SELECT 
    itemno AS itemcombo,
    COUNT(*) AS sales_
  FROM a
  GROUP BY itemno
),
-- 统计多商品组合的交易次数
multi_item_combo AS (
  SELECT 
    array_join(array_sort(collect_set(itemno)), ',') AS itemcombo,
    COUNT(*) AS sales_
  FROM a
  GROUP BY trans
  HAVING size(collect_set(itemno)) >= 2
)
-- 合并两类统计结果并按热度排序
SELECT * FROM single_item_sales
UNION ALL
SELECT * FROM multi_item_combo
ORDER BY sales_ DESC;

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