Datos y automatizacion M11 intermedio 16 min

Capitulo 01

SQL Avanzado para Analistas

El salto de nivel que separa a un Analyst intermedio de uno senior, y que el mercado pide en el 73% de las ofertas

Window functions, CTEs, procedimientos almacenados, optimizacion de queries, modelado en star schema y las diferencias practicas entre dialectos SQL.

Por que este es el primer capitulo

En un analisis de 33 ofertas de trabajo reales para perfiles de datos en Chile, SQL aparecio en el 73% — mas que Python, mas que cualquier herramienta de BI. Y no pedian “saber hacer un SELECT”: pedian SQL avanzado, incluso en ofertas que no mencionaban Python en absoluto.

La mayoria de los cursos de SQL se detienen en SELECT, JOIN y GROUP BY. Ese es el SQL de un Analyst junior. El SQL que te separa de ahi es otro: window functions, CTEs, optimizacion de queries y modelado de datos. Este capitulo cubre exactamente eso.

1.1 Window Functions: el salto mas rentable

Una window function calcula un valor sobre un conjunto de filas relacionadas (“ventana”) sin colapsarlas en una sola fila, a diferencia de GROUP BY. Es la herramienta que te permite responder preguntas como “cual fue la ultima compra de cada cliente” o “cual es el ranking de vendedores por mes” sin subqueries anidadas ilegibles.

Window Function

M11

Funcion que opera sobre un conjunto de filas relacionadas por PARTITION BY, devolviendo un valor por fila sin reducir el numero de filas del resultado.

#sql #window-functions

sql ranking_clientes.sql

-- Ranking de clientes por monto gastado, dentro de cada region
SELECT
    cliente_id,
    region,
    monto_total,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY monto_total DESC) AS ranking_region,
    RANK()       OVER (PARTITION BY region ORDER BY monto_total DESC) AS ranking_con_empates,
    LAG(monto_total)  OVER (PARTITION BY cliente_id ORDER BY fecha) AS monto_anterior,
    SUM(monto_total)  OVER (PARTITION BY cliente_id ORDER BY fecha
                            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
FROM ventas;

-- Resultado esperado (fragmento):
-- cliente_id | region  | monto_total | ranking_region | ranking_con_empates | monto_anterior | acumulado
-- 102        | Norte   | 850000      | 1              | 1                   | NULL            | 850000
-- 87         | Norte   | 850000      | 2              | 1                   | 620000          | 1470000
-- 55         | Norte   | 640000      | 3              | 3                   | 850000          | 2110000

Por que window function y no GROUP BY + subquery correlacionada

La alternativa “naive” para este mismo resultado es una subquery correlacionada que, por cada fila, vuelve a escanear la tabla completa para calcular el ranking o el acumulado de ESE cliente — un costo que crece cuadraticamente con el tamaño de la tabla. La window function calcula todo en una sola pasada sobre los datos ya ordenados y particionados. Regla practica: si la pregunta es “para cada fila, como se compara con las demas de su mismo grupo” (ranking, acumulado, diferencia con la fila anterior), es una window function — no una subquery ni un JOIN contra la misma tabla.

Concepto	Descripcion
ROW_NUMBER()	Asigna un numero unico y consecutivo. Nunca repite valores, incluso con empates. Usalo para "top N por grupo".
RANK()	Si hay empate, ambos reciben el mismo rank, y el siguiente salta un numero (1, 1, 3). Usalo cuando el empate es real (mismo monto).
DENSE_RANK()	Igual que RANK pero sin saltar numeros (1, 1, 2). Preferido cuando quieres "top 3 valores distintos".
LAG / LEAD	Trae el valor de una fila anterior (LAG) o posterior (LEAD) dentro de la misma particion. Clave para calcular variaciones periodo a periodo.

Trampa comun

Confundir PARTITION BY con GROUP BY. GROUP BY colapsa filas; PARTITION BY (dentro de una window function) NO colapsa nada — cada fila original se conserva, solo se le agrega una columna calculada. Si te piden “el detalle de cada venta MAS el ranking del cliente”, necesitas window function, no GROUP BY.

Caso real: control de gestion en retail

En un rol de Data Analyst de una cadena retail (el perfil mas frecuente en la muestra de mercado: Falabella, COPEC, Banco de Chile), el reporte semanal de “top vendedores por sucursal” se resuelve exactamente con este patron: ROW_NUMBER() OVER (PARTITION BY sucursal ORDER BY ventas DESC) para el ranking, y LAG() para mostrar la variacion contra la semana anterior en el mismo dashboard. Antes de que existieran window functions, este mismo reporte se armaba con multiples subqueries anidadas dificiles de mantener.

1. Olvidar el ORDER BY dentro de OVER en un acumulado

✗ SUM(monto) OVER (PARTITION BY cliente_id) -- sin ORDER BY: suma TODO el grupo en cada fila, no un acumulado

✓ SUM(monto) OVER (PARTITION BY cliente_id ORDER BY fecha ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

2. Usar ROW_NUMBER cuando la regla de negocio pide reconocer empates

✗ ROW_NUMBER() para 'el segundo lugar' -- si hay empate en el primero, salta al segundo real

✓ DENSE_RANK() cuando los empates deben compartir posicion y no correrse el ranking

1.2 CTEs: legibilidad antes que ingenio

Una CTE (Common Table Expression), definida con WITH, es una tabla temporal nombrada valida solo dentro de esa query. Su valor no es tecnico (no es mas rapida que una subquery equivalente en la mayoria de los motores) — es de legibilidad y mantenibilidad.

sql cte_basica.sql

WITH ventas_mensuales AS (
    SELECT
        DATE_TRUNC('month', fecha) AS mes,
        cliente_id,
        SUM(monto) AS total_mes
    FROM ventas
    GROUP BY 1, 2
),
clientes_top AS (
    SELECT cliente_id, SUM(total_mes) AS total_anual
    FROM ventas_mensuales
    GROUP BY cliente_id
    HAVING SUM(total_mes) > 1000000
)
SELECT vm.*
FROM ventas_mensuales vm
JOIN clientes_top ct ON vm.cliente_id = ct.cliente_id
ORDER BY vm.mes;

Por que CTE y no una subquery anidada

En la mayoria de los motores (PostgreSQL, BigQuery, SQL Server) una CTE no ejecuta mas rapido que la subquery equivalente — el optimizador suele generar el mismo plan. La razon para usar CTEs es legibilidad y mantenibilidad: separas la query en pasos con nombre (ventas_mensuales, clientes_top) que se leen de arriba hacia abajo, en vez de subqueries anidadas 3 niveles adentro que hay que leer de adentro hacia afuera. Si tu query tiene mas de un nivel de subquery, es candidata a convertirse en CTE.

Caso real: cierre mensual en banca

En un rol de Data Analyst/Engineer de banca (Itaú, Banco de Chile en la muestra de mercado), el reporte de “cierre mensual de cartera” tipicamente encadena 3-4 CTEs: una para normalizar transacciones, otra para calcular saldos, otra para clasificar clientes por riesgo. Nombrar cada paso como una CTE hace que otro analista pueda auditar la logica sin tener que descifrar una query de 200 lineas anidada.

Una CTE recursiva se referencia a si misma, y es la forma estandar de resolver queries jerarquicas: organigramas, arboles de categorias de productos, listas de materiales.

sql cte_recursiva_organigrama.sql

WITH RECURSIVE organigrama AS (
    -- Caso base: el CEO, sin jefe
    SELECT id, nombre, jefe_id, 1 AS nivel
    FROM empleados
    WHERE jefe_id IS NULL

    UNION ALL

    -- Caso recursivo: cada empleado cuyo jefe ya esta en el resultado
    SELECT e.id, e.nombre, e.jefe_id, o.nivel + 1
    FROM empleados e
    JOIN organigrama o ON e.jefe_id = o.id
)
SELECT * FROM organigrama ORDER BY nivel, nombre;

1. CTE recursiva sin condicion de corte clara

✗ Un caso recursivo que no reduce el problema (ej. JOIN sin filtro que garantice avanzar de nivel) puede entrar en loop infinito.

✓ Verificar siempre que el caso recursivo (la parte despues de UNION ALL) avanza hacia el caso base — en el ejemplo, jefe_id = id del nivel anterior garantiza terminar.

2. Asumir que una CTE se ejecuta una sola vez si se referencia varias veces

✗ Referenciar la misma CTE 3 veces en la query externa asumiendo que el resultado se calcula y cachea una sola vez.

✓ Verificar en PostgreSQL/BigQuery si la CTE se materializa o se re-ejecuta por cada referencia (varia segun motor y version) — si el calculo es costoso y se repite, considera una tabla temporal en su lugar.

1.3 Procedimientos almacenados y funciones definidas por el usuario

Cuando una logica de negocio se repite en muchas queries (ej. “clasificar un cliente segun su gasto”), conviene encapsularla en una funcion definida por el usuario (UDF) o un procedimiento almacenado.

sql udf_clasificar_cliente.sql

CREATE OR REPLACE FUNCTION clasificar_cliente(monto_total NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF monto_total > 5000000 THEN RETURN 'VIP';
    ELSIF monto_total > 1000000 THEN RETURN 'Frecuente';
    ELSE RETURN 'Ocasional';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Uso:
SELECT cliente_id, monto_total, clasificar_cliente(monto_total) AS categoria
FROM resumen_clientes;

Cuando SI usar procedimientos almacenados

Un procedimiento almacenado (a diferencia de una UDF) puede ejecutar multiples sentencias, incluyendo INSERT/UPDATE/DELETE, y se invoca con CALL. Usalos para logica de ETL dentro de la base (ej. “actualizar la tabla de resumen cada noche”), no para calculos simples reutilizables en un SELECT — para eso, una UDF alcanza y es mas facil de testear.

Caso real: job nocturno de clasificacion

Un patron comun en Data Engineering (SII Group, Akzio en la muestra de mercado) es un procedimiento almacenado que corre cada noche via un scheduler: recalcula la clasificacion de todos los clientes activos y actualiza una tabla de resumen que el equipo de BI consulta al dia siguiente. La ventaja de resolverlo con un procedimiento (en vez de un script externo) es que toda la logica queda versionada junto a la base y no depende de un servidor externo disponible a esa hora.

1. Poner logica de negocio compleja dentro de un procedimiento sin tests

✗ Un procedimiento de 200 lineas con reglas de negocio que solo se valida corriendolo manualmente en produccion.

✓ Mantener el procedimiento simple (una responsabilidad) y, si el motor lo permite, escribir tests que verifiquen el resultado sobre datos de prueba antes de desplegar.

1.4 Optimizacion de queries

Concepto	Descripcion
EXPLAIN	Muestra como el motor va a ejecutar la query: que indices usa, en que orden hace los JOINs, cuantas filas estima procesar. EXPLAIN ANALYZE ademas la ejecuta y muestra tiempos reales.
Indices	Una columna sin indice fuerza un table scan completo. Las columnas usadas en WHERE, JOIN y ORDER BY son candidatas. Un indice mal elegido tambien puede ralentizar los INSERT/UPDATE.
SELECT *	Trae solo las columnas que necesitas. Menos I/O, menos memoria, y evita romper el codigo si la tabla cambia de estructura. Especialmente critico en tablas con columnas de texto largo o BLOBs.
Particionamiento	Dividir una tabla enorme en particiones fisicas (ej. por mes) para que las queries solo escaneen la particion relevante. Fundamental en tablas de eventos/logs con millones de filas.

Caso real: dashboard que tardaba 40 segundos

Un caso tipico: un dashboard de Power BI que consulta una tabla de transacciones de 50 millones de filas tarda 40 segundos en refrescar porque la query hace SELECT * y filtra por fecha sin que esa columna tenga indice ni la tabla este particionada. Agregar un indice sobre la columna de fecha y particionar la tabla por mes puede bajar ese tiempo a menos de 2 segundos — sin cambiar una sola linea de logica de negocio, solo la estrategia de acceso a los datos.

1. Agregar un indice a cada columna 'por si acaso'

✗ Indexar todas las columnas de una tabla con mucha escritura (ej. una tabla de eventos que recibe miles de INSERT por minuto).

✓ Indexar solo las columnas realmente usadas en WHERE/JOIN/ORDER BY frecuentes — cada indice adicional hace mas lento cada INSERT/UPDATE.

1.5 Modelado: star schema

El star schema es el modelo de datos estandar para BI: una tabla de hechos central (ventas, transacciones) rodeada de tablas de dimension (cliente, producto, tiempo). Power BI, Tableau y Looker estan optimizados para este patron.

Star Schema

M11

Modelo de datos con una tabla de hechos central que contiene metricas numericas y claves foraneas, rodeada de tablas de dimension con atributos descriptivos. Optimiza las consultas analiticas frente al modelo normalizado tradicional.

#sql #modelado #bi

Por que star schema y no un modelo normalizado

Un modelo totalmente normalizado (3FN) minimiza redundancia, pero para BI implica muchos JOINs entre tablas pequeñas cada vez que alguien arma un dashboard — lento y dificil de razonar para quien construye el reporte. El star schema acepta algo de redundancia en las dimensiones a cambio de que casi toda consulta analitica sea “una tabla de hechos + 1-2 JOINs directos a dimension”, que es exactamente el patron que Power BI/Tableau/Looker esperan y optimizan.

Caso real: modelo de ventas para Power BI

En ofertas que piden DAX avanzado (Practia, SEIDOR en la muestra de mercado), el prerequisito casi siempre invisible es que el modelo de datos detras del dashboard ya sea un star schema: una tabla hechos_ventas (monto, cantidad, claves foraneas) rodeada de dim_cliente, dim_producto, dim_tiempo, dim_sucursal. Sin este modelo, ni el DAX mas avanzado compensa un dashboard lento o dificil de mantener.

1.6 Dialectos: lo que cambia entre motores

Concepto	PostgreSQL	SQL Server (T-SQL)	BigQuery
Limitar filas	LIMIT n LIMIT n
Concatenar	a \|\| b CONCAT(a,b)
Fecha actual	CURRENT_DATE CURRENT_DATE()

Consejo profesional

No memorices los tres dialectos de memoria. Lo que un entrevistador quiere ver es que sabes que existen diferencias y que las buscas quando corresponde, no que asumas que el SQL es 100% portable entre motores.

1.7 Recursos

Curso: Mode Analytics SQL Tutorial (gratis, muy practico con window functions)

Articulo: PostgreSQL Docs: Window Functions (documentacion oficial, la referencia definitiva)

Herramienta: use the index, luke! (guia practica sobre indices y optimizacion)

1.8 Preguntas de entrevista

Q: ¿Cual es la diferencia entre WHERE y HAVING?

WHERE filtra filas ANTES de la agregacion (GROUP BY); HAVING filtra grupos DESPUES de la agregacion. Por eso HAVING puede usar funciones agregadas (SUM, COUNT) y WHERE no.

🪤 Decir que son intercambiables o que HAVING 'es lo mismo pero para grupos' sin explicar el orden de ejecucion.

Q: Te piden el segundo salario mas alto de cada departamento. ¿Como lo resolverias?

Con DENSE_RANK() OVER (PARTITION BY departamento ORDER BY salario DESC) en una CTE, y despues filtrar WHERE rank = 2 en la query externa. Uso DENSE_RANK y no ROW_NUMBER porque si hay empate en el primer lugar, el segundo lugar real no debe saltarse.

🪤 Usar ROW_NUMBER sin justificar por que, ignorando el caso de empates.

Practica: reescribi 5 queries con window functions intermedio

Busca (o crea) un dataset con ventas/transacciones y al menos una columna de fecha y una de categoria/cliente.
Escribe una query que hoy usarias con una subquery correlacionada, y reescribela con una window function equivalente.
Calcula el ranking de los 3 mejores clientes por region usando DENSE_RANK.
Calcula la variacion mes a mes de las ventas de un cliente usando LAG.
Corre EXPLAIN ANALYZE sobre tu query final y anota que indice agregarias.