7 Replies Latest reply: Feb 26, 2009 1:46 PM by 589626 RSS

    How to count continuous years

    589626
      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
        • 1. Re: How to count continuous years
          Frank Kulash
          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
          • 2. Re: How to count continuous years
            Frank Kulash
            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;
            • 3. Re: How to count continuous years
              589626
              Works like a charm, thanks so much.

              Andrew
              • 4. Re: How to count continuous years
                Aketi Jyuuzou
                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
                • 5. Re: How to count continuous years
                  user503699
                  Aketi,

                  Hats off to you and your "sense of TabibitoZan" (whatever that means)...
                  Wish I can learn to "THINK" like this.
                  • 6. Re: How to count continuous years
                    Aketi Jyuuzou
                    I mentions "Tabibitozan" in below threads ;-)
                    Sum over group
                    Group by preserving the order

                    "Tabibitozan" is one of math problem.
                    I do not know what "Tabibitozan" is called in English.
                    • 7. Re: How to count continuous years
                      589626
                      That's some sweet code. Thanks for all the advice guys.