优化含NULL值的列差异查询：PostgreSQL更优方案探讨

原始数据表 #

需求 #

找出同一ID分组下各列的差异情况：

• 同一ID组内，某列只要存在值差异（NULL与NULL视为相同，NULL和非NULL视为不同），就返回一条带该列名称的记录。

期望输出 #

原实现代码 #

SELECT
    ID,
    'Amount' AS Difference
FROM
    table
GROUP BY
    ID
HAVING
    COUNT(DISTINCT amount) > 1
    OR (COUNT(amount) != COUNT(*) AND COUNT(DISTINCT amount) > 0)

UNION ALL 

SELECT
    ID,
    'Brand' AS Difference
FROM
    table
GROUP BY
    ID
HAVING
    COUNT(DISTINCT brand) > 1
    OR (COUNT(brand) != COUNT(*) AND COUNT(DISTINCT brand) > 0)

优化方案 #

原查询需要扫描两次表，以下两种方案只需要单次扫描，性能更优：

方案1：使用LATERAL JOIN（推荐） #

通过一次聚合计算各列的差异标记，再用LATERAL JOIN展开结果，逻辑简洁高效：

WITH id_agg AS (
    SELECT
        id,
        -- 判断Amount是否有差异：存在多个非NULL不同值，或同时有NULL和非NULL
        (BOOL_OR(amount IS NOT NULL) <> BOOL_AND(amount IS NOT NULL)) 
            OR COUNT(DISTINCT amount) > 1 AS has_amount_diff,
        -- 判断Brand是否有差异
        (BOOL_OR(brand IS NOT NULL) <> BOOL_AND(brand IS NOT NULL)) 
            OR COUNT(DISTINCT brand) > 1 AS has_brand_diff
    FROM your_table -- 替换成实际表名
    GROUP BY id
)
SELECT
    id,
    diff_col AS Difference
FROM id_agg
CROSS JOIN LATERAL (
    VALUES
        ('Amount', has_amount_diff),
        ('Brand', has_brand_diff)
) AS diffs(diff_col, is_diff)
WHERE is_diff = true
ORDER BY id, diff_col;

方案2：使用窗口函数 #

用窗口函数计算每个ID组的列值特征，再去重展开结果，适合需要保留中间计算逻辑的场景：

WITH id_window AS (
    SELECT
        DISTINCT id,
        -- 标记Amount是否存在差异
        CASE
            WHEN COUNT(DISTINCT amount) OVER (PARTITION BY id) > 1 THEN true
            WHEN COUNT(amount) OVER (PARTITION BY id) <> COUNT(*) OVER (PARTITION BY id) 
                 AND COUNT(DISTINCT amount) OVER (PARTITION BY id) > 0 THEN true
            ELSE false
        END AS amount_diff,
        -- 标记Brand是否存在差异
        CASE
            WHEN COUNT(DISTINCT brand) OVER (PARTITION BY id) > 1 THEN true
            WHEN COUNT(brand) OVER (PARTITION BY id) <> COUNT(*) OVER (PARTITION BY id) 
                 AND COUNT(DISTINCT brand) OVER (PARTITION BY id) > 0 THEN true
            ELSE false
        END AS brand_diff
    FROM your_table -- 替换成实际表名
)
SELECT
    id,
    diff_col AS Difference
FROM id_window
CROSS JOIN LATERAL (
    VALUES
        ('Amount', amount_diff),
        ('Brand', brand_diff)
) AS diffs(diff_col, is_diff)
WHERE is_diff = true
ORDER BY id, diff_col;

优化点说明 #

• 两种方案都只扫描一次原始表，数据量越大，相比原查询的两次扫描性能提升越明显。
• LATERAL JOIN的写法更紧凑，聚合逻辑集中，维护起来更方便。
• 窗口函数方案保留了更多中间计算的可能性，扩展性更强。

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