SQL中含重复项的商品组合统计：需按各商品为首项展示结果

针对你提出的需求——统计相同商品组合的购买次数，同时要求每个商品都作为组合首项展示（允许重复统计同一原始组合），我可以用SQL来实现这个逻辑，下面以PostgreSQL为例给出具体方案，其他数据库也可以参考思路调整。

核心思路 #

1. 先将每个订单的所有商品聚合为一个有序集合（确保同一商品组合的订单生成一致的集合）；
2. 为每个订单中的每个商品生成以该商品为首项的组合字符串，首项后的商品按固定顺序排列（保证同一原始组合、不同首项的后续商品顺序一致）；
3. 最后统计每个组合字符串对应的订单数量（即购买次数）。

具体SQL代码 #

-- 第一步：聚合每个订单的所有商品为有序数组
WITH order_product_groups AS (
    SELECT 
        ID,
        array_agg(Products ORDER BY Products) AS product_array
    FROM orders
    GROUP BY ID
),
-- 第二步：为每个订单的每个商品生成"首项候选"
leader_candidates AS (
    SELECT 
        ID,
        unnest(product_array) AS leader,
        product_array
    FROM order_product_groups
),
-- 第三步：生成以当前首项开头的组合字符串
formatted_combinations AS (
    -- 处理包含多个商品的组合
    SELECT 
        ID,
        leader || ', ' || string_agg(p, ', ' ORDER BY p) AS Combination
    FROM leader_candidates
    CROSS JOIN unnest(product_array) AS p
    WHERE p != leader
    GROUP BY ID, leader
    -- 合并处理单个商品的组合（如果有需要）
    UNION ALL
    SELECT 
        ID,
        leader AS Combination
    FROM leader_candidates
    WHERE array_length(product_array, 1) = 1
)
-- 第四步：统计每个组合的购买次数
SELECT 
    Combination,
    COUNT(DISTINCT ID) AS Total
FROM formatted_combinations
GROUP BY Combination
ORDER BY Total DESC, Combination;

代码解释 #

• order_product_groups：把每个订单的商品按字母排序后聚合为数组，这样像ID2（Apple、Banana）和ID3（Banana、Apple）的商品数组会完全一致，避免因原始顺序不同导致的分组错误。
• leader_candidates：将每个订单的商品数组拆分为多行，每行对应一个商品作为组合的首项（leader）。
• formatted_combinations：对于每个首项，把首项放在最前面，剩下的商品排序后拼接成字符串；如果订单只有单个商品，直接用该商品作为组合。
• 最后一步统计时，用COUNT(DISTINCT ID)确保每个订单只被统计一次，得到的Total就是该组合的购买次数。

适配其他数据库（以MySQL为例） #

如果使用MySQL，需要替换PostgreSQL的数组函数为字符串聚合函数，核心思路不变：

-- 第一步：聚合每个订单的商品为有序字符串
WITH order_product_groups AS (
    SELECT 
        ID,
        GROUP_CONCAT(Products ORDER BY Products SEPARATOR ',') AS product_list
    FROM orders
    GROUP BY ID
),
-- 第二步：生成每个订单的首项候选（需要借助数字表或生成序列来拆分字符串）
-- 这里假设你有一个数字表nums，包含1到N的数字（N为最大商品数）
leader_candidates AS (
    SELECT 
        opg.ID,
        SUBSTRING_INDEX(SUBSTRING_INDEX(opg.product_list, ',', n.n), ',', -1) AS leader,
        opg.product_list
    FROM order_product_groups opg
    JOIN nums n 
        ON n.n <= LENGTH(opg.product_list) - LENGTH(REPLACE(opg.product_list, ',', '')) + 1
),
-- 第三步：生成以首项开头的组合字符串
formatted_combinations AS (
    SELECT 
        lc.ID,
        CONCAT(
            lc.leader,
            ', ',
            GROUP_CONCAT(
                SUBSTRING_INDEX(SUBSTRING_INDEX(lc.product_list, ',', m.n), ',', -1)
                ORDER BY SUBSTRING_INDEX(SUBSTRING_INDEX(lc.product_list, ',', m.n), ',', -1)
                SEPARATOR ', '
            )
        ) AS Combination
    FROM leader_candidates lc
    JOIN nums m 
        ON m.n <= LENGTH(lc.product_list) - LENGTH(REPLACE(lc.product_list, ',', '')) + 1
    WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(lc.product_list, ',', m.n), ',', -1) != lc.leader
    GROUP BY lc.ID, lc.leader
    UNION ALL
    SELECT 
        ID,
        leader AS Combination
    FROM leader_candidates
    WHERE LENGTH(product_list) - LENGTH(REPLACE(product_list, ',', '')) + 1 = 1
)
-- 统计次数
SELECT 
    Combination,
    COUNT(DISTINCT ID) AS Total
FROM formatted_combinations
GROUP BY Combination
ORDER BY Total DESC, Combination;

这段MySQL代码需要依赖一个数字辅助表nums，用来拆分逗号分隔的商品列表，你可以临时创建一个：

CREATE TABLE nums (n INT);
INSERT INTO nums VALUES (1),(2),(3); -- 数量覆盖你的订单最大商品数即可

结果验证 #

运行PostgreSQL版本的代码后，会得到你期望的结果：

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