Use the WITH clause in a SELECT statement - AnalyticDB - Centre de documentation Alibaba Cloud

La clause WITH définit une expression de table commune (CTE), c'est-à-dire un ensemble de résultats temporaire nommé, dont la portée est limitée à une seule instruction SQL. Utilisez la CTE comme une table dans l'instruction SELECT qui suit. Les CTE simplifient les requêtes complexes en remplaçant les sous-requêtes profondément imbriquées par des blocs lisibles et réutilisables.

Fonctionnement

Toutes les CTE d'une clause WITH sont définies avant l'exécution de la requête principale. La sous-requête de la clause WITH n'est exécutée qu'une seule fois, ce qui peut améliorer les performances de la requête. Lorsque l'optimisation de l'exécution des CTE est activée, une CTE référencée plusieurs fois s'exécute exactement une fois, et toutes les références exploitent ce résultat partagé.

Syntaxe

WITH
  cte_name AS (subquery)
  [, cte_name2 AS (subquery2) ...]
SELECT ...
FROM cte_name [, cte_name2 ...];

Séparez plusieurs CTE par une virgule.
Chaque CTE doit être suivie d'une autre CTE ou de l'instruction SQL principale.
Les CTE définies plus tôt dans la liste peuvent être référencées par les CTE définies ultérieurement.

Notes d'utilisation

La pagination n'est pas prise en charge dans les instructions CTE.
La clause WITH est prise en charge dans les instructions SELECT.

Exemples

Remplacer une sous-requête imbriquée

Ces deux requêtes sont équivalentes. La version utilisant une CTE est plus facile à lire et à maintenir.

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

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

Définir plusieurs CTE

Utilisez une seule clause WITH pour définir plusieurs CTE et les joindre dans la requête principale.

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;

Enchaîner des CTE

Les CTE peuvent faire référence à des CTE antérieures dans la même clause 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;

Optimisation de l'exécution des CTE

Les clusters AnalyticDB for MySQL exécutant la version du noyau 3.1.9.3 ou ultérieure prennent en charge l'optimisation de l'exécution des CTE. Cette fonctionnalité est désactivée par défaut. Pour l'activer, définissez le paramètre de configuration CTE_EXECUTION_MODE. Lorsqu'elle est activée, une sous-requête CTE référencée plusieurs fois n'est exécutée qu'une seule fois, et toutes les références exploitent ce résultat partagé, évitant ainsi les calculs redondants.

Important

L'activation de l'optimisation de l'exécution des CTE peut réduire les performances de certaines requêtes. Si vous constatez une baisse significative des performances, désactivez l'optimisation.

Activer l'optimisation de l'exécution des CTE

Pour une requête spécifique, ajoutez l'indication /*cte_execution_mode=shared*/ avant l'instruction :

/*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;

Pour toutes les requêtes, exécutez :

SET adb_config cte_execution_mode=shared;

Désactiver l'optimisation de l'exécution des CTE

Pour une requête spécifique, ajoutez l'indication /*cte_execution_mode=inline*/ avant l'instruction :

/*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;

Pour toutes les requêtes, exécutez :

SET adb_config cte_execution_mode=inline;