Respuesta

Las funciones de ventana permiten realizar cálculos sobre una "ventana" de filas sin agruparlas en filas separadas, lo que es conveniente para informes y análisis.

ROW_NUMBER() — asigna un número secuencial único a las filas dentro de cada partición de la ventana, ordenándolas según el criterio establecido. Puede devolver saltos en la numeración al haber valores iguales en ORDER BY.
RANK() — asigna el mismo rango a las filas con valores iguales en ORDER BY, pero salta los números de los siguientes elementos (habrá un vacío).
DENSE_RANK() — también asigna el mismo rango a las filas con valores iguales, pero los números van seguidos, sin saltos.

Ejemplo:

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS num,
       RANK() OVER (ORDER BY salary DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Tabla:

name	salary
Vasya	10000
Petya	10000
Masha	9000

Resultado:

name	salary	num	rank	dense_rank
Vasya	10000	1	1	1
Petya	10000	2	1	1
Masha	9000	3	3	2

Trampas:

Un ORDER BY incorrectamente seleccionado puede llevar a un orden incorrecto.
(row_number) Si no se elige un campo único en ORDER BY, no hay garantía de orden estable.
Usar sin PARTITION BY cuando es necesario dividir puede llevar a una numeración incorrecta a través de todo el conjunto de filas.

Pregunta capciosa

Si no se especifica PARTITION BY en la función de ventana, ¿cómo se numerarán las filas en ROW_NUMBER()?

Respuesta: Toda la selección de datos será considerada una sola partición. Es decir, la numeración será continua a través de todas las filas, sin tener en cuenta agrupaciones.

Ejemplo:

SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) FROM employees;
-- Todos los empleados recibirán un número único, comenzando desde 1 sin división por departamentos

Historia #1

En el informe de BI, se olvidó especificar PARTITION BY por departamento. Todos los empleados de la empresa recibieron una numeración continua, mientras que la tarea era identificar a los mejores dentro de cada departamento. Como resultado, se obtuvo un TOP-N incorrecto de empleados por departamentos.

Historia #2

El desarrollador eligió RANK() en lugar de ROW_NUMBER() para determinar al "mejor" del grupo, pero al haber indicadores iguales se asignaron los mismos números, lo que resultó en duplicados no intencionados de líderes en el análisis.

Historia #3

Al usar DENSE_RANK() no se tuvo en cuenta que prohíbe los saltos en los rangos, lo que distorsionó el informe sobre la cantidad de posiciones "únicas" significativas al analizar ventas. El control sobre la lógica del negocio identificó el error en la distribución de lugares.

¿Cómo funcionan y en qué se diferencian las funciones de ventana ROW_NUMBER(), RANK(), DENSE_RANK() al programar informes en SQL? ¿Qué trampas hay al usarlas?

Respuesta

Pregunta capciosa