9-17 最新の連続期間

SQLパズル

Pensionsテーブル
 ID  year  months
---  ----  ------
AAA  2006      12  ← 出力対象
AAA  2005      12  ← 出力対象
AAA  2004      12  ← 出力対象
AAA  2003      12  ← 出力対象
AAA  2002      12  ← 出力対象
AAA  2001      12
BBB  2007      12
BBB  2006      12
BBB  2005      12
BBB  2004      12
BBB  2000      12
CCC  2007      12
CCC  2006      12
CCC  2004      12  ← 出力対象
CCC  2003      12  ← 出力対象
CCC  2002      12  ← 出力対象
CCC  2001      12  ← 出力対象
CCC  2000      12  ← 出力対象
CCC  1999      12
DDD  2007      12  ← 出力対象
DDD  2006      12  ← 出力対象
DDD  2005      12  ← 出力対象
DDD  2004      12  ← 出力対象
DDD  2003      10  ← 出力対象
DDD  2002      10  ← 出力対象
EEE  2007      12
EEE  2006       0
EEE  2005       0
EEE  2004      12  ← 出力対象
EEE  2003      12  ← 出力対象
EEE  2002      12  ← 出力対象
EEE  2001      12  ← 出力対象
EEE  2000      12  ← 出力対象
FFF  2009       5
FFF  2007       5  ← 出力対象
FFF  2006       1  ← 出力対象
FFF  2005       1  ← 出力対象
FFF  2004      10  ← 出力対象
FFF  2003      12  ← 出力対象
FFF  2002      12  ← 出力対象
FFF  2001      12  ← 出力対象
FFF  2000      12  ← 出力対象
FFF  1995      12
FFF  1994      12
FFF  1993      12
FFF  1992      12
FFF  1991      12

monthsが1以上で、yearが連続していて
yearの降順にmonthsの累計が60以上になるまでの
連続した最新のレコードを出力する

SQLパズル(日本語版)のパズル9 [社会保険の支払い] を参考にさせていただきました
SQLパズル 第2版のパズル10 [年金おくれよ] を参考にさせていただきました

データ作成スクリプト

create table Pensions(
ID     varchar2(3),
year   number(4),
months number(2) check(months between 0 and 12),
primary key(ID,year));

insert into Pensions values('AAA',2006,12);
insert into Pensions values('AAA',2005,12);
insert into Pensions values('AAA',2004,12);
insert into Pensions values('AAA',2003,12);
insert into Pensions values('AAA',2002,12);
insert into Pensions values('AAA',2001,12);
insert into Pensions values('BBB',2007,12);
insert into Pensions values('BBB',2006,12);
insert into Pensions values('BBB',2005,12);
insert into Pensions values('BBB',2004,12);
insert into Pensions values('BBB',2000,12);
insert into Pensions values('CCC',2007,12);
insert into Pensions values('CCC',2006,12);
insert into Pensions values('CCC',2004,12);
insert into Pensions values('CCC',2003,12);
insert into Pensions values('CCC',2002,12);
insert into Pensions values('CCC',2001,12);
insert into Pensions values('CCC',2000,12);
insert into Pensions values('CCC',1999,12);
insert into Pensions values('DDD',2007,12);
insert into Pensions values('DDD',2006,12);
insert into Pensions values('DDD',2005,12);
insert into Pensions values('DDD',2004,12);
insert into Pensions values('DDD',2003,10);
insert into Pensions values('DDD',2002,10);
insert into Pensions values('EEE',2007,12);
insert into Pensions values('EEE',2006, 0);
insert into Pensions values('EEE',2005, 0);
insert into Pensions values('EEE',2004,12);
insert into Pensions values('EEE',2003,12);
insert into Pensions values('EEE',2002,12);
insert into Pensions values('EEE',2001,12);
insert into Pensions values('EEE',2000,12);
insert into Pensions values('FFF',2009, 5);
insert into Pensions values('FFF',2007, 5);
insert into Pensions values('FFF',2006, 1);
insert into Pensions values('FFF',2005, 1);
insert into Pensions values('FFF',2004,10);
insert into Pensions values('FFF',2003,12);
insert into Pensions values('FFF',2002,12);
insert into Pensions values('FFF',2001,12);
insert into Pensions values('FFF',2000,12);
insert into Pensions values('FFF',1995,12);
insert into Pensions values('FFF',1994,12);
insert into Pensions values('FFF',1993,12);
insert into Pensions values('FFF',1992,12);
insert into Pensions values('FFF',1991,12);
commit;

SQL

--■■■分析関数を使う方法1(10g以降)■■■
select ID,year,months
from (select ID,year,months,StartYear,sumMonth,
      max(StartYear) over(partition by ID) as maxStartYear
      from (select ID,year,months,StartYear,
            sum(months) over(partition by ID,StartYear order by year desc)-months as sumMonth,
            sum(months) over(partition by ID,StartYear) as TotalsumMonth
            from (select ID,year,months,
                  Last_Value(LagYear ignore nulls)
                  over(partition by ID order by year desc) as StartYear
                  from (select ID,year,months,
                        case Lag(year) over(partition by ID order by year desc)
                        when year+1 then null else year end as LagYear
                          from Pensions
                        where months > 0)))
      where sumMonth < 60
        and TotalsumMonth >= 60)
where StartYear = maxStartYear
order by ID,year desc;

--■■■分析関数を使う方法2(旅人算の感覚を使用)■■■
select ID,year,months
from (select ID,year,months,dis,max(dis) over(partition by ID) as maxDis
      from (select ID,year,months,dis,
            sum(months) over(partition by ID,dis) as sumMonths,
            sum(months) over(partition by ID,dis order by year desc)-months as RunSum
            from (select ID,year,months,
                  Year+Row_Number() over(partition by ID
                                         order by year desc) as dis
                    from Pensions
                   where months >= 1))
       where sumMonths >= 60
         and RunSum <60)
 where dis = maxDis
order by ID,year desc;

--■■■分析関数を使う方法3■■■
select ID,year,months
from (select ID,year,months,
      sum(months) over(partition by ID order by year desc)-months as SumMonths
        from Pensions a
       where 0 < a.months
         and exists(select 1 from Pensions b,Pensions c
                     where a.ID = ALL(b.ID,c.ID)
                       and 0 < ALL(b.months,c.months)
                       and a.year between b.year and c.year
                       and (select sum(d.months)
                              from Pensions d
                             where d.ID = a.ID
                               and 0 < d.months
                               and d.year between b.year and c.year
                           having max(d.year) - min(d.year) = count(*) -1) >= 60))
where SumMonths < 60
order by ID,year desc;

解説

分析関数を使う方法3では、
having max(d.year) - min(d.year) = count(*) -1
で、yearが連続していることをチェックしてます

year(重複なし)が昇順に並んだ数列を元にした、
階差が1以上の整数ならば、
公差が1の等差数列 ⇔ max(year) - min(year) = count(*) - 1
の証明

max(year) は、末項
min(year) は、初項
count(*)  は、項数
とみなせます

■■■■■■■■■■■■■■■■■■■■■■■■■■
公差が1の等差数列 ⇒ 末項 - 初項 = 項数 - 1 の証明

末項 = 初項 + (項数 - 1) * 公差
なので
公差=1より 末項 = 初項 + (項数 - 1)
よって
末項 - 初項 = 項数 - 1
が成立

■■■■■■■■■■■■■■■■■■■■■■■■■■
末項 - 初項 = 項数 - 1 ⇒ 公差が1の等差数列 の証明

階差の平均をXとして
末項 = 初項 + (項数 - 1) * X
よって
初項 + (項数 - 1) * X　- 初項 = 項数 - 1
(項数 - 1) * X = 項数 - 1

項数が1の場合は、明らかに真である

項数が2以上の場合は、X = 1
階差は、1以上の整数なので
階差の平均が1ということは、階差が全て1、
すなわち、公差が1の等差数列ということである

■■■■■■■■■■■■■■■■■■■■■■■■■■

階差が1以上の整数という条件がなかったら、
このような数列でも
1 , 3 , 3.5 , 4
max-min = 4-1= 3
count(*) -1 = 4-1 = 3

すなわち max-min = count(*) -1
を満たすので注意する必要があります

■■■■■■■■■■■■■■■■■■■■■■■■■■

SQLは集合志向なので、基本的には、集合で考えるのですが、
集合の要素数が1で、重複がない時には、
ソートした数列で考えることも、時には必要ということでしょうか

---