如何基于状态流转规则按id提取PostgreSQL表中的指定行数据

实现方案 #

核心思路是通过窗口函数给状态分组，关联missing_info和后续的pending状态，再结合首尾状态的筛选规则实现：

1. 按created_at升序排序所有行，统计到当前行之前出现的pending状态次数，将这个次数作为missing_info的分组标识，同组的missing_info都对应同一个后续的pending
2. 对每个missing_info分组，保留组内创建时间最晚的一行
3. 所有pending状态的行直接保留
4. 单独保留最早的created状态行和最晚的successful状态行

完整实现代码 #

针对你提供的补充示例数据，可直接运行的SQL如下：

WITH t1 AS (
SELECT 'A' AS id, 'missing_info' AS status, '2021-07-15 11:10:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'missing_info' AS status, '2021-07-15 11:20:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'pending' AS status, '2021-07-15 11:30:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'missing_info' AS status, '2021-07-15 12:10:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'missing_info' AS status, '2021-07-15 12:20:00'::timestamp AS created_at UNION ALL
SELECT 'A' AS id, 'pending' AS status, '2021-07-15 12:30:00'::timestamp AS created_at
),
marked_data AS (
    SELECT *,
        -- 统计当前行之前出现的pending次数，作为missing_info的分组ID
        COUNT(CASE WHEN status = 'pending' THEN 1 END) OVER (
            PARTITION BY id 
            ORDER BY created_at 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS pending_group
    FROM t1
)
SELECT id, status, created_at
FROM marked_data
WHERE 
    -- 保留所有pending行
    status = 'pending'
    -- 每个missing_info分组取最后一条
    OR (
        status = 'missing_info' 
        AND ROW_NUMBER() OVER (
            PARTITION BY id, pending_group, status 
            ORDER BY created_at DESC
        ) = 1
    )
    -- 保留最早的created行
    OR (
        status = 'created' 
        AND ROW_NUMBER() OVER (
            PARTITION BY id, status 
            ORDER BY created_at ASC
        ) = 1
    )
    -- 保留最晚的successful行
    OR (
        status = 'successful' 
        AND ROW_NUMBER() OVER (
            PARTITION BY id, status 
            ORDER BY created_at DESC
        ) = 1
    )
ORDER BY created_at;

兼容说明 #

如果你使用的PostgreSQL版本不支持在WHERE子句中直接调用窗口函数，可以额外套一层子查询，先计算出所有窗口函数的结果，再在外层做筛选即可。

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