> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentación de la cláusula WITH

# WITH

ClickHouse admite expresiones de tabla comunes ([CTE](https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL)), 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](#materialized-common-table-expressions)).
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

```sql theme={null}
WITH <identifier> AS [MATERIALIZED] <subquery expression>
```

### Ejemplo

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

```sql theme={null}
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 inserta 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.

<Note>
  Las CTE materializadas son una característica **experimental**.
  Requieren que la configuración `enable_materialized_cte` esté habilitada.
  Si la configuración está deshabilitada, se ignora la palabra clave `MATERIALIZED`: la CTE se inserta en línea en cada referencia como una CTE normal y se registra una advertencia.
</Note>

### Sintaxis

```sql theme={null}
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.

<Tip>
  Si un CTE materializado solo se referencia una vez, ClickHouse lo integra automáticamente como una subconsulta normal para evitar una sobrecarga innecesaria.
</Tip>

### 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.

```sql theme={null}
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;
```

```response theme={null}
┌─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:

```sql theme={null}
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:

```sql theme={null}
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;
```

```response theme={null}
┌─count()─┐
│       3 │
└─────────┘
```

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

```sql theme={null}
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;
```

```response theme={null}
┌─count()─┐
│       3 │
└─────────┘
```

### Restricciones

* **Se requiere una configuración experimental**: La configuración `enable_materialized_cte` debe estar habilitada. Si está deshabilitada, se ignora la palabra clave `MATERIALIZED`: el CTE se inserta en línea en cada referencia como un CTE normal y se registra una advertencia.
* **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.

<Note>
  Si una expresión escalar común hace referencia a algo distinto de un literal constante, puede dar lugar a la presencia de [variables libres](https://en.wikipedia.org/wiki/Free_variables_and_bound_variables).
  ClickHouse resuelve cualquier identificador en el ámbito más cercano posible, lo que significa que las variables libres pueden hacer referencia a entidades inesperadas en caso de conflictos de nombres o dar lugar a una subconsulta correlacionada.
  Se recomienda definir CSE como una [función lambda](/es/reference/functions/regular-functions/overview#arrow-operator-and-lambda), vinculando todos los identificadores usados para lograr un comportamiento más predecible en la resolución de los identificadores de la expresión.
</Note>

### Sintaxis

```sql theme={null}
WITH <expression> AS <identifier>
```

### Ejemplos

**Ejemplo 1:** Uso de una expresión constante a modo de "variable"

```sql theme={null}
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

```sql theme={null}
WITH
    '.txt' as extension,
    (id, extension) -> concat(lower(id), extension) AS gen_name
SELECT gen_name('test', '.sql') as file_name;
```

```response theme={null}
   ┌─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`.

```sql theme={null}
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;
```

```response theme={null}
   ┌─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)`

```sql theme={null}
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

```sql theme={null}
/* 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:

```sql theme={null}
WITH (value) -> value + 1 AS increment
SELECT increment(first_result) AS second_increment
FROM
(
    SELECT increment(number) AS first_result
    FROM numbers(3)
);
```

```response theme={null}
┌─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

```sql theme={null}
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;
```

```text theme={null}
┌─sum(number)─┐
│        5050 │
└─────────────┘
```

<Note>
  Los CTE recursivos dependen del [analizador de consultas](/es/guides/clickhouse/performance-and-monitoring/analyzer), introducido en la versión **`24.3`**, que es el predeterminado desde esa versión y obligatorio desde la **`26.9`**. En versiones anteriores en las que el analizador siga deshabilitado en la instancia, el rol o el perfil, un CTE recursivo genera una excepción **`(UNKNOWN_TABLE)`** o **`(UNSUPPORTED_METHOD)`**; habilita allí la configuración `enable_analyzer` o actualiza la versión.
</Note>

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:

1. Evaluar el término no recursivo. Colocar el resultado de la consulta del término no recursivo en una tabla de trabajo temporal.
2. 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:

```sql theme={null}
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

```sql theme={null}
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;
```

```text theme={null}
┌─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

```sql theme={null}
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;
```

```text theme={null}
┌─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

```sql theme={null}
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;
```

```text theme={null}
┌─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:

```sql theme={null}
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

```sql theme={null}
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;
```

```text theme={null}
┌─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`:

```sql theme={null}
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;
```

```text theme={null}
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

```sql theme={null}
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;
```

```text theme={null}
┌─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

```sql theme={null}
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);
```

```text theme={null}
┌─sum(number)─┐
│        5050 │
└─────────────┘
```

## Coma final

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

```sql theme={null}
WITH
    (SELECT sum(number) FROM numbers(10)) AS total,
    total * 2 AS doubled,
SELECT total, doubled;
```