MySQL道普請便り

第181回SQLの共通テーブル式(CTE)使ってみよう

近年ではRedashやMetabaseのようなBIツールを簡単に導入でき、さらにはMySQLはバージョン8.0からwindow関数が利用できるようになり、より分析的なSQLを記述する機会が増えた読者もいるかも知れません。このような分析に利用するSQLを記述をすると、どうしても長いSQLになってしまいがちで、記述した当時は理解できても、時間の経過や他人のSQLを読み解くのは難しいときもあります。

そこで、今回は共通テーブル式(CTE)について紹介し、分析時に長くなってしまいがちなSQLの読みやすさ改善に少しでもお役にたてればと思います。なお、今回利用しているMySQLのバージョンは8.0.28となります。

CTEとは?

CTEとはCommon Table Expressionsの略で、OracleやPostgreSQLにはすでにあった機能であるため知っている方もいるかもしれません。CTEは単一ステートメントのスコープ内に存在し、あとでそのステートメント内で複数回参照できる名前付き一時結果セットです。SQL内で有効な名前付きテンポラリーテーブルを作成する、といったイメージだと理解しやすいかと思います。MySQLではバージョン8.0から採用されており、さらに8.0.19では再帰CTEのSELECT部分でLIMIT句を使用することが可能になりました。

共通テーブル式(CTE)を利用するにはWITH句を使用します。

WITH CTE1 AS (SELECT a, b FROM t1)
SELECT * FROM CTE1 

さっそく使ってみる

Worldデータベースを使って共通テーブル式を使ってみましょう。まずはCountryテーブルのContinentがAsiaのものだけに絞ったテーブル(Asia)を作り、そこから3件表示させてみます。

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_max_recursion_depthという変数で決まっており、これを超えて再帰させようとした場合、以下のようなエラーが表示されます。

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.0.19以前ではLIMIT、OFFSETの利用もできませんでしたが、バージョン8.0.19でこの制限が解除されました。

CTEの利用

CTEの利用シーンとして、分析用のSQLを記述する場合で考えてみましょう。分析用に一時的にtemporaryテーブルを作成したい場合やサブクエリを使ってSQLを記述することはあるかと思います。そのようなときに、その対象のサブクエリやtemporaryテーブルの条件をCTEとして利用することが可能です。

CTEであればCREATE TEMPORARY TABLE権限も不要で利用できます。ただし、temporaryテーブルと違ってCTEにINDEXや制約をつけるようなことはできないので、規模が大きくなる場合は注意が必要です。

また、日付単位でgroup byするときなどにも有効です。通常、日付単位でgroup byするときに、特定の日に関してのデータがない場合はデータが出力されません。しかし、再帰CTEを利用して日付の一覧を出力し、そのテーブルと日付を外部結合する形で出力することで、データのない日付に関しても日付を表示することが可能です。この例に関しては、公式ドキュメントにも記載してあるのでご一読ください。

まとめ

今回は共通テーブル式(CTE)に関して簡単な使い方を説明しました。複雑なSQLを記入するときはぜひ利用してみてください。

また、公式ドキュメントには再帰CTEを利用した例としてフィボナッチ数列や日付、改装データトラバースなどの例もあるので、こちらもご一読してみてください。

おすすめ記事

記事・ニュース一覧