Expression de table communeCommon table expression

Une expression de table commune (CTE) définit un jeu de résultats temporaire qu’un utilisateur peut référencer éventuellement plusieurs fois dans l’étendue d’une instruction SQL.A common table expression (CTE) defines a temporary result set that a user can reference possibly multiple times within the scope of a SQL statement. Une CTE est utilisée principalement dans une instruction SELECT.A CTE is used mainly in a SELECT statement.

SyntaxeSyntax

WITH common_table_expression [ , ... ]

Tandis que common_table_expression est défini commeWhile common_table_expression is defined as

expression_name [ ( column_name [ , ... ] ) ] [ AS ] ( query )

ParamètresParameters

  • expression_nameexpression_name

    Spécifie le nom de l’expression de table commune.Specifies a name for the common table expression.

  • requêtequery

    Instruction SELECT.A SELECT statement.

ExemplesExamples

-- CTE with multiple column aliases
WITH t(x, y) AS (SELECT 1, 2)
SELECT * FROM t WHERE x = 1 AND y = 2;
+---+---+
|  x|  y|
+---+---+
|  1|  2|
+---+---+

-- CTE in CTE definition
WITH t AS (
    WITH t2 AS (SELECT 1)
    SELECT * FROM t2
)
SELECT * FROM t;
+---+
|  1|
+---+
|  1|
+---+

-- CTE in subquery
SELECT max(c) FROM (
    WITH t(c) AS (SELECT 1)
    SELECT * FROM t
);
+------+
|max(c)|
+------+
|     1|
+------+

-- CTE in subquery expression
SELECT (
    WITH t AS (SELECT 1)
    SELECT * FROM t
);
+----------------+
|scalarsubquery()|
+----------------+
|               1|
+----------------+

-- CTE in CREATE VIEW statement
CREATE VIEW v AS
    WITH t(a, b, c, d) AS (SELECT 1, 2, 3, 4)
    SELECT * FROM t;
SELECT * FROM v;
+---+---+---+---+
|  a|  b|  c|  d|
+---+---+---+---+
|  1|  2|  3|  4|
+---+---+---+---+

-- If name conflict is detected in nested CTE, then AnalysisException is thrown by default.
-- SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED (which is recommended),
-- inner CTE definitions take precedence over outer definitions.
SET spark.sql.legacy.ctePrecedencePolicy = CORRECTED;
WITH
    t AS (SELECT 1),
    t2 AS (
        WITH t AS (SELECT 2)
        SELECT * FROM t
    )
SELECT * FROM t2;
+---+
|  2|
+---+
|  2|
+---+