PostgreSQL多列表分组获取首个非空值的高效实现需求

针对你在处理多列分组、按时间排序取最新非空值的需求，结合你提到的性能痛点（40+列、窗口函数过慢），我整理了几个实用且高效的方案：

1. 自定义first_non_null聚合函数（推荐） #

PostgreSQL默认没有直接取首个非空值的聚合函数，但我们可以自己创建一个，完美匹配你需要的「按行顺序保留第一个非空值」的逻辑，而且性能远优于重复使用窗口函数。

步骤1：创建自定义聚合函数 #

-- 定义聚合逻辑：如果已有值非空则保留，否则取新值
CREATE OR REPLACE FUNCTION first_non_null_func(anyelement, anyelement)
RETURNS anyelement AS $$
BEGIN
  IF $1 IS NOT NULL THEN
    RETURN $1;
  ELSE
    RETURN $2;
  END IF;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- 注册聚合函数
CREATE AGGREGATE first_non_null(anyelement) (
  SFUNC = first_non_null_func,
  STYPE = anyelement,
  INITCOND = NULL
);

步骤2：使用聚合函数查询 #

先将每组内的行按submitted降序排列（确保最新的行先被处理），再按group分组，用自定义聚合函数取每个列的首个非空值：

SELECT
  "group",
  first_non_null(num1) AS num1,
  first_non_null(num2) AS num2,
  first_non_null(num3) AS num3,
  first_non_null(str1) AS str1,
  first_non_null(str2) AS str2,
  first_non_null(str3) AS str3
  -- 继续添加你的40+列
FROM (
  SELECT *
  FROM your_table
  ORDER BY "group", submitted DESC  -- 按组+时间倒序，确保最新行优先
) sorted_rows
GROUP BY "group";

性能优化关键 #

给表创建复合索引("group", submitted DESC)，这样子查询的排序可以直接利用索引，避免全表排序，大幅提升速度。

2. 优化窗口函数方案（解决原方案过慢问题） #

如果你不想创建自定义函数，可以优化窗口函数的写法——核心是只做一次分区排序，而非每个列重复分区：

WITH ranked_rows AS (
  SELECT
    *,
    -- 按组分区，按时间倒序排名
    ROW_NUMBER() OVER (PARTITION BY "group" ORDER BY submitted DESC) AS rn
  FROM your_table
)
SELECT
  "group",
  -- 对每个列，取该组内第一个非空值
  COALESCE(
    (SELECT num1 FROM ranked_rows rr WHERE rr."group" = r."group" AND rr.num1 IS NOT NULL ORDER BY rr.rn LIMIT 1),
    NULL
  ) AS num1,
  COALESCE(
    (SELECT num2 FROM ranked_rows rr WHERE rr."group" = r."group" AND rr.num2 IS NOT NULL ORDER BY rr.rn LIMIT 1),
    NULL
  ) AS num2
  -- 其他列同理
FROM ranked_rows r
GROUP BY "group";

不过这个方案的性能还是不如自定义聚合函数，因为每个列都需要一次子查询，适合列数较少的场景，40+列的话还是优先用方案1。

3. 类比Pandas的first()逻辑 #

你的需求和Pandas中df.groupby('group').first()完全一致——都是按顺序取每组内各列的第一个非空值。自定义聚合函数的方案就是在PostgreSQL中实现了这个逻辑，而且通过索引优化后，性能可以满足大表需求。

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