Este artículo cubrirá la cláusula SQL PARTITION BY y, en particular, la diferencia con GROUP BY en una declaración de selección. También exploraremos varios casos de uso de SQL PARTITION BY.
Usamos SQL PARTITION BY para dividir el conjunto de resultados en particiones y realizar el cálculo en cada subconjunto de datos particionados.
Preparación de la muestra Datos
Creemos una tabla de Pedidos en mi base de datos de muestra SQLShackDemo e insertemos registros para escribir más consultas.
Utilizo ApexSQL Generate para insertar datos de muestra en este artículo. Haga clic derecho en la tabla de Órdenes y Genere datos de prueba.
Lanza ApexSQL Generate. Genere un script para insertar datos en la tabla Pedidos. Ejecute este script para insertar 100 registros en la tabla de Órdenes.
Una vez que ejecutamos las sentencias de inserción, podemos ver los datos en la tabla de Órdenes en la siguiente imagen.
Usamos la cláusula GROUP BY de SQL para agrupar los resultados por columna especificada y usamos funciones agregadas como Avg (), Min (), Max () para calcular los valores requeridos.
Agrupar por función de sintaxis
|
1
2
3
4
|
SELECCIONAR expresión, función agregada ()
DESDE tablas
condiciones DONDE
GROUP BY expresión
|
Supongamos que queremos encontrar los siguientes valores en la tabla de pedidos
- Valor mínimo de pedido en una ciudad
- Valor máximo de pedido en una ciudad
- Valor de pedido promedio en una ciudad
Ejecute la siguiente consulta con la cláusula GROUP BY para calcular estos valores.
|
1
2
3
4
5
6
|
SELECT Customercity,
AVG (Cantidad de pedido) AS AvgOrderAmount,
MIN (Cantidad de pedido) AS MinOrderAmount,
SUM ( Cantidad de pedido) Cantidad de pedido total
FROM.
GROUP BY Customercity;
|
En la siguiente captura de pantalla, podemos ver Promedio, Mínimo y valores máximos agrupados por CustomerCity.
Ahora, queremos agregar la columna CustomerName y OrderAmount también en la salida. Agreguemos estas columnas en la instrucción select y ejecutemos el siguiente código.
|
1
2
3
4
5
6
|
SELECT Customercity, CustomerName, OrderAmount,
AVG (Orderamount) AS AvgOrderAmount,
MIN (OrderAmount) AS MinOrderAmount,
SUM (Orderamount) TotalOrderAmount
DESDE.
GROUP BY Customercity;
|
Una vez que ejecutamos esta consulta, obtenemos un mensaje de error . En la cláusula GROUP BY de SQL, podemos usar una columna en la instrucción select si también se usa 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.
Podemos usar SQL PARTITION BY cláusula para resolver este problema. Vamos a explorarlo más en la siguiente sección.
SQL PARTITION BY
Podemos usar la cláusula SQL PARTITION BY con la cláusula OVER para especificar la columna en la que necesitamos realizar la agregación . En el ejemplo anterior, usamos Group By con la columna CustomerCity y calculamos los valores promedio, mínimo y máximo.
Volvamos a ejecutar este escenario con la cláusula SQL PARTITION BY usando la siguiente consulta.
|
1
2
3
4
5
|
SELECT Customercity,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (OrderAmount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
En la salida, obtenemos valores agregados similares a un GRUPO Por cláusula. Es posible que observe una diferencia en la salida de la salida de la cláusula SQL PARTITION BY y GROUP BY.
|
Agrupar por |
SQL PARTITION BY |
|
Obtenemos un número limitado de registros usando la cláusula Group By |
Obtenemos todos los registros en una tabla usando la cláusula PARTITION BY. |
|
Da uno fila por grupo en el conjunto de resultados. Por ejemplo, obtenemos un resultado para cada grupo de CustomerCity en la cláusula GROUP BY. |
Da columnas agregadas con cada registro en la tabla especificada. Tenemos 15 registros en la tabla Pedidos. En la salida de la consulta de SQL PARTITION BY, también obtenemos 15 filas junto con los valores mínimo, máximo y promedio. |
En el ejemplo anterior, obtenemos un mensaje de error si intentamos agregar una columna que no es parte de la cláusula GROUP BY.
Podemos agregar columnas requeridas en una instrucción select con la cláusula SQL PARTITION BY . Agreguemos las columnas CustomerName y OrderAmount y ejecutemos la siguiente consulta.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount ,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (OrderAmount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Obtenemos la columna CustomerName y OrderAmount junto con la salida de la función agregada. También obtenemos todas las filas disponibles en la tabla Pedidos.
En la siguiente captura de pantalla, puedes para CustomerCity Chicago, agregaciones (Avg, Min y Max) y da valores en las columnas respectivas.
De manera similar, podemos usar otras funciones agregadas como as count para averiguar el número total de pedidos en una ciudad en particular con la cláusula SQL PARTITION BY.
|
1
2
3
4
5
6
7
8
|
SELECCIONE Customercity,
CustomerName,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITIO N BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTITION BY Customercity) TotalOrderAmount
FROM.;
|
Podemos ver los recuentos de pedidos para una ciudad en particular. Por ejemplo, tenemos dos pedidos de la ciudad de Austin; muestra el valor 2 en la columna CountofOrders.
cláusula PARTITION BY con ROW_NUMBER ()
Podemos use la cláusula SQL PARTITION BY con la función ROW_NUMBER () para tener un número de fila de cada fila. Definimos los siguientes parámetros para usar ROW_NUMBER con la cláusula SQL PARTITION BY.
- Columna PARTITION BY: en este ejemplo, queremos particionar datos en la columna CustomerCity
- Order By : En la columna ORDER BY, definimos una columna o condición que define el número de fila. En este ejemplo, queremos ordenar los datos en la columna OrderAmount
|
1
2
3
4
5
6
7
8
9
10
|
SELECCIONE Customercity,
CustomerName,
ROW_NUMBER () OVER (PARTICIÓN POR Customercity
ORDER BY OrderAmount DESC) COMO «Número de fila»,
OrderAmount,
COUNT (OrderID) OVER (PARTITION BY Customercity) AS CountOfOrders,
AVG (Orderamount) OVER (PARTITION BY Customercity) AS AvgOrderAmount,
MIN (OrderAmount) OVER (PARTITION BY Customercity) AS MinOrderAmount,
SUM (Orderamount) OVER (PARTICIÓN POR Customercity) TotalOrderAmount
FROM.;
|
En la siguiente captura de pantalla, vemos CustomerCity Chicago , tenemos la Fila número 1 para el pedido con la cantidad más alta 7577.90. proporciona un número de fila con OrderAmount descendente.
Cláusula PARTITION BY con valor total acumulativo
Supongamos que desea obtener un total acumulativo de los pedidos en una partición.El total acumulado debe ser de la fila actual y la fila siguiente en la partición.
Por ejemplo, en la ciudad de Chicago, tenemos cuatro pedidos.
|
CustomerCity |
CustomerName |
Clasificación |
OrderAmount |
Filas totales acumuladas |
Total acumulado |
|
Chicago |
Marvin |
Rango 1 +2 |
|||
|
Chicago |
Lawrence |
Rango 2 + 3 |
|||
|
Chicago |
Alex |
Rango 3 + 4 |
|||
|
Chicago |
Jerome |
Rango 4 |
En la siguiente consulta, la cláusula ROWS especificada para se leccione la fila actual (usando CURRENT ROW) y la siguiente fila (usando 1 SIGUIENTE). Calcula además la suma en esas filas usando sum (Orderamount) con una partición en CustomerCity (usando OVER (PARTITION BY Customercity ORDER BY OrderAmount DESC).
|
1
2
3
4
5
6
7
|
SELECCIONAR Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTICIÓN POR Customercity
ORDER BY OrderAmount DESC) AS «Row Number»,
CONVERT (VARCHAR (20), SUM (orderamount) OVER (PARTICIÓN POR Customercity
ORDEN POR OrderAmount DESC FILAS ENTRE LA FILA ACTUAL Y 1 SIGUIENTE), 1) COMO CumulativeTotal,
|
De manera similar, podemos calcular el promedio acumulativo usando el siguiente consulta con la cláusula SQL PARTITION BY.
|
1
2
3
4
5
6
7
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) COMO «Número de fila»,
CONVERTIR (VARCHAR (20), AVG (cantidad de pedido) OVER (PARTICIÓN POR ciudad del cliente
PEDIDO POR Cantidad de pedido DESC FILAS ENTRE LA FILA ACTUAL Y 1 SIGUIENTE) , 1) AS CumulativeAVG
|
ROWS UNBOUNDED PRECEDING con la cláusula PARTITION BY
Podemos usar ROWS UNBOUNDED PRECEDING con la cláusula SQL PARTITION BY para seleccionar una fila en una partición antes de la fila actual y el valor más alto r ujo después de la fila actual.
En la siguiente tabla, podemos ver la fila 1; no tiene ninguna fila con un valor alto en esta partición. Por lo tanto, el valor promedio acumulado es el mismo que el de la fila 1. OrderAmount.
Para Row2, busca el valor de fila actual (7199.61) y el valor más alto de la fila 1 (7577.9). Calcula el promedio para estas dos cantidades.
Para la Fila 3, busca el valor actual (6847.66) y un valor de cantidad más alto que este valor que es 7199.61 y 7577.90. Calcula el promedio de estos y devuelve.
|
CustomerCity |
CustomerName |
Clasificación |
OrderAmount |
Filas de promedio acumulativo |
Promedio acumulado |
|
Chicago |
Marvin |
Rango 1 |
|||
|
Chicago |
Lawrence |
Rango 1 + 2 |
|||
|
Chicago |
Alex |
Rango 1 + 2 + 3 |
|||
|
Chicago |
Jerome |
Rango 1 + 2 + 3 + 4 |
Ejecute la siguiente consulta para obtener este resultado con nuestros datos de muestra.
|
1
2
3
4
5
6
7
8
|
SELECT Customercity,
CustomerName,
OrderAmount,
ROW_NUMBER () OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC) AS «Row Number»,
CONVERT (VARCHAR (20), AVG (orderamount) OVER (PARTITION BY Customercity
ORDER BY OrderAmount DESC ROWS UNBOUNDED PRECEDING), 1) AS CumulativeAvg
FROM.;
|
Conclusión
En este artículo, exploramos la cláusula SQL PARTIION BY y su comparación con la cláusula GROUP BY. También aprendimos su uso con algunos ejemplos. Espero que encuentre útil este artículo y no dude en hacer cualquier pregunta en los comentarios a continuación
- Autor
- Publicaciones recientes
Es el creador de una de las mayores colecciones gratuitas de artículos en línea sobre un solo tema, con su serie de 50 partes sobre los grupos de disponibilidad AlwaysOn de SQL Server. Basado en su contribución a la comunidad de SQL Server, ha sido reconocido con varios premios, incluido el prestigioso «Mejor autor del año» de forma continua en 2020 y 2021 en SQLShack.
Raj siempre está interesado en nuevos desafíos, así que si necesita consultoría ayuda sobre cualquier tema tratado en sus escritos, puede ser contactado en [email protected]
Ver todas las publicaciones de Rajendra Gupta
- Tiempos de espera de sesión en SQL Server Always On Availability Grupos: 8 de febrero de 2021
- Realización de actualizaciones de versiones menores y mayores para AWS RDS SQL Server: 29 de enero de 2021
- Implementación de instancias de AWS RDS PostgreSQL: 27 de enero de 2021