10-37 後続データの存在チェック

SQLパズル

Tes1テーブル
Code  Year    Kekka
----  ------  -----
  1   200401      1
  1   200402      1  ←出力対象
  1   200403      2
  1   200404      1  ←出力対象
  2   200401      1  ←出力対象
  2   200402      2
  2   200403      1
  2   200404      1  ←出力対象
  3   200401      1  ←出力対象
  4   200401      1  ←出力対象
  4   200402      2
  4   200403      2
  4   200404      1  ←出力対象
  5   200312      1
  5   200401      1  ←出力対象
  5   200412      1  ←出力対象

Year列には年月が入り、
Kekka列には、その月の結果有無が入る(1:無し、2:有り)

Kekkaに1が入っているYearを出力する。
ただし、Kekkaに1が入っているYearの月が連続している場合は、最大のYearのみを出力する。

データ作成スクリプト

create table Tes1(
Code  number(1),
Year  char(6),
Kekka number(1));

insert into tes1 values(1,'200401',1);
insert into tes1 values(1,'200402',1);
insert into tes1 values(1,'200403',2);
insert into tes1 values(1,'200404',1);
insert into tes1 values(2,'200401',1);
insert into tes1 values(2,'200402',2);
insert into tes1 values(2,'200403',1);
insert into tes1 values(2,'200404',1);
insert into tes1 values(3,'200401',1);
insert into tes1 values(4,'200401',1);
insert into tes1 values(4,'200402',2);
insert into tes1 values(4,'200403',2);
insert into tes1 values(4,'200404',1);
insert into tes1 values(5,'200312',1);
insert into tes1 values(5,'200401',1);
insert into tes1 values(5,'200412',1);
commit;

SQL

--■■■existsを使う方法■■■
select Code,Year,Kekka
from Tes1 a
where not exists(select 1 from Tes1 b
                  where b.Code=a.Code
                    and b.Year=to_char(add_months(to_date(a.Year,'yyyymm'),1),'yyyymm')
                    and b.Kekka=1)
  and a.Kekka=1
order by Code,Year;

--■■■分析関数を使う方法■■■
select Code,Year,Kekka
from (select Code,Year,
      Lead(Year)  over(partition by Code order by Year) as LeadYear,
      Kekka,
      Lead(Kekka) over(partition by Code order by Year) as LeadKekka
      from Tes1)
where Kekka=1
  and not (LeadKekka = 1
           and LeadYear=to_char(add_months(to_date(Year,'yyyymm'),1),'yyyymm'))
   or LeadYear is null;

解説

add_months関数とto_char関数を組み合わせて、
1ヵ月後の月をyyyymm形式で取得してます。

10-250 12進数変換を行って、旅人算の感覚

---