Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

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

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

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

 本連載では、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句
  2. 1から5までの整数を出力
  3. 再帰With句で行の分割
  4. データ探索(木構造)
  5. データ探索(有向グラフ)
  6. 枝切り(レベル制限)
  7. 枝切り(外部結合後のwhere句)
  8. 枝切り(union集合演算)
  9. バックトラック問題(ナップサック問題)
  10. バックトラック問題(ナイト巡回問題)
  11. OracleのSearch句(深さ優先探索)を模倣
  12. OracleのSearch句(幅優先探索)を模倣
  13. OracleのCycle句を模倣

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のイメージは下記となります。非再帰項による木の根の作成をイメージしています。

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


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

著者プロフィール

バックナンバー

連載:MySQL 8.0のSQLの新機能を解説
All contents copyright © 2005-2019 Shoeisha Co., Ltd. All rights reserved. ver.1.5