PostgreSQL中按行计算众数并自定义平局规则的实现方法

问题背景 #

现有如下vals表：

create table  vals (k, v, z, y, m) AS VALUES 
(0, 0, 1, 8, 0), 
(1, 2, 1, 1, 6), 
(1, 0, 0, 2, 2),
(5, 4, 6, 8, 9), 
(0, 0, 0, 6, 6);

需求为计算每行的众数（出现次数最多的值），当存在多个众数时，返回该行中最先出现的那个值。

解决方案 #

可以通过将每行的列转换为带位置序号的行数据，再分组统计排序的方式实现，具体SQL如下：

SELECT 
  original.*,
  (
    SELECT val
    FROM unnest(ARRAY[k, v, z, y, m]) WITH ORDINALITY AS t(val, pos)
    GROUP BY val
    ORDER BY COUNT(*) DESC, MIN(pos) ASC
    LIMIT 1
  ) AS modal
FROM vals original;

逻辑解释 #

1. 列转行并保留顺序：unnest(ARRAY[k, v, z, y, m]) WITH ORDINALITY将每行的5个字段打包成数组后拆分为行，pos字段记录了原字段的出现顺序（k对应pos=1，v对应pos=2，以此类推）。
2. 分组统计次数：GROUP BY val按每个数值分组，统计其在当前行的出现次数。
3. 排序取最优值：
   • 首先按出现次数COUNT(*) DESC降序排序，确保次数最多的数值优先；
   • 次数相同时，按该数值最早出现的位置MIN(pos) ASC升序排序，保证最先出现的数值被选中；
4. 取结果：LIMIT 1获取排序后的第一个值，即为符合要求的众数。

验证结果 #

执行上述SQL后，将得到与需求示例完全一致的结果：

内容的提问来源于stack exchange，提问作者Любовь Пономарева