WITH 句は、共通テーブル式 (CTE) を定義します。CTE は、単一の SQL ステートメントのスコープ内で参照できる、名前付きの一時的な結果セットです。CTE は、後続の SELECT ステートメント内でテーブルと同様に参照できます。CTE を使用すると、深くネストされたサブクエリを読みやすく再利用可能な構成要素に置き換え、複雑なクエリを簡素化できます。
仕組み
WITH 句内のすべての CTE は、メインクエリが実行される前に定義されます。WITH 句内のサブクエリは 1 回だけ実行されるため、クエリのパフォーマンスを向上させることができます。CTE 実行の最適化が有効になっている場合、複数回参照される CTE は正確に 1 回だけ実行され、すべての参照はその共有結果から読み取られます。
構文
WITH
cte_name AS (subquery)
[, cte_name2 AS (subquery2) ...]
SELECT ...
FROM cte_name [, cte_name2 ...];-
複数の CTE はカンマで区切ります。
-
各 CTE の後には、別の CTE またはメイン SQL ステートメントが続く必要があります。
-
リスト内で先に定義された CTE は、その後に定義された CTE から参照できます。
注意事項
-
CTE ステートメントではページングはサポートされていません。
-
WITH句はSELECTステートメントでサポートされています。
例
ネストされたサブクエリの置き換え
次の 2 つのクエリは同等です。CTE バージョンの方が読みやすく、保守しやすくなっています。
-- ネストされたサブクエリ
SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;
-- 同等の CTE
WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;複数の CTE の定義
単一の WITH 句を使用して複数の CTE を定義し、メインクエリ内でそれらを JOIN できます。
WITH
t1 AS (SELECT a, MAX(b) AS b FROM x GROUP BY a),
t2 AS (SELECT a, AVG(d) AS d FROM y GROUP BY a)
SELECT t1.*, t2.*
FROM t1 JOIN t2 ON t1.a = t2.a;CTE のチェーン
CTE は、同じ WITH 句内で先に定義された CTE を参照できます。
WITH
x AS (SELECT a FROM t),
y AS (SELECT a AS b FROM x),
z AS (SELECT b AS c FROM y)
SELECT c FROM z;CTE 実行の最適化
カーネルバージョン 3.1.9.3 以降を実行している AnalyticDB for MySQL クラスターは、CTE 実行の最適化をサポートしています。この機能は、デフォルトでは無効になっています。この機能を有効にするには、cte_execution_mode 設定項目を設定します。有効にすると、複数回参照される CTE サブクエリは 1 回だけ実行され、すべての参照はその共有結果から読み取られます。これにより、冗長な計算を回避できます。
CTE 実行の最適化を有効にすると、一部のクエリのパフォーマンスが低下する可能性があります。パフォーマンスが大幅に低下した場合は、最適化を無効にしてください。
CTE 実行の最適化の有効化
特定のクエリの場合、ステートメントの前に /*cte_execution_mode=shared*/ ヒントを追加します。
/*cte_execution_mode=shared*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;すべてのクエリの場合、次のコマンドを実行します。
SET adb_config cte_execution_mode=shared;CTE 実行の最適化の無効化
特定のクエリの場合、ステートメントの前に /*cte_execution_mode=inline*/ ヒントを追加します。
/*cte_execution_mode=inline*/
WITH shared AS (SELECT L_ORDERKEY, L_SUPPKEY FROM ADB_SampleData_TPCH_10GB.lineitem JOIN ADB_SampleData_TPCH_10GB.orders WHERE L_ORDERKEY = O_ORDERKEY)
SELECT * FROM shared s1, shared s2 WHERE s1.L_ORDERKEY = s2.L_SUPPKEY;すべてのクエリの場合、次のコマンドを実行します。
SET adb_config cte_execution_mode=inline;