¿Cuál es la diferencia entre un GROUP BY y un PARTITION BY?

Las funciones de ventana son una gran adición a SQL, y pueden hacer tu vida mucho más fácil si sabes cómo utilizarlas correctamente. Hoy abordaremos las diferencias entre un GROUP BY y un PARTITION BY. Empezaremos con lo más básico y poco a poco llegaremos a un punto en el que puedas seguir investigando por tu cuenta.

PARTITION BY vs. GROUP BY

Las cláusulas PARTITION BY y GROUP BY se utilizan frecuentemente en SQL cuando se necesita crear un informe complejo. Aunque devolver los datos en sí es útil (e incluso necesario) en muchos casos, a menudo se requieren cálculos más complejos. Aquí es donde entran en juego GROUP BY y PARTITION BY. Aunque son muy similares en el sentido de que ambos realizan agrupaciones, existen diferencias clave. Analizaremos estas diferencias en este artículo.

GROUP BY

La cláusula GROUP BY se utiliza en las consultas SQL para definir grupos basados en unos criterios determinados. Estos criterios son los que normalmente encontramos como categorías en los informes. Ejemplos de criterios de agrupación son

• agrupar todos los empleados por su nivel salarial anual
• agrupar todos los trenes por su primera estación
• agrupar los ingresos y gastos por mes
• agrupar a los estudiantes según la clase en la que están matriculados

El uso de la cláusula GROUP BY transforma los datos en un nuevo conjunto de resultados en el que los registros originales se colocan en diferentes grupos utilizando los criterios que proporcionamos. Puedes consultar más detalles sobre la cláusula GROUP BY en este artículo.

Podemos realizar algunas acciones o cálculos adicionales sobre estos grupos, la mayoría de los cuales están estrechamente relacionados con las funciones de agregación. Como repaso rápido, las funciones de agregado se utilizan para agregar nuestros datos, y por lo tanto en el proceso, perdemos los detalles originales en el resultado de la consulta. Hay muchas funciones de agregación, pero las más utilizadas son COUNT, SUM, AVG, MIN, y MAX.

Si quieres practicar el uso de la cláusula GROUP BY, te recomendamos nuestro curso interactivo Creación de informes en SQL. Las funciones agregadas y la cláusula GROUP BY son esenciales para escribir informes en SQL.

Consideremos el siguiente ejemplo. Aquí tenemos la train con la información de los trenes, la tabla journey tabla con la información sobre los trayectos realizados por los trenes, y la route tabla con la información sobre las rutas de los viajes. Vea a continuación los datos y cómo se relacionan las tablas:

Ejecutamos la siguiente consulta que devuelve la información sobre los trenes y los trayectos relacionados con ellos, utilizando las tablas train y la tabla journey tablas.

SELECT
        train.id,
        train.model,
        journey.*
FROM train
INNER JOIN journey ON journey.train_id = train.id
ORDER BY
        train.id ASC;

Aquí está el resultado:

Puedes ver que el tren con id = 1 tiene 5 filas diferentes, el tren con id = 2 tiene 4 filas diferentes, etc.

Ahora, vamos a ejecutar una consulta con las mismas dos tablas utilizando un GROUP BY.

SELECT
  	train.id,
	train.model,
	COUNT(*) AS routes
FROM train
INNER JOIN journey ON journey.train_id = train.id
GROUP BY
  	train.id,
	train.model
ORDER BY
  	train.id ASC;

Y el resultado es el siguiente:

A partir del resultado de la consulta, se puede ver que hemos agregado la información, diciéndonos el número de trayectos de cada tren. En el proceso, hemos perdido los detalles a nivel de fila de la tabla journey tabla.

Puedes comparar este conjunto de resultados con el anterior y comprobar que el número de filas devueltas por la primera consulta (número de rutas) coincide con la suma de los números de la columna agregada (routes) del resultado de la segunda consulta.

Aunque se pueden utilizar funciones de agregación en una consulta sin la cláusula GROUP BY, ésta es necesaria en la mayoría de los casos. Las funciones de agregación funcionan así:

1. Se generan grupos mediante una sentencia GROUP BY especificando una o más columnas que tienen el mismo valor dentro de cada grupo.
2. La función de agregación calcula el resultado.
3. Las filas originales son "colapsadas". Puede acceder a las columnas de la sentencia GROUP BY y a los valores producidos por las funciones de agregación, pero los detalles originales a nivel de fila ya no están ahí.

El "colapso" de las filas está bien en la mayoría de los casos. Sin embargo, a veces es necesario combinar los detalles originales a nivel de fila con los valores devueltos por las funciones de agregación. Esto puede hacerse con subconsultas vinculando las filas de la tabla original con el conjunto resultante de la consulta mediante funciones de agregación. O bien, puede probar un enfoque diferente, que veremos a continuación.

PARTICIÓN POR

Dependiendo de lo que necesite hacer, puede utilizar un PARTITION BY en nuestras consultas para calcular valores agregados en los grupos definidos. El PARTITION BY se combina con las funciones OVER() y windows para calcular valores agregados. Esto es muy similar a GROUP BY y a las funciones de agregación, pero con una diferencia importante: cuando se utiliza un PARTITION BY, los detalles a nivel de fila se conservan y no se colapsan. Es decir, sigue teniendo a su disposición los detalles originales a nivel de fila, así como los valores agregados. Todas las funciones de agregación pueden utilizarse como funciones de ventana.

Veamos la siguiente consulta. Además de train y journeyahora incorporamos también la tabla de rutas.

SELECT
  	train.id,
	train.model,
	route.name,
	route.from_city,
	route.to_city,
	COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes,
	COUNT(*) OVER () AS routes_total
FROM train
INNER JOIN journey ON journey.train_id = train.id
INNER JOIN route ON journey.route_id = route.id;

Este es el resultado de la consulta:

Del conjunto de resultados, observamos varios puntos importantes:

• No utilizamos un GROUP BY pero aun así obtuvimos valores agregados (routes y routes_total).
• Tenemos las mismas columnas (id y model) de GROUP BY en la consulta anterior, pero se conservaron los detalles originales a nivel de fila. Los valores agregados se repiten en todas las filas con los mismos valores de id y modelo. Esto es lo esperado; como ejemplo, tenemos 5 registros de viajes para id = 1, todos los cuales tienen valores idénticos para estas columnas.
• También tenemos valores en las columnas nombre, from_city, y to_city que son diferentes dentro de un valor dado de id. Si hubiéramos utilizado un GROUP BY en las columnas id y model, estos detalles a nivel de fila se perderían.
• COUNT(*) OVER () AS routes_total produjo el mismo recuento agregado, 30, que COUNT y GROUP BY. Sin embargo, en este conjunto de resultados, este valor se incluye en cada fila.
• La parte COUNT(*) OVER (PARTITION BY train.id ORDER BY train.id) AS routes es muy interesante. Hemos definido el grupo sobre el que se debe utilizar esta función de ventana con la cláusula PARTITION BY. Por lo tanto, en la columna routes, tenemos un recuento de filas sólo para ese grupo. Las funciones ventana se aplican después de filtrar las filas, con lo que se mantienen los detalles a nivel de fila mientras se siguen definiendo los grupos a través de PARTITION BY.

El uso de funciones agregadas estándar como funciones de ventana con la palabra clave OVER() nos permite combinar los valores agregados y mantener los valores de las filas originales. Podemos lograr lo mismo utilizando funciones de agregación, pero eso requiere subconsultas para cada grupo o partición.

Es importante tener en cuenta que todas las funciones agregadas estándar se pueden utilizar como funciones de ventana de esta manera.

Funciones de ventana

Además de las funciones de agregación, hay otras funciones de ventana importantes, como:

• ROW_NUMBER(). Devuelve el número de secuencia de la fila en el conjunto de resultados.
• RANK(). Similar a ROW_NUMBER(), pero puede tomar una columna como argumento. El orden de clasificación se determina sobre el valor de esta columna. Si dos o más filas tienen el mismo valor en esta columna, todas estas filas obtienen el mismo rango. El siguiente rango continuará desde el número equivalente de filas hacia arriba; por ejemplo, si dos filas comparten un rango de 10, el siguiente rango será 12.
• DENSE_RANK(). Muy similar a RANK(), excepto que no tiene "huecos". En el ejemplo anterior, si dos filas comparten un rango de 10, el siguiente rango será 11..
• NTILE. Se utiliza para calcular cuartiles, deciles o cualquier otro percentil.
• LAG & LEAD. Se utiliza para extraer valores de la fila anterior (LAG) o de la siguiente (LEAD).

No hay una regla general sobre cuándo se deben utilizar las funciones de ventana, pero se puede desarrollar una sensación para ellos. Definitivamente, le recomiendo que visite el cursoFunciones de ventana ; allí encontrará todos los detalles que querrá saber.

PARTITION BY y GROUP BY: Similitudes y diferencias

Aunque usamos un GROUP BY la mayor parte del tiempo, hay numerosos casos en los que un PARTITION BY sería una mejor opción. En algunos casos, se podría usar un GROUP BY usando subconsultas para simular un PARTITION BY, pero estas pueden terminar con consultas muy complejas.

Terminemos con las similitudes y diferencias más importantes:

• Similitudes: Ambos se utilizan para devolver valores agregados.
• Diferencia: El uso de una cláusula GROUP BY colapsa las filas originales; por esa razón, no se puede acceder a los valores originales más adelante en la consulta. Por otro lado, el uso de una cláusula PARTITION BY mantiene los valores originales a la vez que nos permite producir valores agregados.
• Diferencia: La cláusula PARTITION BY se combina con las funciones OVER() y windows para añadir muchas más funcionalidades.