# PostgreSQL desde cero (III): CTEs, window functions y consultas avanzadas

*Tercera entrega de la serie **[PostgreSQL desde cero a pro](/search?tag=postgresql-desde-cero)**. Tiempo de lectura estimado: 13 minutos.*

Ya sabes instalar PostgreSQL ([I](/post/postgresql-desde-cero-i-instalacion-psql-y-primeros-pasos)) y diseñar un buen esquema con tipos y restricciones ([II](/post/postgresql-desde-cero-ii-tipos-restricciones-y-relaciones)). Ahora toca la parte divertida: las partes del lenguaje SQL que separan a alguien que "sabe SQL" de alguien que de verdad saca partido a PostgreSQL.

Si ya tienes algo de experiencia, partes de este post te sonarán del post [PostgreSQL: 10 consultas que todo desarrollador debería conocer](/post/postgresql-10-consultas-que-todo-desarrollador-deberia-conocer). Lo que hago aquí es sistematizar y ampliar.

## CTEs (Common Table Expressions)

Una CTE es una subquery con nombre. La sintaxis es `WITH ... AS (...)` y se puede encadenar:

```sql
WITH paid_invoices AS (
  SELECT * FROM invoices WHERE status = 'paid'
),
monthly AS (
  SELECT
    date_trunc('month', paid_at) AS month,
    sum(amount) AS total
  FROM paid_invoices
  GROUP BY 1
)
SELECT
  month,
  total,
  total - lag(total) OVER (ORDER BY month) AS diff
FROM monthly
ORDER BY month;
```

Ventajas:

- **Legibilidad**: rompe consultas grandes en pasos con nombre.
- **Reutilización dentro de la consulta**: una CTE puede referenciarse varias veces.
- **Separación de lógica**: cada CTE es una unidad comprensible por sí sola.

Históricamente, PostgreSQL trataba las CTEs como **optimization fences** (materializaba el resultado intermedio). Desde la versión 12, el planner puede inline la CTE cuando es mejor, salvo que la marques como `WITH ... AS MATERIALIZED (...)` o `NOT MATERIALIZED`.

### CTEs recursivas

El nombre asusta pero la idea es simple: una CTE que se auto-referencia. La forma canónica:

```sql
WITH RECURSIVE
  anchor AS (SELECT ... base_case ...),
  recursive_step AS (SELECT ... FROM anchor ...)
SELECT * FROM (anchor UNION ALL recursive_step);
```

En la práctica se escribe así:

```sql
WITH RECURSIVE tree AS (
  -- Caso base
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- Paso recursivo
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;
```

Para cualquier estructura jerárquica (categorías, comentarios anidados, árbol de organización, grafo acíclico) esto es el patrón de libro.

### Mutating CTEs

Las CTEs pueden contener `INSERT`, `UPDATE` o `DELETE` con `RETURNING`:

```sql
WITH deleted_rows AS (
  DELETE FROM expired_sessions WHERE expires_at < now() RETURNING id, user_id
)
INSERT INTO audit_log (action, entity, entity_id, user_id)
SELECT 'session_expired', 'session', id, user_id FROM deleted_rows;
```

Borrar y auditar en una sola consulta, con garantías transaccionales. Muy útil para pipelines de limpieza.

## Window functions

Las window functions calculan un valor **por fila** usando una ventana de filas relacionadas, sin colapsar el resultado como hace `GROUP BY`.

Estructura general:

```sql
función() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)
```

### Funciones de ranking

```sql
SELECT
  department,
  employee,
  salary,
  row_number()  OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
  rank()        OVER (PARTITION BY department ORDER BY salary DESC) AS rk,
  dense_rank()  OVER (PARTITION BY department ORDER BY salary DESC) AS drk
FROM employees;
```

Diferencia clave:

- `row_number()`: números consecutivos únicos dentro de la partición.
- `rank()`: misma posición para empates, salta números (1, 2, 2, 4).
- `dense_rank()`: misma posición para empates, sin saltar (1, 2, 2, 3).

Top N por grupo, un clásico:

```sql
SELECT *
FROM (
  SELECT
    category_id, product_id, views,
    row_number() OVER (PARTITION BY category_id ORDER BY views DESC) AS rn
  FROM products
) ranked
WHERE rn <= 3;
```

### Agregaciones como ventana

Cualquier función de agregación (`sum`, `avg`, `count`, `min`, `max`, `string_agg`, `array_agg`) puede usarse como window:

```sql
SELECT
  order_id,
  customer_id,
  total,
  sum(total) OVER (PARTITION BY customer_id ORDER BY created_at
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;
```

### lag, lead, first_value, last_value

```sql
SELECT
  day,
  value,
  lag(value, 1)  OVER (ORDER BY day) AS prev_day,
  lead(value, 1) OVER (ORDER BY day) AS next_day,
  value - lag(value, 1) OVER (ORDER BY day) AS diff
FROM metrics;
```

`lag(x, n)` devuelve el valor `n` filas antes; `lead(x, n)` lo contrario. Para series temporales, cálculo de deltas y movings, son insustituibles.

### FILTER

Desde PostgreSQL 9.4, puedes filtrar dentro de una agregación sin usar `CASE`:

```sql
SELECT
  department,
  count(*)                                           AS total,
  count(*) FILTER (WHERE status = 'active')          AS active,
  count(*) FILTER (WHERE hired_at > now() - '1 year'::interval) AS new,
  avg(salary) FILTER (WHERE status = 'active')       AS avg_active_salary
FROM employees
GROUP BY department;
```

Es legible, rápido y hace la misma pasada por los datos.

## LATERAL joins

`LATERAL` permite que una subquery a la derecha de un join **se referencie a columnas de la izquierda**. Es un "para cada fila de la izquierda, ejecuta esta subquery".

Ejemplo: últimos 3 pedidos de cada cliente, todo en una consulta:

```sql
SELECT
  c.id,
  c.name,
  o.created_at,
  o.total
FROM customers c
LEFT JOIN LATERAL (
  SELECT created_at, total
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 3
) o ON true;
```

Equivalente funcional al "top N por grupo" que antes hacíamos con window + filtro, a veces más eficiente, a veces menos. Mide.

## UPSERT con ON CONFLICT

Insertar-o-actualizar en una sola instrucción atómica:

```sql
INSERT INTO page_views (path, day, views)
VALUES ('/home', CURRENT_DATE, 1)
ON CONFLICT (path, day)
DO UPDATE SET views = page_views.views + EXCLUDED.views;
```

- `ON CONFLICT (...)` declara el conflicto (una restricción única).
- `DO UPDATE` indica qué hacer si hay conflicto.
- `EXCLUDED` representa los valores que **intentabas** insertar.
- `DO NOTHING` es la variante "INSERT IGNORE".

Esto es fundamental para contadores incrementales, estado denormalizado y cualquier flujo idempotente.

## RETURNING

Cualquier `INSERT`, `UPDATE` o `DELETE` puede devolver filas:

```sql
UPDATE orders
SET status = 'shipped', shipped_at = now()
WHERE status = 'paid' AND paid_at < now() - '1 hour'::interval
RETURNING id, customer_id;
```

Una sola *round trip* para cambiar estado y recuperar los IDs afectados. Ideal para pipelines de procesamiento en lote.

## Manipulación de JSONB

Recuperación y filtrado ya los vimos en la [entrega II](/post/postgresql-desde-cero-ii-tipos-restricciones-y-relaciones). Para modificar JSONB:

```sql
-- Añadir / sobreescribir clave
UPDATE events
SET data = data || '{"processed": true}'::jsonb
WHERE id = 42;

-- Eliminar clave
UPDATE events SET data = data - 'tmp' WHERE ...;

-- Ruta profunda con jsonb_set
UPDATE events
SET data = jsonb_set(data, '{user,plan}', '"premium"', true)
WHERE id = 42;
```

Desde PostgreSQL 16, el soporte de `JSON_TABLE` y expresiones `jsonpath` SQL/JSON es enorme. Para consultas muy específicas, míratelo.

## Generate series

Una función que genera filas artificiales:

```sql
SELECT * FROM generate_series(1, 10);

SELECT generate_series(
  date_trunc('month', now()) - interval '11 months',
  date_trunc('month', now()),
  interval '1 month'
) AS month;
```

Muy útil para rellenar huecos en series temporales con `LEFT JOIN`:

```sql
SELECT
  gs.day,
  coalesce(count(v.id), 0) AS views
FROM generate_series(
  CURRENT_DATE - 30,
  CURRENT_DATE,
  interval '1 day'
) AS gs(day)
LEFT JOIN page_views v ON v.created_at::date = gs.day
GROUP BY gs.day
ORDER BY gs.day;
```

Si vienes de la [entrega III de ClickHouse](/post/clickhouse-desde-cero-iii-consultas-analiticas-en-profundidad), esto es el equivalente a `WITH FILL` pero más verboso.

## DISTINCT ON

Una extensión de PostgreSQL muy útil:

```sql
SELECT DISTINCT ON (user_id)
  user_id, event_type, created_at
FROM events
ORDER BY user_id, created_at DESC;
```

Devuelve "la primera fila por `user_id` según el orden dado". Equivalente a un window + filtro, pero mucho más conciso para el caso de "la última fila de cada grupo".

## Full text search

PostgreSQL incluye búsqueda full-text integrada:

```sql
-- Columna auto-generada con el vector de búsqueda
ALTER TABLE posts
ADD COLUMN search tsvector GENERATED ALWAYS AS (
  to_tsvector('spanish', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED;

CREATE INDEX posts_search_idx ON posts USING GIN (search);

SELECT id, title, ts_rank(search, q) AS rank
FROM posts, plainto_tsquery('spanish', 'postgresql rendimiento') AS q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 10;
```

No reemplaza a Elasticsearch o Typesense para búsquedas muy sofisticadas, pero para un blog, un catálogo mediano o una documentación es más que suficiente y no añade infraestructura.

## Ejemplo completo

Funnel de conversión calculado en una sola consulta:

```sql
WITH sessions AS (
  SELECT
    session_id,
    user_id,
    min(created_at) FILTER (WHERE event = 'visit')     AS visited_at,
    min(created_at) FILTER (WHERE event = 'signup')    AS signed_up_at,
    min(created_at) FILTER (WHERE event = 'purchase')  AS purchased_at
  FROM events
  WHERE created_at >= now() - '30 days'::interval
  GROUP BY session_id, user_id
),
funnel AS (
  SELECT
    count(*)                                              AS visits,
    count(*) FILTER (WHERE signed_up_at IS NOT NULL)      AS signups,
    count(*) FILTER (WHERE purchased_at IS NOT NULL)      AS purchases
  FROM sessions
  WHERE visited_at IS NOT NULL
)
SELECT
  visits,
  signups,
  purchases,
  round(100.0 * signups   / nullif(visits, 0), 2)   AS signup_rate,
  round(100.0 * purchases / nullif(signups, 0), 2)  AS conversion_rate
FROM funnel;
```

CTEs, `FILTER`, agregaciones condicionales y `nullif` para evitar divisiones por cero. Todo en una consulta declarativa que se lee de arriba a abajo.

## Por dónde seguir

- **[IV: índices, EXPLAIN y rendimiento](/post/postgresql-desde-cero-iv-indices-explain-y-rendimiento)** — cómo averiguar por qué esta consulta que acabamos de escribir tarda tres segundos.
- **[V: replicación, backups y producción](/post/postgresql-desde-cero-v-replicacion-backups-y-produccion)** — último tramo de la serie.

Si te perdiste algo: [I](/post/postgresql-desde-cero-i-instalacion-psql-y-primeros-pasos) o [II](/post/postgresql-desde-cero-ii-tipos-restricciones-y-relaciones).