假设您有前两个数据行，并想要衍生后两个数据行，请将此视为间隙与岛的问题：

with groups as (  -- Assign partitions
  select *, 
         coalesce(
           sum(case when status = 'approved' then 1 else 0 end) 
             over (order by date rows between unbounded preceding 
                                          and 1 preceding),
           0
         ) + 1 as partition
    from do_part
)
select date, status, 
       case   -- Construct text descriptions
         when partition != coalesce(lead(partition) over w, partition) 
           then format('Group %s Ends', partition)
         when partition = lag(partition) over w
           then '' 
         else format('Group %s Starts', partition)
       end as "group",
       partition
  from groups
window w as (order by date);

Fiddle here

demo基于（Mike Organek）的小提琴。
想法：带distinct on的左连接可以正确地剪切组。

SELECT DISTINCT ON (date,status)
    date,
    status,
    coalesce(date_d, CURRENT_DATE) AS date_end
FROM
    do_part t
    LEFT JOIN (
        SELECT
            date AS date_d
        FROM
            do_part
        WHERE
            status = 'approved'
        ORDER BY
            date) s ON s.date_d >= t.date
ORDER BY
    date,status,
    date_d;

最终查询（可简化）：

WITH cte AS (
    SELECT DISTINCT ON (date,
        status)
        date,
        status,
        coalesce(date_d, CURRENT_DATE) AS date_end
    FROM
        do_part t
        LEFT JOIN (
            SELECT
                date AS date_d
            FROM
                do_part
            WHERE
                status = 'approved'
            ORDER BY
                date) s ON s.date_d >= t.date
        ORDER BY
            date,
            status,
            date_d
),
cte1 AS (
    SELECT
        *,
        date_end - first_value(date) OVER (PARTITION BY date_end ORDER BY date) AS date_gap,
    dense_rank() OVER (ORDER BY date_end),
    CASE WHEN (date = first_value(date) OVER (PARTITION BY date_end ORDER BY date)) THEN
        'group begin'
    WHEN (status = 'approved') THEN
        'group end '
    ELSE
        NULL
    END AS grp
FROM
    cte
)
SELECT
    *,
    CASE WHEN grp IS NOT NULL THEN
        grp || dense_rank::text
    END
FROM
    cte1;