使用 lag() . 我想这正是你想要的：

select t.ref, t.status, t.fromDate as changedDate
from (select t.*,
             lag(status) over (partition by ref order by fromDate) as prev_status,
             row_number() over (partition by ref order fromDate) as seqnum
      from t
     ) t
where prev_status <> status or
      prev_status is null and status is not null or
      prev_status is not null and status is null or
      seqnum = 1;

这个 NULL 值使这有点棘手。最后一个条件处理ref的第一行为 NULL .
另一种方法比较简单，我认为：

select t.ref, t.status, t.fromDate as changedDate
from (select t.*,
             lag(fromDate) over (partition by ref order by fromDate) as prev_date,
             lag(fromDate) over (partition by ref, status order by fromDate) as prev_status_date
      from t
     ) t
where prev_status_date is null or
      prev_status_date <> prev_date;

这只是将状态的前一个日期与整个记录的前一个日期进行比较。