GROUPING SETS

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

В двух последних статьях приводились примеры агрегации WITH ROLLUP и WITH CUBE. В SQL Server 2008 появился новый, более мощный синтаксис, совместимый с ANSI SQL 2006. В этой статье будет рассказано об этих изменениях.

Во-первых, давайте посмотрим, как будут выглядеть простые запросы WITH ROLLUP и WITH CUBE, если мы вместо них будем использовать ANSI-синтаксис. Для простоты сравнения, давайте будем использовать ту же схему и запросы, что и в предыдущих статьях, которые доступны по ссылкам выше:

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

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

Используя новый синтаксис эти два запроса можно переписать так:

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

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

Эти запросы семантически эквивалентны запросам WITH ROLLUP и WITH CUBE, и используют такие же планы запросов. Обратите внимание, что ANSI-синтаксис ROLLUP и CUBE доступен только для уровней совместимости 100 и выше. Более общий синтаксис GROUPING SETS, о котором мы поговорим в следующих статьях, также доступен и для уровней совместимости меньше 100.

ANSI-синтаксис GROUPING SETS значительно мощнее, и позволяет точно указать, какие агрегаты мы хотим вычислить. Как показано в таблице ниже, наша простая двумерная схема имеет всего четыре возможных агрегата:

		Yr
		2005	2006	2007	ALL
EmpId	1	GROUP BY (EmpId, Yr)			GROUP BY (EmpId)
	2
	3
	ALL	GROUP BY (Yr)			GROUP BY ()

WITH ROLLUP и WITH CUBE — это всего лишь синтаксическая «обёртка» для двух распространенных вариантов использования GROUPING SETS. Можно выразить приведенный выше запрос WITH ROLLUP через синтаксис по госту:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), ())

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

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

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId), (Yr), ())

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

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

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), (EmpId))

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

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

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr), ())

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

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

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), (Yr))

EmpId       Yr          Sales
----------- ----------- ---------------------
NULL        2005        27000.00
NULL        2006        44000.00
NULL        2007        49000.00
1           NULL        55000.00
2           NULL        21000.00
3           NULL        44000.00

Обратите внимание, что мы могли написать GROUPING SETS (EmpId, Yr) без вторых круглых скобок, но такая запись с годом в круглых скобках упрощает понимание цели запроса, и помогает уловить отличие предыдущего запроса от следующего, который просто выполняет обычное агрегирование по сотрудникам и годам:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr))

EmpId       Yr          Sales
----------- ----------- ---------------------
1           2005        12000.00
2           2005        15000.00
1           2006        18000.00
2           2006        6000.00
3           2006        20000.00
1           2007        25000.00
3           2007        24000.00

Следует учитывать несколько важных правил для синтаксиса GROUPING SETS. Как и в случае с любым другим запросом с агрегатами, если колонка указана в списке SELECT и не обрабатывается агрегатной функцией, он должен появиться где-то в предложении GROUP BY. По этой причине следующих запрос является неправильным:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId), ())

Msg 8120, Level 16, State 1, Line 1
Column 'Sales.Yr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Порядок колонок внутри каждого GROUPING SET и сам порядок в GROUPING SET не имеет значения. Таким образом, оба представленных ниже запроса вычисляют один и тот же CUBE, хотя порядок строк в выводе результата будет отличаться:

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS ((EmpId, Yr), (EmpId), (Yr), ())

SELECT EmpId, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS ((), (Yr), (EmpId), (Yr, EmpId))

Если имеет значение порядок строк в результирующем выводе задайте его явно предложением ORDER BY.

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

CREATE TABLE Sales (EmpId INT, Month INT, Yr INT, Sales MONEY)

Теперь предположим, что мы хотим вычислить продажи для каждого сотрудника по месяцам и годам, по годам и в целом. Мы могли бы это записать явно через стандартный GROUPING SETS:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS((EmpId, Yr, Month), (EmpId, Yr), (EmpId))

Или мы можем использовать ROLLUP, чтобы упростить запрос:

SELECT EmpId, Month, Yr, SUM(Sales) AS Sales
FROM Sales
GROUP BY GROUPING SETS( ( EmpId, ROLLUP(Yr, Month)) )

Обратите внимание, что правильное использование круглых скобок имеет решающее
значение. Если мы опустим одну пару скобок в приведенном выше запросе, смысл существенно изменится, и в конечном итоге мы будем отдельно агрегировать данные по сотрудникам, а затем вычислять ROLLUP года и месяца для всех сотрудников.