Conceptos básicos avanzados de SQL

9 ejemplos para empezar con SQL avanzado: 6 básicos y 3 intermedios.

Prerrequisitos

Comodidad con JOIN y GROUP BY de la sección Consulta SQL.

# Desarrollo local con Docker (PostgreSQL 18)
docker run -d --name pg18 -e POSTGRES_PASSWORD=dev -p 5432:5432 postgres:18
psql postgres://postgres:dev@localhost:5432/postgres

Ejemplos básicos

1. Pipeline CTE

Nombra los pasos intermedios.

WITH recent AS (
  SELECT * FROM app.orders WHERE created_at > now() - interval '7 days'
)
SELECT account_id, count(*) FROM recent GROUP BY 1;

• Mejora la legibilidad
• El planificador puede insertar o materializar
• No es una varita mágica de rendimiento

2. Número de fila de ventana

Clasifica sin colapsar GROUP BY.

SELECT id, account_id,
  row_number() OVER (PARTITION BY account_id ORDER BY created_at DESC) AS rn
FROM app.orders;

• PARTITION BY define los grupos
• ORDER BY en OVER define la clasificación
• Filtra rn en la consulta externa

3. LAG para la fila anterior

Compara con el evento anterior por partición.

SELECT id, total,
  lag(total) OVER (PARTITION BY account_id ORDER BY id) AS prev_total
FROM app.orders;

• LAG/LEAD comunes en analítica
• El marco predeterminado es RANGE UNBOUNDED PRECEDING
• NULL cuando no hay fila anterior

4. FILTER en agregados

Recuentos condicionales en una pasada.

SELECT account_id,
  count(*) FILTER (WHERE total > 100) AS big_orders
FROM app.orders GROUP BY 1;

• Más limpio que SUM(CASE)
• Funciona con muchos agregados
• La misma consulta escanea una vez

5. Formato legible

Alinea CTEs y JOINs.

WITH open_orders AS (
  SELECT * FROM app.orders WHERE status = 'open'
)
SELECT a.name, o.total
FROM open_orders o
JOIN app.accounts a ON a.id = o.account_id;

• Los revisores detectan el fan-out temprano
• Mantén las líneas por debajo del ancho del equipo
• Almacena en archivos .sql

6. Intención de comentario

Comentarios SQL para reglas de negocio no obvias.

-- Los ingresos se reconocen solo para los pedidos enviados
SELECT sum(total) FROM app.orders WHERE status = 'shipped';

• Los comentarios sobreviven en el historial de migraciones
• No comentes SQL obvio
• Enlaza IDs de tickets con moderación

Ejemplos intermedios

7. EXPLAIN Consulta compleja

Línea base antes de la optimización.

EXPLAIN (ANALYZE, BUFFERS)
WITH x AS (SELECT account_id, sum(total) s FROM app.orders GROUP BY 1)
SELECT * FROM x WHERE s > 1000;

• Comprueba los nodos de ordenación y agregación hash
• work_mem se vuelca a disco si es demasiado bajo
• Compara después de cambios de índice

8. Vista previa LATERAL

Subconsulta correlacionada por fila.

SELECT a.id, latest.total
FROM app.accounts a
JOIN LATERAL (
  SELECT total FROM app.orders o
  WHERE o.account_id = a.id ORDER BY created_at DESC LIMIT 1
) latest ON true;

• LATERAL es potente y preciso
• Necesita índice en (account_id, created_at)
• Ver artículo dedicado a LATERAL

9. Pivotar con FILTER

Pivotar con agregación condicional.

SELECT account_id,
  sum(total) FILTER (WHERE status='open') AS open_total,
  sum(total) FILTER (WHERE status='closed') AS closed_total
FROM app.orders GROUP BY 1;

• Evita crosstab para pocas columnas
• Las columnas dinámicas necesitan SQL dinámico o crosstab
• Ver artículo de pivot

Versiones de la pila: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x y PostGIS 3.5+.