如何获取每个id下每段连续status对应的首行记录

我们需要获取每个id对应的每段连续status的首行记录。同一status可能存在多条连续记录，因此需要基于与上一条记录的status对比，取每次status切换后的首条记录。

举例说明：info_required首次出现在第2行，随后在第4行切换为pending状态，之后第6行又切换回info_required；同理pending首次出现在第4行，第4行后status发生过变化，因此第8行的pending记录也需要被纳入结果集。最终需要得到行号为1、2、4、6、8的记录。

WITH t1 AS (
SELECT 1 AS row, 'A' AS id, 'created' AS status, '2021-05-18 18:30:00'::timestamp AS created_at UNION ALL
SELECT 2 AS row, 'A' AS id, 'info_required' AS status, '2021-05-19 11:30:00'::timestamp AS created_at UNION ALL
SELECT 3 AS row, 'A' AS id, 'info_required' AS status, '2021-05-19 12:00:00'::timestamp AS created_at UNION ALL
SELECT 4 AS row, 'A' AS id, 'pending' AS status, '2021-05-19 12:30:00'::timestamp AS created_at UNION ALL
SELECT 5 AS row, 'A' AS id, 'pending' AS status, '2021-05-20 13:30:00'::timestamp AS created_at UNION ALL
SELECT 6 AS row, 'A' AS id, 'info_required' AS status, '2021-05-20 14:30:00'::timestamp AS created_at UNION ALL
SELECT 7 AS row, 'A' AS id, 'info_required' AS status, '2021-05-20 15:30:00'::timestamp AS created_at UNION ALL
SELECT 8 AS row, 'A' AS id, 'pending' AS status, '2021-05-20 16:30:00'::timestamp AS created_at
    )
SELECT *
FROM t1

使用LAG()窗口函数即可实现需求：

1. 先按id分组，按行号/创建时间升序排序，取每条记录的上一条status值
2. 筛选出「分组内第一条记录（上一条status为空）」或「当前status和上一条status不相等」的记录，就是每次状态切换后的首条记录

实现代码如下：

WITH t1 AS (
SELECT 1 AS row, 'A' AS id, 'created' AS status, '2021-05-18 18:30:00'::timestamp AS created_at UNION ALL
SELECT 2 AS row, 'A' AS id, 'info_required' AS status, '2021-05-19 11:30:00'::timestamp AS created_at UNION ALL
SELECT 3 AS row, 'A' AS id, 'info_required' AS status, '2021-05-19 12:00:00'::timestamp AS created_at UNION ALL
SELECT 4 AS row, 'A' AS id, 'pending' AS status, '2021-05-19 12:30:00'::timestamp AS created_at UNION ALL
SELECT 5 AS row, 'A' AS id, 'pending' AS status, '2021-05-20 13:30:00'::timestamp AS created_at UNION ALL
SELECT 6 AS row, 'A' AS id, 'info_required' AS status, '2021-05-20 14:30:00'::timestamp AS created_at UNION ALL
SELECT 7 AS row, 'A' AS id, 'info_required' AS status, '2021-05-20 15:30:00'::timestamp AS created_at UNION ALL
SELECT 8 AS row, 'A' AS id, 'pending' AS status, '2021-05-20 16:30:00'::timestamp AS created_at
),
-- 新增列存储同id下上一条记录的status
status_compare AS (
    SELECT 
        *,
        LAG(status) OVER(PARTITION BY id ORDER BY row ASC) AS prev_status
    FROM t1
)
SELECT row, id, status, created_at
FROM status_compare
WHERE prev_status IS NULL OR status != prev_status
ORDER BY row ASC;

执行后返回的结果行号为1、2、4、6、8，完全符合预期。

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