CodeZine(コードジン)

特集ページ一覧

MySQL 8.0で『SQLパズル』の問題を解く

MySQL 8.0のSQLの新機能を解説 第3回

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2019/06/06 11:00
目次

パズル59. 期間を結合する

 以下の問題を解きます。なお、問題は『SQLパズル 第2版』242ページより引用、簡潔に改変したものです。

 スケジュール表の中でOverLapした期間をまとめるSelect文を考えます。

テーブルデータ
TimeSheetsテーブル
TaskID  StartDate   EndDate
------  ----------  ----------
     1  2019-01-01  2019-01-03
     2  2019-01-02  2019-01-04
     3  2019-01-04  2019-01-05
     4  2019-01-06  2019-01-09
     5  2019-01-09  2019-01-09
     6  2019-01-09  2019-01-09
     7  2019-01-12  2019-01-15
     8  2019-01-13  2019-01-14
     9  2019-01-14  2019-01-14
    10  2019-01-17  2019-01-17
    11  2019-02-01  2019-02-05
    12  2019-02-03  2019-02-28
    13  2019-02-07  2019-02-11
    14  2019-03-01  2019-03-31
    15  2019-03-01  2019-03-15

 TimeSheetsテーブルの重複した期間をまとめて、期間の開始と終了を表示します。

出力結果
StartDate   EndDate
----------  ----------
2019-01-01  2019-01-05
2019-01-06  2019-01-09
2019-01-12  2019-01-15
2019-01-17  2019-01-17
2019-02-01  2019-02-28
2019-03-01  2019-03-31
データ作成スクリプト
create table TimeSheets(
TaskID    int primary key,
StartDate date,
EndDate   date);

insert into TimeSheets
values( 1,date '2019-01-01',date '2019-01-03'),
      ( 2,date '2019-01-02',date '2019-01-04'),
      ( 3,date '2019-01-04',date '2019-01-05'),
      ( 4,date '2019-01-06',date '2019-01-09'),
      ( 5,date '2019-01-09',date '2019-01-09'),
      ( 6,date '2019-01-09',date '2019-01-09'),
      ( 7,date '2019-01-12',date '2019-01-15'),
      ( 8,date '2019-01-13',date '2019-01-14'),
      ( 9,date '2019-01-14',date '2019-01-14'),
      (10,date '2019-01-17',date '2019-01-17'),
      (11,date '2019-02-01',date '2019-02-05'),
      (12,date '2019-02-03',date '2019-02-28'),
      (13,date '2019-02-07',date '2019-02-11'),
      (14,date '2019-03-01',date '2019-03-31'),
      (15,date '2019-03-01',date '2019-03-15');

 解として、以下の方法が考えられます。

解1 下界との連結有無を求め、累計でグループ化する方法
with tmp1 as(
select StartDate,max(EndDate) as EndDate,
case when StartDate
       <= max(max(EndDate)) over(order by StartDate
                                 Rows between Unbounded Preceding
                                          and 1 Preceding)
     then 0 else 1 end as WillSum
  from TimeSheets
group by StartDate),
tmp2 as(
select StartDate,EndDate,
sum(WillSum) over(order by StartDate) as GID
  from tmp1)
select min(StartDate) as StartDate,max(EndDate) as EndDate
  from tmp2
group by GID
order by GID;

 最初に、StartDateの重複排除で、group by StartDateとし、EndDateは、max(EndDate)とします。

 期間の連結について、考察してみます。行Xの期間が他の行Yの期間と連結するのは、下記の2つの場合が考えられます。

期間が連結する場合
場合1 X.StartDate < Y.StartDate かつ Y.StartDate <= X.EndDate
場合2 X.StartDate > Y.StartDate かつ X.StartDate >= Y.EndDate

 場合1と場合2の数式で、行Aから見て他の行Bと連結しているなら、行Bから見ても行Aと連結していると分かります。

 以上により、StartDateの昇順に行を見ていって、StartDateが自分未満の行(下界の行)と連結してたら0、連結してなかったら1とする数の累計で、グループ化すればいいと分かります。

 SQLのイメージ(第1段階)は下記となります。group by StartDateで赤線を引いています。

 SQLのイメージ(第2段階)は下記となります。max(max(EndDate)) over(order by StartDate Rows between Unbounded Preceding and 1 Preceding) に対応する黄緑線を引いています。

 SQLのイメージ(第3段階)は下記となります。sum(WillSum) over(order by StartDate) に対応する黄緑線と、group by GID に対応する赤線を引いています。

パズル62. レポートの整形

 以下の問題を解きます。なお、問題は『SQLパズル 第2版』252、253ページより引用、簡潔に改変したものです。

 決まった列に値を配置してレポートのような形で表示するSelect文を考えます。

テーブルデータ
Namesテーブル
name
-------
Al
Ben
Charlie
David
Ed
Frank
Greg
Howard
Ida
Joe
Ken
Larry
Mike
Neal

 Namesテーブルのname列を、3行ごとに、1行で表示します(最終行の余った列は、nullとします)。出力順は、行も列も、Namesテーブルのname列の昇順とします。

出力結果
name1  name2   name3
-----  ------  -------
Al     Ben     Charlie
David  Ed      Frank
Greg   Howard  Ida
Joe    Ken     Larry
Mike   Neal    null
データ作成スクリプト
create table Names(name char(7) primary key);

insert into Names values('Al'),
                        ('Ben'),
                        ('Charlie'),
                        ('David'),
                        ('Ed'),
                        ('Frank'),
                        ('Greg'),
                        ('Howard'),
                        ('Ida'),
                        ('Joe'),
                        ('Ken'),
                        ('Larry'),
                        ('Mike'),
                        ('Neal');

 解として、以下の方法が考えられます。

解1 商と余りで分類する方法
with tmp as(
select name,
-1 + Row_Number() over(order by name) as rn
  from Names)
select
max(case rn%3 when 0 then name end) as name1,
max(case rn%3 when 1 then name end) as name2,
max(case rn%3 when 2 then name end) as name3
  from tmp
group by truncate(rn/3 , 0)
order by truncate(rn/3 , 0);

 0以上の整数を3で割った時の、商と余りは下記となります。

商と余り
0 割る 3 = 0 余り 0
1 割る 3 = 0 余り 1
2 割る 3 = 0 余り 2
3 割る 3 = 1 余り 0
4 割る 3 = 1 余り 1
5 割る 3 = 1 余り 2
6 割る 3 = 2 余り 0
7 割る 3 = 2 余り 1
8 割る 3 = 2 余り 2
省略

 以上により、商でグループ化しつつ、余りに対応したname列を、max関数とcase式を組み合わせて表示すればいいと分かります。

 SQLのイメージは下記となります。group by truncate(rn/3 , 0) で赤線を引いています。

パズル63. 連続的なグルーピング

 以下の問題を解きます。なお、問題は『SQLパズル 第2版』261ページより引用、簡潔に改変したものです。

 指定列の値が連続した区間をまとめる、Select文を考えます。

テーブルデータ
Tテーブル
num  data
---  ----
  1  aaaa
  2  aaaa
  3  bbbb
  6  bbbb
  8  aaaa
 10  cccc
 12  bbbb
 15  bbbb

 各data値がnumの何番から何番まで連続しているかを、その出現順でまとめます。

出力結果
Low  High  data
---  ----  ----
  1     2  aaaa
  3     6  bbbb
  8     8  aaaa
 10    10  cccc
 12    15  bbbb
データ作成スクリプト
create table T(
num  int primary key,
data char(4));

insert into T values( 1,'aaaa'),
                    ( 2,'aaaa'),
                    ( 3,'bbbb'),
                    ( 6,'bbbb'),
                    ( 8,'aaaa'),
                    (10,'cccc'),
                    (12,'bbbb'),
                    (15,'bbbb');

 解として、以下の2つの方法が考えられます。

解1 Lag関数でシーケンス開始を検知する方法
with tmp1 as(
select num,data,
case when data = Lag(data) over(order by num)
     then 0 else 1 end as WillSum
  from T),
tmp2 as(
select num,data,
sum(WillSum) over(order by num) as GID
  from tmp1)
select min(num) as low,max(num) as high,data
  from tmp2
group by GID,data
order by GID;

 numの昇順で行を見ていって、Lag関数を使って、シーケンス開始なら1、シーケンス開始でなければ0とする数を求め、その累計でグループ化しています。

 SQLのイメージは下記となります。sum(WillSum) over(order by num) as GIDに対応する黄緑線と、group by GID,dataに対応する赤線を引いています。

解2 旅人算メソッドを使う方法
with tmp as(
select num,data,
 Row_Number() over(order by num)
-Row_Number() over(partition by data order by num) as distance
  from T)
select min(num) as low,max(num) as high,data
  from tmp
group by data,distance
order by min(num);

 中学受験の算数で有名な旅人算の感覚を使う方法もあります。

 1進む条件が異なる4人の旅人(旅人X, 旅人A, 旅人B, 旅人C)が、数直線の原点からプラス方向に同時にスタートしたとして、下記のように考えています。

旅人X, 旅人A, 旅人B, 旅人Cの位置
・必ず1進む旅人Xの位置 = Row_Number() over(order by num)
・dataがaaaaなら1進む旅人Aの位置 = Row_Number() over(partition by data order by num)
・dataがbbbbなら1進む旅人Bの位置 = Row_Number() over(partition by data order by num)
・dataがccccなら1進む旅人Cの位置 = Row_Number() over(partition by data order by num)

 そして、group by data, distance によって、dataに対応した旅人の種類(旅人A, 旅人B, 旅人Cのどれか)と、旅人Xとの距離でグループ化してます。

 SQLのイメージは下記となります。Row_Number() over(order by num)に対応する1人の旅人と、Row_Number() over(partition by data order by num)に対応する3人の旅人をイメージし、group by data, distanceに対応する赤線を引いてます。

パズル66. 数独パズル

 ニコリの数独の問題を解きます。なお、問題は『SQLパズル 第2版』270、271ページより引用、簡潔に改変したものです。

 数独を再帰With句で解いてみましょう。

数独を解くのに使う文字列関数
select
InStr(Val,'a') as tes1,
InStr(Val,'X') as tes2,
SubStr(Val,1,2) as tes3,
SubStr(Val,3,2) as tes4,
Insert(Val,2,3,'XYZ') as tes5,
Insert(Val,2,0,'XYZ') as tes6
  from (select 'abcdef' as Val) tmp;

+------+------+------+------+--------+-----------+
| tes1 | tes2 | tes3 | tes4 | tes5   | tes6      |
+------+------+------+------+--------+-----------+
|    1 |    0 | ab   | cd   | aXYZef | aXYZbcdef |
+------+------+------+------+--------+-----------+

 数独を解くのに使う文字列関数の紹介です。

 解として、以下の方法が考えられます。

解1 幅優先探索を使う方法
with recursive Question(Val) as(
select concat('800005100',
              '001000800',
              '040200090',
              '000030002',
              '123406789',
              '600010000',
              '080009050',
              '002000400',
              '007600001')),
Renban1To9(Cnt) as(
select 1 union select 2 union select 3 union
select 4 union select 5 union select 6 union
select 7 union select 8 union select 9),
rec(LV,Val) as(
select 1,Val from Question
union all
select LV+1,
case when SubStr(Val,LV,1) = '0'
     then Insert(Val,LV,1,Cnt)
     else Val end
  from rec a Join Renban1To9 b
    on a.LV <= 81
   and (SubStr(a.Val,a.LV,1) = '0' or b.Cnt=1)
 where SubStr(Val,LV,1) != '0'
    or (InStr(SubStr(Val,truncate((LV-1)/9,0)*9+1,9),Cnt) = 0 -- 横チェック
    and InStr(SubStr(Val,(LV-1)%9+1   ,1),Cnt) = 0 -- 縦チェック
    and InStr(SubStr(Val,(LV-1)%9+1+ 9,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+18,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+27,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+36,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+45,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+54,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+63,1),Cnt) = 0
    and InStr(SubStr(Val,(LV-1)%9+1+72,1),Cnt) = 0
    -- 正方形チェック
    and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
                         +9*(truncate(truncate((LV-1)/9,0)/3,0)*3)   ,3),Cnt)=0
    and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
                         +9*(truncate(truncate((LV-1)/9,0)/3,0)*3)+ 9,3),Cnt)=0
    and InStr(SubStr(Val,truncate((LV-1)%9/3,0)*3+1
                         +9*(truncate(truncate((LV-1)/9,0)/3,0)*3)+18,3),Cnt)=0))
select SubStr(a.Val,1+9*(b.Cnt-1),9) as Answer
  from rec a,Renban1To9 b
 where a.LV=82
order by b.Cnt;

+-----------+
| Answer    |
+-----------+
| 839765124 |
| 261394875 |
| 745281396 |
| 594837612 |
| 123456789 |
| 678912543 |
| 386149257 |
| 912573468 |
| 457628931 |
+-----------+

 再帰項で、1から9までの連番テーブルとの内部結合を繰り返しつつ、where句で、その連番でマスを埋めることが可能かをチェックしています。

 SQLのイメージ(探索木の深さは3まで)は下記となります。幅優先探索の探索木をイメージしています。未確定マスは、1から9までのノードが作成候補になりつつ、枝切りされていくイメージです。

 高さ1のノードが、問題の1番上の行の1番左のマスに対応し、高さ2のノードが、問題の1番上の行の左から2番目のマスに対応し、高さ3のノードが、問題の1番上の行の左から3番目のマスに対応します。

最後に

 3回に分けて、MySQL 8.0のSQLの新機能である、Window関数と再帰With句の解説を行いました。本連載は、今回で終了となります。

参考資料



  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:MySQL 8.0のSQLの新機能を解説

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2022 Shoeisha Co., Ltd. All rights reserved. ver.1.5