sql - transact - Comprender las diferencias entre CUBE y ROLLUP

sql transact group by

Question

Mi tarea me pidió que averiguara "¿cuántas facturas se escriben para cada fecha?"

Estaba un poco atrapado y le pedí ayuda a mi profesor. Ella me envió una pregunta por correo electrónico que respondería a la pregunta: "¿Cuántas estufas de cada tipo y versión se han construido? Para un desafío pero sin puntos extra, incluya el número total de estufas".

Esta fue la consulta que ella me envió:

SELECT STOVE.Type + STOVE.Version AS ''Type+Version''
, COUNT(*) AS ''The Count''
FROM STOVE
GROUP BY STOVE.Type + STOVE.Version WITH ROLLUP;

Entonces, ajusté esa consulta hasta que cumplió con mis necesidades. Esto es lo que se me ocurrió:

SELECT InvoiceDt
, COUNT(InvoiceNbr) AS ''Number of Invoices'' 
FROM INVOICE 
GROUP BY InvoiceDt WITH ROLLUP 
ORDER BY InvoiceDt ASC;

Y devolvió los siguientes resultados que yo quería.

De todos modos, decidí leer la cláusula ROLLUP y comencé con un artículo de Microsoft . Dijo que la cláusula ROLLUP era similar a la cláusula CUBE pero que se distinguía de la cláusula CUBE de la siguiente manera:

1. CUBE genera un conjunto de resultados que muestra agregados para todas las combinaciones de valores en las columnas seleccionadas.
2. ROLLUP genera un conjunto de resultados que muestra agregados para una jerarquía de valores en las columnas seleccionadas.

Entonces, decidí reemplazar el ROLLUP en mi consulta con CUBE para ver qué pasaría. Ellos produjeron los mismos resultados. Supongo que es allí donde me confundo.

Parece que, si estás usando el tipo de consulta que estoy aquí, no hay ninguna diferencia práctica entre las dos cláusulas. ¿Está bien? O, ¿no estoy entendiendo algo? Cuando terminé de leer el artículo de Microsoft, pensé que mis resultados deberían haber sido diferentes utilizando la cláusula CUBE.

Answer 1

Esto se debe a que solo tiene una columna por la que está agrupando.

Agregue Group by InvoiceDt, InvoiceCountry (o cualquier campo que le proporcione más datos).

Con Cube le dará una Suma para cada Factura y obtendrá una Suma para cada Factura en el país.

Answer 2

No verá ninguna diferencia ya que solo está desplegando una sola columna. Considera un ejemplo donde lo hacemos

ROLLUP (YEAR, MONTH, DAY)

Con un ROLLUP , tendrá los siguientes resultados:

YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()

Con CUBE , tendrá lo siguiente:

YEAR, MONTH, DAY
YEAR, MONTH
YEAR, DAY
YEAR
MONTH, DAY
MONTH
DAY
()

CUBE esencialmente contiene todos los escenarios posibles de rollup para cada nodo, mientras que ROLLUP mantendrá ROLLUP la jerarquía (para que no omita MONTH y muestre AE / DAY, mientras que CUBE hará)

Es por eso que no notó una diferencia ya que solo tenía una sola columna que estaba enrollando.

Espero que ayude.

Answer 3

Podemos entender la diferencia entre ROLLUP y CUBE con un ejemplo simple. Considere que tenemos una tabla que contiene los resultados de la prueba trimestral de estudiantes. En ciertos casos, necesitamos ver el total correspondiente al trimestre así como a los estudiantes. Aquí está la tabla de muestra

SELECT * INTO #TEMP
FROM
(
    SELECT ''Quarter 1'' PERIOD,''Amar'' NAME ,97 MARKS
    UNION ALL
    SELECT ''Quarter 1'',''Ram'',88 
    UNION ALL
    SELECT ''Quarter 1'',''Simi'',76 
    UNION ALL
    SELECT ''Quarter 2'',''Amar'',94 
    UNION ALL
    SELECT ''Quarter 2'',''Ram'',82 
    UNION ALL
    SELECT ''Quarter 2'',''Simi'',71 
    UNION ALL
    SELECT ''Quarter 3'' ,''Amar'',95 
    UNION ALL
    SELECT ''Quarter 3'',''Ram'',83 
    UNION ALL
    SELECT ''Quarter 3'',''Simi'',77
    UNION ALL
    SELECT ''Quarter 4'' ,''Amar'',91 
    UNION ALL
    SELECT ''Quarter 4'',''Ram'',84 
    UNION ALL
    SELECT ''Quarter 4'',''Simi'',79
)TAB

1. ROLLUP (puede encontrar el total correspondiente a una columna)

(a) Obtenga el puntaje total de cada estudiante en todos los trimestres.

SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH ROLLUP
HAVING PERIOD IS NULL AND NAME IS NOT NULL 
// Having is used inorder to emit a row that is the total of all totals of each student

El siguiente es el resultado de (a)

(b) En caso de que necesite obtener el puntaje total de cada trimestre

SELECT * FROM #TEMP
UNION ALL
SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY PERIOD,NAME 
WITH ROLLUP
HAVING PERIOD IS NOT NULL AND NAME IS NULL

Siguiente es el resultado de (b)

2. CUBE (Encuentra el total para el trimestre y los estudiantes en una sola toma)

SELECT PERIOD,NAME,SUM(MARKS) TOTAL
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH CUBE
HAVING PERIOD IS NOT NULL OR NAME IS NOT NULL

Siguiente es el resultado de CUBE

Ahora se puede estar preguntando sobre el uso en tiempo real de ROLLUP y CUBE. A veces necesitamos un informe en el que necesitamos ver el total de cada trimestre y el total de cada alumno en una sola toma. Aquí hay un ejemplo

Estoy cambiando ligeramente la consulta CUBE anterior, ya que necesitamos total para ambos totales.

SELECT CASE WHEN PERIOD IS NULL THEN ''TOTAL'' ELSE PERIOD END PERIOD,
CASE WHEN NAME IS NULL THEN ''TOTAL'' ELSE NAME END NAME,
SUM(MARKS) MARKS
INTO #TEMP2
FROM #TEMP
GROUP BY NAME,PERIOD 
WITH CUBE

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + '',['' + PERIOD + '']'', 
               ''['' + PERIOD + '']'')
               FROM    (SELECT DISTINCT PERIOD FROM #TEMP2) PV  
               ORDER BY PERIOD    


DECLARE @query NVARCHAR(MAX)
SET @query = ''SELECT * FROM 
             (
                 SELECT * FROM #TEMP2
             ) x
             PIVOT 
             (
                 SUM(MARKS)
                 FOR [PERIOD] IN ('' + @cols + '')
            ) p;'' 

EXEC SP_EXECUTESQL @query

Ahora obtendrás el siguiente resultado

Answer 4

Puede encontrar más detalles sobre GROUPING SET, CUBE, ROLL UP. TL; DR simplemente expanden GROUP BY + UNION ALL de alguna manera para obtener agregación :)

https://technet.microsoft.com/en-us/library/bb510427(v=sql.105).aspx