Funciones de ventana ROW_NUMBER, RANK y DENSE_RANK

Las funciones de ventana permiten asignar números secuenciales o posiciones de ranking a las filas de una consulta sin colapsar los resultados originales.

Hasta ahora, cuando queríamos calcular agregados (como sumas o conteos), teníamos que usar GROUP BY. Pero eso tenía un precio: perdíamos el detalle de las filas individuales. Colapsábamos todo en un resumen.

¿Qué pasa si quiero saber el “Top 3 de ventas” pero sin perder la información de quién hizo la venta y cuándo? ¿O si quiero numerar las filas de una consulta?

Aquí entran en juego las funciones de ventana. En este artículo nos centraremos en las funciones de Ranking: ROW_NUMBER, RANK y DENSE_RANK.

La cláusula OVER

La cláusula OVER define una ventana de datos sobre la cual se aplicará una función de agregación. Esta ventana puede ser toda la tabla, un subconjunto de filas o incluso filas relacionadas con la fila actual.

La sintaxis básica es la siguiente:

FUNCION_AGREGACION() OVER ( [PARTITION BY columna] [ORDER BY columna] )

Los tres compañeros de OVER

Imaginad que hemos hecho un examen en clase y tenemos las siguientes notas. Vamos a ver cómo puntúa cada función.

Fíjate que Ana y Pedro han empatado con 800€. Aquí es donde surgen las diferencias.

Ejemplo en código

Vamos a verlo con una consulta real sobre una tabla de Ventas.

SELECT 
    Vendedor,
    Importe,
    ROW_NUMBER() OVER (ORDER BY Importe DESC) as Fila,
    RANK()       OVER (ORDER BY Importe DESC) as Ranking,
    DENSE_RANK() OVER (ORDER BY Importe DESC) as RankingDenso
FROM Ventas;

Resultado:

La potencia de PARTITION BY

La cláusula PARTITION BY es opcional y se utiliza dentro de OVER para dividir los datos en grupos más pequeños, llamados particiones.

Esto nos permite reiniciar el ranking para cada grupo de datos.

Por ejemplo, queremos saber quiénes son los 2 mejores vendedores de cada departamento.

SELECT * FROM (
    SELECT 
        Departamento,
        Vendedor,
        Ventas,
        DENSE_RANK() OVER (
            PARTITION BY Departamento -- Reinicia el contador al cambiar de Dpto
            ORDER BY Ventas DESC
        ) as Posicion
    FROM Empleados
) TablaDerivada
WHERE Posicion <= 2;

WITH CTE_Duplicados AS (
    SELECT 
        Nombre, 
        Email,
        -- Numeramos las repeticiones. La primera será 1, la repetida será 2, etc.
        ROW_NUMBER() OVER (PARTITION BY Email ORDER BY FechaRegistro) as Nro
    FROM Usuarios
)
DELETE FROM CTE_Duplicados
WHERE Nro > 1;