[Перевод] Агрегат WITH CUBE
В предыдущей статье говорилось о том как работает агрегат 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