10-283 最新の年月からの連続数を取得

SQLパズル

ID  Year
--  ----
 1  2009
 1  2008
 1  2007
 1  2006
 1  2005
 1  2004
 1  2002
 1  2001
 2  2009
 2  2008
 2  2005
 3  2010
 3  2009
 3  2008
 4  2020

IDごとに、最新のYearからの連続したレコード数を求める。

出力結果
ID  cnt
--  ---
 1    6
 2    2
 3    3
 4    1

こちらを参考にさせていただきました(英語)

データ作成スクリプト

create table YearT(ID,Year) as
select 1,2009 from dual union all
select 1,2008 from dual union all
select 1,2007 from dual union all
select 1,2006 from dual union all
select 1,2005 from dual union all
select 1,2004 from dual union all
select 1,2002 from dual union all
select 1,2001 from dual union all
select 2,2009 from dual union all
select 2,2008 from dual union all
select 2,2005 from dual union all
select 3,2010 from dual union all
select 3,2009 from dual union all
select 3,2008 from dual union all
select 4,2020 from dual;

SQL

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;

解説

旅人算の感覚です。

9-52 最大のリージョンを求める(境界なし)

---