Rajendra Gupta
Examples for Average,Min and Max values

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

November 4, 2019 by

En este artículo se va a cubrir la cláusula PARTITION BY de SQL y en particular, la diferencia con GROUP BY en una instrucción select. También vamos a explorar varios casos del uso de SQL PARTITION BY.

Utilizaremos el SQL PARTITION BY para poder dividir el conjunto de resultados en particiones y así poder realizar cálculos en cada subconjunto de datos particionados.

Preparación de datos de muestra

Empecemos creando una tabla de pedidos en una base de datos de ejemplo SQLShackDemo e inserte los registros para escribir más consultas.

Vamos a utilizar el ApexSQL Generate para poder insertar datos de muestra en este artículo. Tiene que hacer clic derecho en la tabla de pedidos y generar datos de prueba.

Generate Text data using ApexSQL Generate

Esto Lanzara el ApexSQL Generate. Ahora generé un script para insertar datos en la tabla de pedidos. Después ejecute este script para insertar 100 registros en la tabla Pedidos.

Una vez que ya estén ejecutadas las instrucciones de inserción, podemos ver los datos en la tabla Órdenes en la imagen a continuación.

Sample data

Utilizaremos la cláusula SQL GROUP BY para poder agrupar los resultados por columna especificada y a continuación utilizaremos funciones agregadas como Avg( ), Min(), Max() para poder calcular los valores requeridos.

Sintaxis de la función Agrupar por

Supongamos que queremos encontrar los siguientes valores en la tabla Órdenes:

  • Valor mínimo de pedido en una ciudad
  • Valor máximo de pedido en una ciudad
  • Valor medio de pedido en una ciudad

Ejecute la siguiente consulta con la cláusula GROUP BY para poder calcular estos valores.

En la captura de pantalla que tenemos a continuación, podemos ver los valores promedio, mínimo y máximo agrupados por CustomerCity.

Output of SQL Group By clause

En este momento, queremos agregar también la columna CustomerName y OrderAmount en la salida. Agreguemos estas columnas en la instrucción select y ejecutemos el siguiente código.

Una vez que ya ejecutemos esta consulta, recibiremos un mensaje de error. En la cláusula SQL GROUP BY, podemos utilizar una columna en la instrucción select, si a su vez se utiliza en la cláusula Group by. No permite ninguna columna en la cláusula select que no sea parte de la cláusula GROUP BY.

Error in output of SQL Group By clause.

Podemos utilizar la cláusula PARTITION BY de SQL para poder resolver este problema. Vamos a explorarlo más a fondo en la siguiente sección.

SQL PARTITION BY

Podemos utilizar la cláusula SQL PARTITION BY con la cláusula OVER para poder especificar la columna en la que necesitamos efectuar la agregación. En el ejemplo anterior, utilizamos Agrupar por, con la columna CustomerCity y calculamos los valores promedio, mínimo y máximo.

Ahora vuelva a ejecutar este escenario con la cláusula SQL PARTITION BY utilizando la siguiente consulta.

En la salida, vamos a obtener los valores agregados similares a una cláusula GROUP By . Usted puede notar una diferencia en la salida del PARTITION BY y de salida de la cláusula de GROUP BY de SQL.

Example of SQL PARTITION BY clause


Group By

SQL PARTITION BY

Obtiene un número limitado de registro usando la sentencia Group By

Se obtiene todos los registros en una table usando la sentencia PARTITION BY.

Retorna una fila por grupo en el conjunto de resultados. Por ejemplo, se obtiene un resultado por cada grupo de CustomerCity en la sentencia GROUP BY.

Provee columnas sumarizadas con cada registro de la columna especificada

Tenos 15 registros de la tabla de Órdenes. En la salida de la consulta del SQL PARTITION BY, también tenemos 15 filas con el valor Mínimo, Máximo y promedio.

En el ejemplo anterior, recibimos un mensaje de error si intentamos agregar una columna que no es parte de la cláusula GROUP BY.

Podemos ahora agregar las columnas requeridas en una instrucción select con la cláusula SQL PARTITION BY. Entonces añadamos las columnas CustomerName y OrderAmout y a continuación ejecutar la siguiente consulta.

Obtenemos las columnas CustomerName y OrderAmount junto con la salida de la función agregada. A su vez obtenemos todas las filas disponibles en la tabla Pedidos.

Example of SQL PARTITION BY

En la captura de pantalla que se muestra a continuación, usted puede realizar agregaciones (Promedio , Mínimo y Máx.) para CustomerCity Chicago. Y así puede proporcionar valores en las columnas respectivas.

Examples for Average,Min and Max values

De la misma manera, podemos utilizar otras funciones agregadas, como por ejemplo el recuento, para poder averiguar el número total de pedidos en una ciudad en particular con la cláusula SQL PARTITION BY.

Con esto podemos ver los recuentos de pedidos para una ciudad en particular. Como por ejemplo, tenemos dos órdenes de la ciudad de Austin por lo tanto; nos muestra el valor 2 en la columna CountofOrders.

Count of Orders example

Cláusula PARTITION BY con ROW_ NUMBER ()

Podemos utilizar la cláusula SQL PARTITION BY con la función ROW_ NUMBER ( ) para así poder obtener un número de fila de cada fila. Ahora definimos los siguientes parámetros para usar ROW_NUMBER con la cláusula SQL PARTITION BY.

  • PARTITION BY columna: En este ejemplo, queremos particionar datos en la columna CustomerCity
  • ORDER BY columna: En la columna ORDER BY, establecemos una columna o condición que define el número de fila. En este ejemplo, nosotros queremos ordenar los datos en la columna OrderAmount

En la captura de pantalla que tenemos a continuación, vemos CustomerCity Chicago , en el cual tenemos la fila número 1 para el pedido con la cantidad más alta 7577.90, este proporciona el número de fila con OrderAmount descendente.

ROW Number using SQL PARTITION BY

PARTITION BY cláusula con valor total acumulado

Supongamos que queremos obtener un total acumulativo para los pedidos en una partición. El total acumulado debe ser de la fila actual y la siguiente fila de la partición.

Cumulative total value example

Como por ejemplo, en la ciudad de Chicago, tenemos cuatro pedidos.

CustomerCity

CustomerName

Rank

OrderAmount

Cumulative Total Rows

Cumulative Total

Chicago

Marvin

1

7577.9

Rank 1 +2

14777.51

Chicago

Lawrence

2

7199.61

Rank 2+3

14047.21

Chicago

Alex

3

6847.66

Rank 3+4

8691.49

Chicago

Jerome

4

1843.83

Rank 4

1843.83

En la siguiente consulta, emplearemos la cláusula ROWS especificada para poder seleccionar la fila actual (utilizando CURRENT ROW) y la siguiente fila (utilizando 1 FOLLOWING). Además, calcula la suma en esas filas utilizando sum (Orderamount) con una partición en CustomerCity (utilizando OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).


Cumulative average value examples

De la misma manera, podemos calcular el promedio acumulativo utilizando la siguiente consulta con la cláusula SQL PARTITION BY.


cumulative average example

PRECEDENTES SIN LÍMITES DE FILAS con la cláusula PARTITION BY

Podemos utilizar PRECEDENTES SIN LÍMITES DE FILAS con la cláusula PARTITION BY de SQL para poder seleccionar una fila en una partición antes de la fila actual y la fila de mayor valor después de la fila actual.

En la siguiente tabla, podemos apreciar la fila 1; la cual no tiene ninguna fila con un valor alto en esta partición. Por lo tanto, el valor promedio acumulativo es el mismo que para la fila 1 OrderAmount.

Para Row2, Se va a buscar la fila con valor actual (7.199,61) y el valor más alto de la fila 1 (7.577,9). Este va a calcular el promedio de estas dos cantidades.

Para la fila 3, va a buscar el valor actual (6847.66) y un valor de cantidad mayor que este valor que es 7199.61 y 7577.90. este va a calcular el promedio de estos y los retornos.


CustomerCity

CustomerName

Rank

OrderAmount

Cumulative Average Rows

Cumulative Average

Chicago

Marvin

1

7577.9

Rank 1

7577.90

Chicago

Lawrence

2

7199.61

Rank 1+2

7388.76

Chicago

Alex

3

6847.66

Rank 1+2+3

7208.39

Chicago

Jerome

4

1843.83

Rank 1+2+3+4

5867.25

Ahora ejecute la siguiente consulta para obtener este resultado con nuestros datos de muestra.


ROWS UNBOUNDED PRECEDING example

Conclusión

En este artículo, exploramos la cláusula SQL PARTIION BY y su comparación con la cláusula GROUP BY. A su vez aprendimos su uso con algunos ejemplos. Espero que lo haya encontrado útil este artículo y no dude en hacer cualquier pregunta en los comentarios a continuación.

Rajendra Gupta

Rajendra Gupta

Rajendra has 8+ years of experience in database administration having a passion for database performance optimization, monitoring, and high availability and disaster recovery technologies, learning new things, new features.

While working as a Senior consultant DBA for big customers and having certified with MCSA SQL 2012, he likes to share knowledge on various blogs.
He can be reached at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta
Rajendra Gupta
909 Views