Forum Stats

  • 3,733,243 Users
  • 2,246,736 Discussions
  • 7,856,631 Comments

Discussions

How to count continuous years

589626
589626 Member Posts: 11
edited February 2009 in SQL & PL/SQL
I have a table with the following data:

ID YEAR
1 2009
1 2008
1 2007
1 2006
1 2005
1 2004
1 2002
1 2001
2 2009
2 2008
2 2005

I want to be able to count the number of records from the current year back, but if there is a break I want to stop the count, so it is only counting continuous years that are being counted. So for the above data I would want the following returned:

ID COUNT
1 6
2 2

I cannot figure out how to do this using either a cursor or Analytical functions. Any ideas would be greatly appreciated.

Thanks,
Andrew

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    edited February 2009
    Hi, Andrew,

    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:
    LAG (year) OVER (PARTITION BY id ORDER BY year DESC) 
    - year
    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.
    Then, using the analytic SUM function, you can add up all those numbers through the present row
    SUM (dif) OVER (PARTITION BY id ORDER BY year DESC) AS grp
    Since analytic functions can't be nested, this usually involves two sub-queries.
    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;
    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.

    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
  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 40,492 Red Diamond
    Hi,

    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;
  • 589626
    589626 Member Posts: 11
    Works like a charm, thanks so much.

    Andrew
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    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
  • user503699
    user503699 Member Posts: 2,098
    Aketi,

    Hats off to you and your "sense of TabibitoZan" (whatever that means)...
    Wish I can learn to "THINK" like this.
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I mentions "Tabibitozan" in below threads ;-)
    584668
    450745

    "Tabibitozan" is one of math problem.
    I do not know what "Tabibitozan" is called in English.
  • 589626
    589626 Member Posts: 11
    That's some sweet code. Thanks for all the advice guys.
This discussion has been closed.