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

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

パズル21. 飛行機と飛行士

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

　工場のすべての飛行機を操縦できるパイロットを出力するSelect文を考えます。

テーブルデータ

PilotSkillsテーブル     Hangarテーブル
Pilot    Plane          Plane
-------  --------       --------
Celko    ペリカン       ジャビィ
Higgins  ハンター       ハンター
Higgins  ファルコ       ファルコ
Higgins  ペリカン
Jones    ハンター
Jones    ファルコ
Smith    ジャビィ
Smith    ハンター
Smith    ファルコ
Wilson   ジャビィ
Wilson   ハンター
Wilson   ファルコ
Wilson   ラップル

　Hangarテーブルの全レコードのPlaneを持つPilotを出力します（Hangarテーブルが空集合のケースは考えません）。

出力結果

Pilot
------
Smith
Wilson

データ作成スクリプト

create table PilotSkills(
Pilot char(7),
Plane char(16),
primary key(Pilot, Plane));

insert into PilotSkills values('Celko'  ,'ペリカン'),
                              ('Higgins','ハンター'),
                              ('Higgins','ファルコ'),
                              ('Higgins','ペリカン'),
                              ('Jones'  ,'ハンター'),
                              ('Jones'  ,'ファルコ'),
                              ('Smith'  ,'ジャビィ'),
                              ('Smith'  ,'ハンター'),
                              ('Smith'  ,'ファルコ'),
                              ('Wilson' ,'ジャビィ'),
                              ('Wilson' ,'ハンター'),
                              ('Wilson' ,'ファルコ'),
                              ('Wilson' ,'ラップル');

create table Hangar(Plane char(16) primary key);
insert into Hangar values('ジャビィ'),
                         ('ハンター'),
                         ('ファルコ');

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

解1 not in述語で差集合を求める方法

select distinct Pilot
  from PilotSkills a
 where not exists(select b.Plane from Hangar b
                   where b.Plane
           not in(select c.Plane from PilotSkills c
                   where c.Pilot = a.Pilot))
order by Pilot;

　not in述語で求めた差集合が、空集合かどうかで、包含関係をチェックしています。数学的には、下記の同値な命題を踏まえています。なお、X∩Not(Y) は not in述語で実現しています。

同値な命題

X∩Not(Y) = 空集合 ⇔ X⊆Y

解2 Window関数のcount関数で件数を求めてから結合する方法

select b.Pilot
from (select Plane,count(*) over() as cnt
        from Hangar) a
Join PilotSkills b
  on a.Plane = b.Plane
group by b.Pilot,a.cnt
having count(*) = a.cnt
order by b.Pilot;

　最初にインラインビューでWindow関数のcount関数を使い、Hangarテーブルの行数を列別名cntとして求めています。

　次に、Planeが等しいことを条件として内部結合（等価結合）しています。そして、group by b.Pilot,a.cntでグループ化し、having count(*) = a.cntによって、内部結合した結果の件数がHangarテーブルの行数と同じであることを抽出条件としています。

　数学的には、下記の同値な命題を踏まえています。なお、X∩Y は、内部結合で実現しています。

同値な命題

X∩Y = X ⇔ X⊆Y

　SQLのイメージは下記となります。Window関数のcount関数に対応する黄緑線を引き、Planeが等しいことを条件とした内部結合（等価結合）に対応するベン図をイメージしながら、青線や紫線などを引き、group by b.Pilot, a.cntに対応する赤線を引いています。

パズル27. 等しい集合を見つける

　以下の問題を解きます。『達人に学ぶ SQL徹底指南書 第2版』の、「等しい部分集合を見つける」（189ページ）で扱われているのと同じテーブルデータを用います。なお、問題は『SQLパズル 第2版』116、117ページより引用、簡潔に改変したものです。

　同一テーブルで等しい集合の組み合わせを求めるSelect文を考えます。

テーブルデータ

SupPartsテーブル
sup  part
---  --------
A    ボルト
A    ナット
A    パイプ
B    ボルト
B    パイプ
C    ボルト
C    ナット
C    パイプ
D    ボルト
D    パイプ
E    ヒューズ
E    ナット
E    パイプ
F    ヒューズ

　数も種類もまったく同じpartを取り扱うsupの組み合わせを求めます。

出力結果

s1  s2
--  --
A   C
B   D

データ作成スクリプト

create table SupParts(
sup  char(1),
part char(16),
primary key(sup,part));

insert into SupParts values('A','ボルト'),
                           ('A','ナット'),
                           ('A','パイプ'),
                           ('B','ボルト'),
                           ('B','パイプ'),
                           ('C','ボルト'),
                           ('C','ナット'),
                           ('C','パイプ'),
                           ('D','ボルト'),
                           ('D','パイプ'),
                           ('E','ヒューズ'),
                           ('E','ナット'),
                           ('E','パイプ'),
                           ('F','ヒューズ');

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

解1 内部結合後の件数を調べる方法

with tmp as(
select sup,part,count(*) over(partition by sup) as cnt
  from SupParts)
select a.sup as s1,b.sup as s2
  from tmp a Join tmp b
    on a.sup < b.sup
   and a.cnt = b.cnt
   and a.part = b.part
group by a.sup,b.sup,a.cnt
having count(*) = a.cnt
order by a.sup,b.sup;

　まず、Window関数のcount関数でsupごとの件数を求めた結果を、仮想表tmpとしています。

　次に、supが自分より大きいこと、件数が等しいこと、partが等しいことを条件として自己内部結合させています。With句は、select文の結果同士を自己結合させる際に使うと便利です。

　そして、GroupBy句でsupの組み合わせでグループ化して、having count(*) = a.cntで、内部結合によって件数が減らなかったsupの組み合わせを出力対象としています。

　SQLのイメージは、下記となります。仮想表tmpのselect文のcount(*) over(partition by sup)に対応する赤線と黄緑線を引いてから、仮想表tmp同士の、supが自分より大きいこと、件数が等しいこと、partが等しいことを条件とした自己内部結合をイメージしています。

　数学の集合では、集合の相等性を調べる公式として、下記の公式

(A ⊆ B ) かつ (A ⊇ B) ⇔ (A = B)

　が有名ですが、下記も成立します。

(集合Aと集合Bの要素数が等しい) かつ (A ⊆ B) ⇔ (A = B)

　集合Aと集合Bが両方とも空集合の場合は、成立するのは明らかです。また、集合Aと集合Bが両方とも空集合でない場合は、要素数が等しくて包含関係が成立するのは、A=Bの場合しかないからです。

　要素数は、Window関数のcount関数を使えば求まりますし、包含関係は、要素が等しいことを条件として内部結合して、要素数が減らなかったかを調べれば分かります。

解2 group_concat関数を使う方法

with tmp as(
select sup,group_concat(part order by part) as PartList
  from SupParts
group by sup)
select a.sup,b.sup
  from tmp a Join tmp b
    on a.sup < b.sup
   and a.PartList = b.PartList
order by a.sup,b.sup;

　group_concat関数にOrderByを指定しておいて、連結した文字列同士が等しいかをチェックしてもいいです。

パズル48. 非グループ化

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

　1行を複数行に変換するSelect文を考えます。

テーブルデータ

Inventoryテーブル
goods  pieces
-----  ------
AAAAA       0
BBBBB       1
CCCCC       2
DDDDD       3

　商品（piece）1個につき、1行として出力しましょう。

出力結果

goods  piece
-----  -----
BBBBB      1
CCCCC      1
CCCCC      1
DDDDD      1
DDDDD      1
DDDDD      1

データ作成スクリプト

create table Inventory(
goods  char(5) primary key,
pieces int);

insert into Inventory values('AAAAA',0),
                            ('BBBBB',1),
                            ('CCCCC',2),
                            ('DDDDD',3);

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

解1 再帰With句を使う方法

with recursive rec(goods,pieces,cnt) as(
select goods,pieces,1
  from Inventory
 where 1 <= pieces
union all
select goods,pieces,cnt+1
  from rec
 where cnt+1 <= pieces)
select goods,1 as piece
  from rec
order by goods;

　非再帰項でpiecesが1以上である行を抽出して、再帰項でcntのインクリメントを繰り返しています。

　非再帰項の段階での、SQLのイメージは下記となります。非再帰項による木の根の作成をイメージしています。

　再帰項の段階での、SQLのイメージは下記となります。再帰項による木のノードの作成をイメージしています。

　再帰With句の処理は、非再帰項と再帰項で2段階に分けてイメージすると分かりやすいです。

パズル53. テーブルを列ごとに折りたたむ

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

　特定の順序に従って、最初の非null値をまとめる、Select文を考えます。

テーブルデータ

Foobarテーブル
lvl  color  length  width  hgt
---  -----  ------  -----  ----
  1  RED         8     10    12
  2  null     null   null    20
  3  null        9     25    25
  4  BLUE     null     67  null
  5  GRAY     null   null  null

　lvlの降順に、各列の値を見ていって、それぞれの最初の非null値をまとめて出力します。

出力結果

color  length  width  hgt
-----  ------  -----  ---
GRAY        9     67   25

データ作成スクリプト

create table Foobar(
lvl    int primary key,
color  char(4),
length int,
width  int,
hgt    int);

insert into Foobar values(1,'RED' ,   8,  10,  12),
                         (2,null  ,null,null,  20),
                         (3,null  ,   9,  25,  25),
                         (4,'BLUE',null,  67,null),
                         (5,'GRAY',null,null,null);

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

解1 非nullな最大のソートキーを求める方法

with tmp as(
select lvl,color,length,width,hgt,
max(case when color  is not null then lvl end) over() as colorTarget,
max(case when length is not null then lvl end) over() as lengthTarget,
max(case when width  is not null then lvl end) over() as widthTarget,
max(case when hgt    is not null then lvl end) over() as hgtTarget
  from Foobar)
select max(case lvl when colorTarget  then color  end) as color,
       max(case lvl when lengthTarget then length end) as length,
       max(case lvl when widthTarget  then width  end) as width,
       max(case lvl when hgtTarget    then hgt    end) as hgt
  from tmp;

　まず、Window関数のmax関数で、colorやlengthなどが非nullであるlvlの最大値を求めています。次に、集約関数のmax関数で、そのlvlの行の、colorやlengthなどの値を求めています。

　SQLのイメージは下記となります。4つの、Window関数のmax関数にそれぞれ対応した、黄緑線と青線、緑線、紫線を引いています。

パズル56. ホテルの部屋番号

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

　ホテルのルームナンバーを割りあてるUpdate文を考えます。

テーブルデータ

Hotelテーブル
floor_nbr  room_nbr
---------  --------
        1      null
        1      null
        1      null
        2      null
        2      null
        3      null

　room_nbrを(floor_nbr*100) + 1から始まる連番に更新します。

更新結果

floor_nbr  room_nbr
---------  --------
        1       101
        1       102
        1       103
        2       201
        2       202
        3       301

データ作成スクリプト

create table Hotel(
floor_nbr int,
room_nbr  int);

insert into Hotel values(1,null),
                        (1,null),
                        (1,null),
                        (2,null),
                        (2,null),
                        (3,null);

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

解1 インラインビューと結合させる方法

alter table Hotel add column ColPK int auto_increment primary key;

with tmp as(
select ColPK,
floor_nbr * 100 + Row_Number() over(partition by floor_nbr) as NewVal
  from Hotel)
update Hotel a Join tmp Using(ColPK)
   set a.room_nbr = tmp.NewVal;

　Window関数を使用したビューに対してUpdate文を実行すると、MySQL 8.0では文法エラーになるので、Window関数を使用したインラインビューと結合させています。

　主キーがないので、オートインクリメントな主キーを追加しています。

　SQLのイメージは下記となります。Row_Number() over(partition by floor_nbr)に対応する赤線と黄緑線を引いています。

• 参考リンク：MySQL Bugs: #88397: CTE with window function don't work with update/delete as expected.