如何用SQL从交易表统计同客户下热门商品配对及出现次数

这个需求完全可以通过纯SQL实现，不需要借助其他技术方案，核心思路是通过自关联生成同一客户下的不重复商品两两组合，再聚合统计出现次数。

实现逻辑 #

• 假设存储交易数据的表名为customer_purchases，首先过滤掉示例中残留的表头测试行（Customer email值为First的脏数据，生产环境无该类数据可跳过这步）
• 对同一张表做自连接，关联条件为同一客户，同时增加约束t1.item < t2.item，避免出现(112,111)这类逆序重复组合，也能排除商品和自身配对的无效数据
• 按生成的两个商品字段分组，统计不同客户的数量，就是对应商品组合的出现次数

通用SQL代码（兼容绝大多数数据库：MySQL、PostgreSQL、SQL Server等） #

SELECT
  t1.item AS item1,
  t2.item AS item2,
  COUNT(DISTINCT t1.`Customer email`) AS `number of occurrences`
FROM customer_purchases t1
INNER JOIN customer_purchases t2
  ON t1.`Customer email` = t2.`Customer email`
  AND t1.item < t2.item
WHERE t1.`Customer email` != 'First' -- 过滤示例脏数据，生产可删除
GROUP BY t1.item, t2.item
ORDER BY `number of occurrences` DESC, item1, item2;

结果校验 #

基于你给出的示例数据运行上述代码，会完全匹配预期结果：

• 客户a生成组合：(111,112)、(111,113)、(112,113)
• 客户b生成组合：(111,112)
• 客户c生成组合：(110,111)、(110,113)、(111,113)
  聚合后111+112、111+113各出现2次，其余组合各出现1次，和预期输出完全一致。

优化提示 #

• 如果数据量达到百万级以上，可以给表创建(Customer email, item)的联合索引，能大幅提升自关联的查询效率
• 如果你使用的是大数据数仓引擎（Hive、Spark SQL、Snowflake等），也可以先按客户聚合得到购买商品数组，通过数组内置的组合生成函数炸开所有两两配对后计数，超大数据量下性能比自连接更优，但上述自连接写法是所有SQL环境都通用的方案。

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