近年ではRedashやMetabaseのようなBIツールを簡単に導入でき、さらにはMySQLはバージョン8.
そこで、今回は共通テーブル式
CTEとは？
CTEとはCommon Table Expressionsの略で、OracleやPostgreSQLにはすでにあった機能であるため知っている方もいるかもしれません。CTEは単一ステートメントのスコープ内に存在し、あとでそのステートメント内で複数回参照できる名前付き一時結果セットです。SQL内で有効な名前付きテンポラリーテーブルを作成する、といったイメージだと理解しやすいかと思います。MySQLではバージョン8.
共通テーブル式
WITH CTE1 AS (SELECT a, b FROM t1)
SELECT * FROM CTE1
さっそく使ってみる
Worldデータベースを使って共通テーブル式を使ってみましょう。まずはCountryテーブルのContinentがAsiaのものだけに絞ったテーブル
mysql> WITH Asia AS (SELECT * FROM country WHERE Continent = 'Asia') SELECT Continent, Region, Name FROM Asia limit 3; +-----------+---------------------------+----------------------+ | Continent | Region | Name | +-----------+---------------------------+----------------------+ | Asia | Southern and Central Asia | Afghanistan | | Asia | Middle East | United Arab Emirates | | Asia | Middle East | Armenia | +-----------+---------------------------+----------------------+ 3 rows in set (0.00 sec)
Asiaという名前付きテーブルから3件引けることが確認できました。
共通テーブル式は、1つだけでなく複数設定することもできます。今度はAsiaとNorth Americaの2つのテーブルを設定して、そこから各テーブルの人口が2億人以上の国を抽出してみます。
mysql> WITH Asia AS (SELECT * FROM country WHERE Continent = 'Asia'), North_America AS (SELECT * FROM country WHERE Continent = 'North America') SELECT Continent, Region, Name FROM Asia WHERE Population >= 200000000 UNION SELECT Continent, Region, Name FROM North_America WHERE Population >= 200000000; +---------------+---------------------------+---------------+ | Continent | Region | Name | +---------------+---------------------------+---------------+ | Asia | Eastern Asia | China | | Asia | Southeast Asia | Indonesia | | Asia | Southern and Central Asia | India | | North America | North America | United States | +---------------+---------------------------+---------------+ 4 rows in set (0.00 sec)
2つのテーブルでも利用できることが確認できました。今回は簡単な例で利用しましたが、複雑なSQLの結果に名前をつけて再利用するときなどに非常に便利です。
出力するカラム名を指定する場合は、CTEの名前の後にカッコ書きでカラム名を指定することができます。
mysql> WITH cte (col1, col2) AS
-> (
-> SELECT 1, 2
-> UNION ALL
-> SELECT 3, 4
-> )
-> SELECT col1, col2 FROM cte;
+------+------+
| col1 | col2 |
+------+------+
| 1 | 2 |
| 3 | 4 |
+------+------+
2 rows in set (0.01 sec)
再帰CTE
CTEは自己参照をさせて再帰的に利用することも可能です。たとえば、以下のCTEは再帰を利用して、1～5までの数字を表示する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 | +------+
再帰CTEを利用するときは、再帰を終了する条件が含まれていることが重要になります。
また、再帰させる回数はcte_
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
デフォルトは1000で設定されており、SET SESSIONで変更が可能です。再帰させる回数が足りない場合は、この変数を変更する必要があります。
また、再帰CTE内で利用するSQLについては集計関数、window関数、group by, order by, distinctが利用できません。バージョン8.
CTEの利用
CTEの利用シーンとして、分析用のSQLを記述する場合で考えてみましょう。分析用に一時的にtemporaryテーブルを作成したい場合やサブクエリを使ってSQLを記述することはあるかと思います。そのようなときに、その対象のサブクエリやtemporaryテーブルの条件をCTEとして利用することが可能です。
CTEであればCREATE TEMPORARY TABLE権限も不要で利用できます。ただし、temporaryテーブルと違ってCTEにINDEXや制約をつけるようなことはできないので、規模が大きくなる場合は注意が必要です。
また、日付単位でgroup byするときなどにも有効です。通常、日付単位でgroup byするときに、特定の日に関してのデータがない場合はデータが出力されません。しかし、再帰CTEを利用して日付の一覧を出力し、そのテーブルと日付を外部結合する形で出力することで、データのない日付に関しても日付を表示することが可能です。この例に関しては、公式ドキュメントにも記載してあるのでご一読ください。
まとめ
今回は共通テーブル式
また、公式ドキュメントには再帰CTEを利用した例としてフィボナッチ数列や日付、改装データトラバースなどの例もあるので、こちらもご一読してみてください。