This content has been marked as final. Show 7 replies
Analytic fucntions are great for that, but not as easy as you might wish.
Using the analytic LAG (or LEAD) function, you can see iwhat the difference is between a year and the last year for the same id:
Using a CASE staemnet, you can mark each row where the difference was 1 as 0, and all the rows where the difference was more than 1 as 1.
LAG (year) OVER (PARTITION BY id ORDER BY year DESC) - year
Then, using the analytic SUM function, you can add up all those numbers through the present row
Since analytic functions can't be nested, this usually involves two sub-queries.
SUM (dif) OVER (PARTITION BY id ORDER BY year DESC) AS grp
In this solution, ecery id can have a different starting point. For example, if you delete the row with id=2 and year=2009, then the count for id=2 is 1, not 0.
WITH got_new_grp AS ( SELECT id , year , CASE WHEN LAG (year) OVER ( PARTITION BY id ORDER BY year DESC ) - year > 1 THEN 1 END AS new_grp FROM table_x ) , got_grp AS ( SELECT got_new_grp.* , COUNT (new_grp) OVER ( PARTITION BY id ORDER BY year DESC ) AS grp FROM got_new_grp ) SELECT id , COUNT (*) AS cnt FROM got_grp WHERE grp = 0 GROUP BY id ORDER BY id;
You might have a special case, where the combination of id and year is unique, and the difference is always 1 or more. If so, there's a cute trick to avoid one of the sub-queries.
Edited by: Frank Kulash on Feb 25, 2009 1:48 PM
Here's a solution for the special case (no duplicate years, difference is always 1 or more):
WITH got_grp AS ( SELECT id , year , ( MAX (year) OVER (PARTITION BY id) - year ) - ROW_NUMBER () OVER (PARTITION BY id ORDER BY year DESC) AS grp FROM table_x ) SELECT id , COUNT (*) AS cnt FROM got_grp WHERE grp = -1 GROUP BY id ORDER BY id;
I used sense of TabibitoZan B-)
with YearT as( select 1 as ID,2009 as YEAR from dual union select 1,2008 from dual union select 1,2007 from dual union select 1,2006 from dual union select 1,2005 from dual union select 1,2004 from dual union select 1,2002 from dual union select 1,2001 from dual union select 2,2009 from dual union select 2,2008 from dual union select 2,2005 from dual) select ID, count(*) Keep(Dense_Rank Last order by distance) as cnt from (select ID, Year+Row_Number() over(partition by ID order by YEAR desc) as distance from YearT) group by ID; ID CNT -- --- 1 6 2 2