怎样在PostgreSQL中使用MODE()函数寻找分组众数_实现基础统计功能-SQL-PHP中文网

PostgreSQL 14+ 首次内置 MODE() 聚合函数，需配合 WITHIN GROUP (ORDER BY col)，要求 col 非空且排序确定；旧版本须用 GROUP BY + COUNT() 与 ROW_NUMBER() 窗口函数等效实现，并显式过滤 NULL 以确保语义正确与性能可控。

怎样在postgresql中使用mode()函数寻找分组众数_实现基础统计功能

PostgreSQL 14+ 才有内置 `MODE()` 窗口函数，旧版本必须手写

PostgreSQL 直到 14 版本才原生支持 MODE() 作为聚合函数（需配合 WITHIN GROUP (ORDER BY ...)），且仅支持单列、非空值、确定性排序。如果你用的是 13 或更早版本，MODE() 会直接报错 ERROR: function mode() does not exist。别急着升级——多数场景下，用 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），可明显提速