如何使用或不使用LAG函数查询列的最后变更值及变更日期

假设我们有一张记录数值随时间变化的表（比如包含id、record_date、current_value字段），需要提取每个分组（如id）最后一次数值发生变更的具体值和对应的日期。下面提供两种实现方案：

方法一：用LAG函数快速实现 #

LAG函数能直接获取同一分组中上一行的数值，结合窗口函数可以轻松标记出变更行，再筛选最后一条即可。

示例SQL #

WITH change_marks AS (
    SELECT 
        id,
        record_date,
        current_value,
        -- 标记值发生变更的行：首次记录或与上一行值不同
        CASE 
            WHEN LAG(current_value) OVER (PARTITION BY id ORDER BY record_date) IS NULL 
                OR LAG(current_value) OVER (PARTITION BY id ORDER BY record_date) != current_value
            THEN 1
            ELSE 0
        END AS is_change
    FROM value_changes
),
latest_changes AS (
    SELECT 
        id,
        current_value AS last_changed_value,
        record_date AS last_change_date,
        -- 给每个分组的变更行按日期倒序排名
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY record_date DESC) AS rn
    FROM change_marks
    WHERE is_change = 1
)
SELECT id, last_changed_value, last_change_date
FROM latest_changes
WHERE rn = 1;

逻辑说明 #

1. change_marks CTE里，用LAG对比当前行和上一行的数值，标记出所有值变化的行（包括每个分组的第一条记录，因为它是初始值）
2. latest_changes CTE里，给每个分组的变更行按日期倒序排号，取排名为1的就是最后一次变更的记录

方法二：不使用LAG函数的实现方案 #

如果数据库不支持LAG函数，或者你不想用窗口函数，可以通过分组关联的方式实现。

方案1：结合分组与窗口函数（无LAG） #

WITH value_first_dates AS (
    SELECT 
        id,
        current_value,
        MIN(record_date) AS first_change_date
    FROM value_changes
    GROUP BY id, current_value
),
latest_change AS (
    SELECT 
        id,
        current_value AS last_changed_value,
        first_change_date AS last_change_date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY first_change_date DESC) AS rn
    FROM value_first_dates
)
SELECT id, last_changed_value, last_change_date
FROM latest_change
WHERE rn = 1;

方案2：纯关联查询（无窗口函数） #

适合不支持窗口函数的老版本数据库：

SELECT 
    v.id,
    v.current_value AS last_changed_value,
    v.record_date AS last_change_date
FROM value_changes v
INNER JOIN (
    SELECT 
        id,
        MAX(first_change_date) AS latest_date
    FROM (
        SELECT 
            id,
            current_value,
            MIN(record_date) AS first_change_date
        FROM value_changes
        GROUP BY id, current_value
    ) t
    GROUP BY id
) t ON v.id = t.id AND v.record_date = t.latest_date
WHERE NOT EXISTS (
    SELECT 1
    FROM value_changes v2
    WHERE v2.id = v.id 
      AND v2.record_date > v.record_date 
      AND v2.current_value != v.current_value
);

逻辑说明 #

• 方案1先分组得到每个分组下每个数值的首次出现日期，然后取每个分组中最大的首次日期对应的数值，就是最后一次变更的值
• 方案2通过子查询找到每个分组的最晚变更日期，再关联原表，同时确保该日期之后没有其他数值变更，最终得到最后一次变更记录

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