Агрегат WITH CUB

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

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

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)

Рассмотрим, используемый в прошлый раз запрос:

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

Будет нагляднее, если мы посмотрим на сводные данные о продажах в таком виде:

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

Из таблицы ясно видно что предложение WITH ROLLUP вычисляет итоговую сумму для каждого сотрудника за все годы и общую сумму для всех сотрудников за все года. Этот запрос не вычисляет итоговые суммы за каждый год для всех сотрудников. Более того, порядок столбцов в предложении GROUP BY определяет в каком порядке будут суммироваться данные.

Теперь повторим тот же запрос, но заменим предложение WITH ROLLUP предложением WITH CUBE:

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

Этот запрос вычисляет все возможные промежуточные и общие итоги:

		Yr
		2005	2006	2007	ALL
EmpId	1	12000.00	18000.00	25000.00	55000.00
	2	15000.00	6000.00		21000.00
	3		20000.00	24000.00	44000.00
	ALL	27000.00	44000.00	49000.00	120000.00

Поскольку предложение WITH CUBE вычисляет все возможные итоги, порядок столбцов в предложении GROUP BY не имеет значения. Конечно, по умолчанию SQL Server не делает такой красивой таблицы со сводными результатами ни для одного из приведенных выше запросов. Вот реальное представление результата запроса WITH CUBE:

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
NULL        2005        27000.00
NULL        2006        44000.00
NULL        2007        49000.00

А теперь далее давайте посмотрим на план запроса WITH CUBE:

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

Этот план состоит из двух частей. SQL Server фактически переписал наш запрос следующим образом:

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

Первая часть плана вычисляет результат приведенного выше запроса WITH ROLLUP. Описание того как работает этот план, было в предыдущей статье. Вторая часть этого плана вычисляет недостающие промежуточные итоги года, чем как раз и отличается полный результат с CUBE. Обратите внимание, что этот план использует Table Spool. Как я уже говорил в одной из предыдущих статей, вложенные ветки с Table Spool используют сохранённые в рабочую таблицу полученные строки, которые затем извлекаются из этой рабочей таблицы несколько раз — в данном случае дважды. Такая буферизация предназначена для повышения производительности, хотя в данном примере это даёт совсем небольшой выигрыш, поскольку сервер с таким же успехом мог бы перечитать всю исходную таблицу продаж. Однако, если бы входные данные для агрегирования были бы более сложными и их оценка стоила бы дороже, Table Spool мог бы очень помочь.

Если мы используем предложение WITH CUBE при агрегировании более двух столбцов, SQL Server просто сгенерирует ещё более сложные планы с дополнительными данными на входе оператора конкатенации. Как и в простом примере с двумя столбцами, идея состоит в том, чтобы вычислить весь CUBE, вычислив все отдельные элементы ROLLUP, которые его составляют.

Наконец, мы можем объединить операторы WITH CUBE и PIVOT, чтобы сделать нашу таблицу такой же красивой, как была в качестве наглядного примера. 

SELECT EmpId, [2005], [2006], [2007], [ALL]
FROM
      (
      SELECT
            CASE WHEN GROUPING(EmpId) = 0
                  THEN CAST (EmpId AS CHAR(7))
                  ELSE 'ALL'
            END AS EmpId,
            CASE WHEN GROUPING(Yr) = 0
                  THEN CAST (Yr AS CHAR(7))
                  ELSE 'ALL'
            END AS Yr,
            SUM(Sales) AS Sales
      FROM Sales
      GROUP BY EmpId, Yr WITH CUBE
      ) AS s
PIVOT (SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [ALL])) AS p

Вот результат этого запроса:

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