如何用SQL统计多类别会员的类别组合及对应会员数、总数量

原始表结构 #

现有数据表结构及数据如下：

category  member  quantity
 Clothes     A          1 
 Clothes     B          2 
 Clothes     C          3 
 Clothes     D          1 
 Cards       A          1 
 Cards       B          1 
 Cards       C          2 
 Cards       E          3 
 Cards       F          3 
 Trips       A          1 
 Trips       B          2 
 Trips       C          2 
 Trips       F          1 
 Dining      E          2
 Dining      F          1 

需求说明 #

需要获取所有拥有2个及以上消费类别的会员的类别组合，同时统计每个组合对应的：

• 会员数量（mem）
• 该组合下所有会员的总购买数量（quant_total）

期望输出示例：

Categories             mem    quant_total   
Clothes    Cards    Trips      3         15             ==> 会员A、B、C
Cards      Trips    Dining     1         5              ==> 仅会员F
Cards      Dining              1         5              ==> 仅会员E

现有代码 #

目前已实现筛选多类别会员并统计单个类别出现次数的SQL：

SELECT category, count(category)
FROM table
WHERE member IN  (SELECT member FROM table
                    GROUP BY member 
                    HAVING count(distinct category) > 1 ) 
GROUP BY category

解决方案SQL #

要支持任意数量的类别组合，可通过以下步骤实现：

完整可执行代码 #

替换your_table_name为实际表名即可运行：

WITH member_categories AS (
    SELECT 
        member,
        -- 按顺序拼接类别，确保相同组合的字符串一致
        STRING_AGG(category, ', ' ORDER BY category) AS category_combination,
        SUM(quantity) AS member_total_quant
    FROM your_table_name
    GROUP BY member
    -- 筛选出拥有2个及以上类别的会员
    HAVING COUNT(DISTINCT category) >= 2
)
SELECT 
    category_combination AS Categories,
    COUNT(member) AS mem,
    SUM(member_total_quant) AS quant_total
FROM member_categories
GROUP BY category_combination
-- 按会员数、总消费降序排序，方便查看高频组合
ORDER BY mem DESC, quant_total DESC;

数据库适配说明 #

不同数据库的字符串拼接语法略有差异，可按需替换：

• MySQL：将STRING_AGG(category, ', ' ORDER BY category)替换为GROUP_CONCAT(category ORDER BY category SEPARATOR ', ')
• SQL Server：替换为STRING_AGG(category, ', ') WITHIN GROUP (ORDER BY category)

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