23 de julio de 202610 min de lectura

SQL Avanzado: 40 Conceptos Nivel Senior/DBA

Window functions, CTEs, rendimiento, concurrencia, seguridad y administración: 40 temas para trabajo serio con datos.

sql
banco-de-dados
avancado
dba
performance

SQL Avanzado: 40 Conceptos de Nivel Senior / DBA

Aquí vive la diferencia entre "funciona" y "funciona rápido y seguro en producción". Son 40 temas de window functions, CTEs, rendimiento, concurrencia, seguridad y administración — el kit de quien trabaja en serio con datos.

1. Window Functions — OVER / PARTITION BY

Agrega sin colapsar las filas. Conservas el detalle Y ves el total del grupo en la misma fila.

SELECT nome, cidade, total,
  SUM(total) OVER (PARTITION BY cidade) AS total_cidade
FROM pedidos;

2. ROW_NUMBER — numerar filas

Da un número secuencial por grupo. La base para 'el más reciente de cada uno'.

SELECT * FROM (
  SELECT p.*,
    ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data DESC) AS rn
  FROM pedidos p
) t
WHERE rn = 1; -- último pedido de cada cliente

3. RANK / DENSE_RANK

Clasifican con empates. RANK salta posiciones; DENSE_RANK no.

SELECT nome, total,
  RANK()       OVER (ORDER BY total DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY total DESC) AS dense
FROM pedidos;

4. LAG / LEAD — fila anterior/siguiente

Compara una fila con su vecina. Perfecto para la variación mes a mes.

SELECT mes, receita,
  LAG(receita) OVER (ORDER BY mes) AS mes_anterior,
  receita - LAG(receita) OVER (ORDER BY mes) AS variacao
FROM receita_mensal;

5. Running total — suma acumulada

Una suma que corre fila a fila usando ORDER BY dentro de la ventana.

SELECT data, valor,
  SUM(valor) OVER (ORDER BY data
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
FROM movimentacoes;

6. NTILE — dividir en tramos

Divide las filas en N grupos de tamaño parecido (cuartiles, deciles).

SELECT nome, total,
  NTILE(4) OVER (ORDER BY total DESC) AS quartil
FROM clientes_gasto;

7. FIRST_VALUE / LAST_VALUE

Toma el primer/último valor de la ventana. Cuidado con el frame en LAST_VALUE.

SELECT cidade, nome, total,
  FIRST_VALUE(nome) OVER (
    PARTITION BY cidade ORDER BY total DESC) AS maior_comprador
FROM pedidos;

8. CTE — WITH (consulta con nombre)

Da nombre a las subconsultas y hace legibles las consultas complejas, de arriba abajo.

WITH faturamento AS (
  SELECT cidade, SUM(total) AS total FROM pedidos GROUP BY cidade
)
SELECT * FROM faturamento WHERE total > 10000;

9. Recursive CTE — jerarquías

Una CTE que se llama a sí misma: recorre árboles (organigrama, categorías, grafos).

WITH RECURSIVE subordinados AS (
  SELECT id, nome, gerente_id FROM funcionarios WHERE id = 1
  UNION ALL
  SELECT f.id, f.nome, f.gerente_id
  FROM funcionarios f
  JOIN subordinados s ON f.gerente_id = s.id
)
SELECT * FROM subordinados;

10. Pivot — filas a columnas

Convierte valores en columnas con agregación condicional (funciona en cualquier base de datos).

SELECT produto,
  SUM(CASE WHEN mes = 1 THEN qtd END) AS jan,
  SUM(CASE WHEN mes = 2 THEN qtd END) AS fev,
  SUM(CASE WHEN mes = 3 THEN qtd END) AS mar
FROM vendas
GROUP BY produto;

11. Unpivot — columnas a filas

Lo inverso del pivot: normaliza columnas en pares (clave, valor).

SELECT produto, 'jan' AS mes, jan AS qtd FROM vendas_wide
UNION ALL
SELECT produto, 'fev', fev FROM vendas_wide
UNION ALL
SELECT produto, 'mar', mar FROM vendas_wide;

12. MERGE / UPSERT

Inserta si no existe, actualiza si existe — en una sola sentencia.

-- PostgreSQL / SQLite
INSERT INTO estoque (produto_id, qtd) VALUES (10, 5)
ON CONFLICT (produto_id)
DO UPDATE SET qtd = estoque.qtd + EXCLUDED.qtd;

13. ON DUPLICATE KEY (MySQL)

La versión del UPSERT en MySQL.

INSERT INTO estoque (produto_id, qtd) VALUES (10, 5)
ON DUPLICATE KEY UPDATE qtd = qtd + VALUES(qtd);

14. Índice compuesto y covering index

Un índice sobre varias columnas puede responder la consulta sin tocar la tabla.

-- El orden importa: filtra por cliente_id y ordena por data
CREATE INDEX idx_ped_cli_data ON pedidos(cliente_id, data);

-- Covering: incluye columnas del SELECT (PostgreSQL)
CREATE INDEX idx_cover ON pedidos(cliente_id) INCLUDE (total);

15. EXPLAIN — leer el plan de ejecución

Muestra CÓMO va a ejecutar la base de datos. Es la herramienta nº1 de tuning.

EXPLAIN ANALYZE
SELECT * FROM pedidos WHERE cliente_id = 42;
-- Busca 'Seq Scan' (malo en una tabla grande) vs 'Index Scan' (bueno)

16. Predicados sargable

Escribe el WHERE de forma que el índice pueda usarse. No envuelvas la columna en una función.

-- MALO: una función sobre la columna impide el índice
WHERE YEAR(data) = 2024

-- BUENO: usa el índice de 'data'
WHERE data >= '2024-01-01' AND data < '2025-01-01'

17. Materialized View

Como una VIEW, pero almacena el resultado. Rápida de leer; hay que refrescarla.

CREATE MATERIALIZED VIEW mv_dashboard AS
SELECT cidade, SUM(total) AS total FROM pedidos GROUP BY cidade;

REFRESH MATERIALIZED VIEW mv_dashboard;

18. Stored Procedure

Un bloque de SQL con nombre y reutilizable, con parámetros y lógica dentro de la base de datos.

CREATE PROCEDURE dar_desconto(IN pid INT, IN pct DECIMAL)
BEGIN
  UPDATE produtos SET preco = preco * (1 - pct/100) WHERE id = pid;
END;

CALL dar_desconto(10, 15);

19. Función (UDF)

Devuelve un valor y puede usarse dentro de las consultas.

CREATE FUNCTION preco_com_iva(preco DECIMAL)
RETURNS DECIMAL
RETURN preco * 1.23;

SELECT nome, preco_com_iva(preco) FROM produtos;

20. Triggers — reacciones automáticas

Ejecuta código automáticamente en INSERT/UPDATE/DELETE. Excelente para auditoría.

CREATE TRIGGER trg_log_preco
AFTER UPDATE ON produtos
FOR EACH ROW
INSERT INTO log_precos (produto_id, preco_antigo, preco_novo)
VALUES (OLD.id, OLD.preco, NEW.preco);

21. Isolation levels

Controlan lo que una transacción ve de las demás. Un equilibrio entre consistencia y concurrencia.

-- READ COMMITTED (por defecto en muchas bases), REPEATABLE READ, SERIALIZABLE
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
  -- lecturas consistentes, sin 'phantom reads'
COMMIT;

22. Deadlocks y locking

Dos transacciones se bloquean esperándose la una a la otra. Prevénlo accediendo a los recursos en el mismo orden.

-- Bloquea las filas hasta el COMMIT (evita la carrera)
BEGIN;
  SELECT * FROM contas WHERE id = 1 FOR UPDATE;
  UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

23. Particionado de tablas

Divide una tabla gigante en trozos (por ejemplo, por mes). Las consultas recorren solo la partición correcta.

CREATE TABLE pedidos (id BIGINT, data DATE, total DECIMAL)
PARTITION BY RANGE (data);

CREATE TABLE pedidos_2024 PARTITION OF pedidos
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

24. Normalización (1FN, 2FN, 3FN)

Organiza los datos para evitar repeticiones y anomalías. Cada hecho en un solo sitio.

-- MALO (repite el cliente en cada pedido):
-- pedidos(id, cliente_nome, cliente_email, total)

-- BUENO (3FN): separa en dos tablas unidas por una FK
-- clientes(id, nome, email)
-- pedidos(id, cliente_id, total)

25. Desnormalización

Repetir datos a propósito para ganar velocidad de lectura (habitual en BI/analytics).

-- Guarda el total ya calculado para no recalcularlo en cada lectura
ALTER TABLE clientes ADD COLUMN total_gasto DECIMAL(12,2) DEFAULT 0;
-- (mantenido por un trigger o un job)

26. Índice parcial / filtrado

Indexa solo las filas que importan. Más pequeño y más rápido.

-- Solo pedidos abiertos (PostgreSQL)
CREATE INDEX idx_abertos ON pedidos(cliente_id) WHERE status = 'aberto';

27. GROUPING SETS / ROLLUP / CUBE

Varios niveles de totalización en una sola consulta (subtotales + total general).

SELECT cidade, categoria, SUM(total)
FROM pedidos
GROUP BY ROLLUP (cidade, categoria);

28. LATERAL JOIN / CROSS APPLY

Un JOIN donde el lado derecho ve el izquierdo. Excelente para 'top N por grupo'.

SELECT c.nome, p.*
FROM clientes c
CROSS JOIN LATERAL (
  SELECT * FROM pedidos p
  WHERE p.cliente_id = c.id ORDER BY data DESC LIMIT 3
) p; -- los 3 últimos pedidos de cada cliente

29. JSON en SQL

Las bases de datos modernas guardan y consultan JSON de forma nativa.

-- PostgreSQL
SELECT dados->>'email' AS email
FROM eventos
WHERE dados->>'tipo' = 'login';

-- Índice sobre un campo JSON
CREATE INDEX idx_tipo ON eventos ((dados->>'tipo'));

30. Full-text search

Búsqueda textual de verdad (relevancia, stemming), mucho más allá del LIKE '%x%'.

-- PostgreSQL
SELECT * FROM artigos
WHERE to_tsvector('portuguese', corpo) @@ to_tsquery('portuguese', 'banco & dados');

31. Evita las trampas más comunes

SELECT * en producción, N+1 y funciones en el WHERE matan el rendimiento.

-- Evita: SELECT * (trae demasiadas columnas, rompe el covering index)
-- Evita: consultar dentro de un bucle en la app (N+1) -> usa un JOIN
-- Evita: WHERE LOWER(email) = ... -> usa una columna/índice adecuado

32. EXISTS vs IN vs JOIN

Semánticas parecidas, rendimiento distinto. EXISTS suele ganar con subconjuntos grandes; cuidado con NOT IN y los NULL.

-- Prefiere EXISTS para 'tiene al menos uno'
SELECT c.* FROM clientes c
WHERE EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

-- NOT IN se rompe si la subconsulta tiene un NULL -> usa NOT EXISTS

33. Tablas temporales vs CTE vs variables

La CTE es excelente para la legibilidad; una tabla temporal materializa el resultado y puede indexarse (bueno para reutilización intensa).

CREATE TEMP TABLE tmp_top AS
SELECT cliente_id, SUM(total) AS total
FROM pedidos GROUP BY cliente_id;

CREATE INDEX ON tmp_top(total);
SELECT * FROM tmp_top ORDER BY total DESC LIMIT 10;

34. Paginación eficiente (keyset)

Un OFFSET grande es lento. Pagina por el último id/valor visto (keyset/seek).

-- LENTO en páginas altas:
SELECT * FROM pedidos ORDER BY id LIMIT 20 OFFSET 100000;

-- RÁPIDO (keyset):
SELECT * FROM pedidos WHERE id > 100000 ORDER BY id LIMIT 20;

35. Batching — por lotes

Actualiza/borra por lotes para no bloquear la tabla ni desbordar el log.

-- Borra 10 mil cada vez, en un bucle en la app
DELETE FROM logs
WHERE id IN (
  SELECT id FROM logs WHERE criado_em < '2023-01-01' LIMIT 10000
);

36. SQL Injection — el fallo nº1

Nunca concatenes la entrada del usuario en la consulta. Usa siempre consultas parametrizadas.

-- PELIGROSO (¡inyección!):
-- "SELECT * FROM users WHERE email = '" + input + "'"

-- SEGURO (placeholder / prepared statement):
SELECT * FROM users WHERE email = ?;   -- el valor se pasa aparte

37. Dynamic SQL con cuidado

Montar SQL en tiempo de ejecución es potente, pero peligroso. Valida los identificadores y parametriza los valores.

-- Si necesitas una columna/tabla dinámica, usa una whitelist:
-- if (col not in ['nome','email']) throw;
-- y SIEMPRE parametriza los VALORES, nunca la entrada directa.

38. GRANT / REVOKE — permisos

Principio del menor privilegio: cada usuario accede solo a lo que necesita.

GRANT SELECT, INSERT ON pedidos TO app_user;
REVOKE DELETE ON pedidos FROM app_user;

39. Backup y restore

Sin un backup probado, no hay datos. Automatízalo y prueba la restauración.

-- PostgreSQL
-- pg_dump -Fc meubanco > backup.dump
-- pg_restore -d meubanco backup.dump

-- Point-in-time recovery con WAL para restaurar hasta un instante dado

40. Replicación y sharding

Escala las lecturas con réplicas; escala la escritura/volumen dividiendo los datos (sharding) por una clave.

-- Réplica de lectura: la app manda los SELECT a la réplica y las escrituras al primary.
-- Sharding: cliente_id % 4 decide en qué shard vive la fila.
-- (configuración de infraestructura, no un único comando)

Jhonatan Pinheiro

Cargando página...