MySQL 8.0の再帰With句のサンプル集

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

更新日: 2019/05/30
公開日: 2019/04/26

　本連載では、MySQL 8.0のリリースを記念して、3回にわたってSQLの新機能を解説します。第2回の本稿では、再帰With句について解説します。

はじめに

　本稿では、MySQL 8.0の新機能である再帰With句について解説します。前半は、学習効率が良いと思われる順序で、再帰With句の各機能をイメージを交えて解説します。後半は、バックトラック問題を解き、Oracleの再帰With句の機能をMySQL 8.0で模倣する方法も解説します。

対象読者

MySQLの再帰With句の理解を深めたい方

必要な環境

　本稿で扱うSQLは、MySQL 8.0.15で動作確認しました。

With句とは

　With句は、select文やupdate文、delete文で、インラインビューを作成する用途で使われます。With句には、再帰のないWith句と、再帰のあるWith句（再帰With句）があります。

　木構造や深さ優先探索、幅優先探索や有向グラフ、無向グラフに関する知識があると、再帰With句を理解しやすくなります。

　本稿では、以下の機能を順に、サンプルとともに紹介していきます。

1. 再帰のないWith句

　再帰のないWith句の主な使い道は、3つあります。1つは、テスト用の仮想テーブルの作成です。

テスト用の仮想テーブルの作成

with work(Val1,Val2) as(
select 1,8 union all
select 2,9 union all
select 6,1)
select sum(Val1) as SumVal1,
sum(Val2) as SumVal2
  from work;

+---------+---------+
| SumVal1 | SumVal2 |
+---------+---------+
|       9 |      18 |
+---------+---------+

　2つ目は、select文の結果を自己結合などで複数回使いたい時の2度書き防止です。以下がサンプルです。

create table NonRecWithSample(ID char(3),Val int);
insert into NonRecWithSample values('AAA',100),
                                   ('AAA',300),
                                   ('BBB',500),
                                   ('CCC',100),
                                   ('DDD',200),
                                   ('DDD',700);

select文の2度書き防止

with tmp as(
select ID,sum(Val) as SumVal
  from NonRecWithSample
group by ID)
select a.ID as a_ID,a.SumVal as a_SumVal,
b.ID as b_ID,b.SumVal as b_SumVal
  from tmp a Join tmp b
    on a.ID < b.ID
   and a.SumVal < b.SumVal
order by a_ID,b_ID;

+------+----------+------+----------+
| a_ID | a_SumVal | b_ID | b_SumVal |
+------+----------+------+----------+
| AAA  |      400 | BBB  |      500 |
| AAA  |      400 | DDD  |      900 |
| BBB  |      500 | DDD  |      900 |
| CCC  |      100 | DDD  |      900 |
+------+----------+------+----------+

　3つ目は、インラインビューを含むSQLを、上から下に読めるようにすることによる、可読性の向上です。

インラインビューを含むSQL

select ID,Val
from (select ID,Val,
      count(*) over(partition by ID) as cnt
      from NonRecWithSample) a
where cnt = 1;

With句を使って、上から下に読めるようにしたSQL

with tmp as(
select ID,Val,
count(*) over(partition by ID) as cnt
  from NonRecWithSample)
select ID,Val
  from tmp
 where cnt = 1;

2. 1から5までの整数を出力

　1から5までの整数を出力してみます。なお、再帰のあるWith句では、recursiveキーワードが必須となります。

1から5までの整数を出力

with recursive rec(Val) as(
select 1
union all
select Val+1
  from rec
 where Val+1 <= 5)
select Val from rec;

+------+
| Val  |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+

　再帰With句では、union allの上を「非再帰項」と呼び、union allの下を「再帰項」と呼びます。

　再帰With句は、最初に非再帰項のselect文を実行して、そのselect文の結果を使って再帰項のselect文を実行し、またそのselect文の結果を使って再帰項のselect文を実行して、さらにそのselect文の結果を使って再帰項のselect文を実行して（以下続く……）といった処理を、再帰項のselect文の結果が0件になるまで行います。

　上記のSQLでは、最初に非再帰項のselect 1 で、1行作成しています。次に再帰項において、select句のVal+1とwhere句のVal+1 <= 5で、2から5までの整数を持つ行を作成しています。

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

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

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

3. 再帰With句で行の分割

　再帰With句で1行を複数行に分割してみます。

create table RowToRows(ID char(2),FromVal int,ToVal int);
insert into RowToRows values('AA',2,4),
                            ('BB',8,9),
                            ('CC',3,5);

　IDごとに、FromValからToValまでの整数の行を作成します。

1行を複数行に分割

with recursive rec(ID,FromVal,ToVal) as(
select ID,FromVal,ToVal
  from RowToRows
union all
select ID,FromVal+1,ToVal
  from rec
 where FromVal+1 <= ToVal)
select ID,FromVal as Val from rec
order by ID,Val;

+------+------+
| ID   | Val  |
+------+------+
| AA   |    2 |
| AA   |    3 |
| AA   |    4 |
| BB   |    8 |
| BB   |    9 |
| CC   |    3 |
| CC   |    4 |
| CC   |    5 |
+------+------+

　再帰項でToValを上限として数値の加算を行って、行を再帰的に作成しています。

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