如何在PostgreSQL中找出商品集合及数量完全一致的订单

需求说明 #

现有orders表，每条记录对应订单（transaction_id）中的一个商品条目，包含client_id、item_id、quantity字段。需要筛选出商品集合（item_id+对应quantity）完全相同的订单组。

根据示例数据，预期结果为订单组：(1001, 1004)、(1005, 1006)。

解决方案 #

方法1：通过聚合生成订单唯一特征标识 #

思路：将每个订单下的item_id和quantity按固定顺序拼接成唯一字符串，以此作为订单的特征标识，再筛选出标识重复的订单组。

MySQL 实现代码

WITH order_features AS (
    SELECT 
        transaction_id,
        -- 按item_id排序后拼接，避免商品顺序导致标识不一致
        GROUP_CONCAT(CONCAT(item_id, ':', quantity) ORDER BY item_id SEPARATOR '|') AS item_quantity_signature
    FROM orders
    GROUP BY transaction_id
)
SELECT 
    item_quantity_signature,
    GROUP_CONCAT(transaction_id ORDER BY transaction_id) AS matching_orders
FROM order_features
GROUP BY item_quantity_signature
-- 只保留有重复的订单组
HAVING COUNT(*) > 1;

PostgreSQL 实现代码

WITH order_features AS (
    SELECT 
        transaction_id,
        STRING_AGG(CONCAT(item_id, ':', quantity), '|' ORDER BY item_id) AS item_quantity_signature
    FROM orders
    GROUP BY transaction_id
)
SELECT 
    item_quantity_signature,
    STRING_AGG(transaction_id, ', ' ORDER BY transaction_id) AS matching_orders
FROM order_features
GROUP BY item_quantity_signature
HAVING COUNT(*) > 1;

关键细节：

• 必须按item_id排序后拼接，避免同一商品集合因存储顺序不同生成不同标识。
• 用:分隔商品ID与数量、|分隔不同商品，避免拼接歧义（比如item_id"11"+quantity"1"与item_id"1"+quantity"11"不会混淆）。

方法2：通过集合匹配验证（无字符串拼接，更严谨） #

思路：先通过订单的商品总数、总数量快速过滤候选订单，再双向验证两个订单的所有商品条目完全一致。

WITH order_summary AS (
    SELECT 
        transaction_id,
        COUNT(*) AS item_count,
        SUM(quantity) AS total_quantity
    FROM orders
    GROUP BY transaction_id
)
SELECT DISTINCT
    LEAST(a.transaction_id, b.transaction_id) AS order1,
    GREATEST(a.transaction_id, b.transaction_id) AS order2
FROM order_summary a
JOIN order_summary b 
    ON a.transaction_id < b.transaction_id
    AND a.item_count = b.item_count
    AND a.total_quantity = b.total_quantity
-- 验证两个订单的商品条目完全双向匹配
WHERE NOT EXISTS (
    -- 检查a订单是否有b订单没有的商品
    SELECT 1
    FROM orders oa
    LEFT JOIN orders ob 
        ON ob.transaction_id = b.transaction_id
        AND oa.item_id = ob.item_id
        AND oa.quantity = ob.quantity
    WHERE oa.transaction_id = a.transaction_id
      AND ob.item_id IS NULL
    
    UNION ALL
    
    -- 检查b订单是否有a订单没有的商品
    SELECT 1
    FROM orders ob
    LEFT JOIN orders oa 
        ON oa.transaction_id = a.transaction_id
        AND ob.item_id = oa.item_id
        AND ob.quantity = oa.quantity
    WHERE ob.transaction_id = b.transaction_id
      AND oa.item_id IS NULL
);

关键细节：

• 先通过item_count和total_quantity过滤，减少后续验证的计算量。
• 双向左连接验证确保两个订单的商品集合完全一致，无遗漏或多余商品。

示例结果 #

两种方法运行后，都会得到符合预期的匹配订单组：

• 方法1结果：

• 方法2结果：

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