CODEWITHSUNDEEP
sqlhive
B.Mr.W.
B.Mr.W.

Reputation: 19628

SQL identify status transition between rows

I have a fact table which stores the historical status at an individual level, in a format similar to this:

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

And I am trying to calcuate the "transition" between consecutive years within each individual, the expected output will be

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

I was planning joining the table with itself like below, however, the table is big and this feel like a full cartesian join. I am wondering if there is a more efficient way like window function or similar that can be more efficient and elegant.

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;

Thoughts?

Upvotes: 1

Views: 335

Answers (1)

forpas
forpas

Reputation: 164099

You can do it with LEAD() window function.

If there are no gaps between the years:

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

If there are gaps between the years:

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

Upvotes: 1

Related Questions