PostgreSQL中如何优化查询以识别当前列与上一行列的差异？

我有如下审计表：

需要生成如下结果，标记出每一行与上一条（更早的）记录相比发生变化的列：X表示该列有变更，null表示无变更：

目前我用以下查询实现需求，但因为涉及近20列，重复的子查询导致代码冗余，希望优化得更简洁：

SELECT 
    ta.audit_date,
    ta.audit_user,
    CASE
        WHEN ta.audit_operation = 'I' THEN 'Insert'
        WHEN ta.audit_operation = 'U' THEN 'Update'
    END AS action,
    CASE WHEN ta.column1 <> (SELECT column1
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column1,
    CASE WHEN ta.column2 <> (SELECT column2
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column2,
    CASE WHEN ta.column3 <> (SELECT column3
                                    FROM audit_table ta1
                                    WHERE ta1.id = 9207 AND ta1.audit_date < ta.audit_date
                                    ORDER BY ta1.audit_date DESC
                                    LIMIT 1) 
        THEN 'X' ELSE null END column3
FROM
    audit_table ta
WHERE
    ta.id = 9207
ORDER BY
    audit_date DESC

优化方案：使用窗口函数LAG() #

利用SQL的窗口函数LAG()可以直接获取分组内上一行的对应列值，避免重复子查询，代码更简洁且性能更优（只需一次排序，而非每行每列单独查询）：

SELECT 
    ta.audit_date,
    ta.audit_user,
    CASE
        WHEN ta.audit_operation = 'I' THEN 'Insert'
        WHEN ta.audit_operation = 'U' THEN 'Update'
    END AS action,
    -- 对比当前列与上一行值，处理NULL场景
    CASE 
        WHEN ta.column1 IS NOT DISTINCT FROM LAG(ta.column1) OVER (PARTITION BY ta.id ORDER BY ta.audit_date) 
        THEN NULL 
        ELSE 'X' 
    END AS column1,
    CASE 
        WHEN ta.column2 IS NOT DISTINCT FROM LAG(ta.column2) OVER (PARTITION BY ta.id ORDER BY ta.audit_date) 
        THEN NULL 
        ELSE 'X' 
    END AS column2,
    CASE 
        WHEN ta.column3 IS NOT DISTINCT FROM LAG(ta.column3) OVER (PARTITION BY ta.id ORDER BY ta.audit_date) 
        THEN NULL 
        ELSE 'X' 
    END AS column3,
    -- 剩余列按同样格式复制即可
    CASE 
        WHEN ta.columnN IS NOT DISTINCT FROM LAG(ta.columnN) OVER (PARTITION BY ta.id ORDER BY ta.audit_date) 
        THEN NULL 
        ELSE 'X' 
    END AS columnN
FROM
    audit_table ta
WHERE
    ta.id = 9207
ORDER BY
    ta.audit_date DESC;

说明：

1. LAG(column) OVER (PARTITION BY ta.id ORDER BY ta.audit_date)：按id分组，按audit_date升序排序，获取当前行的上一行对应列的值
2. IS NOT DISTINCT FROM：解决NULL值对比的问题（直接用<>对比NULL会返回UNKNOWN，导致判断错误），如果当前列和上一行列值完全一致（包括都是NULL），则返回NULL，否则标记为X
3. 剩余列只需复制上述CASE结构，替换列名即可，无需重复编写子查询

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