如何通过PostgreSQL的MODE()函数查找分组数据中的众数，实现基础统计查询？

本文共计1019个文字，预计阅读时间需要5分钟。

PostgreSQL 14及以上版本原生支持使用MODE作为聚合函数（需配合WITHIN GROUP (ORDER BY ...)使用），仅支持单列、非空值和确定性排序。如果使用的是13或更早版本，直接使用MODE会报错ERROR: function mode() does not exist。

在这种情况下，不要慌张，可以通过升级到PostgreSQL 14或更高版本来解决。在多数场景下，使用GROUP BY + COUNT()配合窗口函数就能达到相同的效果，并且更加灵活可控。

• 14+ 中正确写法是：MODE() WITHIN GROUP (ORDER BY col)，不是 MODE(col)
• 该函数要求 col 不能含 NULL，否则整个分组返回 NULL
• 若多个值并列最高频（如 [a,a,b,b,c]），PostgreSQL 只返回排序靠前的那个（依赖 ORDER BY 规则），不报错也不警告

用 ROW_NUMBER() + COUNT() 实现兼容所有版本的众数

这是最稳妥、可读性高、且能处理并列众数的写法。核心思路：先按分组和值计数，再对每个分组内按频次降序排号，取 rn = 1 的记录。

SELECT category, val AS mode_val FROM ( SELECT category, val, ROW_NUMBER() OVER (PARTITION BY category ORDER BY COUNT(*) DESC, val) AS rn FROM products WHERE val IS NOT NULL GROUP BY category, val ) t WHERE rn = 1;

• WHERE val IS NOT NULL 必须显式加，否则 NULL 会被计入并可能成为“众数”
• ORDER BY COUNT(*) DESC, val 中的 val 是为打破频次相同时的不确定性；若业务允许任选一个，可只留 COUNT(*) DESC
• 若要返回全部并列众数（不止一个），把 ROW_NUMBER() 换成 RANK()，再改成 RANK() = 1

用 ARRAY_AGG() + MODE() 组合在 14+ 中获取多值众数

原生 MODE() 不支持返回多个结果，但你可以先聚合为数组，再用 UNNEST + 子查询模拟“取最高频集合”。不过更实用的做法是：用 ARRAY_AGG(DISTINCT val) FILTER (WHERE ...) 配合频次判断。

但注意——下面这个看似简洁的写法是**错误的**：

-- ❌ 错误：MODE() 不能直接作用于 ARRAY_AGG 结果 SELECT MODE(ARRAY_AGG(val)) FROM products GROUP BY category;

正确路径仍是先统计频次，再用数组聚合筛选：

SELECT category, (ARRAY_AGG(val ORDER BY cnt DESC))[1] AS mode_val FROM ( SELECT category, val, COUNT(*) AS cnt FROM products WHERE val IS NOT NULL GROUP BY category, val ) t GROUP BY category;

• (ARRAY_AGG(...) [1]) 取第一个，等价于 ROW_NUMBER() = 1 的效果，但少一层子查询
• 若想返回全部众数（数组形式），改用：ARRAY_AGG(val) FILTER (WHERE cnt = MAX(cnt) OVER (PARTITION BY category)) —— 这里嵌套了窗口最大值，需两层子查询或 CTE

性能与 NULL 处理：为什么不能跳过 WHERE col IS NOT NULL

在真实业务数据中，NULL 往往占比不小。如果漏掉 IS NOT NULL 过滤，会出现两种隐蔽问题：

• 当某分组中 NULL 出现次数最多时，MODE() 或自定义逻辑会返回 NULL，看起来像“无众数”，实则是脏数据干扰
• GROUP BY col 会把所有 NULL 归为同一组，导致频次虚高，尤其在低基数列（如状态字段）上极易误导结论
• 索引无法覆盖含 NULL 的聚合，全表扫描概率上升；加 WHERE col IS NOT NULL 后，若该列上有部分索引（CREATE INDEX idx ON t(col) WHERE col IS NOT NULL），可明显提速

众数本身是对“典型值”的刻画，而 NULL 表达的是“未知”或“不适用”，语义上就不该参与统计。这步过滤不是可选项，是建模前提。