Una secuencia de números

Parte de la sección Más allá de lo básico del Journey de SQL de Coddy — lección 20 de 27.

Conociste la palabra clave WITH en Fundamentos para nombrar una subconsulta, también conocida como CTE (Expresión de Tabla Común). Una CTE recursiva va un paso más allá: permite que la subconsulta se refiera a sí misma, construyendo un resultado fila por fila.

La versión más simple genera una secuencia de números sin una tabla subyacente. La forma es siempre la misma:

WITH RECURSIVE counter(n) AS (
    SELECT 1                              -- ancla: la fila inicial
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5 -- recursivo: construye el siguiente a partir del anterior
)
SELECT n FROM counter

Léalo como: comience con n = 1, luego siga agregando filas donde cada n es el n + 1 anterior, deteniéndose cuando el WHERE deje de coincidir. El resultado es 1, 2, 3, 4, 5.

Las dos partes del cuerpo se unen mediante UNION ALL. La primera es el ancla: las filas semilla. La segunda es el paso recursivo: realiza una consulta a la propia CTE.

Desafío

Fácil

Escribe una CTE recursiva llamada powers con dos columnas:

i: el índice de iteración, comenzando en 1
p: 2 elevado a la i-ésima potencia (2, 4, 8, …)

Detente una vez que p exceda 1000. Devuelve ambas columnas, ordenadas por i de forma ascendente.

Hoja de referencia

Un CTE recursivo permite que una subconsulta se refiera a sí misma, construyendo los resultados fila por fila:

WITH RECURSIVE counter(n) AS (
    SELECT 1                               -- anchor: starting row
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 5  -- recursive step
)
SELECT n FROM counter

Ancla (Anchor): la(s) fila(s) semilla (primer SELECT)
Paso recursivo: consulta al propio CTE para producir la siguiente fila
Se detiene cuando la condición WHERE ya no se cumple
Las dos partes se unen con UNION ALL

Pruébalo tú mismo

WITH RECURSIVE powers(i, p) AS (
    -- ancla: i=1, p=2
    -- recursivo: i+1, p*2, hasta que p alcance 1000
)
SELECT i, p FROM powers ORDER BY i

Ponte a prueba

Esta lección incluye un breve cuestionario. Empieza la lección para responderlo y registrar tu progreso.

Todas las lecciones de Más allá de lo básico

1Lógica condicional

CASE WHEN CASE en ORDER BY COALESCE para NULLs NULLIF e IIF Resumen - Asistencia

4Operaciones de conjuntos

UNION vs UNION ALL INTERSECT EXCEPT Repaso - Pertenencia

2Funciones de cadena

LENGTH, UPPER, LOWER SUBSTR INSTR REPLACE y TRIM Concatenación con ||Repaso - Facturas

5CTEs recursivas

Una secuencia de númerosRecorriendo una jerarquía Resumen - Organigrama

3Agregación condicional

COUNT con CASE SUM con CASE Pivotar filas a columnas Resumen - Fuentes de tráfico

6Recetas de Analítica

Top-N por grupo Porcentaje del total Total acumulado Mantener una fila por clave Resumen - Mejores clientes