Visión general de las funciones de clasificación en SQL

Las funciones de clasificación de SQL facilitan el trabajo con las bases de datos relacionales, especialmente para los analistas de datos, los profesionales del marketing y los especialistas en finanzas. Estas funciones se utilizan para asignar un número de clasificación a cada registro y permiten crear informes útiles de forma eficaz.

Las funciones de clasificación de SQL son funciones de ventana. Las funciones de ventana calculan el resultado basándose en un conjunto de filas. La palabra "ventana" se refiere a este conjunto de filas. Veamos la sintaxis de las funciones de clasificación:

rank_function OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY order_expression [ASC | DESC] ...
)

La sintaxis comienza con el nombre de la función de clasificación, como RANK(), DENSE_RANK(), ROW_NUMBER(), o PERCENT_RANK(), y la cláusula OVER(). En la cláusula OVER() se especifican las cláusulas PARTITION BY y ORDER BY. Para las funciones de clasificación, la cláusula ORDER BY, que incluye el nombre de la(s) columna(s) o una expresión, es obligatoria.

Antes de la cláusula ORDER BY está la cláusula opcional PARTITION BY, que incluye el nombre(s) de la(s) columna(s) o una expresión. La cláusula PARTITION BY divide el conjunto de filas en grupos de filas para clasificarlas por separado.

No se preocupe si esta sintaxis parece complicada. Lo explicaré paso a paso en las siguientes secciones.

Los ejemplos de las siguientes secciones utilizarán la tabla saleque almacena datos en las columnas salesman_id, sale_date, y sale_amount. Vea la venta de la tabla a continuación:

ROW_NUMBER()

La primera función de clasificación que trataré es ROW_NUMBER(). Devuelve el número secuencial de cada registro en el conjunto de resultados o dentro de la partición del conjunto de resultados, empezando por el 1. Utilizando ROW_NUMBER(), puede seleccionar todos los registros y numerarlos. Véase el ejemplo 1 a continuación.

Ejemplo 1

SELECT 
ROW_NUMBER() OVER(ORDER BY sale_amount) 
  AS row_number, 
sale_date, 
salesman_id,
sale_amount 
FROM sale;

Esta consulta devuelve el resultado:

En este caso, la cláusula OVER contiene sólo la cláusula ORDER BY con la columna sale_amount (esta cláusula ordena las filas según el importe de la venta de forma ascendente desde 9.500 dólares hasta 31.000 dólares). La consulta devuelve el número secuencial a partir del 1 en la columna row_number.

Observe que las filas que tienen el mismo valor en la columna sale_amount reciben números diferentes. Por lo tanto, si desea clasificar todos los registros con un número único, utilice ROW_NUMBER().

¿Y si desea numerar grupos de filas por separado? Puede utilizar la cláusula opcional PARTITION BY antes de la cláusula ORDER BY. Consulte el ejemplo 2 a continuación.

Ejemplo 2

SELECT
ROW_NUMBER() OVER(PARTITION BY sale_date ORDER BY sale_amount) 
  AS row_number, 
sale_date, salesman_id, sale_amount 
FROM sale;

Los registros se dividen en grupos (llamados "particiones") por la fecha de venta. Dentro de cada partición, los registros se numeran por separado.

Esta consulta devuelve el resultado:

Para la fecha de venta 2020-04-20, las filas tienen los números 1 y 2, aunque los valores del importe de la venta sean los mismos. En la siguiente partición, la fila con el valor de importe de venta más bajo tiene el número 1, y la fila con el valor de importe de venta más alto tiene el número 2. Los registros de cada partición se ordenan según la columna de ORDER BY.

RANK()

La segunda función de clasificación es RANK(). Esta función añade un número de rango, que es un número secuencial, a cada fila del conjunto de resultados o dentro de la partición del conjunto de resultados.

La diferencia entre RANK() y ROW_NUMBER() es que RANK() omite los valores duplicados. Cuando hay valores duplicados, se asigna la misma clasificación y aparece un hueco en la secuencia para cada clasificación duplicada.

Véase el ejemplo 1 a continuación.

Ejemplo 1

SELECT
RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Esta consulta devuelve el resultado:

En este caso, RANK() asigna un número de clasificación para cada registro como ROW_NUMBER(), pero para el mismo valor en sale_amount, el número de clasificación es el mismo. Los vendedores 11 y 13 del día 2020-04-22 consiguieron el mismo importe de venta de 11.000 dólares. Por lo tanto, tienen el mismo número de rango, 2. En este caso, ROW_NUMBER() asignó un número de rango diferente.

Observe que el siguiente registro no tiene el número 3. RANK() omite el número de rango de las filas adicionales con el mismo valor. Así, después de dos filas con el número de rango 2, el siguiente número de rango es el 4, no el 3.

Por supuesto, RANK() también asigna números dentro de las particiones. Véase el ejemplo 2 a continuación.

Ejemplo 2

SELECT
RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Esta consulta devuelve el resultado:

La consulta anterior dividió el conjunto de resultados en conjuntos de filas con la misma fecha de venta. Por ejemplo, una partición contiene las ventas del 2020-04-22. Las filas se numeran por separado para cada fecha de venta.

En el 2020-04-22, los vendedores 11 y 13 tienen el mismo importe de venta de $11000. Por lo tanto, estos registros tienen el rango número 1, y el siguiente registro tiene el rango número 3 porque la fila adicional se salta.

DENSE_RANK()

La tercera función de clasificación es DENSE_RANK(). Si desea asignar el mismo número a las filas con el mismo valor en una columna determinada pero sin saltarse los números siguientes, utilice DENSE_RANK().

DENSE_RANK() es similar a RANK(), pero con DENSE_RANK(), el número de clasificación no se salta para los mismos valores. Véase el ejemplo 1 a continuación.

Ejemplo 1

SELECT
DENSE_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Esta consulta devuelve el resultado:

Para el mismo importe de venta, las filas tienen el mismo número. Sin embargo, las filas siguientes no se saltan, y tienen el siguiente número secuencial.

Observe que para el mismo monto de venta de $11.000 por los vendedores 11 y 13 el 2020-04-22, el número de rango asignado es 2, pero para los dos registros siguientes con el monto de venta de $12.500, el número de rango es 3. Esta función no salta el número siguiente.

RANK() funciona de forma diferente. En este caso, para el monto de venta de $12,500, RANK() asignaría el número de rango 4, saltando el 3 porque dos filas tenían 2.

DENSE_RANK() también funciona con particiones. Véase el ejemplo 2 a continuación.

Ejemplo 2

SELECT
DENSE_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Esta consulta devuelve el resultado:

En este caso, el día 2020-04-22, los vendedores con el importe de la venta si $11.000 tienen el número de rango 1, pero el siguiente registro tiene el número de rango 2, no 3 como con RANK().

PERCENT_RANK()

La última función de clasificación que discutiré es PERCENT_RANK(). Esta función devuelve los rangos porcentuales. Véase el ejemplo 1 a continuación.

Ejemplo 1

SELECT
PERCENT_RANK() OVER(ORDER BY sale_amount)
  AS row_number,
sale_date,
salesman_id,
sale_amount
FROM sale;

Esta consulta devuelve el resultado:

Esta consulta calcula el rango relativo de cada fila del conjunto de resultados. Al valor más alto del importe de la venta se le asigna 1 como rango porcentual, y al valor más bajo se le asigna 0. Los valores entre ellos se devuelven como rango de un rango de valores, que son mayores que 0 y menores que 1.

A mitad de camino entre los valores más altos y más bajos, el número de rango porcentual es 0,5. En este caso, a los vendedores 11 y 12 del 2020-04-20 se les asigna el número de rango porcentual 0,5. Los registros de los vendedores 11 y 13 en 2020-04-22 están entre 0 y 0,5, por lo que tienen el número de rango porcentual 0,1666666.

PERCENT_RANK() funciona de forma similar para las particiones de registros. Véase el ejemplo 2 a continuación.

Ejemplo 2

SELECT
PERCENT_RANK() OVER(PARTITION BY sale_date ORDER BY sale_amount)
  AS row_number,
sale_date, salesman_id, sale_amount
FROM sale;

Esta consulta devuelve el resultado:

En cada partición, la fila más alta devuelta tiene el rango porcentual 1, y la más baja tiene 0. En estas particiones, no hay registros entre el más alto y el más bajo. Por lo tanto, no hay un número de rango porcentual entre 0 y 1 como en el ejemplo anterior.

Resumen

En este artículo, he hablado de las funciones de clasificación de SQL, explicando su sintaxis y utilizándolas en ejemplos del mundo real. Si quieres aprender más sobre las funciones de clasificación, consulta el curso "Funciones de ventana" en LearnSQL.es o lea los artículos "Ejemplo de función de ventana SQL con explicaciones", "Cómo utilizar las funciones de clasificación en SQL" y "SQL común Funciones de ventana: Uso de particiones con funciones de clasificación".