Агрегат WITH ROLLUP

Автор оригинала: Craig Freedman: Aggregation WITH ROLLUP

В этой статье мы обсудим, как устроен агрегат WITH ROLLUP. Использование предложения WITH ROLLUP позволяет выполнить несколько «уровней» агрегации в одном операторе. Например, предположим, что у нас есть некие данные о продажах (это те же данные, которые я использовал в серии статей об операторе PIVOT).

CREATE TABLE Sales (EmpId INT, Yr INT, Sales MONEY)
INSERT Sales VALUES(1, 2005, 12000)
INSERT Sales VALUES(1, 2006, 18000)
INSERT Sales VALUES(1, 2007, 25000)
INSERT Sales VALUES(2, 2005, 15000)
INSERT Sales VALUES(2, 2006, 6000)
INSERT Sales VALUES(3, 2006, 20000)
INSERT Sales VALUES(3, 2007, 24000)

Мы можем написать простой запрос c агрегатом для вычисления общего объема продаж по годам:

SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr

Как и ожидалось, этот запрос возвращает три строки — по одной для каждого года:

Yr          Sales
----------- ---------------------
2005        27000.00
2006        44000.00
2007        49000.00

План запроса представляет собой простой Stream Aggregate:

  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1010]=(0) THEN NULL ELSE [Expr1011] END))
       |--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1010]=COUNT_BIG([Sales].[Sales]), [Expr1011]=SUM([Sales].[Sales])))
            |--Sort(ORDER BY:([Sales].[Yr] ASC))
                 |--Table Scan(OBJECT:([Sales]))

Теперь предположим, что мы хотим вычислить не только продажи по годам, но и общие продажи. Мы могли бы написать запрос с UNION ALL:

SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr
UNION ALL
SELECT NULL, SUM(Sales) AS Sales
FROM Sales

Этот запрос вернёт правильный результат:

Yr          Sales
----------- ---------------------
2005        27000.00
2006        44000.00
2007        49000.00
NULL        120000.00

Однако план запроса содержит два просмотра и два агрегирования (одно для вычисления продаж по годам, а второе для вычисления общих продаж):

  |--Concatenation
       |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1023]=(0) THEN NULL ELSE [Expr1024] END))
       |    |--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1023]=COUNT_BIG([Sales].[Sales]), [Expr1024]=SUM([Sales].[Sales])))
       |         |--Sort(ORDER BY:([Sales].[Yr] ASC))
       |              |--Table Scan(OBJECT:([Sales]))
       |--Compute Scalar(DEFINE:([Expr1010]=NULL))
            |--Compute Scalar(DEFINE:([Expr1009]=CASE WHEN [Expr1025]=(0) THEN NULL ELSE [Expr1026] END))
                 |--Stream Aggregate(DEFINE:([Expr1025]=COUNT_BIG([Sales].[Sales]), [Expr1026]=SUM([Sales].[Sales])))
                      |--Table Scan(OBJECT:([Sales]))

Мы можем улучшить ситуацию, добавив в исходный запрос предложение WITH ROLLUP:

SELECT Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr WITH ROLLUP

Этот запрос проще, и он использует более эффективный план, у которого только один просмотр:

  |--Compute Scalar(DEFINE:([Expr1004]=CASE WHEN [Expr1005]=(0) THEN NULL ELSE [Expr1006] END))
       |--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1005]=SUM([Expr1007]), [Expr1006]=SUM([Expr1008])))
            |--Stream Aggregate(GROUP BY:([Sales].[Yr]) DEFINE:([Expr1007]=COUNT_BIG([Sales].[Sales]), [Expr1008]=SUM([Sales].[Sales])))
                 |--Sort(ORDER BY:([Sales].[Yr] ASC))
                      |--Table Scan(OBJECT:([Sales]))

В этом плане запроса нижний Stream Aggregate такой же, как потоковый агрегат у исходного запроса без ROLLUP. Это обычное агрегирование, которое может быть реализовано с помощью Stream Aggregate (как в примере) или Hash Aggregate (попробуйте добавить предложение OPTION (HASH GROUP) в приведенный выше запрос). Всё это прекрасно распараллеливается.

Верхний Stream Aggregate — это специальный агрегат, который вычисляет ROLLUP (к сожалению, в SQL Server 2005 было невозможно определить из плана запроса, что этот агрегат реализует ROLLUP, однако проблема была исправлена в графическом и XML-планах в SQL Server 2008). Агрегат ROLLUP всегда реализуется с использованием Stream Aggregate и не может распараллеливаться. В этом простом примере потоковый агрегат ROLLUP возвращает каждую полученную на входе предагрегированную строку, и вычисляет промежуточный итог по столбцу Sales. После обработки последней строки агрегат добавляет одну дополнительную строку с общей суммой. Поскольку в SQL отсутствует концепция значения ALL, для столбца Yr в последней строке установлено значение NULL. Если для Yr значение NULL является допустимым, мы можем идентифицировать строку ROLLUP с помощью конструкции GROUPING(Yr) и подставить вместо него ALL.

SELECT
      CASE WHEN GROUPING(Yr) = 0
            THEN CAST (Yr AS CHAR(5))
            ELSE 'ALL'
      END AS Yr,
      SUM(Sales) AS Sales
FROM Sales
GROUP BY Yr WITH ROLLUP

Yr    Sales
----- ---------------------
2005  27000.00
2006  44000.00
2007  49000.00
ALL   120000.00

Также можно вычислить несколько уровней ROLLUP в одном запросе. Например, если мы хотим рассчитать продажи по сотрудникам, а затем по годам для каждого из них:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY EmpId, Yr WITH ROLLUP

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
1           2006        18000.00
1           2007        25000.00
1           NULL        55000.00
2           2005        15000.00
2           2006        6000.00
2           NULL        21000.00
3           2006        20000.00
3           2007        24000.00
3           NULL        44000.00
NULL        NULL        120000.00

В этом запросе стоит отметить несколько моментов. Во-первых, поскольку комбинация значений в столбцах EmpId и Yr уникальна, без предложения WITH ROLLUP запрос просто вернул бы исходные данные. Однако с предложением WITH ROLLUP запрос дает желаемый результат. Во-вторых, порядок столбцов в предложении GROUP BY соответствует предложению WITH ROLLUP. Чтобы понять, почему так, просто попробуйте в запросе поменять местами столбцы EmpId и Yr. После этого, вместо вычисления продаж по сотруднику сначала будут продажи по годам.

План для этого запроса идентичен плану запроса для предыдущего запроса, за исключением того, что он группируется по столбцам EmpId и Yr, а не только по столбцу EmpId. Как и предыдущий, этот план запроса включает два потоковых агрегата: нижний является обычным, а верхний вычисляет ROLLUP. Потоковый агрегат ROLLUP фактически вычисляет два промежуточных итога: общий объем продаж для сотрудника за все годы, и общий объем продаж для всех сотрудников за все годы. В таблице ниже продемонстрировано, как происходит вычисление ROLLUP:

EmpId	Yr	SUM(Sales) BY EmpId, Yr	SUM(Sales) BY EmpId	SUM(Sales)
1	2005	12000.00	12000.00	12000.00
1	2006	18000.00	30000.00	30000.00
1	2007	25000.00	55000.00	55000.00
1	NULL		55000.00	55000.00
2	2005	15000.00	15000.00	70000.00
2	2006	6000.00	21000.00	76000.00
2	NULL		21000.00	76000.00
3	2006	20000.00	20000.00	96000.00
3	2007	24000.00	44000.00	120000.00
3	NULL		44000.00	120000.00
NULL	NULL			120000.00