如何用高效SQL计算Item-User数据的用户购买交集与各类统计

问题描述 #

我有一张包含ItemId和UserId的数据表，每一行代表用户购买了某商品，且<ItemId, UserId>是主键。示例数据如下：

ItemId UserId
200 user1
200 user3
200 user4
300 user5
300 user3

需要为每对商品（包括商品自身配对、顺序不同的配对）计算以下指标：

• users(i)：购买商品i的用户数
• users(j)：购买商品j的用户数
• users(i,j)：同时购买商品i和j的用户数
• users(i,~j)：购买商品i但未购买j的用户数
• users(~i,j)：购买商品j但未购买i的用户数

期望输出示例：

i_itemId j_itemId users(i) users(j) users(i,j) users(i,~j) users(~i, j)
200 200 3 3 3 0 0
200 300 3 2 1 2 1
300 300 2 2 2 0 0
300 200 3 2 1 1 2

数据表规模达11GB，仅能通过SQL框架处理，需要高效的解决方案。

高效解决方案（分步骤预聚合） #

针对大数据量，我们避免直接做全表自关联，而是通过预聚合缩小数据规模，再进行关联计算，同时利用主键特性减少不必要的去重操作。

步骤1：预计算每个商品的独立用户数 #

先统计每个商品的购买用户数，生成一个小维度表（商品数通常远小于用户数，这个表会非常小，后续可以用广播关联优化）：

-- 创建临时表存储各商品的用户数
CREATE TEMP TABLE item_user_counts AS
SELECT 
  ItemId AS item_id,
  COUNT(UserId) AS user_count  -- 利用主键特性，无需DISTINCT，每个ItemId下UserId唯一
FROM your_table_name
GROUP BY ItemId;

步骤2：计算商品对的共同用户数 #

这里我们可以通过用户维度的自关联来统计共同用户，同时利用<ItemId,UserId>主键的特性，省去DISTINCT操作提升性能：

-- 优化版：先计算无序商品对，再生成有序对（减少一半计算量）
CREATE TEMP TABLE item_pair_common_users_unordered AS
SELECT 
  LEAST(a.ItemId, b.ItemId) AS i_itemId,
  GREATEST(a.ItemId, b.ItemId) AS j_itemId,
  COUNT(a.UserId) AS users_i_j  -- 每个用户的商品对仅出现一次，无需DISTINCT
FROM your_table_name a
JOIN your_table_name b 
  ON a.UserId = b.UserId 
  AND a.ItemId <= b.ItemId  -- 只处理无序对，减少计算量
GROUP BY LEAST(a.ItemId, b.ItemId), GREATEST(a.ItemId, b.ItemId);

-- 生成所有有序商品对（包括i和j互换的情况）
CREATE TEMP TABLE item_pair_common_users AS
SELECT i_itemId, j_itemId, users_i_j FROM item_pair_common_users_unordered
UNION ALL
SELECT j_itemId, i_itemId, users_i_j FROM item_pair_common_users_unordered;

如果你的SQL引擎对UNION ALL支持很好，这个方式能将商品对的计算量减少50%，非常适合大数据场景。

步骤3：关联计算所有指标 #

将前两步的结果关联，通过简单的算术运算得到衍生指标：

SELECT 
  ip.i_itemId,
  ip.j_itemId,
  ic_i.user_count AS users(i),
  ic_j.user_count AS users(j),
  ip.users_i_j AS users(i,j),
  ic_i.user_count - ip.users_i_j AS users(i,~j),
  ic_j.user_count - ip.users_i_j AS users(~i,j)
FROM item_pair_common_users ip
JOIN item_user_counts ic_i 
  ON ip.i_itemId = ic_i.item_id
JOIN item_user_counts ic_j 
  ON ip.j_itemId = ic_j.item_id
ORDER BY ip.i_itemId, ip.j_itemId;

性能优化要点 #

1. 利用主键特性：去掉所有DISTINCT操作，COUNT(UserId)直接得到用户数，大幅提升计算速度。
2. 无序对转有序对：减少一半的自关联计算量，对于商品数较多的场景效果显著。
3. 广播小表：如果你的SQL引擎支持（比如Spark SQL、Hive），可以将item_user_counts设置为广播表，避免大表关联时的shuffle操作。
4. 临时表存储：将中间结果存在内存临时表中，避免重复计算。

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