Clause WITH - ClickHouse Documentation

ClickHouse prend en charge les expressions de table communes (CTE), les expressions scalaires communes et les requêtes récursives.

Expressions de table communes

Les expressions de table communes représentent des sous-requêtes nommées. Elles peuvent être référencées par leur nom partout dans une requête SELECT où une expression de table est autorisée. Les sous-requêtes nommées peuvent être référencées par leur nom dans la portée de la requête courante ou dans les portées des sous-requêtes enfants. Chaque référence à une expression de table commune dans une requête SELECT est toujours remplacée par la sous-requête de sa définition si la CTE n’est pas explicitement définie comme matérialisée (voir Expressions de table communes matérialisées). La récursivité est évitée en masquant la CTE courante du processus de résolution des identifiants. Veuillez noter que les CTE ne garantissent pas les mêmes résultats partout où elles sont appelées, car la requête est réexécutée à chaque utilisation.

Syntaxe

WITH <identifier> AS [MATERIALIZED] <subquery expression>

Exemple

Voici un exemple de cas où une sous-requête est réexécutée :

WITH cte_numbers AS
(
    SELECT
        num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT
    count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers)

Si les CTE transmettaient exactement les résultats, et pas seulement un morceau de code, vous verriez toujours 1000000 Cependant, comme nous faisons référence deux fois à cte_numbers, des nombres aléatoires sont générés à chaque fois et nous obtenons donc des résultats aléatoires différents : 280501, 392454, 261636, 196227, etc.

Expressions de table communes matérialisées

Par défaut, ClickHouse intègre la sous-requête d’une CTE à chaque référence, et la réexécute donc à chaque fois. L’ajout du mot-clé MATERIALIZED indique à ClickHouse d’exécuter la sous-requête de la CTE exactement une fois, de stocker les résultats dans une table temporaire, puis d’utiliser cette table pour toutes les références. Cela est particulièrement utile lorsque la même CTE est référencée plusieurs fois dans une requête (par exemple, dans des auto-jointures ou plusieurs sous-requêtes IN), car le calcul sous-jacent n’est effectué qu’une seule fois.

Les CTE matérialisées sont une fonctionnalité expérimentale. Elles nécessitent que l’analyseur et le paramètre enable_materialized_cte soient activés.

Syntaxe

WITH <identifier> AS MATERIALIZED (<subquery>)
SELECT ...

Quand utiliser

Les CTE matérialisées sont particulièrement utiles dans les cas suivants :

La même CTE est référencée plus d’une fois dans une requête. Sans MATERIALIZED, chaque référence réexécute la sous-requête de manière indépendante.
La CTE contient des fonctions non déterministes comme generateRandom. La matérialisation garantit que toutes les références voient les mêmes données.
La CTE implique des calculs coûteux (agrégations, jointures, lecture de grands volumes de données) qu’il ne faut pas répéter.

Si une CTE matérialisée n’est référencée qu’une seule fois, ClickHouse la réintègre automatiquement sous forme de sous-requête classique afin d’éviter tout surcoût inutile.

Exemples

Exemple 1 : Auto-jointure sur une CTE matérialisée Sans MATERIALIZED, les deux côtés de la jointure exécuteraient la sous-requête indépendamment. Avec MATERIALIZED, la table n’est parcourue qu’une seule fois et les deux côtés de la jointure lisent dans la même table temporaire.

SET enable_materialized_cte = 1;

CREATE TABLE users (uid Int16, name String, age Int16) ENGINE = Memory;
INSERT INTO users VALUES (1231, 'John', 33), (6666, 'Ksenia', 48), (8888, 'Alice', 50);

WITH
    a AS MATERIALIZED (SELECT * FROM users WHERE name = 'Alice')
SELECT count() FROM a AS l JOIN a AS r ON l.uid = r.uid;

┌─count()─┐
│       1 │
└─────────┘

Exemple 2 : Résultats déterministes avec des fonctions non déterministes Les CTE classiques avec generateRandom produisent des résultats différents à chaque référence. La matérialisation de la CTE garantit la cohérence :

SET enable_materialized_cte = 1;

WITH cte_numbers AS MATERIALIZED
(
    SELECT num
    FROM generateRandom('num UInt64', NULL)
    LIMIT 1000000
)
SELECT count()
FROM cte_numbers
WHERE num IN (SELECT num FROM cte_numbers);

Comme les deux références s’appuient sur les mêmes données matérialisées, le résultat est toujours 1000000. Exemple 3 : Chaînage de CTE matérialisées Les CTE matérialisées peuvent faire référence à d’autres CTE matérialisées. ClickHouse résout les dépendances et les matérialise dans le bon ordre :

SET enable_materialized_cte = 1;

WITH
    a AS MATERIALIZED (SELECT uid, name FROM users),
    b AS MATERIALIZED (SELECT uid FROM a)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

L’ordre des définitions de CTE n’a pas d’importance — il est possible de faire référence à des CTE définies plus loin :

SET enable_materialized_cte = 1;

WITH
    b AS MATERIALIZED (SELECT uid FROM a),
    a AS MATERIALIZED (SELECT uid FROM users)
SELECT count() FROM b AS l LEFT SEMI JOIN b AS r ON l.uid = r.uid;

┌─count()─┐
│       3 │
└─────────┘

Restrictions

Paramètre expérimental requis : le paramètre enable_materialized_cte doit être activé.
Analyseur requis : les CTE matérialisées fonctionnent uniquement lorsque l’analyseur est activé (enable_analyzer = 1).
Non pris en charge avec RECURSIVE : la combinaison des mots-clés MATERIALIZED et RECURSIVE n’est pas autorisée et entraîne une exception UNSUPPORTED_METHOD.
Les CTE corrélées sont interdites : une CTE matérialisée ne peut pas référencer des colonnes provenant de portées de requête englobantes.

Expressions scalaires communes

ClickHouse vous permet de déclarer des alias pour des expressions scalaires arbitraires dans la clause WITH. Les expressions scalaires communes peuvent être référencées n’importe où dans la requête.

Si une expression scalaire commune fait référence à autre chose qu’un littéral constant, elle peut entraîner la présence de variables libres. ClickHouse résout chaque identifiant dans la portée la plus proche possible, ce qui signifie que des variables libres peuvent faire référence à des entités inattendues en cas de conflit de noms, ou conduire à une sous-requête corrélée. Il est recommandé de définir une CSE sous forme de fonction lambda (possible uniquement lorsque l’analyseur est activé), en liant tous les identifiants utilisés afin d’obtenir un comportement plus prévisible lors de la résolution des identifiants d’expression.

Syntaxe

WITH <expression> AS <identifier>

Exemples

Exemple 1 : Utiliser une expression constante comme “variable”

WITH '2019-08-01 15:23:00' AS ts_upper_bound
SELECT *
FROM hits
WHERE
    EventDate = toDate(ts_upper_bound) AND
    EventTime <= ts_upper_bound;

Exemple 2 : Utiliser des fonctions d’ordre supérieur pour lier les identifiants

WITH
    '.txt' as extension,
    (id, extension) -> concat(lower(id), extension) AS gen_name
SELECT gen_name('test', '.sql') as file_name;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

Exemple 3 : Utilisation de fonctions d’ordre supérieur avec des variables libres Les requêtes d’exemple suivantes montrent que les identifiants non liés sont résolus vers une entité dans la portée la plus proche. Ici, extension n’est pas liée dans le corps de la fonction lambda gen_name. Bien que extension soit définie comme '.txt' en tant qu’expression scalaire commune dans la portée de définition et d’utilisation de generated_names, elle est résolue comme une colonne de la table extension_list, car elle est disponible dans la sous-requête generated_names.

CREATE TABLE extension_list
(
    extension String
)
ORDER BY extension
AS SELECT '.sql';

WITH
    '.txt' as extension,
    generated_names as (
        WITH
            (id) -> concat(lower(id), extension) AS gen_name
        SELECT gen_name('test') as file_name FROM extension_list
    )
SELECT file_name FROM generated_names;

   ┌─file_name─┐
1. │ test.sql  │
   └───────────┘

Exemple 4 : Retrait du résultat de l’expression sum(bytes) de la liste des colonnes de la clause SELECT

WITH sum(bytes) AS s
SELECT
    formatReadableSize(s),
    table
FROM system.parts
GROUP BY table
ORDER BY s;

Exemple 5 : Utilisation du résultat d’une sous-requête scalaire

/* this example would return TOP 10 of most huge tables */
WITH
    (
        SELECT sum(bytes)
        FROM system.parts
        WHERE active
    ) AS total_disk_usage
SELECT
    (sum(bytes) / total_disk_usage) * 100 AS table_disk_usage,
    table
FROM system.parts
GROUP BY table
ORDER BY table_disk_usage DESC
LIMIT 10;

Exemple 6 : Réutilisation d’une expression dans une sous-requête

WITH test1 AS (SELECT i + 1, j + 1 FROM test1)
SELECT * FROM test1;

Requêtes récursives

Le modificateur RECURSIVE, facultatif, permet à une requête WITH de faire référence à son propre résultat. Exemple : Exemple : Additionner les entiers de 1 à 100

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table WHERE number < 100
)
SELECT sum(number) FROM test_table;

┌─sum(number)─┐
│        5050 │
└─────────────┘

Les CTE récursifs s’appuient sur l’analyseur de requêtes, introduit dans la version 24.3. Si vous utilisez la version 24.3+ et rencontrez une exception (UNKNOWN_TABLE) ou (UNSUPPORTED_METHOD), cela indique que l’analyseur est désactivé sur votre instance, votre rôle ou votre profil. Pour activer l’analyseur, activez le paramètre allow_experimental_analyzer ou mettez à jour le paramètre compatibility vers une version plus récente. À partir de la version 24.8, l’analyseur est pleinement passé en production, et le paramètre allow_experimental_analyzer a été renommé en enable_analyzer.

La forme générale d’une requête récursive WITH est toujours la suivante : un terme non récursif, puis UNION ALL, puis un terme récursif, seul ce dernier pouvant contenir une référence à la propre sortie de la requête. Une requête CTE récursive s’exécute comme suit :

Évaluez le terme non récursif. Placez le résultat de cette requête dans une table de travail temporaire.
Tant que la table de travail n’est pas vide, répétez ces étapes :
1. Évaluez le terme récursif en remplaçant l’auto-référence récursive par le contenu actuel de la table de travail. Placez le résultat de cette requête dans une table intermédiaire temporaire.
2. Remplacez le contenu de la table de travail par celui de la table intermédiaire, puis videz la table intermédiaire.

Les requêtes récursives sont généralement utilisées pour manipuler des données hiérarchiques ou structurées en arbre. Par exemple, nous pouvons écrire une requête qui effectue un parcours d’arbre : Exemple : Parcours d’arbre Commençons par créer la table de l’arbre :

DROP TABLE IF EXISTS tree;
CREATE TABLE tree
(
    id UInt64,
    parent_id Nullable(UInt64),
    data String
) ENGINE = MergeTree ORDER BY id;

INSERT INTO tree VALUES (0, NULL, 'ROOT'), (1, 0, 'Child_1'), (2, 0, 'Child_2'), (3, 1, 'Child_1_1');

Nous pouvons parcourir ces arbres à l’aide de la requête suivante : Exemple : Parcours d’arbre

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree;

┌─id─┬─parent_id─┬─data──────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │
│  1 │         0 │ Child_1   │
│  2 │         0 │ Child_2   │
│  3 │         1 │ Child_1_1 │
└────┴───────────┴───────────┘

Ordre de parcours

Pour établir un parcours en profondeur, nous calculons pour chaque ligne de résultat un tableau des lignes déjà visitées : Exemple : Parcours d’arbre en profondeur

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id])
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY path;

┌─id─┬─parent_id─┬─data──────┬─path────┐
│  0 │      ᴺᵁᴸᴸ │ ROOT      │ [0]     │
│  1 │         0 │ Child_1   │ [0,1]   │
│  3 │         1 │ Child_1_1 │ [0,1,3] │
│  2 │         0 │ Child_2   │ [0,2]   │
└────┴───────────┴───────────┴─────────┘

Pour obtenir un parcours en largeur, l’approche standard consiste à ajouter une colonne qui indique la profondeur de la recherche : Exemple : parcours d’arbre en largeur

WITH RECURSIVE search_tree AS (
    SELECT id, parent_id, data, [t.id] AS path, toUInt64(0) AS depth
    FROM tree t
    WHERE t.id = 0
UNION ALL
    SELECT t.id, t.parent_id, t.data, arrayConcat(path, [t.id]), depth + 1
    FROM tree t, search_tree st
    WHERE t.parent_id = st.id
)
SELECT * FROM search_tree ORDER BY depth;

┌─id─┬─link─┬─data──────┬─path────┬─depth─┐
│  0 │ ᴺᵁᴸᴸ │ ROOT      │ [0]     │     0 │
│  1 │    0 │ Child_1   │ [0,1]   │     1 │
│  2 │    0 │ Child_2   │ [0,2]   │     1 │
│  3 │    1 │ Child_1_1 │ [0,1,3] │     2 │
└────┴──────┴───────────┴─────────┴───────┘

Détection des cycles

Commençons par créer la table du graphe :

DROP TABLE IF EXISTS graph;
CREATE TABLE graph
(
    from UInt64,
    to UInt64,
    label String
) ENGINE = MergeTree ORDER BY (from, to);

INSERT INTO graph VALUES (1, 2, '1 -> 2'), (1, 3, '1 -> 3'), (2, 3, '2 -> 3'), (1, 4, '1 -> 4'), (4, 5, '4 -> 5');

Nous pouvons parcourir ce graphe avec la requête suivante : Exemple : Parcours du graphe sans détection de cycles

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
    UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

┌─from─┬─to─┬─label──┐
│    1 │  4 │ 1 -> 4 │
│    1 │  2 │ 1 -> 2 │
│    1 │  3 │ 1 -> 3 │
│    2 │  3 │ 2 -> 3 │
│    4 │  5 │ 4 -> 5 │
└──────┴────┴────────┘

Mais si l’on ajoute un cycle dans ce graphe, la requête précédente échouera avec l’erreur Maximum recursive CTE evaluation depth :

INSERT INTO graph VALUES (5, 1, '5 -> 1');

WITH RECURSIVE search_graph AS (
    SELECT from, to, label FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label
    FROM graph g, search_graph sg
    WHERE g.from = sg.to
)
SELECT DISTINCT * FROM search_graph ORDER BY from;

Code: 306. DB::Exception: Received from localhost:9000. DB::Exception: Maximum recursive CTE evaluation depth (1000) exceeded, during evaluation of search_graph AS (SELECT from, to, label FROM graph AS g UNION ALL SELECT g.from, g.to, g.label FROM graph AS g, search_graph AS sg WHERE g.from = sg.to). Consider raising max_recursive_cte_evaluation_depth setting.: While executing RecursiveCTESource. (TOO_DEEP_RECURSION)

La méthode standard pour gérer les cycles consiste à calculer un tableau contenant les nœuds déjà visités : Exemple : parcours du graphe avec détection de cycles

WITH RECURSIVE search_graph AS (
    SELECT from, to, label, false AS is_cycle, [tuple(g.from, g.to)] AS path FROM graph g
UNION ALL
    SELECT g.from, g.to, g.label, has(path, tuple(g.from, g.to)), arrayConcat(sg.path, [tuple(g.from, g.to)])
    FROM graph g, search_graph sg
    WHERE g.from = sg.to AND NOT is_cycle
)
SELECT * FROM search_graph WHERE is_cycle ORDER BY from;

┌─from─┬─to─┬─label──┬─is_cycle─┬─path──────────────────────┐
│    1 │  4 │ 1 -> 4 │ true     │ [(1,4),(4,5),(5,1),(1,4)] │
│    4 │  5 │ 4 -> 5 │ true     │ [(4,5),(5,1),(1,4),(4,5)] │
│    5 │  1 │ 5 -> 1 │ true     │ [(5,1),(1,4),(4,5),(5,1)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

Requêtes infinies

Il est également possible d’utiliser des requêtes CTE récursives infinies si LIMIT est utilisé dans la requête externe : Exemple : Requête CTE récursive infinie

WITH RECURSIVE test_table AS (
    SELECT 1 AS number
UNION ALL
    SELECT number + 1 FROM test_table
)
SELECT sum(number) FROM (SELECT number FROM test_table LIMIT 100);

┌─sum(number)─┐
│        5050 │
└─────────────┘

Virgule finale

Une virgule est autorisée après le dernier élément de la clause WITH :

WITH
    (SELECT sum(number) FROM numbers(10)) AS total,
    total * 2 AS doubled,
SELECT total, doubled;

​Expressions de table communes

​Syntaxe

​Exemple

​Expressions de table communes matérialisées

​Syntaxe

​Quand utiliser

​Exemples

​Restrictions

​Expressions scalaires communes

​Syntaxe

​Exemples

​Requêtes récursives

​Ordre de parcours

​Détection des cycles

​Requêtes infinies

​Virgule finale

Expressions de table communes

Syntaxe

Exemple

Expressions de table communes matérialisées

Syntaxe

Quand utiliser

Exemples

Restrictions

Expressions scalaires communes

Syntaxe

Exemples

Requêtes récursives

Ordre de parcours

Détection des cycles

Requêtes infinies

Virgule finale