WITH | ClickHouse Docs

ClickHouse admite expresiones de tabla comunes (CTE), expresiones escalares comunes y consultas recursivas.

Expresiones de tabla comunes

Las expresiones de tabla comunes representan subconsultas con nombre. Se puede hacer referencia a ellas por su nombre en cualquier parte de una consulta SELECT donde se permita una expresión de tabla. Se puede hacer referencia a las subconsultas con nombre dentro del ámbito de la consulta actual o de los ámbitos de las subconsultas hijas.

Cada referencia a una expresión de tabla común en consultas SELECT siempre se sustituye por la subconsulta de su definición si la CTE no está definida explícitamente como materializada (consulte Expresiones de tabla comunes materializadas). La recursión se evita ocultando la CTE actual del proceso de resolución de identificadores.

Tenga en cuenta que las CTE no garantizan los mismos resultados en todos los lugares donde se usan, porque la consulta se vuelve a ejecutar en cada caso.

Sintaxis

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

Ejemplo

Un ejemplo de cuándo se vuelve a ejecutar una subconsulta:

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 las CTE devolvieran exactamente los resultados y no solo un fragmento de código, siempre verías 1000000

Sin embargo, como hacemos referencia a cte_numbers dos veces, se generan números aleatorios cada vez y, por lo tanto, vemos resultados aleatorios distintos, 280501, 392454, 261636, 196227, y así sucesivamente…

Expresiones de tabla comunes materializadas

De forma predeterminada, ClickHouse expande en línea la subconsulta de una CTE en cada punto en el que se hace referencia a ella, volviéndola a ejecutar cada vez. Agregar la palabra clave MATERIALIZED indica a ClickHouse que ejecute la subconsulta de la CTE exactamente una vez, almacene los resultados en una tabla temporal y resuelva todas las referencias a partir de esa tabla. Esto resulta especialmente útil cuando se hace referencia a la misma CTE varias veces en una consulta (p. ej., en autouniones o en varias subconsultas IN), porque el cálculo subyacente solo se realiza una vez.

Sintaxis

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

Cuándo usar

Los CTE materializados son más útiles cuando:

Se hace referencia al mismo CTE más de una vez en una consulta. Sin MATERIALIZED, cada referencia vuelve a ejecutar la subconsulta de forma independiente.
El CTE contiene funciones no deterministas como generateRandom. La materialización garantiza que todas las referencias vean los mismos datos.
El CTE implica cálculos costosos (agregaciones, joins, escaneos grandes) que no deberían repetirse.

Ejemplos

Ejemplo 1: Autounión en una CTE materializada

Sin MATERIALIZED, ambos lados de la unión ejecutarían la subconsulta de forma independiente. Con MATERIALIZED, la tabla se escanea una sola vez y ambos lados de la unión leen de la misma tabla temporal.

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 │
└─────────┘

Ejemplo 2: Resultados deterministas con funciones no deterministas

Las CTE normales con generateRandom producen resultados distintos en cada referencia. Materializar la CTE garantiza la consistencia:

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);

Como ambas referencias leen de los mismos datos materializados, el resultado siempre es 1000000.

Ejemplo 3: Encadenamiento de CTE materializadas

Las CTE materializadas pueden hacer referencia a otras CTE materializadas. ClickHouse resuelve las dependencias y las materializa en el orden adecuado:

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 │
└─────────┘

El orden de las definiciones de las CTE no importa: se permiten las referencias adelantadas.

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 │
└─────────┘

Restricciones

Se requiere una configuración experimental: La configuración enable_materialized_cte debe estar habilitada.
Se requiere el analizador: Los CTE materializados solo funcionan con el analizador habilitado (enable_analyzer = 1).
No compatible con RECURSIVE: No se permite combinar las palabras clave MATERIALIZED y RECURSIVE, y ello da lugar a una excepción UNSUPPORTED_METHOD.
Los CTE correlacionados no están permitidos: Un CTE materializado no puede hacer referencia a columnas de ámbitos externos de la consulta.

Expresiones escalares comunes

ClickHouse le permite declarar alias para expresiones escalares arbitrarias en la cláusula WITH. Las expresiones escalares comunes pueden usarse en cualquier parte de la consulta.

Sintaxis

WITH <expression> AS <identifier>

Ejemplos

Ejemplo 1: Uso de una expresión constante a modo de "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;

Ejemplo 2: Uso de funciones de orden superior para acotar los identificadores

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  │
   └───────────┘

Ejemplo 3: Uso de funciones de orden superior con variables libres

Las siguientes consultas de ejemplo muestran que los identificadores sin vincular se resuelven como una entidad en el ámbito más cercano. Aquí, extension no está vinculado en el cuerpo de la función lambda gen_name. Aunque extension se define como '.txt' como una expresión escalar común en el ámbito de la definición y el uso de generated_names, se resuelve como una columna de la tabla extension_list, porque está disponible en la subconsulta 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  │
   └───────────┘

Ejemplo 4: Excluir de la lista de columnas de la cláusula SELECT el resultado de una expresión sum(bytes)

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

Ejemplo 5: Uso de los resultados de una subconsulta escalar

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

Ejemplo 6: Reutilizar una expresión escalar común definida como lambda en una subconsulta

Defina la expresión escalar común como una lambda para vincular explícitamente su argumento:

WITH (value) -> value + 1 AS increment
SELECT increment(first_result) AS second_increment
FROM
(
    SELECT increment(number) AS first_result
    FROM numbers(3)
);

┌─second_increment─┐
│                2 │
│                3 │
│                4 │
└──────────────────┘

Consultas recursivas

El modificador opcional RECURSIVE permite que una consulta WITH haga referencia a sus propios resultados. Ejemplo:

Ejemplo: Sumar los enteros del 1 al 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 │
└─────────────┘

La forma general de una consulta WITH recursiva consta siempre de un término no recursivo, luego UNION ALL y, después, un término recursivo, donde solo el término recursivo puede contener una referencia a la propia salida de la consulta. Una consulta CTE recursiva se ejecuta de la siguiente manera:

Evaluar el término no recursivo. Colocar el resultado de la consulta del término no recursivo en una tabla de trabajo temporal.
Mientras la tabla de trabajo no esté vacía, repetir estos pasos:
1. Evaluar el término recursivo, sustituyendo la autorreferencia recursiva por el contenido actual de la tabla de trabajo. Colocar el resultado de la consulta del término recursivo en una tabla intermedia temporal.
2. Reemplazar el contenido de la tabla de trabajo por el de la tabla intermedia y, a continuación, vaciar la tabla intermedia.

Las consultas recursivas suelen usarse para trabajar con datos jerárquicos o con estructura de árbol. Por ejemplo, podemos escribir una consulta que recorra un árbol:

Ejemplo: Recorrido de árbol

Primero, creemos la tabla del árbol:

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');

Podemos recorrer esos árboles con una consulta como la siguiente:

Ejemplo: Recorrido del árbol

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 │
└────┴───────────┴───────────┘

Orden de búsqueda

Para crear un orden de recorrido en profundidad, calculamos, para cada fila de resultado, un Array de filas que ya hemos visitado:

Ejemplo: Recorrido de árbol en profundidad

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]   │
└────┴───────────┴───────────┴─────────┘

Para crear un recorrido en anchura, el enfoque estándar consiste en añadir una columna que registre la profundidad de la búsqueda:

Ejemplo: Recorrido del árbol en anchura

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 │
└────┴──────┴───────────┴─────────┴───────┘

Detección de ciclos

Primero, vamos a crear la tabla del grafo:

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');

Podemos recorrer ese grafo con una consulta como esta:

Ejemplo: Recorrido del grafo sin detección de ciclos

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 │
└──────┴────┴────────┘

Pero, si añadimos un ciclo a ese grafo, la consulta anterior fallará con el error 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)

El método estándar para gestionar los ciclos consiste en calcular un Array con los nodos ya visitados:

Ejemplo: Recorrido de grafos con detección de ciclos

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)] │
└──────┴────┴────────┴──────────┴───────────────────────────┘

Consultas infinitas

También es posible usar consultas CTE recursivas infinitas si la consulta externa usa LIMIT:

Ejemplo: Consulta CTE recursiva infinita

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 │
└─────────────┘

Coma final

Se permite una coma después del último elemento de la cláusula WITH:

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