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

SQLパズル

Tabibitoテーブル
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
   6  200612      1
   6  200701      1
   6  200702      1
   7  200611      1
   7  200701      1
   7  200703      1
   8  200610      1
   8  200712      1
   8  200801      1
   9  200611      1
   9  200612      1
   9  200701      2

codeごとで、kekkaが1で、
連続したYearごとの、最小のYearと最大のYear
を出力する。

出力結果
code  MinYear  MaxYear
----  -------  -------
   1  200401   200402
   1  200404   200404
   2  200401   200401
   2  200403   200404
   3  200401   200401
   4  200401   200401
   4  200404   200404
   6  200612   200702
   7  200611   200611
   7  200701   200701
   7  200703   200703
   8  200610   200610
   8  200712   200801
   9  200611   200612

データ作成スクリプト

create table Tabibito(
code  number(1),
Year  char(6),
kekka number(1));

insert into Tabibito Values(1,'200401',1);
insert into Tabibito Values(1,'200402',1);
insert into Tabibito Values(1,'200403',2);
insert into Tabibito Values(1,'200404',1);
insert into Tabibito Values(2,'200401',1);
insert into Tabibito Values(2,'200402',2);
insert into Tabibito Values(2,'200403',1);
insert into Tabibito Values(2,'200404',1);
insert into Tabibito Values(3,'200401',1);
insert into Tabibito Values(4,'200401',1);
insert into Tabibito Values(4,'200402',2);
insert into Tabibito Values(4,'200403',2);
insert into Tabibito Values(4,'200404',1);
insert into Tabibito Values(6,'200612',1);
insert into Tabibito Values(6,'200701',1);
insert into Tabibito Values(6,'200702',1);
insert into Tabibito Values(7,'200611',1);
insert into Tabibito Values(7,'200701',1);
insert into Tabibito Values(7,'200703',1);
insert into Tabibito Values(8,'200610',1);
insert into Tabibito Values(8,'200712',1);
insert into Tabibito Values(8,'200801',1);
insert into Tabibito Values(9,'200611',1);
insert into Tabibito Values(9,'200612',1);
insert into Tabibito Values(9,'200701',2);
commit;

SQL

--■■■旅人算の感覚を使わない方法■■■
select code,min(Year) as MinYear,max(Year) as MaxYear
from (select code,Year,kekka,willSum,
      sum(willSum) over(partition by code order by Year) as GID
      from (select code,Year,kekka,
            case Lag(Year) over(partition by code order by Year)
            when to_char(add_months(to_date(Year,'yyyymm'),-1),'yyyymm')
            then 0 else 1 end as willSum
              from Tabibito
             where kekka = 1))
group by code,GID
order by code,GID;

--■■■旅人算の感覚を使う方法■■■
select code,min(Year) as MinYear,max(Year) as MaxYear
from (select code,Year,kekka,
       to_number(substr(Year,1,4))*12+to_number(substr(Year,-2))
      -Row_Number() over(partition by code order by Year) as GID
        from Tabibito
       where kekka = 1)
group by code,GID
order by code,GID;

解説

yyyymmを12進数に変換して、旅人算の感覚を使ってます。

9-52 最大のリージョンを求める(境界なし)
10-37 後続データの存在チェック

8-19 12進数変換とrangeの組み合わせ
10-113 12進数の減算で月数を求める

---