sets ejemplos sql sql-server grouping cube rollup

sql - ejemplos - Cuándo usar GROUPING SETS, CUBE y ROLLUP



pivot sql (3)

Recientemente he aprendido acerca de GROUPING SETS, CUBE y ROLLUP para definir múltiples conjuntos de agrupación en un servidor SQL

Lo que pregunto es ¿en qué circunstancias utilizamos estas funciones? ¿Cuáles son los beneficios y ventajas de usarlos?

SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders FROM Sales.Orders GROUP BY GROUPING SETS ( ( shipperid, YEAR(shippeddate) ), ( shipperid ), ( YEAR(shippeddate) ), ( ) ); SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders FROM Sales.Orders GROUP BY CUBE( shipperid, YEAR(shippeddate) ); SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders FROM Sales.Orders GROUP BY ROLLUP( shipcountry, shipregion, shipcity );

El CUBE es el mismo de GROUPING SETS con todas las combinaciones posibles.

Así que esto (usando CUBE )

GROUP BY CUBE (C1, C2, C3, ..., Cn-2, Cn-1, Cn)

es lo mismo de esto (usando GROUPING SETS )

GROUP BY GROUPING SETS ( (C1, C2, C3, ..., Cn-2, Cn-1, Cn) -- All dimensions are included. ,( , C2, C3, ..., Cn-2, Cn-1, Cn) -- n-1 dimensions are included. ,(C1, C3, ..., Cn-2, Cn-1, Cn) … ,(C1, C2, C3, ..., Cn-2, Cn-1,) ,(C3, ..., Cn-2, Cn-1, Cn) -- n-2 dimensions included ,(C1 ..., Cn-2, Cn-1, Cn) … ,(C1, C2) -- 2 dimensions are included. ,… ,(C1, Cn) ,… ,(Cn-1, Cn) ,… ,(C1) -- 1 dimension included ,(C2) ,… ,(Cn-1) ,(Cn) ,() ) -- Grand total, 0 dimension is included.

Entonces, si realmente no necesita todas las combinaciones, debe usar GROUPING SETS lugar de CUBE

Los operadores ROLLUP y CUBE generan algunos de los mismos conjuntos de resultados y realizan algunos de los mismos cálculos que las aplicaciones OLAP. El operador CUBE genera un conjunto de resultados que se puede utilizar para informes de tabulación cruzada. Una operación ROLLUP puede calcular el equivalente de una dimensión o jerarquía OLAP.

Mira aquí para ver los conjuntos de grupos equivalentes

ACTUALIZAR

Creo que un ejemplo ayudaría aquí. Supongamos que tiene una tabla de avistamientos de ovnis por país y género, como a continuación:

╔═════════╦═══════╦═════════╗ ║ COUNTRY ║ GENDER║ #SIGHTS ║ ╠═════════╬═══════╬═════════╣ ║ USA ║ F ║ 450 ║ ║ USA ║ M ║ 1500 ║ ║ ITALY ║ F ║ 704 ║ ║ ITALY ║ M ║ 720 ║ ║ SWEDEN ║ F ║ 317 ║ ║ SWEDEN ║ M ║ 310 ║ ║ BRAZIL ║ F ║ 144 ║ ║ BRAZIL ║ M ║ 159 ║ ╚═════════╩═══════╩═════════╝

Luego, si desea conocer los totales de cada país, solo por género y total general, debe utilizar GROUPING SETS

select Country, Gender, sum(Number_Of_Sights) from Table1 group by GROUPING SETS((Country), (Gender), ()) order by Country, Gender

SQL Fiddle

Para obtener el mismo resultado con GROUP BY , usaría UNION ALL como:

select Country, NULL Gender, sum(Number_Of_Sights) from Table1 GROUP BY Country UNION ALL select NULL Country, Gender, sum(Number_Of_Sights) from Table1 GROUP BY GENDER UNION ALL SELECT NULL Country, NULL Gender, sum(Number_Of_Sights) FROM TABLE1 ORDER BY COUNTRY, GENDER

SQL Fiddle

Pero no es posible obtener el mismo resultado con CUBE, ya que devolverá todas las posibilidades.

Ahora, si quieres saber todas las combinaciones posibles, debes usar CUBE

En primer lugar, para aquellos que aún no han leído sobre el tema:

Dicho esto, no piense en estas opciones de agrupación como formas de obtener un conjunto de resultados. Estas son herramientas de rendimiento .

Tomemos ROLLUP como un ejemplo simple.

Puedo usar la siguiente consulta para obtener el recuento de registros para cada valor de GrpCol.

SELECT GrpCol, count(*) AS cnt FROM dbo.MyTable GROUP BY GrpCol

Y puedo usar la siguiente consulta para resumir "acumular" el recuento de TODOS los registros.

SELECT NULL, count(*) AS cnt FROM dbo.MyTable

Y podría UNION ALL las dos consultas anteriores para obtener exactamente los mismos resultados que obtendría si hubiera escrito la primera consulta con la cláusula ROLLUP (es por eso que puse el NULL aquí).

En realidad, podría ser más conveniente para mí ejecutar esto como dos consultas diferentes porque entonces tengo los resultados agrupados separados de mis totales. ¿Por qué querría que mi total final se mezclara con el resto de los resultados? La respuesta es que hacer ambas cosas juntas usando la cláusula ROLLUP es más eficiente. SQL Server utilizará un plan de ejecución que calcula todas las agregaciones juntas en una sola pasada. Compare eso con el ejemplo de UNION ALL que proporcionaría exactamente los mismos resultados, pero utilice un plan de ejecución menos eficiente (dos exploraciones de tabla en lugar de una).

Imagine un ejemplo extremo en el que está trabajando en un conjunto de datos tan grande que cada escaneo de los datos toma una hora completa. Básicamente, debe proporcionar los totales en cada dimensión posible (forma de segmentar) los datos todos los días. Jajaja Apuesto a que una de estas opciones de agrupación es exactamente lo que necesita. Si guarda los resultados de ese escaneo en un esquema de esquema especial, podrá ejecutar informes para el resto del día de los resultados guardados.

Así que básicamente estoy diciendo que estás trabajando en un proyecto de almacén de datos. Para el resto de nosotros, principalmente cae en la categoría de "lo que hay que saber".

Me parece que son buenos cuando se está produciendo un informe y el resultado no es algo que pueda acumularse dentro del cliente.

Por ejemplo, si está haciendo algo con COUNT(DISTINCT...) , el resultado en un grupo más grande no es necesariamente el mismo valor que la suma de las partes. Por ejemplo, a lo largo de dos días individuales puede tener 1500 visitantes y 2000 visitantes, pero el total podría ser entre 2000 y 3500, dependiendo de la superposición. Es bueno hacer esto en el cliente, pero como el cliente no puede saber cuál es la superposición, puede usar GROUPING SETS para proporcionar la respuesta (y luego manejar esa fila adicional que aparece en el informe).