Cómo encontrar el enésimo salario más alto por departamento con SQL

Aprende a encontrar el enésimo salario más alto en SQL, y aprenderás a obtener el enésimo valor en cualquier dato.

En este artículo, me centraré en un problema y te daré varias soluciones a ese problema. El problema ya está indicado en el título: encontrar el enésimo salario más alto por departamento usando SQL.

Esto puede sonar demasiado específico. Pero si aprendes a resolverlo, podrás encontrar el enésimo valor en cualquier dato, no sólo en los salarios. Así te harás una idea de cómo resolver otros problemas similares que te encuentres.

Como ya he dicho, veremos un solo problema pero exploraremos varias soluciones para él. De hecho, te mostraré cuatro soluciones utilizando estas funciones de ventana:

• NTH_VALUE()
• ROW_NUMBER()
• RANK()
• DENSE_RANK()

Si necesitas ayuda con la sintaxis o cualquier detalle sobre estas funciones, no dudes en consultar la hoja de trucos de SQL Funciones de ventana . También puedes echar un vistazo a este ejemplo para ver cómo funcionan generalmente las funciones de ventana.

Los datos que utilizaré

Utilizaremos los datos almacenados en las siguientes dos tablas:

• employee
• department

Así es como se ven los datos de la tabla employee se ven:

La tabla department tiene los siguientes datos:

¿Cuál es la tarea aquí?

Vamos a encontrar el tercer salario más alto por departamento. Esto significa encontrar el tercer valor más alto, no en general sino dentro de cada subconjunto, donde un subconjunto tiene los salarios de un departamento determinado. La herramienta más útil para hacer esto son las funciones de ventana.

Así que, aquí está la primera solución usando una función ventana.

Uso de NTH_VALUE()

El propósito de la función NTH_VALUE() es obtener el valor de la enésima fila del conjunto de datos. Así es como podemos utilizarla para obtener el tercer salario más alto por departamento:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		NTH_VALUE (salary, 3) OVER (
PARTITION BY department_name ORDER BY salary DESC
			RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS third_highest_salary
FROM department d JOIN employee e ON d.id = e.department_id;

Este código primero selecciona algunas columnas de las tablas employee y department. Para utilizar NTH_VALUE(), tenemos que especificar la columna y el valor de N. Como queremos obtener el tercer salario más alto salary, la columna es salario, y N = 3; por lo tanto, tenemos NTH_VALUE(salary, 3). Esto nos dará el tercer salario más alto.

Para que una función de ventana funcione, necesitamos utilizar una cláusula OVER(). Recuerde que estamos buscando el tercer salario más alto en cada departamento. Para ello, utilizamos PARTITION BY para dividir los datos en subconjuntos por la columna nombre_departamento de la tabla department. Como estamos buscando el tercermás alto salario, utilizamos ORDER BY para que las funciones de ventana realicen los cálculos desde el salario más alto hasta el más bajo. Esta es la razón por la que ORDER BY salario DESC en el código. Además, necesitamos la cláusula RANGE, en este caso, RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Esto significa que la función de ventana buscará entre la primera fila y la última fila de la partición.

Por supuesto, para obtener todo esto, tenemos que unir las dos tablas.

Este es el resultado:

El resultado nos dice que el tercer sueldo más alto en Contabilidad es de 5.371,02, que es el sueldo de Lois Skain. El salario de Abra Clemon (5.564,25) es el tercero más alto en Recursos Humanos. En Investigación y Desarrollo, el salario de Zachariah Rapi es el tercero más alto (6.657,11).

Utilizar ROW_NUMBER()

La segunda opción para obtener el tercer salario más alto por departamento es utilizar ROW_NUMBER(). Esta función de ventana devuelve los números de secuencia de las filas de un conjunto de datos. Si ordenamos los salarios dentro de cada departamento, será fácil elegir el tercero más alto. Esta es la solución:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		ROW_NUMBER() OVER (
PARTITION BY d.id ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
ORDER BY department_name;

Esta solución selecciona todas las mismas columnas que en la solución anterior. La diferencia es que aquí utilizamos ROW_NUMBER() en lugar de NTH_VALUE(). Todos los criterios de la cláusula OVER() son los mismos que en la solución anterior.

El resultado tiene este aspecto:

Se ve un poco diferente del resultado de la solución anterior. La última columna no contiene el valor del tercer salario más alto. En su lugar, contiene una clasificación, y podemos ver fácilmente que el efecto es el mismo. Los valores del tercer salario más alto por departamento son 5.371,02 (Contabilidad), 5.564,25 (Recursos Humanos) y 6.657,11 (Investigación y Desarrollo), lo mismo que obtuvimos en la primera solución.

También podemos utilizar ROW_NUMBER() en un CTE. Si no estás familiarizado con los CTEs, este artículo explica lo que es. Por ejemplo, podemos escribir el siguiente código:

WITH salaries_ranks AS (
SELECT e.first_name,
 e.last_name,
 d.department_name,
 salary,
 ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY salary DESC
 ) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id
)

SELECT *
FROM salaries_ranks
WHERE salary_rank = 3;

Comenzamos nuestro CTE utilizando un WITH, seguido del nombre del CTE, salaries_ranks. Luego, escribimos una sentencia SELECT, que es exactamente la misma que en el código anterior.

Ahora que hemos escrito una CTE, podemos utilizarla como cualquier otra tabla. Lo hacemos en una sentencia SELECT separada que selecciona todas las columnas del CTE salaries_ranksmostrando sólo las filas en las que salary_rank = 3.

El resultado de este código es:

Esta es una solución bastante elegante. Obtenemos sólo los datos necesarios: sólo tres filas que muestran el tercer salario más alto de cada departamento.

Usando RANK()

La tercera opción es utilizar la función RANK(). Es similar a ROW_NUMBER() en el sentido de que también clasifica las filas dentro de una partición. Es similar, pero no es lo mismo. Hablaré de las diferencias más adelante, o puedes leerlas aquí.

Escribamos el código usando RANK():

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC
) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

De nuevo, este código no es muy diferente de los anteriores, excepto por la función de ventana específica que se utiliza. Aquí, es RANK(), con todos los mismos criterios en la cláusula OVER(). El resultado será exactamente el mismo que cuando utilizamos ROW_NUMBER(), por lo que no creo que sea necesario volver a mostrar la misma tabla de resultados.

Utilizando DENSE_RANK()

La última solución que mostraré aquí es la función de ventana DENSE_RANK() . Al igual que ROW_NUMBER() y RANK(), clasifica los valores dentro de un conjunto de datos.

El código tampoco es realmente diferente. Es el mismo que antes, sólo que utiliza una función de ventana diferente:

SELECT	e.first_name,
		e.last_name,
		d.department_name,
		salary,
		DENSE_RANK() OVER (
PARTITION BY d.department_name ORDER BY salary DESC 
		) AS salary_rank
FROM department d JOIN employee e ON d.id = e.department_id;

Cuando ejecutamos este código, obtenemos exactamente el mismo resultado que con las dos soluciones anteriores.

Resumen de los conceptos y sus diferencias

Como ha visto, puede utilizar cualquiera de estas cuatro funciones de ventana para obtener el tercer salario más alto por departamento.

La función NTH_VALUE() le muestra explícitamente el valor del tercer salario más alto por departamento.

Las funciones ROW_NUMBER(), RANK(), y DENSE_RANK() clasifican los salarios dentro de cada departamento. A continuación, puede encontrar simplemente el valor del salario asociado al rango número 3. Estas tres funciones son similares pero no iguales. Parecen idénticas en este ejemplo, pero otros datos pueden exponer sus diferencias.

A continuación se explica en qué se diferencian para ahorrarle algunas sorpresas desagradables.

Las diferencias salen a la luz cuando hay vínculos (varias filas con el mismo valor) en los datos. Yo sabía que no tenía ningún empate en mis datos, así que sabía que las tres funciones me darían la misma solución.

Pero, ¿qué pasa si hay empates? En ese caso, ROW_NUMBER() asignaría los rangos secuencialmente; es decir, los empates tendrían diferentes números de rango que se asignan arbitrariamente. En cambio, RANK() asigna el mismo número de rango a los mismos valores y se salta un número de rango para compensarlo cuando llega a una fila con un valor diferente. Por último, DENSE_RANK() asigna el mismo número de rango a los empates, sin saltarse un número de rango en el siguiente valor. He aquí un ejemplo para ilustrarlo:

Si busca el valor del tercer salario más alto en estos datos, ROW_NUMBER() le dará la solución correcta; el salario de 4.000 es el tercero más alto. Sin embargo, le dará una solución incorrecta si busca a todos los empleados con el tercer salario más alto. Sólo mostraría el empleado 2 cuando debería mostrarse también el empleado 5. En este caso, RANK() o DENSE_RANK() serían una mejor opción.

¿Y si busca el valor del cuarto salario más alto? La función ROW_NUMBER() le daría una solución totalmente incorrecta, ya que el tercer y el cuarto valor más alto son el mismo. El uso de RANK() no devuelve ningún resultado, porque se salta el rango número cuatro - como se ha explicado anteriormente, asigna el mismo número de rango a los empates y se salta el siguiente número de rango para compensarlo. Sólo DENSE_RANK() da una solución correcta en este caso.

Antes de decidir qué función utilizar, asegúrese de entender los datos y lo que está tratando de obtener como solución. Lo más seguro es utilizar NTH_VALUE(). Si quiere usar una función de clasificación, generalmente es mejor usar DENSE_RANK() cuando no sabe si hay algún empate en los datos. Si utiliza ROW_NUMBER() o RANK() en su lugar, asegúrese de saber cómo sería el resultado. Puede que una de estas dos funciones, o ambas, le den lo que necesita. Elige la función según tus necesidades.

También puede utilizar estas cuatro funciones cuando quiera encontrar el salario máximo o mínimo por departamento. O, por ejemplo, el producto más o menos caro por categoría de producto. O cualquier producto con el enésimo precio más alto. También puede buscar las enésimas ventas, los ingresos, las horas trabajadas, los costes, el número de "me gusta", los inicios de sesión, los compromisos, los flujos, los comentarios, etc.; lo que quiera.

Si utiliza MySQL, esta completa introducción le mostrará estas cuatro y todas las demás funciones de ventana en MySQL.

Diferentes formas de obtener el valor Nth de SQL

La función NTH_VALUE() es perfecta para encontrar el enésimo salario o el enésimo valor de cualquier otra columna. Después de todo, fue diseñada exactamente para ese propósito. Le he mostrado formas de obtener el mismo efecto con tres funciones de ventana adicionales: ROW_NUMBER(), RANK(), y DENSE_RANK(). Utiliza la que mejor se adapte a tus necesidades y datos.

Pero éstas son sólo cuatro funciones de ventana. Es sólo la punta del iceberg, ¡y hay mucho más! Estas y otras funciones de ventana se explican en este curso sobre funciones de ventana.