Cómo utilizar la cláusula PARTITION BY en SQL

Hoy nos ocuparemos de las funciones ventana. Específicamente, nos enfocaremos en la función CLÁUSULA PARTITION BY y explicaremos lo que hace.

PARTITION BY es una de las cláusulas utilizadas en las funciones ventana. En SQL, las funciones ventana se utilizan para organizar datos en grupos y calcular estadísticas para ellos. Suena muy familiar, ¿verdad? Aunque suenen parecidas, las funciones ventana y GROUP BY no son lo mismo; las funciones ventana son más como GROUP BY con esteroides. ¿Por qué? Porque las funciones de ventana conservan los detalles de las filas individuales mientras calculan las estadísticas de los grupos de filas. GROUP BY no puede hacer eso.

PARTITION BY es crucial para esa distinción; es la cláusula que divide el resultado de una función ventana en subconjuntos de datos o particiones. En cierto modo, es GROUP BY para las funciones de ventana. Pronto aprenderá cómo funciona.

Existe una versión mucho más completa (e interactiva) de este artículo: nuestro Funciones de ventana curso. Abarca todo lo que vamos a hablar y mucho más. A través de sus ejercicios interactivos, aprenderás todo lo que necesitas saber sobre las funciones de ventana. Repasará las cláusulas OVER(), PARTITION BY, y ORDER BY y aprenderá a utilizar las funciones de ventana de clasificación y análisis. El curso también te ofrece 47 ejercicios para practicar y un cuestionario final. Si estás indeciso, aquí tienes por qué deberías aprender las funciones de ventana.

¡Ahora, hablemos de PARTITION BY!

Sintaxis PARTITION BY

La sintaxis para la cláusula PARTITION BY es:

SELECT column_name,
  	 window_function (expression) OVER (PARTITION BY column name)
FROM table;

En la parte window_function, pones la función de ventana específica.

La cláusula OVER() es una cláusula obligatoria que hace que la función de ventana funcione. Define virtualmente la función ventana.

La subcláusula PARTITION BY va seguida del nombre de la(s) columna(s). La(s) columna(s) que especifique en esta cláusula serán las particiones/grupos en los que se agruparán los resultados de la función ventana.

Los siguientes ejemplos lo harán más claro. Sabemos que no puede memorizarlo todo inmediatamente, así que siéntase libre de tener cerca nuestra hoja de trucos SQL Funciones de ventana mientras repasamos los ejemplos. Es un recordatorio útil de las diferentes funciones de ventana y su sintaxis.

Ejemplos de PARTITION BY

El conjunto de datos de ejemplo consta de una tabla, empleados. Sus columnas son las siguientes

• id - ID del empleado.
• first_name - Nombre del empleado.
• last_name - Apellidos del empleado.
• job_title - Cargo del empleado.
• department - Departamento del empleado.
• date_of_employment - Fecha de inicio de la relación laboral.
• salary - El salario del empleado.

Echa un vistazo a los datos de la tabla antes de empezar a escribir el código:

Si quieres seguir escribiendo tus propias consultas SQL, aquí tienes el código para crear este conjunto de datos.

Uso de OVER (PARTITION BY)

Ahora es el momento de que te mostremos cómo funciona PARTITION BY en uno o dos ejemplos.

Ejemplo nº 1

En el primer ejemplo, el objetivo es mostrar los salarios de los empleados y el salario medio de cada departamento. Si estuviste atento, ya sabes cómo PARTITION BY puede ayudarnos aquí:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 AVG(salary) OVER (PARTITION BY department) AS average_salary_by_department
FROM employees;

Para calcular el promedio, necesitas usar la función agregada AVG(). Escribe la columna salary entre paréntesis. Esta es, por ahora, una función agregada ordinaria. Para convertirla en una función agregada de ventana, escribe la cláusula OVER().

Ahora, recuerde que no necesitamos la media total (es decir, para toda la empresa) sino la media por departamento. Para tener esta métrica, ponga la columna departamento en la cláusula PARTITION BY.

¿Se obtiene el resultado deseado? Veámoslo.

Puede ver que el resultado muestra todos los empleados y sus salarios. Para el departamento de TI, el salario medio es de 7.636,59. Este valor se repite para todos los empleados de TI. Este valor se repite para todos los empleados de TI.

Cuando llegamos a los empleados de otro departamento, la media cambia. En este caso, es de 6.418,12 en Marketing. Siguiendo esta lógica, el salario medio en Gestión de riesgos es de 6.760,01.

Como puede ver, PARTITION BY ha utilizado la función de ventana para calcular la media departamental.

¿En qué se diferencia de GROUP BY? Veamos qué ocurre si calculamos el salario medio por departamento utilizando GROUP BY.

Como puede ver, se obtienen los mismos salarios medios por departamento. Sin embargo, una gran diferencia es que no se obtiene el salario individual de cada empleado. Puede ampliar esta diferencia leyendo un artículo sobre la diferencia entre PARTITION BY y GROUP BY.

Ejemplo #2

Ahora queremos mostrar todos los salarios de los empleados junto con el salario más alto por cargo.

La consulta es muy similar a la anterior. Los dos únicos cambios son la función de agregado y la columna en PARTITION BY.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 MAX(salary) OVER (PARTITION BY job_title) AS max_salary_by_job_title
FROM employees;

Esta vez, utilizamos la función agregada MAX() y dividimos la salida por cargo.

Este es el resultado:

Observa las dos primeras filas. Bob Mendelsohn y Frances Jackson son analistas de datos que trabajan en Gestión de Riesgos y Marketing, respectivamente. La tabla muestra sus salarios y el salario más alto para este puesto de trabajo. Son 5.412,47, el sueldo de Bob Mendelsohn.

La misma lógica se aplica al resto de los resultados. Por supuesto, cuando sólo hay un puesto de trabajo, el salario del empleado y el salario máximo de ese puesto serán iguales. Es el caso del ingeniero de datos y el analista de sistemas.

Este ejemplo también puede mostrar las limitaciones de GROUP BY.

El siguiente código mostrará el salario más alto por el título del puesto:

SELECT job_title,
	 MAX(salary) AS max_salary_by_job_title
FROM employees
GROUP BY job_title;

Y aquí está la salida:

Sí, los salarios son los mismos que con PARTITION BY. Pero con este resultado, no tiene ni idea de cuál es el salario de cada empleado y quién tiene el salario más alto.

Uso de OVER (ORDER BY)

La cláusula ORDER BY es otra subcláusula de la función ventana. Ordena los datos dentro de una partición o, si la partición no está definida, todo el conjunto de datos.

Cuando decimos ordenar, no nos referimos a la salida. Cuando se utiliza con funciones ventana, la cláusula ORDER BY define el orden en el que una función ventana realizará sus cálculos.

ORDER BY puede utilizarse con o sin PARTITION BY.

Veamos primero cómo funciona sin PARTITION BY. La utilizaremos para mostrar los datos de los empleados y ordenarlos según su fecha de contratación. La clasificación se hará de la fecha más antigua a la más reciente.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 date_of_employment,
	 RANK() OVER (ORDER BY date_of_employment ASC) AS employment_date_rank
FROM employees;

La función de ventana que utilizamos ahora es RANK(). Es una de las funciones utilizadas para clasificar los datos. De nuevo, la cláusula OVER() es obligatoria.

La cláusula ORDER BY indica a la función de clasificación que asigne las clasificaciones según la fecha de empleo en orden descendente.

Ejecute la consulta y obtendrá el siguiente resultado:

Todos los empleados se clasifican según su fecha de contratación. El primer empleado ocupa el primer lugar y el último el décimo.

Uso de OVER (PARTITION BY ORDER BY)

Como ya hemos mencionado, PARTITION BY y ORDER BY también pueden utilizarse simultáneamente. Veamos algunos ejemplos.

Ejemplo 1

Imagina que tienes que ordenar a los empleados de cada departamento según su salario. ¿Cómo lo haría?

He aquí la solución:

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM employees;

Volvemos a utilizar la función de ventana RANK(). En la cláusula OVER(), los datos deben particionarse por departamento. Para ordenar los empleados, utilice la columna salario en ORDER BY y ordene los registros en orden descendente.

Veamos lo que hace esta consulta:

En el departamento de informática, Carolina Oliveira tiene el salario más alto. Después vienen Ines Owen y Walter Tyson, mientras que el último es Sean Rice. Todos están clasificados en consecuencia.

Cuando la función de ventana llega al siguiente departamento, se reinicia y comienza la clasificación desde el principio. Así, Franck Monteblanc es el mejor pagado, mientras que Simone Hill y Frances Jackson ocupan el segundo y tercer puesto, respectivamente.

Lo mismo se hace con los empleados de Gestión de riesgos.

Ejemplo nº 2

Practiquemos con un ejemplo ligeramente distinto. Seguimos queriendo clasificar a los empleados por salario. Esta vez, no por el departamento, sino por el cargo.

Esto es lo que hay que hacer.

SELECT first_name,
	 last_name,
	 job_title,
	 department,
	 salary,
	 RANK() OVER (PARTITION BY job_title ORDER BY salary DESC) AS salary_rank
FROM employees;

Ahora los datos se dividen por puesto de trabajo. La cláusula ORDER BY permanece igual: sigue ordenando en orden descendente por salario.

Este es el resultado de la consulta:

La lógica es la misma que en el ejemplo anterior. En este ejemplo, hay un máximo de dos empleados con el mismo cargo, por lo que las clasificaciones no van más allá.

Bob Mendelsohn es el mejor pagado de los dos analistas de datos. Entonces, sólo hay un rango 1 para el ingeniero de datos porque sólo hay un empleado con ese cargo. El resto de los datos se ordenan con la misma lógica.

Puedes encontrar más ejemplos en este artículo sobre funciones ventana en SQL. Y si conocer las funciones ventana te abre el apetito por una carrera mejor, te alegrará que hayamos respondido a las 10 mejores preguntas de entrevista sobre funciones ventana en SQL para ti.

Cuando Usar PARTITION BY

Ya hemos respondido al "cómo". La segunda pregunta importante que necesita respuesta es cuándo debe usar PARTITION BY.

Hay dos usos principales. El primer uso es cuando quieres agrupar datos y calcular algunas métricas pero también mantener las filas individuales con sus valores.

El segundo uso de PARTITION BY es cuando desea agregar datos en dos o más grupos y calcular estadísticas para estos grupos.

PARTITION BY Debe haberte picado la curiosidad

PARTITION BY es una cláusula maravillosa con la que hay que familiarizarse. No sólo significa que conoce las funciones de ventana, sino que también aumenta su capacidad para calcular métricas al ir más allá de las cláusulas obligatorias utilizadas en las funciones de ventana.

¿Quieres satisfacer tu curiosidad sobre qué más pueden hacer las funciones de ventana y PARTITION BY? El Funciones de ventana ¡curso te está esperando!