Nivel 3 · 30 min

SQL Avanzado

PostgreSQL ofrece características SQL avanzadas que resuelven problemas complejos elegantemente: window functions para cálculos sobre conjuntos de filas, CTEs para consultas modulares, operadores JSONB para datos semi-estructurados, y full-text search integrado.

Window Functions

Las window functions calculan sobre un conjunto de filas relacionadas sin colapsar el resultado como GROUP BY. ROW_NUMBER() enumera filas dentro de una partición. RANK() y DENSE_RANK() para rankings con empates. LAG/LEAD acceden a la fila anterior/siguiente. SUM OVER para totales acumulativos. La cláusula PARTITION BY define los grupos; ORDER BY dentro de OVER define el orden para funciones de frame.

CTEs y JSONB

WITH (Common Table Expressions) permiten nombrar subqueries para reutilizarlas. WITH RECURSIVE habilita consultas recursivas (árboles, grafos). JSONB tiene operadores poderosos: -'>' para navegar (retorna JSON), -'>''>' para extraer como texto, @'>' para containment, #'>' para path. jsonb_set() para actualización parcial. @? y @@ para JSONPath queries.

Full-Text Search

to_tsvector convierte texto a lexemas. to_tsquery crea la query de búsqueda. @@ es el operador de match. GIN index sobre la columna tsvector para búsqueda rápida. ts_rank y ts_rank_cd para ranking por relevancia. websearch_to_tsquery acepta sintaxis natural. Para búsqueda multilingüe, especificá el diccionario: to_tsvector(''spanish'', texto).

Puntos clave

Window functions resuelven elegantemente rankings, totales acumulativos y acceso a filas vecinas sin self-joins.
JSONB con GIN index permite consultas flexibles sobre datos semi-estructurados con performance cercana a columnas nativas.
CTEs recursivos son la herramienta nativa para traversal de árboles y grafos en SQL.

Code example

-- Window function: rank de ventas por región
SELECT
  salesperson_id, region, total_sales,
  RANK() OVER (PARTITION BY region ORDER BY total_sales DESC) as rank,
  SUM(total_sales) OVER (PARTITION BY region) as region_total
FROM sales;

-- CTE recursivo: árbol de categorías
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.depth + 1
  FROM categories c
  JOIN category_tree ct ON ct.id = c.parent_id
)
SELECT * FROM category_tree ORDER BY depth, name;

-- JSONB query
SELECT id, payload->'>'email'
FROM users
WHERE payload @> '{"role": "admin"}'::jsonb;