PostgreSQL：Partition By分组中过滤NULL值的实现方案

首先明确：窗口函数的PARTITION BY子句无法直接嵌入WHERE条件，这是SQL语法规范限制，以下提供几种可行的替代实现方案：

方案1：支持IGNORE NULLS的数据库（PostgreSQL、Oracle、SQL Server 2022+） #

利用LAST_VALUE()窗口函数结合IGNORE NULLS参数，直接跳过NULL值取分组内最新非空记录：

SELECT DISTINCT
    refer,
    cat,
    -- 取cat分组下col1的最新非空值
    LAST_VALUE(col1) OVER (
        PARTITION BY refer, cat
        ORDER BY event_date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        IGNORE NULLS
    ) AS latest_col1,
    -- 取cat分组下col2的最新非空值
    LAST_VALUE(col2) OVER (
        PARTITION BY refer, cat
        ORDER BY event_date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        IGNORE NULLS
    ) AS latest_col2,
    -- 取cat分组下col3的最新非空值
    LAST_VALUE(col3) OVER (
        PARTITION BY refer, cat
        ORDER BY event_date DESC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
        IGNORE NULLS
    ) AS latest_col3
FROM your_table
WHERE refer = 2;

关键说明： #

• IGNORE NULLS会让窗口函数跳过列值为NULL的记录，仅处理非空数据；
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING确保窗口覆盖整个分组，避免默认行范围（仅到当前行）导致取不到最新值；
• DISTINCT用于去重，最终每个refer+cat分组仅保留一行结果。

方案2：不支持IGNORE NULLS的数据库（如MySQL） #

通过子查询过滤非空记录，按分组取最新行后合并结果：

-- 分别获取各列的最新非空值
WITH col1_latest AS (
    SELECT refer, cat, col1
    FROM (
        SELECT 
            refer, cat, col1,
            ROW_NUMBER() OVER (PARTITION BY refer, cat ORDER BY event_date DESC) AS rn
        FROM your_table
        WHERE col1 IS NOT NULL AND refer = 2
    ) t
    WHERE rn = 1
),
col2_latest AS (
    SELECT refer, cat, col2
    FROM (
        SELECT 
            refer, cat, col2,
            ROW_NUMBER() OVER (PARTITION BY refer, cat ORDER BY event_date DESC) AS rn
        FROM your_table
        WHERE col2 IS NOT NULL AND refer = 2
    ) t
    WHERE rn = 1
),
col3_latest AS (
    SELECT refer, cat, col3
    FROM (
        SELECT 
            refer, cat, col3,
            ROW_NUMBER() OVER (PARTITION BY refer, cat ORDER BY event_date DESC) AS rn
        FROM your_table
        WHERE col3 IS NOT NULL AND refer = 2
    ) t
    WHERE rn = 1
)
-- 合并结果，保留所有分组的非空值
SELECT 
    COALESCE(c1.refer, c2.refer, c3.refer) AS refer,
    COALESCE(c1.cat, c2.cat, c3.cat) AS cat,
    c1.col1 AS latest_col1,
    c2.col2 AS latest_col2,
    c3.col3 AS latest_col3
FROM col1_latest c1
FULL OUTER JOIN col2_latest c2 
    ON c1.refer = c2.refer AND c1.cat = c2.cat
FULL OUTER JOIN col3_latest c3 
    ON COALESCE(c1.refer, c2.refer) = c3.refer 
    AND COALESCE(c1.cat, c2.cat) = c3.cat;

关键说明： #

• 每个CTE先过滤对应列非空的记录，用ROW_NUMBER()标记分组内最新的行（rn=1）；
• FULL OUTER JOIN确保不会丢失“某列有值但其他列无值”的分组；
• COALESCE处理连接后可能出现的NULL值，保证refer和cat字段始终有效。

方案3：PostgreSQL专属（使用FILTER子句） #

PostgreSQL支持窗口函数后加FILTER子句，直接在窗口内过滤非空行：

SELECT DISTINCT
    refer,
    cat,
    FIRST_VALUE(col1) OVER (
        PARTITION BY refer, cat
        ORDER BY event_date DESC
        FILTER (WHERE col1 IS NOT NULL)
    ) AS latest_col1,
    FIRST_VALUE(col2) OVER (
        PARTITION BY refer, cat
        ORDER BY event_date DESC
        FILTER (WHERE col2 IS NOT NULL)
    ) AS latest_col2,
    FIRST_VALUE(col3) OVER (
        PARTITION BY refer, cat
        ORDER BY event_date DESC
        FILTER (WHERE col3 IS NOT NULL)
    ) AS latest_col3
FROM your_table
WHERE refer = 2;

关键说明： #

• FILTER (WHERE colX IS NOT NULL)限定窗口函数仅处理该列非空的行；
• FIRST_VALUE()按event_date DESC排序后，直接取分组内的第一个值（即最新非空值）；
• DISTINCT用于去重，保留每个refer+cat分组的唯一结果。

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