SELECT 文で WITH 句を使用する - AnalyticDB - Alibaba Cloud ドキュメントセンター

WITH 句を使用して、共通テーブル式 (CTE) を作成できます。CTE は、単一の SQL 文の期間中のみ有効な一時的な結果セットです。その後、SELECT クエリでこの結果セットを参照できます。CTE を使用すると、ネストされたクエリをフラット化したり、サブクエリを簡素化したりできます。WITH 句のサブクエリは一度しか実行されないため、クエリパフォーマンスが向上する場合があります。このトピックでは、SELECT 文で WITH 句を使用する方法について説明します。

注意事項

単一の WITH 句を使用して、1 つ以上の CTE を定義できます。複数の CTE はカンマ (,) で区切ります。CTE の後には、SQL 文または別の CTE を続ける必要があります。
CTE 文ではページングはサポートされていません。

使用方法

次の 2 つのクエリは同等です

SELECT a, b
FROM (SELECT a, MAX(b) AS b FROM t GROUP BY a) AS x;

WITH x AS (SELECT a, MAX(b) AS b FROM t GROUP BY a)
SELECT a, b FROM x;

複数のサブクエリに対する WITH 句の使用

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;

WITH 句で定義されたリレーションは相互に参照可能

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 サブクエリは一度しか実行されません。これにより、一部のクエリのパフォーマンスが向上する可能性があります。

重要

CTE 実行の最適化を有効にすると、一部のクエリのパフォーマンスが低下する場合があります。パフォーマンスの大幅な低下が見られる場合は、最適化機能を無効にすることを推奨します。

CTE 実行の最適化の有効化

特定のクエリに対する CTE 実行の最適化の有効化
特定のクエリに対して CTE 実行の最適化を有効にするには、クエリ文の前にヒントを追加します。次の例は、組み込みデータセットに対するクエリを最適化する方法を示しています：
```
/*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;
```
すべてのクエリに対する CTE 実行の最適化の有効化
SET 文を実行して、すべてのクエリに対して CTE 実行の最適化を有効にできます。次の例は、最適化を有効にする方法を示しています：
```
SET adb_config cte_execution_mode=shared;
```

CTE 実行の最適化の無効化

特定のクエリに対する CTE 最適化の無効化
特定のクエリに対して CTE 実行の最適化を無効にするには、クエリ文の前にヒントを追加します。次の例は、組み込みデータセットに対するクエリの最適化を無効にする方法を示しています：
```
/*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;
```
すべてのクエリに対する CTE 実行の最適化の無効化
SET 文を実行して、すべてのクエリに対して CTE 実行の最適化を無効にできます。次の例は、最適化を無効にする方法を示しています：
```
SET adb_config cte_execution_mode=inline;
```