去除SQL购物篮商品组合查询结果中的重复组合

你的问题核心是同一商品组合因列顺序不同被视为不同分组，导致重复统计。以下是三种可行的解决方法，按性能优化程度排序：

方法一：从JOIN阶段避免生成重复排列（最推荐） #

直接在JOIN条件中强制SKU的顺序，让每个组合只以一种固定顺序出现，从源头消除重复：

WITH cte AS (
    SELECT 
        a.order_id, 
        a.SKU_number as product_1, 
        b.SKU_number as product_2, 
        c.SKU_number as product_3, 
        d.SKU_number as product_4
    FROM [cons_customer].[sales_detail_time] a
    JOIN [cons_customer].[sales_detail_time] b
        ON a.order_id = b.order_id AND b.SKU_number > a.SKU_number
    JOIN [cons_customer].[sales_detail_time] c
        ON a.order_id = c.order_id AND c.SKU_number > b.SKU_number
    JOIN [cons_customer].[sales_detail_time] d
        ON a.order_id = d.order_id AND d.SKU_number > c.SKU_number
    WHERE a.SKU_number = 'PBPR108BAU.H01'
)
SELECT TOP 50 
    product_2, 
    product_3, 
    product_4, 
    COUNT(*) AS count
FROM cte
GROUP BY product_2, product_3, product_4
ORDER BY count DESC;

原理 #

将原JOIN条件中的a.SKU_number <> b.SKU_number替换为b.SKU_number > a.SKU_number，强制后续关联的SKU必须比前一个字典序更大。这样每个商品组合只会以升序排列的形式出现在结果中，不会产生不同顺序的重复记录，后续分组统计自然准确。

方法二：通过排序函数统一组合顺序 #

如果无法修改JOIN条件，可在分组前对每个组合的SKU进行排序，生成固定顺序的列后再分组：

WITH cte AS (
    SELECT 
        a.order_id, 
        a.SKU_number as product_1, 
        b.SKU_number as product_2, 
        c.SKU_number as product_3, 
        d.SKU_number as product_4
    FROM [cons_customer].[sales_detail_time] a
    JOIN [cons_customer].[sales_detail_time] b
        ON a.order_id = b.order_id AND a.SKU_number <> b.SKU_number
    JOIN [cons_customer].[sales_detail_time] c
        ON a.order_id = c.order_id AND a.SKU_number <> c.SKU_number AND b.SKU_number <> c.SKU_number
    JOIN [cons_customer].[sales_detail_time] d
        ON a.order_id = d.order_id AND a.SKU_number <> d.SKU_number AND b.SKU_number <> d.SKU_number AND c.SKU_number <> d.SKU_number
    WHERE a.SKU_number = 'PBPR108BAU.H01'
),
sorted_items AS (
    SELECT 
        order_id,
        -- 提取三个商品的最小、中间、最大值，形成固定顺序
        LEAST(product_2, product_3, product_4) AS item_1,
        CASE
            WHEN (product_2 BETWEEN LEAST(product_2, product_3, product_4) AND GREATEST(product_2, product_3, product_4))
                 AND product_2 NOT IN (LEAST(product_2, product_3, product_4), GREATEST(product_2, product_3, product_4)) THEN product_2
            WHEN (product_3 BETWEEN LEAST(product_2, product_3, product_4) AND GREATEST(product_2, product_3, product_4))
                 AND product_3 NOT IN (LEAST(product_2, product_3, product_4), GREATEST(product_2, product_3, product_4)) THEN product_3
            ELSE product_4
        END AS item_2,
        GREATEST(product_2, product_3, product_4) AS item_3
    FROM cte
)
SELECT TOP 50 
    item_1, 
    item_2, 
    item_3, 
    COUNT(*) AS count
FROM sorted_items
GROUP BY item_1, item_2, item_3
ORDER BY count DESC;

原理 #

利用LEAST和GREATEST函数获取三个SKU的最小值和最大值，再通过CASE语句找出中间值，让同一组合的SKU始终以item_1 < item_2 < item_3的顺序排列，确保分组时相同组合会被归为一组。

方法三：用字符串拼接标识唯一组合 #

通过将三个SKU按顺序拼接成字符串，以该字符串作为分组依据，实现去重：

WITH cte AS (
    SELECT 
        a.order_id, 
        a.SKU_number as product_1, 
        b.SKU_number as product_2, 
        c.SKU_number as product_3, 
        d.SKU_number as product_4
    FROM [cons_customer].[sales_detail_time] a
    JOIN [cons_customer].[sales_detail_time] b
        ON a.order_id = b.order_id AND a.SKU_number <> b.SKU_number
    JOIN [cons_customer].[sales_detail_time] c
        ON a.order_id = c.order_id AND a.SKU_number <> c.SKU_number AND b.SKU_number <> c.SKU_number
    JOIN [cons_customer].[sales_detail_time] d
        ON a.order_id = d.order_id AND a.SKU_number <> d.SKU_number AND b.SKU_number <> d.SKU_number AND c.SKU_number <> d.SKU_number
    WHERE a.SKU_number = 'PBPR108BAU.H01'
),
combination_strings AS (
    SELECT 
        order_id,
        -- 按字典序拼接三个SKU，生成唯一标识
        STRING_AGG(sku, ',') WITHIN GROUP (ORDER BY sku) AS sorted_combination
    FROM (
        SELECT order_id, product_2 AS sku FROM cte
        UNION ALL
        SELECT order_id, product_3 AS sku FROM cte
        UNION ALL
        SELECT order_id, product_4 AS sku FROM cte
    ) t
    GROUP BY order_id
)
SELECT TOP 50 
    -- 将拼接字符串拆分为单独列（可选）
    PARSENAME(REPLACE(sorted_combination, ',', '.'), 3) AS item_1,
    PARSENAME(REPLACE(sorted_combination, ',', '.'), 2) AS item_2,
    PARSENAME(REPLACE(sorted_combination, ',', '.'), 1) AS item_3,
    COUNT(*) AS count
FROM combination_strings
GROUP BY sorted_combination
ORDER BY count DESC;

原理 #

先将三个SKU通过UNION ALL拆分成单条记录，再用STRING_AGG按字典序拼接成字符串——同一组合的拼接结果完全一致，以此作为分组键即可合并重复计数。最后可通过PARSENAME拆分字符串还原为单独列。

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