如何去除SQL自连接中的产品自身配对与双向重复配对并合并ID为数组？

修正后的SQL代码 #

SELECT 
  order_id,
  ARRAY[product_id, product_id_1] AS product_pair
FROM (
  SELECT 
    t1.order_id,
    t1.product_id,
    t2.product_id_1
  FROM (
    SELECT *, unnest(product_ids) AS product_id 
    FROM orders
  ) t1
  INNER JOIN (
    SELECT *, unnest(product_ids) AS product_id_1 
    FROM orders
  ) t2 ON t1.order_id = t2.order_id
  -- 过滤产品与自身的配对
  WHERE t1.product_id != t2.product_id_1
  -- 只保留单向配对，避免"A-B"和"B-A"重复
  AND t1.product_id < t2.product_id_1
) t3

核心优化点 #

• 剔除自身配对：通过WHERE t1.product_id != t2.product_id_1直接去掉"A-A"这类无效条目。
• 消除双向重复：用t1.product_id < t2.product_id_1确保每个配对只出现一次——比如只会保留"A-B"，自动排除反向的"B-A"。如果产品ID是数字类型，这个逻辑同样生效。
• 合并为数组：利用ARRAY[product_id, product_id_1]把两个产品ID组合成数组；要是想让数组内元素顺序统一，也可以用ARRAY_SORT(ARRAY[product_id, product_id_1])强制排序。

扩展：跨订单去重配对 #

如果需要去掉不同订单里的重复配对，加一层DISTINCT即可：

SELECT DISTINCT
  ARRAY_SORT(ARRAY[product_id, product_id_1]) AS unique_product_pair
FROM (
  SELECT 
    t1.order_id,
    t1.product_id,
    t2.product_id_1
  FROM (
    SELECT *, unnest(product_ids) AS product_id 
    FROM orders
  ) t1
  INNER JOIN (
    SELECT *, unnest(product_ids) AS product_id_1 
    FROM orders
  ) t2 ON t1.order_id = t2.order_id
  WHERE t1.product_id != t2.product_id_1
  AND t1.product_id < t2.product_id_1
) t3

内容的提问来源于stack exchange，提问作者Георгий Мальцев