Oracle SQL：分组内排序取非NULL值，如何简化FIRST_VALUE重复写法？

现有合并多源数据的表t，需求如下：

• 按列x分组
• 每组内按列z排序，优先保留非NULL值（即非NULL的z排在NULL之前）
• 每组中其余列按排序后的顺序，取第一个非NULL值

尝试直接用GROUP BY+ORDER BY时触发ORA-00979错误（原因是SELECT列表中的列未包含在GROUP BY中，也未使用聚合函数），已通过FIRST_VALUE函数实现需求，但写法重复冗余，希望找到只需定义一次窗口的更优方案。

输入输出示例 #

输入表t #

预期输出 #

原冗长写法示例 #

SELECT DISTINCT
    x,
    FIRST_VALUE(y IGNORE NULLS) OVER (PARTITION BY x ORDER BY CASE WHEN z IS NOT NULL THEN 0 ELSE 1 END, z) AS y,
    FIRST_VALUE(z IGNORE NULLS) OVER (PARTITION BY x ORDER BY CASE WHEN z IS NOT NULL THEN 0 ELSE 1 END, z) AS z
FROM t;

优化方案：使用WINDOW子句复用窗口定义 #

Oracle 12c及以上版本支持WINDOW子句，可以一次性定义窗口规则，供多个分析函数复用，大幅简化代码：

SELECT DISTINCT
    x,
    FIRST_VALUE(y IGNORE NULLS) OVER w AS y,
    FIRST_VALUE(z IGNORE NULLS) OVER w AS z
FROM t
WINDOW w AS (
    PARTITION BY x 
    ORDER BY CASE WHEN z IS NOT NULL THEN 0 ELSE 1 END, z
);

另一种思路：先标记优先级再取首行 #

如果需要避免使用DISTINCT，也可以先通过ROW_NUMBER()标记每组内的优先级行，再筛选首行：

SELECT x, y, z
FROM (
    SELECT 
        x, y, z,
        ROW_NUMBER() OVER (
            PARTITION BY x 
            ORDER BY CASE WHEN z IS NOT NULL THEN 0 ELSE 1 END, z
        ) AS rn
    FROM t
)
WHERE rn = 1;

这个方案的优势是无需处理重复行，且逻辑更直观，适合对DISTINCT有顾虑的场景。

关键说明 #

• ORDER BY CASE WHEN z IS NOT NULL THEN 0 ELSE 1 END实现了非NULL值优先排序的需求
• IGNORE NULLS确保FIRST_VALUE跳过NULL值，直接取第一个有效数据
• WINDOW子句的核心价值是统一管理窗口规则，后续新增列时只需复用OVER w即可，无需重复编写分区和排序逻辑

内容的提问来源于stack exchange，提问作者Tianxiang Xiong