SQL如何查询每个客户分组下出现次数最多的type值

这个需求本质是计算分组内的众数，可以通过SQL原生的窗口函数实现，不需要额外创建持久化的count统计列，也不需要依赖全局聚合结果。

标准实现（支持窗口函数的数据库：MySQL 8.0+、PostgreSQL、SQL Server、Oracle 等） #

实现分两步：

1. 先按CustomerId+type分组，统计每个客户名下每个type的出现次数
2. 给每个客户分组内的type按出现次数降序排名，筛选排名为1的记录即为最高频type

完整SQL：

WITH type_stat AS (
    SELECT
        CustomerId,
        type,
        COUNT(*) AS type_count
    FROM your_table -- 替换成你的实际表名
    GROUP BY CustomerId, type
)
SELECT
    CustomerId,
    type AS most_frequent_type,
    type_count AS occurrence_count
FROM (
    SELECT
        *,
        -- 按客户分组，组内按出现次数降序打排名
        RANK() OVER (PARTITION BY CustomerId ORDER BY type_count DESC) AS freq_rank
    FROM type_stat
) ranked
WHERE freq_rank = 1;

关键说明 #

• 窗口函数的PARTITION BY CustomerId就是用来指定分组计算范围，和全局聚合的区别是：它不会把多行结果折叠成一行，会保留分组内每一行的明细，刚好满足你要的「每个客户分组下的局部统计」需求，之前你拿到全局最大值就是因为没有指定分组计算的范围。
• 排名函数优先选RANK()：如果同一个客户下有多个type出现次数并列最高，RANK()会返回所有并列最高的type，不会丢数据；如果你明确只需要任意一个最高频type，可以把RANK()替换成ROW_NUMBER()。

兼容老版本数据库的实现（不支持窗口函数的场景，如MySQL 5.x） #

如果你的数据库版本不支持窗口函数，可以通过关联子查询实现，性能低于窗口函数方案，写法如下：

SELECT
    t1.CustomerId,
    t1.type AS most_frequent_type,
    COUNT(*) AS occurrence_count
FROM your_table t1
GROUP BY t1.CustomerId, t1.type
HAVING COUNT(*) = (
    SELECT MAX(type_cnt)
    FROM (
        SELECT COUNT(*) AS type_cnt
        FROM your_table t2
        WHERE t2.CustomerId = t1.CustomerId
        GROUP BY t2.type
    ) group_max
);

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