tom__bo’s Blog

MySQL!! MySQL!! @tom__bo

トップ > MySQL > MySQL 8.0新機能 CTE (Common Table Expression)

MySQL 8.0新機能 CTE (Common Table Expression)

MySQL MySQL 8.0

MySQL 8.0の新機能について調べてまとめました。
この記事は公式ドキュメントの以下のページの読んだまとめです。

dev.mysql.com

サンプルのクエリもほぼドキュメントのものですが、手元の8.0.15で実行した結果をつけている場合もあります。場合によって説明の順番を組み替えたり、補足したりしています。

CTE

CTE(Common Table Expression)とは1つのステートメントのスコープ内にできる名前付きの一時結果のこと。 この一時結果は複数回またはCTE内で再帰的に参照できるので、再帰クエリと表現されていたりするが必ずしも再帰クエリを書かなくてはいけないわけではない。

Non-recursive CTEs

with句に続けて、subqueryによる副次句に名前をつけることができ、これがCTE。
CTEはcomma区切りで複数定義することができ、with句以降のtop_levelのselectでその定義されたCTE nameにアクセスできる。

Syntax

with_clause:
    WITH [RECURSIVE]
        cte_name [(col_name [, col_name] ...)] AS (subquery)
        [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

Example

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT b, d FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

この場合、cte1, cte2がCTEの名前でこの名前でアクセスすることでそれぞれのAS以降に続くsubqueryの結果にアクセスできる。

  1. ASのあとの()は必須

  2. 再帰的にCTEにアクセスできる, その場合WITH RECURSIVEと指定する必要がある。

  3. テーブル名をつけたいときはCTE_nameの宣言の次に書く

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;

カラム名はsubqueryの中の最初のSELECTでつけることもできる

WITH cte AS
(
  SELECT 1 AS col1, 2 AS col2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;
  1. WITH句はSELECT, UPDATE, DELETEの前に書くことができる
  2. サブクエリの先頭に書くことができる
  3. SELECT文の直前に書くことができる
  4. 同じ階層(Level)に同名のCTEを書くことはできない
  5. CTEのsubqueryの中で他のCTEを参照することができる。このとき参照先のCTEが先に宣言されている必要がある。(これにより参照先が循環して再帰的にアクセスすることがない)
WITH
  cte1 AS (SELECT * FROM table1),
  cte2 AS (SELECT * FROM cte1)
SELECT * FROM cte1 UNION ALL select * from cte2

Recursive CTEs

CTEは自分自身を参照することができ、これにより再帰的なアクセスができるようになる。

まずはサンプル

mysql> with recursive cte (n) as
    -> (
    ->   select 1
    ->   union all
    ->   select n+1 from cte where n < 5
    -> )
    -> select * from cte;
+------+
| n    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
+------+
5 rows in set (0.00 sec)

再帰的なCTEのなかのsubqueryはUNION [ALL]かUNION DISTINCTを境界に2つのパートに別れている。 (個々の部分はUNION [DISTINCT] or UNION ALL? )

SELECT ...      -- return initial row set ()
UNION ALL
SELECT ...      -- return additional row sets

再帰的なCTEの1つめのselectはCTE内で最初に1度だけアクセスするクエリ.
続く2つめのSELECTは最初は1つめのselectの結果を参照し、それ以降は2つめのクエリを参照し、これを参照先の結果がからになるまで繰り返し実行される。
1つめのpartをnon recursive part, 2つめのpartをrecursive partと呼ぶ

結果の型

結果セットのカラムの型はこの1つめの結果によって決定される。

そのため、桁あふれや文字溢れが発生する場合は(defaultでは)エラーになる。
STRICT modeでない場合はこれらの結果はtruncateされる

mysql> WITH RECURSIVE cte AS
    -> (
    ->   SELECT 1 AS n, 'abc' AS str
    ->   UNION ALL
    ->   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
    -> )
    -> SELECT * FROM cte;
ERROR 1406 (22001): Data too long for column 'str' at row 1

これを防ぐには1つめのqueryをキャストしておく必要がある

mysql> WITH RECURSIVE cte AS
    -> (
    ->   SELECT 1 AS n, CAST('abc' AS CHAR(20)) AS str
    ->   UNION ALL
    ->   SELECT n + 1, CONCAT(str, str) FROM cte WHERE n < 3
    -> )
    -> SELECT * FROM cte;
+------+--------------+
| n    | str          |
+------+--------------+
|    1 | abc          |
|    2 | abcabc       |
|    3 | abcabcabcabc |
+------+--------------+
3 rows in set (0.00 sec)

名前アクセス

CTEはカラムの順番ではなく、名前でアクセスされるので、以下のようなことが可能

mysql> WITH RECURSIVE cte AS
    -> (
    ->   SELECT 1 AS n, 1 AS p, -1 AS q
    ->   UNION ALL
    ->   SELECT n + 1, q * 2, p * 2 FROM cte WHERE n < 5
    -> )
    -> SELECT * FROM cte;
+------+------+------+
| n    | p    | q    |
+------+------+------+
|    1 |    1 |   -1 |
|    2 |   -2 |    2 |
|    3 |    4 |   -4 |
|    4 |   -8 |    8 |
|    5 |   16 |  -16 |
+------+------+------+
5 rows in set (0.00 sec)

recursive CTE内の制限

recursive partでは以下の構文は使えない

  • 集約関数
  • ウィンドウ関数
  • GROUP BY
  • ORDER BY
  • LIMIT
  • DISTINCT

当然non recursive partではこの限りではない。

Explainについて

  • EXPLAINをしたときにrecursive CTEを含むクエリではExtraカラムにRecursiveが表示される
  • EXPLAINの結果では1回のイテレーションのコストが表示されるが、これは全体のコストとは異なるものになる、イテレーションが何回繰り返されるかは実行されないとわからないため、あしからず
  • recursive CTEのコストが大きくなるかはイテレーションごとの結果サイズがどれくらい大きくなるかによるが、結果が大きくなる場合はtemporary table sizeを大きくすることで改善する可能性がある。

関連パラメータ (再帰の回数,時間 制限)

  • cte_max_recursion_depthシステム変数により再帰的なクエリが実行される回数を制限することができる
  • max_execution_time システム変数によりこのセッション内で実行されるSELECTクエリの実行時間を制限することができる
  • MAX_EXECUTION_TIME オプティマイザヒントをつけることで、このヒントがあるクエリごとの実行時間を制限することができる。

サンプル

  • Fibonacci Series Generation
mysql> WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS
    -> (
    ->   SELECT 1, 0, 1
    ->   UNION ALL
    ->   SELECT n + 1, next_fib_n, fib_n + next_fib_n
    ->     FROM fibonacci WHERE n < 10
    -> )
    -> SELECT * FROM fibonacci;
+------+-------+------------+
| n    | fib_n | next_fib_n |
+------+-------+------------+
|    1 |     0 |          1 |
|    2 |     1 |          1 |
|    3 |     1 |          2 |
|    4 |     2 |          3 |
|    5 |     3 |          5 |
|    6 |     5 |          8 |
|    7 |     8 |         13 |
|    8 |    13 |         21 |
|    9 |    21 |         34 |
|   10 |    34 |         55 |
+------+-------+------------+
10 rows in set (0.01 sec)
  • Date Series Generation

準備

create table date_sample (
  `date` date,
  price int
);

insert into date_sample (`date`, `price`) values('2017-01-03', 100),
  ('2017-01-03', 20),
  ('2017-01-03', 300),
  ('2017-01-06', 400),
  ('2017-01-06', 10),
  ('2017-01-08', 20),
  ('2017-01-08', 40),
  ('2017-01-10', 70);

mysql> select * from date_sample;
  +------------+-------+
  | date       | price |
  +------------+-------+
  | 2017-01-03 |   100 |
  | 2017-01-03 |    20 |
  | 2017-01-03 |   300 |
  | 2017-01-06 |   400 |
  | 2017-01-06 |    10 |
  | 2017-01-08 |    20 |
  | 2017-01-08 |    40 |
  | 2017-01-10 |    70 |
  +------------+-------+
  8 rows in set (0.00 sec)

ここでgroup byすると日付が歯抜けなデータになってしまう

mysql> select date, sum(price) from date_sample group by date;
+------------+------------+
| date       | sum(price) |
+------------+------------+
| 2017-01-03 |        420 |
| 2017-01-06 |        410 |
| 2017-01-08 |         60 |
| 2017-01-10 |         70 |
+------------+------------+
4 rows in set (0.00 sec)

歯抜けでないデータをアプリケーション側で作ってもよいが、CTEを使うことで連続したデータを作成することができる

mysql> WITH RECURSIVE dates (date) AS
    -> (
    ->   SELECT MIN(date) FROM date_sample
    ->   UNION ALL
    ->   SELECT date + INTERVAL 1 DAY FROM dates
    ->   WHERE date + INTERVAL 1 DAY <= (SELECT MAX(date) FROM date_sample)
    -> )
    -> SELECT dates.date, COALESCE(SUM(price), 0) AS sum_price
    -> FROM dates LEFT JOIN date_sample ON dates.date = date_sample.date
    -> GROUP BY dates.date
    -> ORDER BY dates.date;
+------------+-----------+
| date       | sum_price |
+------------+-----------+
| 2017-01-03 |       420 |
| 2017-01-04 |         0 |
| 2017-01-05 |         0 |
| 2017-01-06 |       410 |
| 2017-01-07 |         0 |
| 2017-01-08 |        60 |
| 2017-01-09 |         0 |
| 2017-01-10 |        70 |
+------------+-----------+
8 rows in set (0.00 sec)
  • Hierarchical Data Traversal

準備

CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id)
);

INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

どのマネージャの部下かを階層構造を辿りながら取り出す

mysql> WITH RECURSIVE employee_paths (id, name, path) AS
    -> (
    ->   SELECT id, name, CAST(id AS CHAR(200))
    ->     FROM employees
    ->     WHERE manager_id IS NULL
    ->   UNION ALL
    ->   SELECT e.id, e.name, CONCAT(ep.path, ',', e.id)
    ->     FROM employee_paths AS ep JOIN employees AS e
    ->       ON ep.id = e.manager_id
    -> )
    -> SELECT * FROM employee_paths ORDER BY path;
+------+---------+-----------------+
| id   | name    | path            |
+------+---------+-----------------+
|  333 | Yasmina | 333             |
|  198 | John    | 333,198         |
|   29 | Pedro   | 333,198,29      |
| 4610 | Sarah   | 333,198,29,4610 |
|   72 | Pierre  | 333,198,29,72   |
|  692 | Tarek   | 333,692         |
|  123 | Adil    | 333,692,123     |
+------+---------+-----------------+
7 rows in set (0.00 sec)

Derived tablesとの違い

CTEとDerived tablesは以下のような点で似ている

  • どちらの構造も名前をつけられる
  • どちらの構造も1つのstatement内のみのスコープ内で有効

CTEはDerived tableと比べて以下のようなメリットが有る

  • CTEではこれ自体は複数回参照することができる
  • CTEは自分自身を参照して再帰的にアクセスできる
  • CTE内で他のCTEを参照できる
  • CTEはSELECTの直前に書くのでわかりやすい

参照

MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 WITH Syntax (Common Table Expressions)