Aggregating on a repeated group

aggregateoracle

In a current report, I'm trying to track the migration of a person from department n, to n+1, or to n+k.

My data is split into a row per term (university data). An example:

dept |  term
------------
   a | 20120
   a | 20121
   a | 20122
   b | 20123 -- <- person moved to new department
   b | 20130
   c | 20131 -- <- person moved to another new department
   a | 20132 -- <- person went back to a department they've already been in

using the query

select 
  dept, 
  FIRST_VALUE(term) OVER (PARTITION BY dept ORDER BY term) as 
from
  table
order by term;

gives me:

dept |  term
------------
   a | 20120
   a | 20120
   a | 20120
   b | 20123
   b | 20123
   c | 20131 
   a | 20120 -- <- notice this is the same as the above set of a's

The duplicated a's in the first group and b's in the second group are due to other rows in the data (I'm okay with this, but removing that would be a bonus). My main problem is I need that final a to be treated as a different group; so basically showing the first each time a cluster appears.

Best Answer

This kind of problem can be solved with the use of the LAG() function:

WITH cte AS
  ( SELECT dept,
           term,
           LAG(dept) OVER (ORDER BY term) AS prev_dept
     FROM tableX
  )
SELECT dept, term
FROM cte
WHERE prev_dept <> dept 
   OR prev_dept IS NULL
ORDER BY term ;

Test at: SQL-Fiddle

Related Question