如何获取各ID状态变更为F的所有时间点？

现有一张名为my_table的表，数据如下：

id                  |          updated_at           | status 
-----------------------------------------+-------------------------------+--------
 ID1                                     | 2023-12-18 18:37:21.724825+00 | I
 ID1                                     | 2024-01-02 23:08:03.50741+00  | I
 ID1                                     | 2024-01-03 23:07:03.892617+00 | I
 ID1                                     | 2024-01-06 23:09:00.877018+00 | A
 ID1                                     | 2024-01-07 23:08:03.134111+00 | A
 ID1                                     | 2024-01-08 23:06:31.164932+00 | A
 ID1                                     | 2024-03-03 23:39:39.138697+00 | A
 ID1                                     | 2024-03-04 23:55:54.582789+00 | F
 ID1                                     | 2024-03-05 23:37:15.70314+00  | F
 ID1                                     | 2024-03-06 23:47:16.814729+00 | F
 ID1                                     | 2024-03-07 23:42:54.349137+00 | A
 ID1                                     | 2024-03-08 23:35:53.188792+00 | A
 ID1                                     | 2024-03-09 23:30:55.979575+00 | A
 ID1                                     | 2024-04-10 08:30:05.329453+00 | F
 ID2                                     | 2024-03-15 23:52:28.005298+00 | A
 ID2                                     | 2024-03-16 23:53:38.031816+00 | A
 ID2                                     | 2024-03-17 23:57:42.556396+00 | A
 ID2                                     | 2024-03-18 13:33:38.264444+00 | A
 ID2                                     | 2024-03-18 22:48:29.366489+00 | A
 ID2                                     | 2024-03-19 15:00:02.19655+00  | F
 ID2                                     | 2024-03-26 18:37:13.514644+00 | A
 ID2                                     | 2024-03-27 15:19:13.823159+00 | F
 ID2                                     | 2024-03-28 15:31:46.841049+00 | F
 ID2                                     | 2024-04-03 07:43:08.253604+00 | P

需要筛选出状态从任意值变更为F的所有时间点，期望输出：

ID1   | 2024-03-04 23:55:54.582789+00
ID1   | 2024-04-10 08:30:05.329453+00
ID2   | 2024-03-19 15:00:02.19655+00
ID2   | 2024-03-27 15:19:13.823159+00

当前查询只能获取每个id首次变更为F的时间点，语句如下：

SELECT
    id, updated_at
FROM (
    SELECT
        *
        , RANK() OVER (PARTITION BY id ORDER BY updated_at ASC) AS rank_
    FROM ( SELECT * FROM my_table WHERE status='F' ) t_sub
) T
WHERE rank_ = 1

如何修改查询以获取所有状态变更为F的时间点？

要捕捉所有从非F状态切换到F的时间点，需要对比当前行和上一行的状态。可以用LAG()窗口函数获取同一id下上一条记录的状态，然后筛选出当前状态为F且上一条状态不为F的记录。

修改后的查询语句：

SELECT id, updated_at
FROM (
    SELECT
        id,
        updated_at,
        status,
        -- 获取同一id的上一条记录的状态
        LAG(status) OVER (PARTITION BY id ORDER BY updated_at) AS prev_status
    FROM my_table
) t
WHERE 
    status = 'F'
    -- 上一条状态不为F（包括第一条记录就是F的情况，此时prev_status为NULL）
    AND (prev_status != 'F' OR prev_status IS NULL);

逻辑说明 #

1. LAG(status) OVER (PARTITION BY id ORDER BY updated_at)：按id分组，按时间排序，获取当前行的上一行状态。
2. 筛选条件：当前状态是F，且上一行状态不是F（或者是该id的第一条记录且状态为F），这样就能捕捉到所有首次进入F状态的节点，包括后续从其他状态再次切换回F的情况。

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