Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
How to count continuous years

589626
Member Posts: 11
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
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
-
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 rowSUM (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 -
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;
-
Works like a charm, thanks so much.
Andrew -
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
-
Aketi,
Hats off to you and your "sense of TabibitoZan" (whatever that means)...
Wish I can learn to "THINK" like this. -
That's some sweet code. Thanks for all the advice guys.
This discussion has been closed.