使用dense_rank：

with cte as (
   select (select sum((s1.start_date < s.start_date and s1.partition != s.partition)::int) 
     from scd s1) r, s.* 
   from scd s
),
n_part as (
   select dense_rank() over (order by c.r) dr, c.* from cte c
)
select np.start_date, np.end_date, np.partition, max(np1.start_date), max(np1.end_date) 
from n_part np left join n_part np1 on np1.dr = np.dr - 1
group by np.start_date, np.end_date, np.partition
order by np.start_date, np.end_date

See fiddle .

使用窗口函数和gaps-and-islandish方法：

SELECT start_date,
       end_date,
       PARTITION,
       max(start_date) OVER (ORDER BY grp RANGE UNBOUNDED PRECEDING EXCLUDE GROUP) max_start_date, -- use max value without current group
       max(end_date) OVER (ORDER BY grp RANGE UNBOUNDED PRECEDING EXCLUDE GROUP) max_end_date -- use max value without current group
FROM
  (SELECT start_date,
          end_date,
          PARTITION,
          sum(lag) OVER (ORDER BY end_date) AS grp -- use cumulative sum to create a group
   FROM
     (SELECT *,
             CASE
                 WHEN lag(PARTITION) OVER (ORDER BY end_date) != PARTITION THEN 1
                 ELSE 0
             END lag -- use lag to determine if the partition has changed
      FROM mytable) t) tt

Fiddle

您可以执行自左连接和聚合，如下所示：

select T.start_date, T.end_date, T.partition_,
       max(D.start_date) max_start_date,
       max(D.end_date) max_end_date
from SCD T left join SCD D
on T.start_date > D.start_date and 
   T.partition_ <> D.partition_
group by T.start_date, T.end_date, T.partition_
order by T.start_date

See demo