PostgreSQL表分组实现：状态规则+Parent ID多分组需求

核心分组规则梳理 #

• 仅当status = 'approved'之后出现的status = 'open'记录，才标记为新分组的起点
• status = 'approved'作为当前分组的结束标记
• 非approved之后的open记录，不触发新分组，归属于当前已有分组（若存在）

基础场景实现（无Parent ID） #

通过窗口函数标记分组起点，再累计生成分组ID：

WITH marked_records AS (
    SELECT
        date,
        status,
        -- 标记是否为分组启动点：当前是open且上一条记录是approved
        CASE
            WHEN status = 'open' 
                 AND LAG(status) OVER (ORDER BY date) = 'approved'
            THEN 1
            ELSE 0
        END AS is_group_start
    FROM your_table
)
SELECT
    date,
    status,
    -- 累计求和生成分组ID，初始无分组的记录group_id为0，首个启动点后递增
    SUM(is_group_start) OVER (ORDER BY date) AS group_id
FROM marked_records;

逻辑说明 #

1. LAG(status) OVER (ORDER BY date)：按时间顺序获取当前记录的上一条状态
2. 仅当当前是open且上一条是approved时，标记为分组启动点（is_group_start=1）
3. 对is_group_start累计求和，每遇到一个启动点，group_id自动加1，实现分组的连续编号

扩展场景实现（新增Parent ID，按ID独立分组） #

只需在窗口函数中加入PARTITION BY parent_id，让每个Parent ID下的分组独立编号（从1开始）：

WITH marked_records AS (
    SELECT
        parent_id,
        date,
        status,
        CASE
            WHEN status = 'open' 
                 AND LAG(status) OVER (PARTITION BY parent_id ORDER BY date) = 'approved'
            THEN 1
            ELSE 0
        END AS is_group_start
    FROM your_table
)
SELECT
    parent_id,
    date,
    status,
    -- 每个Parent ID下分组从1开始计数
    SUM(is_group_start) OVER (PARTITION BY parent_id ORDER BY date) + 1 AS group_id
FROM marked_records;

逻辑说明 #

• PARTITION BY parent_id：将数据按Parent ID拆分，每个ID下单独处理排序和分组
• 累计求和后加1，确保每个Parent ID的第一个分组编号从1开始，而非0

分组分析示例：统计分组流程耗时 #

基于生成的分组ID，可直接统计每个分组的启动/结束时间及耗时：

WITH marked_records AS (
    SELECT
        parent_id,
        date,
        status,
        CASE
            WHEN status = 'open' 
                 AND LAG(status) OVER (PARTITION BY parent_id ORDER BY date) = 'approved'
            THEN 1
            ELSE 0
        END AS is_group_start
    FROM your_table
),
grouped_records AS (
    SELECT
        parent_id,
        date,
        status,
        SUM(is_group_start) OVER (PARTITION BY parent_id ORDER BY date) + 1 AS group_id
    FROM marked_records
)
SELECT
    parent_id,
    group_id,
    MIN(date) AS group_start_date, -- 分组启动时间（首个open的时间）
    MAX(date) AS group_end_date,   -- 分组结束时间（最后一条approved的时间）
    MAX(date) - MIN(date) AS process_duration_days -- 按天计算的流程耗时
FROM grouped_records
GROUP BY parent_id, group_id
ORDER BY parent_id, group_id;

注意事项 #

• 确保date字段为合法的时间类型（如TIMESTAMP），避免排序错误
• 若分组最后一条记录为open（无对应approved），该分组会被统计为"未结束"，可根据业务需求添加HAVING MAX(status) = 'approved'过滤此类分组

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