如何按交易次数分组统计对应用户数？MySQL分组聚合问题

当然可以！你已经离目标很近了——只需要在现有查询的基础上多做一层分组，再处理一下缺失的交易次数，就能得到你想要的结果。

第一步：基础分组统计（获取有用户的交易次数） #

你当前的查询已经得到了每个用户的交易次数，接下来把这个结果作为子查询，再对number_of_trans做分组统计，就能得到每个交易次数对应的用户数：

SELECT number_of_trans, COUNT(*) AS count
FROM (
    -- 原查询：获取每个用户的交易次数（过滤掉交易次数≤1的用户）
    SELECT cust_id, COUNT(*) AS number_of_trans 
    FROM table 
    GROUP BY cust_id 
    HAVING COUNT(*) > 1
) AS user_trans
GROUP BY number_of_trans
ORDER BY number_of_trans;

这个查询会返回：

但它不会显示没有用户的交易次数（比如1、5），如果要得到你期望的包含这些空值的结果，需要做进一步处理。

第二步：显示所有目标交易次数（包括无用户的情况） #

要显示1-5这类没有用户的交易次数，我们需要先生成一个包含所有目标次数的序列，再通过左连接关联统计结果。不同数据库的语法略有差异，下面以常见的MySQL 8+为例：

方法1：手动生成固定范围的次数序列

WITH trans_counts AS (
    -- 生成1到5的交易次数序列，按需调整范围
    SELECT 1 AS number_of_trans UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4 UNION ALL
    SELECT 5
),
user_trans AS (
    -- 原查询：获取每个用户的交易次数
    SELECT cust_id, COUNT(*) AS number_of_trans 
    FROM table 
    GROUP BY cust_id 
    HAVING COUNT(*) > 1
)
SELECT 
    tc.number_of_trans,
    -- 没有匹配用户时返回null，否则统计用户数
    IFNULL(COUNT(ut.cust_id), NULL) AS count
FROM trans_counts tc
-- 左连接保留所有预先生成的次数
LEFT JOIN user_trans ut ON tc.number_of_trans = ut.number_of_trans
GROUP BY tc.number_of_trans
ORDER BY tc.number_of_trans;

方法2：动态生成次数范围（自动覆盖最小到最大交易次数）

如果你的交易次数范围不固定，想用动态方式覆盖从最小到最大的所有次数，可以用递归CTE：

WITH RECURSIVE trans_counts AS (
    -- 先获取所有用户交易次数中的最小值
    SELECT MIN(number_of_trans) AS number_of_trans
    FROM (
        SELECT COUNT(*) AS number_of_trans 
        FROM table 
        GROUP BY cust_id
    ) AS t
    UNION ALL
    -- 递归生成后续的次数，直到达到最大值
    SELECT number_of_trans + 1
    FROM trans_counts
    WHERE number_of_trans < (
        SELECT MAX(number_of_trans)
        FROM (
            SELECT COUNT(*) AS number_of_trans 
            FROM table 
            GROUP BY cust_id
        ) AS t
    )
),
user_trans AS (
    SELECT cust_id, COUNT(*) AS number_of_trans 
    FROM table 
    GROUP BY cust_id 
    HAVING COUNT(*) > 1
)
SELECT 
    tc.number_of_trans,
    IFNULL(COUNT(ut.cust_id), NULL) AS count
FROM trans_counts tc
LEFT JOIN user_trans ut ON tc.number_of_trans = ut.number_of_trans
GROUP BY tc.number_of_trans
ORDER BY tc.number_of_trans;

关键说明 #

• 左连接(LEFT JOIN)的作用是保留预先生成的所有交易次数，即使某个次数没有对应的用户；
• IFNULL(COUNT(ut.cust_id), NULL)用来把统计结果为0的情况转换成null，符合你期望的输出格式；
• 如果你的数据库不支持CTE（比如旧版MySQL），可以把序列和子查询直接写在FROM子句中，效果是一样的。

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