SQL identify行之间的状态转换

Question

我有一个事实表，它以类似于下面的格式存储单个级别的历史状态：

---------------------------
| person |  year | state |
---------------------------
| P1     | 2001  | S1    |
| P1     | 2002  | S1    | 
| P1     | 2003  | S2    | 
...
| PN     | ...   | SX    |
--------------------------

我正在尝试计算每个人的连续年份之间的“过渡”，预期产出将是

-------------------------------------
| person |  year | state | year | state |
-------------------------------------
| P1     | 2001  | S1    | 2002 | S1
| P1     | 2002  | S1    | 2003 | S2
| P1     | 2003  | S2    | 2004 | ..
...
| PN     | ...   | SX    | ...  | ..
-------------------------------------

我本打算像下面这样自己连接这个表，但是，这个表很大，这感觉像是一个完整的笛卡尔连接。我想知道是否有更有效的方法，如窗口函数或类似的，可以更有效和优雅。

select 
    t1.person, 
    t1.year, 
    t1.state, 
    t2.year as year_next, 
    t2.state as state_next
from table t1
inner join table t2 
on t1.person = t2.person 
and t1.year + 1 = t2.year;

有什么想法？

Answer 1

您可以使用LEAD()窗口函数来完成此操作。

如果年份之间没有间隔：

select *, 
  lead(year) over (partition by person order by year) next_year,
  lead(state) over (partition by person order by year) next_state
from tablename
order by person, year

如果年份之间有差距：

select person, year, state,
       case when next_year = year + 1 then next_year end next_year,
       case when next_year = year + 1 then next_state end next_state
from (
  select *, 
    lead(year) over (partition by person order by year) next_year,
    lead(state) over (partition by person order by year) next_state
  from tablename
) t
order by person, year