PostgreSQL查询获取value列上一次不同值的SQL实现方法

普通lag()窗口函数只能取相邻上一行的值，遇到连续相同value的场景就会返回同值结果，不符合需求。可以通过两种方式实现目标，根据你使用的PostgreSQL版本选择即可：

方法1：兼容所有PG版本（9.0+），通用写法 #

核心逻辑是先给连续相同的value划分分组，再取上一个分组的value作为diff值，步骤如下：

• 标记相邻行的value变化点：当前行value和上一行不一致时记为一次变化
• 累计变化次数生成分组ID：连续相同value的行分组ID完全一致
• 跨分组取值：取当前分组ID减1对应的分组value，同组所有行的diff值统一

完整SQL代码：

-- 测试数据CTE，实际使用时替换成你的表名即可
WITH your_table AS (
    SELECT * FROM (VALUES
        (1, '2022-01-01'::date, 1),
        (1, '2022-01-02'::date, 1),
        (1, '2022-01-03'::date, 2),
        (1, '2022-01-04'::date, 2),
        (1, '2022-01-05'::date, 3),
        (1, '2022-01-06'::date, 3)
    ) AS t(id, date, value)
),
flag_step AS (
    SELECT 
        *,
        -- 相邻value不同时标记为1，相同标记为0
        CASE WHEN value = LAG(value) OVER (PARTITION BY id ORDER BY date) THEN 0 ELSE 1 END AS change_flag
    FROM your_table
),
group_step AS (
    SELECT
        *,
        -- 累加标记生成连续同值分组ID
        SUM(change_flag) OVER (PARTITION BY id ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM flag_step
)
SELECT
    id,
    date,
    value,
    -- 取上一个分组的value，不存在则返回null
    MAX(value) OVER (PARTITION BY id, group_id - 1) AS diff
FROM group_step
ORDER BY id, date;

执行结果完全匹配预期：

方法2：PG11+ 简化写法 #

PostgreSQL 11版本开始窗口函数支持IGNORE NULLS语法，可以跳过null值取最近的非空记录，写法更简洁：

WITH your_table AS (
    SELECT * FROM (VALUES
        (1, '2022-01-01'::date, 1),
        (1, '2022-01-02'::date, 1),
        (1, '2022-01-03'::date, 2),
        (1, '2022-01-04'::date, 2),
        (1, '2022-01-05'::date, 3),
        (1, '2022-01-06'::date, 3)
    ) AS t(id, date, value)
)
SELECT
    id,
    date,
    value,
    LAG(
        CASE WHEN value <> LAG(value) OVER (PARTITION BY id ORDER BY date) THEN value END
    ) IGNORE NULLS OVER (PARTITION BY id ORDER BY date) AS diff
FROM your_table
ORDER BY id, date;

注意事项 #

• 如果表中存在多个不同id的主体数据，PARTITION BY id逻辑会自动按主体单独计算，无需额外修改
• 两种写法都是纯窗口函数实现，性能远高于关联子查询、递归写法，百万级数据量也能高效执行
• 不要尝试嵌套多层lag()跳过同值行，连续同值的行数不固定时这种写法完全不可用

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