Saltar al contenido

Cómo usar Agrupar por y Particionar por en SQL

septiembre 23, 2021
0UIOGAyQ9szrwOTVd

Cómo usar Agrupar por y Particionar por en SQL

Chi Nguyen

1 de marzo·5 min de lectura

0*UIOGAyQ9szrwOTVd
Foto de Markus Spiske en Unsplash

Introducción

Cuando aprendí SQL por primera vez, tuve el problema de diferenciar entre PARTICIÓN POR y AGRUPAR POR, ya que ambos tienen una función de agrupación. Creo que muchas personas que comienzan a trabajar con SQL pueden encontrar el mismo problema. Por lo tanto, en este artículo quiero compartir con ustedes algunos ejemplos de uso PARTICIÓN POR, y la diferencia entre este y AGRUPAR POR en una declaración selecta.

Exploración

Data de muestra

En primer lugar, creo datos simples.mit con 4 columnas. los df La siguiente tabla describe la cantidad de dinero y el tipo de fruta que traerá cada empleado en diferentes funciones en su viaje de empresa.

1*3M6Kvz8gunUfsPOcnMHeDw

Figura 1: tabla df
1*9huYRD8h34 VDvwIc5 RIQ

Figura 2: información df

PARTICIÓN POR vs GRUPO POR

La siguiente es la sintaxis de Partición por:

SELECT expression 1, expression 2, ...
aggregate function ()
OVER (PARTITION BY expression 1 order_clause frame_clause)
FROM table

Cuando queremos hacer una agregación en una columna específica, podemos aplicar PARTICIÓN POR cláusula con el SOBRE cláusula. Veamos el siguiente ejemplo para ver cómo se ha transformado el conjunto de datos.

Figura 3: Salida de partición por cláusula

En el ejemplo, quiero calcular la cantidad total y promedio de dinero que cada función aporta al viaje. Lo que puede ver en la captura de pantalla es el resultado de mi PARTICIÓN POR consulta.

Ahora, si uso AGRUPAR POR en lugar de PARTICIÓN POR en el caso anterior, ¿cómo sería el resultado?

Primero, la sintaxis de AGRUPAR POR Se puede escribir como:

SELECT expression 1, expression 2
aggregate function ()
FROM tables
WHERE conditions
GROUP BY expression 1, expression 2

Cuando aplico esto a la consulta para encontrar la cantidad total y promedio de dinero en cada función, la salida agregada es similar a una PARTICIÓN POR cláusula. Sin embargo, como puede observar, hay una diferencia en la figura 3 y Figura 4 resultado.

1*1TKjhjtaySjwztpYnrRTmw

Figura 4: Salida de la cláusula Group By
  • AGRUPAR POR da un resultado por función en la empresa (Figura 4). Mientras tanto, como tenemos 7 registros en el tabla df, PARTICIÓN POR recupera las 7 filas con total_amount y average_amount en cada fila (Figura 3). Por tanto, en conclusión, el PARTICIÓN POR recupera todos los registros de la tabla, mientras que el AGRUPAR POR solo devuelve un número limitado.
  • Una cosa mas es que AGRUPAR POR no permite agregar columnas que no son parte de AGRUPAR POR cláusula en la declaración de selección. Sin embargo, con PARTICIÓN POR cláusula, podemos agregar columnas requeridas.

PARTICIÓN POR con NÚMERO DE FILA

Podemos combinar PARTICIÓN POR y NUMERO DE FILA para ordenar el número de fila por un valor específico. Por ejemplo, si quiero ver qué persona en cada función aporta la mayor cantidad de dinero, puedo averiguarlo fácilmente aplicando el NUMERO DE FILA función para cada equipo y obtener la cantidad de dinero de cada persona ordenada por valores descendentes.

1*ocBXvrm2EoOkE4 8e5M71Q

Figura 5: Ejemplo de función de número de fila

PARTICIÓN POR con valor acumulativo

PARTICIÓN POR + FILAS SIN LÍMITES ANTERIORES

Para facilitar la imaginación, comenzaré con un ejemplo para explicar la idea de esta sección.

Creé una nueva tabla llamado df8.

Figura 6: Nuevo marco de datos (df8)

Aplicando NUMERO DE FILA, Obtuve el valor del número de fila ordenado por cantidad de dinero para cada empleado en cada función. Básicamente hasta este paso, como puedes ver en figura 7, todo es similar al ejemplo anterior.

1*H2FL9VhBAlWX3CV8536KhA

Figura 7: Salida

Sin embargo, como quiero calcular una columna más, que es la cantidad de dinero promedio de la fila actual y la cantidad de valor más alto antes de la fila actual en la partición. Por ejemplo en el figura 8, Podemos ver eso:

  • En el equipo de tecnología, solo Sam tiene una cantidad acumulada promedio de 400000.
  • Sin embargo, en la fila número 2 del equipo de tecnología, el monto acumulado promedio es 340050, que es igual al promedio de (monto de Hoang + monto de Sam).
  • En la fila número 3, la cantidad de dinero de Dung es menor que la de Hoang y Sam, por lo que su cantidad acumulada promedio es el promedio de (la cantidad de Hoang, Sam y Dung)

=> Esta es una idea general de cómo FILAS SIN LÍMITES PRECEDENTES y PARTICIÓN POR cláusula se utilizan juntos.

1*5n59kpggTZtojf8iA17dnQ

Figura 8: Cantidad promedio acumulada usando FILAS SIN LÍMITES PRECEDENTES

PARTICIÓN POR + FILAS ENTRE LA FILA ACTUAL Y 1

El uso de esta combinación es calcular los valores agregados (promedio, suma, etc.) de la fila actual y la fila siguiente en la partición. Sigamos trabajando con df9 datos para ver cómo se hace esto.

1*GAEwip8mifBUPMKziHuVsQ

Figura 9: Cantidad promedio acumulada usando FILAS ENTRE FILA ACTUAL Y 1

De figura 9, podemos averiguar que:

  • En la función de tecnología fila número 1, la cantidad acumulada promedio de Sam es 340050, que es igual a la cantidad promedio de ella y su siguiente persona (Hoang) en la fila número 2.
  • Entonces, la cantidad acumulada promedio de Hoang es el promedio de la cantidad de Hoang y la cantidad de Dung en la fila número 3.

Conclusión

En este artículo, proporcioné mi comprensión de PARTICIÓN POR y AGRUPAR POR junto con algunos casos diferentes de uso PARTICIÓN POR. Espero que la información anterior le sea útil.

Referencia

Descripción general de la cláusula SQL PARTITION BY

9 de abril de 2019 por Este artículo cubrirá la cláusula SQL PARTITION BY y, en particular, la diferencia con GROUP BY …

www.sqlshack.com

close