Etiqueta: sql

ROW_NUMBER, SUM y MAX con PARTITION BY
Las funciones de ventana (window functions) en SQL permiten hacer cálculos sobre un conjunto de filas relacionadas sin colapsar el resultado, a diferencia de GROUP BY. La cláusula OVER define esa «ventana» de filas, y PARTITION BY la divide en grupos — como un GROUP BY que no oculta el detalle de cada fila.

Datos de ejemplo

Partimos de esta tabla base para todos los ejemplos:

depto cod nombre
1 100 Fran
1 101 Chris
2 102 Alberto
2 103 Hernan
3 104 Julia

ROW_NUMBER con PARTITION BY

Este ejemplo numera las filas dentro de cada departamento, ordenadas por código. El contador se reinicia en 1 cada vez que cambia el valor de depto:
```
SELECT depto, cod, nombre,
    ROW_NUMBER() OVER (PARTITION BY depto ORDER BY cod) AS rw
FROM departamentos
```
Resultado

depto cod nombre rw
1 100 Fran 1
1 101 Chris 2
2 102 Alberto 1
2 103 Hernan 2
3 104 Julia 1

RANK y DENSE_RANK: variantes de numeración

ROW_NUMBER siempre da números consecutivos únicos, incluso si hay empates. Si necesitas que los empates compartan posición, existen dos alternativas:
- RANK() — da la misma posición a los empates, pero deja «huecos» en la numeración siguiente (1, 1, 3).
- DENSE_RANK() — da la misma posición a los empates, sin dejar huecos (1, 1, 2).
```
SELECT depto, cod, nombre,
    RANK() OVER (PARTITION BY depto ORDER BY cod) AS posicion_rank,
    DENSE_RANK() OVER (PARTITION BY depto ORDER BY cod) AS posicion_dense
FROM departamentos
```
Suma de ventas por día

Calcula el total acumulado de ventas partiendo los datos por fecha. Al usar ORDER BY dentro de OVER, la suma se vuelve un running total: cada fila acumula el valor de las anteriores dentro de su misma partición:
```
SELECT fecha, venta_dia,
    SUM(venta_dia) OVER (PARTITION BY fecha ORDER BY fecha DESC) AS suma_acumulada
FROM ventas
```
Sueldos máximos por departamento

Obtiene el sueldo más alto dentro de cada departamento, sin agrupar ni perder las filas individuales — cada empleado sigue apareciendo en su propia fila, junto al máximo de su equipo:
```
SELECT depto, nombre, sueldo,
    MAX(sueldo) OVER (PARTITION BY depto) AS sueldo_max_depto
FROM empleados
```
LAG y LEAD: comparar con la fila anterior o siguiente

Otro uso común de OVER es comparar el valor de una fila con el de la fila anterior o siguiente dentro de la misma partición — útil para calcular variaciones día a día o mes a mes:
```
SELECT depto, cod, nombre,
    LAG(cod) OVER (PARTITION BY depto ORDER BY cod) AS cod_anterior,
    LEAD(cod) OVER (PARTITION BY depto ORDER BY cod) AS cod_siguiente
FROM departamentos
```
Por qué usar OVER en vez de GROUP BY

La diferencia clave es que GROUP BY reduce el número de filas del resultado (una por grupo), mientras que OVER/PARTITION BY mantiene todas las filas originales y solo agrega una columna calculada junto a cada una. Esto es especialmente útil cuando necesitas comparar el valor de una fila contra un agregado de su grupo (como el sueldo máximo del departamento) sin perder el detalle individual.
06/02/2023

depto	cod	nombre
1	100	Fran
1	101	Chris
2	102	Alberto
2	103	Hernan
3	104	Julia

depto	cod	nombre	rw
1	100	Fran	1
1	101	Chris	2
2	102	Alberto	1
2	103	Hernan	2
3	104	Julia	1

JOIN ON vs WHERE: por qué la ubicación de la condición importa

Concepto fundamental

En SQL, una condición puede colocarse en la cláusula ON del JOIN, o en la cláusula WHERE. Aunque en muchos casos parecen producir el mismo resultado, no siempre son equivalentes, especialmente cuando se usan LEFT JOIN, RIGHT JOIN o FULL OUTER JOIN.

Orden lógico de ejecución

De forma simplificada, SQL procesa una consulta en este orden:

FROM
JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

FROM
JOIN
ON
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Esto significa que primero se realizan los JOIN, luego se aplican las condiciones del ON, y después se ejecuta el WHERE sobre el resultado ya obtenido.

Diferencia conceptual

Condición en ON

La condición forma parte del criterio de unión entre las tablas:

SELECT *
FROM tablaA a
JOIN tablaB b
    ON a.id = b.id
   AND b.estado = 'ACTIVO';

SELECT *
FROM tablaA a
JOIN tablaB b
    ON a.id = b.id
   AND b.estado = 'ACTIVO';

Interpretación: une ambas tablas solamente cuando el ID coincida y el registro de B esté ACTIVO.

Condición en WHERE

La unión se realiza primero y luego se filtran los resultados:

SELECT *
FROM tablaA a
JOIN tablaB b
    ON a.id = b.id
WHERE b.estado = 'ACTIVO';

SELECT *
FROM tablaA a
JOIN tablaB b
    ON a.id = b.id
WHERE b.estado = 'ACTIVO';

Interpretación: une ambas tablas por ID y después elimina todas las filas cuyo estado no sea ACTIVO.

Caso 1: INNER JOIN

Tablas de ejemplo:

clientes
id	nombre
1	Ana
2	Luis
3	Pedro

pedidos
id	cliente_id	estado
10	1	ACTIVO
11	1	INACTIVO
12	2	INACTIVO

Opción A: condición en ON

SELECT *
FROM clientes c
INNER JOIN pedidos p
    ON c.id = p.cliente_id
   AND p.estado = 'ACTIVO';

SELECT *
FROM clientes c
INNER JOIN pedidos p
    ON c.id = p.cliente_id
   AND p.estado = 'ACTIVO';

cliente	pedido	estado
Ana	10	ACTIVO

Opción B: condición en WHERE

SELECT *
FROM clientes c
INNER JOIN pedidos p
    ON c.id = p.cliente_id
WHERE p.estado = 'ACTIVO';

SELECT *
FROM clientes c
INNER JOIN pedidos p
    ON c.id = p.cliente_id
WHERE p.estado = 'ACTIVO';

cliente	pedido	estado
Ana	10	ACTIVO

Conclusión para INNER JOIN

En la mayoría de los casos, INNER JOIN ... ON condicion y INNER JOIN ... WHERE condicion producen el mismo resultado. Por eso muchas personas creen erróneamente que siempre es indiferente dónde colocar la condición.

Caso 2: LEFT JOIN

Aquí es donde aparecen los problemas.

LEFT JOIN sin filtros

SELECT *
FROM clientes c
LEFT JOIN pedidos p
    ON c.id = p.cliente_id;

SELECT *
FROM clientes c
LEFT JOIN pedidos p
    ON c.id = p.cliente_id;

cliente	pedido	estado
Ana	10	ACTIVO
Ana	11	INACTIVO
Luis	12	INACTIVO
Pedro	NULL	NULL

Pedro aparece aunque no tenga pedidos — ese es justamente el propósito del LEFT JOIN.

Condición en ON (correcto)

Supongamos que queremos mostrar todos los clientes y únicamente sus pedidos activos:

SELECT *
FROM clientes c
LEFT JOIN pedidos p
    ON c.id = p.cliente_id
   AND p.estado = 'ACTIVO';

SELECT *
FROM clientes c
LEFT JOIN pedidos p
    ON c.id = p.cliente_id
   AND p.estado = 'ACTIVO';

Ana tiene un pedido ACTIVO, se muestra. Luis tiene pedidos pero ninguno ACTIVO, se devuelve NULL. Pedro no tiene pedidos, se devuelve NULL. Todos los clientes siguen apareciendo:

cliente	pedido	estado
Ana	10	ACTIVO
Luis	NULL	NULL
Pedro	NULL	NULL

Condición en WHERE (error típico)

SELECT *
FROM clientes c
LEFT JOIN pedidos p
    ON c.id = p.cliente_id
WHERE p.estado = 'ACTIVO';

SELECT *
FROM clientes c
LEFT JOIN pedidos p
    ON c.id = p.cliente_id
WHERE p.estado = 'ACTIVO';

Primero SQL realiza el LEFT JOIN completo (las 4 filas de arriba), y luego el WHERE elimina todo lo que no sea ACTIVO, incluyendo los NULL. Resultado final:

cliente	pedido	estado
Ana	10	ACTIVO

Problema: desaparecieron Luis y Pedro. El LEFT JOIN terminó comportándose como un INNER JOIN.

La regla más importante

Cuando uses LEFT JOIN, pregúntate: ¿quiero conservar los registros que no tengan coincidencia? Si la respuesta es sí, las condiciones sobre la tabla derecha generalmente deben ir en el ON.

Ejemplo típico en seguros

Supongamos estas tablas:

póliza
P001
P002
P003

póliza	estado
P001	PAGADO
P002	PENDIENTE

Objetivo: mostrar todas las pólizas y solamente los pagos pendientes.

Correcto

SELECT *
FROM polizas p
LEFT JOIN pagos pa
    ON p.poliza = pa.poliza
   AND pa.estado = 'PENDIENTE';

SELECT *
FROM polizas p
LEFT JOIN pagos pa
    ON p.poliza = pa.poliza
   AND pa.estado = 'PENDIENTE';

póliza	estado
P001	NULL
P002	PENDIENTE
P003	NULL

Incorrecto

SELECT *
FROM polizas p
LEFT JOIN pagos pa
    ON p.poliza = pa.poliza
WHERE pa.estado = 'PENDIENTE';

SELECT *
FROM polizas p
LEFT JOIN pagos pa
    ON p.poliza = pa.poliza
WHERE pa.estado = 'PENDIENTE';

póliza	estado
P002	PENDIENTE

Se perdieron P001 y P003.

Regla práctica para el trabajo diario

INNER JOIN

Generalmente da igual si la condición va en el ON o en el WHERE.

LEFT JOIN

Si la condición afecta a la tabla de la derecha:

Normalmente correcto: LEFT JOIN tablaB ON ... AND condicion
Sospechoso: LEFT JOIN tablaB ON ... WHERE tablaB.columna = valor — muchas veces esto destruye el LEFT JOIN.

Truco mental rápido

Si ves un LEFT JOIN seguido de un WHERE que filtra sobre la tabla derecha, detente y pregúntate: ¿quiero eliminar los NULL? Si la respuesta es no, la condición probablemente debe estar en el ON. Si es sí, el WHERE está bien.

Resumen final

Ubicación de la condición	Función
ON	Define cómo se unen las tablas
WHERE	Filtra el resultado después del JOIN
INNER JOIN	Generalmente produce el mismo resultado en ambos casos
LEFT JOIN	Puede cambiar completamente el resultado
ON + LEFT JOIN	Conserva registros sin coincidencia
WHERE + LEFT JOIN	Puede convertirlo en un INNER JOIN

Regla de oro: si utilizas LEFT JOIN y quieres conservar los registros de la tabla izquierda aunque no exista coincidencia en la tabla derecha, coloca las condiciones de la tabla derecha dentro del ON y no en el WHERE.

18/08/2022