Понимание различий между CUBE и ROLLUP

Мое задание попросило меня выяснить, "сколько счетов-фактур написано для каждой даты?"

Я немного застрял и попросил моего профессора о помощи. Она отправила мне по электронной почте запрос, который бы ответил на вопрос: "Сколько печей каждого типа и версии было построено? Для вызова, но без дополнительных очков, укажите общее количество печей.

Это был запрос, который она мне прислала:

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

Итак, я подкрепил этот запрос, пока он не удовлетворил мои потребности. Вот что я придумал:

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

И он вернул следующие результаты, которые я хотел.

В любом случае, я решил прочитать статью ROLLUP и начал статью из Microsoft. В нем говорилось, что предложение ROLLUP аналогично предложению CUBE, но оно отличается от предложения CUBE следующим образом:

CUBE создает набор результатов, который показывает агрегаты для всех комбинаций значений в выбранных столбцах.
ROLLUP создает набор результатов, который показывает агрегаты для иерархии значений в выбранных столбцах.

Итак, я решил заменить ROLLUP в моем запросе CUBE, чтобы узнать, что произойдет. Они дали одинаковые результаты. Я предполагаю, что там, где я запутался.

Похоже, если вы используете тип запроса, который я здесь, что между двумя статьями нет никакой практической разницы. Это правильно? Или я что-то не понимаю? Я думал, когда я закончил читать статью Microsoft, мои результаты должны были быть разными, используя предложение CUBE.

Ответ 1

Вы не увидите никакой разницы, так как вы только скопируете один столбец. Рассмотрим пример, где мы делаем

ROLLUP (YEAR, MONTH, DAY)

С ROLLUP он будет иметь следующие выходы:

YEAR, MONTH, DAY
YEAR, MONTH
YEAR
()

С CUBE он будет иметь следующее:

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

CUBE по существу содержит все возможные сценарии сворачивания для каждого node, тогда как ROLLUP будет поддерживать иерархию в такте (поэтому он не будет пропускать MONTH и показывать YEAR/DAY, тогда как CUBE будет)

Вот почему вы не заметили разницы, так как у вас был только один столбец, который вы свертывали.

Надеюсь, что это поможет.

Ответ 2

Мы можем понять разницу между ROLLUP и CUBE с помощью простого примера. Рассмотрим, что у нас есть таблица, которая содержит результаты ежеквартального теста студентов. В некоторых случаях мы должны видеть общее количество, соответствующее кварталу, а также учащимся. Вот пример таблицы

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 (может найти итоговое значение для одного столбца)

(a) Получите общий балл каждого учащегося во всех кругах.

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

Ниже приведен результат (a)

(b) Если вы хотите получить общий балл за каждый квартал

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

Ниже приведен результат (b)

2. CUBE (найдите итоговое значение для четверти, а также учащихся за один выстрел)

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

Ниже приведен результат CUBE

Теперь вам может быть интересно, как использовать ROLLUP и CUBE в режиме реального времени. Иногда нам нужен отчет, в котором мы должны видеть общее количество каждого квартала и всего каждого учащегося за один выстрел. Вот пример

Я меняю вышеуказанный запрос CUBE немного, так как нам нужно общее количество для обоих итогов.

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

Теперь вы получите следующий результат

Ответ 3

Это потому, что у вас есть только один столбец, который вы группируете.

Добавить Group by InvoiceDt, InvoiceCountry (или другое поле даст вам больше данных.

С помощью Cube вы получите сумму для каждого InvoiceDt, и вы получите сумму для каждой InvoiceCountry.

Ответ 4

Вы можете найти более подробную информацию о GROUPING SET, CUBE, ROLL UP. TL; DR они просто расширяют GROUP BY + UNION ALL в некотором роде для получения агрегации:)

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