PostgreSQL中获取连续相同test_result序列的最后一行

我们使用PostgreSQL 10.23数据库，有如下测试结果表：

需要返回每一组连续相同test_result值序列中id最大的行，预期输出如下：

解决方案 #

核心思路 #

先识别出连续相同test_result的分组，再在每个分组中筛选出id最大的行。

实现SQL #

方法一：通过分组标记筛选最大id

WITH grouped_data AS (
    SELECT
        id,
        test_result,
        name,
        -- 生成连续分组的标记：当前行与上一行结果不同时，创建新分组
        SUM(CASE WHEN test_result = LAG(test_result) OVER (ORDER BY id) THEN 0 ELSE 1 END) 
            OVER (ORDER BY id) AS group_id
    FROM test_table
)
SELECT id, test_result, name
FROM grouped_data
WHERE id = (SELECT MAX(id) FROM grouped_data g WHERE g.group_id = grouped_data.group_id)
ORDER BY id;

方法二：使用行号排序取首行

WITH ranked_data AS (
    SELECT
        id,
        test_result,
        name,
        -- 每个分组内按id倒序排，行号为1的就是id最大的行
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id DESC) AS rn
    FROM (
        SELECT
            *,
            SUM(CASE WHEN test_result = LAG(test_result) OVER (ORDER BY id) THEN 0 ELSE 1 END) 
                OVER (ORDER BY id) AS group_id
        FROM test_table
    ) t
)
SELECT id, test_result, name
FROM ranked_data
WHERE rn = 1
ORDER BY id;

说明 #

• LAG(test_result) OVER (ORDER BY id)：按id顺序获取当前行的上一行test_result值，确保连续序列的判断基于id的递增顺序。
• SUM(...) OVER (ORDER BY id)：通过累加标记值，为每一段连续相同的test_result分配唯一的group_id，实现分组。
• 两种方法最终都能精准筛选出每个连续结果组中id最大的行，输出符合预期。

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