PostgreSQL/Oracle分组获取各列最新非空值的SQL实现

搞定这个需求其实很简单，核心思路就是按gb分组后，让每组内的数据先按time降序排列（最新时间优先），同时把NUM为null的记录排在后面，最后取每组的第一条记录即可。下面针对PostgreSQL和Oracle分别给出具体的实现方案：

PostgreSQL 实现方案 #

方案1：使用DISTINCT ON（简洁高效） #

PostgreSQL提供了DISTINCT ON语法，专门用于分组后取每组的首行数据，非常适合这个场景：

SELECT DISTINCT ON (gb)
       gb,
       num
FROM test
ORDER BY gb,
         time DESC,
         num NULLS LAST;

• 逻辑解释：
  1. DISTINCT ON (gb)确保每个gb分组只返回一行数据；
  2. ORDER BY gb先按分组字段排序，保证同一组的记录在一起；
  3. time DESC让每组内最新时间的记录排在最前面；
  4. num NULLS LAST强制把NUM为null的记录排在同组的末尾，避免null值被选中。

方案2：使用窗口函数ROW_NUMBER()（通用兼容写法） #

如果需要更通用的窗口函数写法，也可以用ROW_NUMBER()来标记每组的行顺序：

WITH ranked_data AS (
    SELECT gb,
           num,
           ROW_NUMBER() OVER (PARTITION BY gb ORDER BY time DESC, num NULLS LAST) AS rn
    FROM test
)
SELECT gb, num
FROM ranked_data
WHERE rn = 1;

• 逻辑解释：
  1. 用PARTITION BY gb按分组字段拆分数据；
  2. ORDER BY time DESC, num NULLS LAST给每组内的记录排序；
  3. ROW_NUMBER()给每组的记录从1开始编号，取编号为1的行就是我们需要的最新非空值。

Oracle 实现方案 #

Oracle没有DISTINCT ON语法，我们可以用窗口函数来实现，有两种常用写法：

方案1：使用ROW_NUMBER() #

WITH ranked_data AS (
    SELECT gb,
           num,
           ROW_NUMBER() OVER (PARTITION BY gb ORDER BY time DESC, num NULLS LAST) AS rn
    FROM test
)
SELECT gb, num
FROM ranked_data
WHERE rn = 1;

• 逻辑和PostgreSQL的窗口函数写法一致，注意Oracle中NULLS LAST需要显式声明，避免降序排序时null值排在前面（Oracle默认降序时null值会被视为最大，排在最前）。

方案2：使用FIRST_VALUE()函数 #

直接用FIRST_VALUE()函数提取每组排序后的第一个非空NUM值：

SELECT DISTINCT
       gb,
       FIRST_VALUE(num) OVER (PARTITION BY gb ORDER BY time DESC, num NULLS LAST) AS latest_non_null_num
FROM test;

• 逻辑解释：FIRST_VALUE()会返回每组排序后的第一个NUM值，配合DISTINCT去重，得到每个分组的结果。

验证示例数据 #

把你提供的测试数据插入后，执行上述SQL都会得到预期结果：

gb | num
---+-----
1  | 3
2  | 3
3  | 1
4  | 2
5  | 3
6  | 2

内容的提问来源于stack exchange，提问作者Martin Mucha