Поделиться через


Использование предложения GROUP BY с операторами ROLLUP, CUBE и GROUPING SETS

Операторы ROLLUP, CUBE и GROUPING SETS являются расширениями предложения GROUP BY. Операторы ROLLUP, CUBE и GROUPING SETS могут сформировать такой же результирующий набор, который получится в результате использования оператора UNION ALL для объединения одиночных запросов группирования, однако использование одного из операторов предложения GROUP BY обычно является более эффективным.

Оператор GROUPING SETS может сформировать такой же результирующий набор, который получится при использовании простых операторов GROUP BY, ROLLUP или CUBE. Если наличие всех группирований, формируемых полными операторами ROLLUP или CUBE, не требуется, можно использовать оператор GROUPING SETS, чтобы задать только необходимые группирования. В списке оператора GROUPING SETS могут содержаться повторяющиеся группирования; также повторяющиеся группирования могут появиться в результате совместного использования оператора GROUPING SETS и операторов ROLLUP и CUBE. Повторяющиеся группирования сохраняются, поскольку в них будет использоваться ключевое слово UNION ALL.

ПримечаниеПримечание

Инструкции CUBE, ROLLUP и GROUPING SETS не поддерживают функцию CHECKSUM_AGG.

Составные и объединенные элементы

Если в списке оператораGROUPING SETS несколько столбцов заключено во внутренние скобки, они считаются единым набором. Например, в предложении GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4) столбцы Column1 и Column2 обрабатываются как один столбец. Как использовать оператор GROUPING SETS для работы с составными элементами, показано в примере З далее в этом разделе.

Если в списке оператора GROUPING SETS содержится несколько разделенных запятыми наборов во внутренних скобках, то выходные данные этих наборов объединяются. Результирующий набор является либо перекрестным произведением, либо декартовым произведением группирующим наборов. Как использовать предложение GROUP BY для работы с объединенными операциями ROLLUP, показано в примере Г далее в этом разделе.

Сравнение операторов ROLLUP и CUBE с измерениями OLAP

Некоторые результирующие наборы, сформированные запросами, в которых используются операторы ROLLUP и CUBE, а также некоторые совершаемые ими вычисления совпадают с вычислениями приложений OLAP. Оператор CUBE формирует результирующий набор, который может быть использован для перекрестных отчетов. Операция ROLLUP может вычислить эквивалент измерения или иерархии OLAP.

Например, если существует измерение времени с уровнями или атрибутами года, месяца и дня, то приведенная ниже операция ROLLUP сформирует следующие группирования.

Операция

Группирования

ROLLUP (DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate)) 

year, month, day

year, month

year

()

Если существует измерение местонахождения, имеющее уровни региона и города, объединенные с уровнями измерения времени — годом, месяцем и днем, то следующая операция ROLLUP выдаст следующие группирования.

Операция

Группирования

ROLLUP (region, city),
ROLLUP (DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))

region, city, year, month, day

region, city, year, month

region, city, year

region, city

region, year, month, day

region, year, month

region, year

region

year, month, day

year, month

year

()

Операция CUBE, использующая те же уровни измерений местоположения и времени, выдаст следующие группирования.

Операция

Группирование

CUBE (region, city
    ,DATEPART(yyyy,OrderDate)
    ,DATEPART(mm,OrderDate)
    ,DATEPART(dd,OrderDate))

region, city, year, month, day

region, city, year, month

region, city, year

region, city

region, city, month, day

region, city, month

region, city, day

region, city, year, day

region, city, day

region, year, month, day

region, year, month

region, year

region, month, day

region, month

region, year, day

region, day

region

city, year, month, day

city, year, month

city, year

city, month, day

city, month

city, year, day

city, day

year, month, day

year, month

year

year, day

month, day

month

day

()

Значения NULL в результирующих наборах

Если результирующие наборы формируются операторами предложения GROUP BY, значения NULL используются следующим способом.

  • Если в столбце, по которому производится группирование, содержится значение NULL, то все значения NULL считаются равными и помещаются в одну группу NULL.

  • При статистической обработке столбца в строку в качестве значения этого столбца отображается значение NULL.

В следующем примере функция GROUPING используется для демонстрации двух этих способов применения значений NULL. Значение UNKNOWN заменяет значение NULL в тех строках, в которых значения NULL столбца были сгруппированы. Значение ALL заменяет значение NULL для столбца, в котором значение NULL означает то, что столбец был включен в статистическое выражение.

USE tempdb;
GO
CREATE TABLE dbo.GroupingNULLS (
    Store nvarchar(19)
    ,SaleYear nvarchar(4)
    ,SaleMonth nvarchar (7))
INSERT INTO dbo.GroupingNULLS VALUES
(NULL,NULL,'January')
,(NULL,'2002',NULL)
,(NULL,NULL,NULL)
,('Active Cycling',NULL ,'January')
,('Active Cycling','2002',NULL)
,('Active Cycling',NULL ,NULL)
,('Active Cycling',NULL,'January')
,('Active Cycling','2003','Febuary')
,('Active Cycling','2003',NULL)
,('Mountain Bike Store','2002','January')
,('Mountain Bike Store','2002',NULL)
,('Mountain Bike Store',NULL,NULL)
,('Mountain Bike Store','2003','January')
,('Mountain Bike Store','2003','Febuary')
,('Mountain Bike Store','2003','March');

SELECT ISNULL(Store,
    CASE WHEN GROUPING(Store) = 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS Store
    ,ISNULL(CAST(SaleYear AS nvarchar(7)),
    CASE WHEN GROUPING(SaleYear)= 0 THEN 'UNKNOWN' ELSE 'ALL' END)
    AS SalesYear
    ,ISNULL(SaleMonth,
    CASE WHEN GROUPING(SaleMonth) = 0 THEN 'UNKNOWN' ELSE 'ALL'END)
    AS SalesMonth
    ,COUNT(*) AS Count
FROM dbo.GroupingNULLS 
GROUP BY ROLLUP(Store, SaleYear, SaleMonth);

Ниже приводится результирующий набор.

Store

SalesYear

SalesMonth

Count

Unknown

Unknown

Unknown

1

Unknown

Unknown

January

1

Unknown

Unknown

ALL

2

Unknown

2002

Unknown

1

Unknown

2002

ALL

1

Unknown

ALL

ALL

3

Active Cycling

Unknown

Unknown

1

Active Cycling

Unknown

January

2

Active Cycling

Unknown

ALL

3

Active Cycling

2002

Unknown

1

Active Cycling

2002

ALL

1

Active Cycling

2003

Unknown

1

Active Cycling

2003

February

1

Active Cycling

2003

ALL

2

Active Cycling

ALL

ALL

6

Mountain Bike Store

Unknown

Unknown

1

Mountain Bike Store

Unknown

ALL

1

Mountain Bike Store

2002

Unknown

1

Mountain Bike Store

2002

January

1

Mountain Bike Store

2002

ALL

2

Mountain Bike Store

2003

February

1

Mountain Bike Store

2003

January

1

Mountain Bike Store

2003

March

1

Mountain Bike Store

2003

ALL

3

Mountain Bike Store

ALL

ALL

6

ALL

ALL

ALL

15

Примеры

В примерах этого раздела используется агрегатная функция SUM. Это сделано для того, чтобы результирующие наборы можно было сравнить. Другие агрегатные функции также могут быть использованы для вычисления различных сводок.

A. Использование простого предложения GROUP BY

В следующем примере простое предложение GROUP BY возвращает результирующий набор, который будет сравниваться с результирующими наборами примеров Б — Л. Эти примеры используют операторы GROUP BY с той же инструкцией SELECT.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID
ORDER BY T.[Group], T.CountryRegionCode
    ,S.Name,H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

Europe

DE

Versatile Sporting Goods Company

284

859.232

Europe

DE

Versatile Sporting Goods Company

289

17691.83

Europe

FR

Spa and Exercise Outfitters

284

32774.36

Europe

FR

Spa and Exercise Outfitters

286

246272.4

Б. Использование оператора GROUP BY ROLLUP

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

  • Region, Country, Store и SalesPersonID

  • Region, Country и Store

  • Regionи Country

  • Region

  • общий итог

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

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales' 
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

297597.8

Europe

NULL

NULL

NULL

297597.8

Europe

DE

NULL

NULL

18551.07

Europe

DE

Versatile Sporting Goods Company

NULL

18551.07

Europe

DE

Versatile Sporting Goods Company

284

859.232

Europe

DE

Versatile Sporting Goods Company

289

17691.83

Europe

FR

NULL

NULL

279046.8

Europe

FR

Spa and Exercise Outfitters

NULL

279046.8

Europe

FR

Spa and Exercise Outfitters

284

32774.36

Europe

FR

Spa and Exercise Outfitters

286

246272.4

В. Использование оператора GROUP BY ROLLUP с обратным порядком столбцов

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

  • SalesPersonID, Store, Country и Region

  • SalesPersonID, Store и Country

  • SalesPersonIDи Store

  • SalesPersonID

  • общий итог

В списке предложения ROLLUP содержатся те же столбцы, что и в примере Б, но они расположены в обратном порядке. Столбцы обрабатывались справа налево, поэтому такой порядок повлиял на группирования. Количество строк в результирующем наборе может зависеть от порядка столбцов.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 290, 288)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY ROLLUP(
    H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group])
ORDER BY H.SalesPersonID, S.Name, T.CountryRegionCode, T.[Group];

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

297597.8

NULL

NULL

NULL

284

33633.59

NULL

NULL

Spa and Exercise Outfitters

284

32774.36

NULL

FR

Spa and Exercise Outfitters

284

32774.36

Europe

FR

Spa and Exercise Outfitters

284

32774.36

NULL

NULL

Versatile Sporting Goods Company

284

859.232

NULL

DE

Versatile Sporting Goods Company

284

859.232

Europe

DE

Versatile Sporting Goods Company

284

859.232

NULL

NULL

NULL

286

246272.4

NULL

NULL

Spa and Exercise Outfitters

286

246272.4

NULL

FR

Spa and Exercise Outfitters

286

246272.4

Europe

FR

Spa and Exercise Outfitters

286

246272.4

NULL

NULL

NULL

289

17691.83

NULL

NULL

Versatile Sporting Goods Company

289

17691.83

NULL

DE

Versatile Sporting Goods Company

289

17691.83

Europe

DE

Versatile Sporting Goods Company

289

17691.83

Г. Использование предложения GROUP BY с объединенными операциями ROLLUP

В следующем примере возвращается перекрестное произведение двух операций ROLLUP.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
GROUP BY 
    ROLLUP(T.[Group], T.CountryRegionCode)
    ,ROLLUP(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Ниже приводится результирующий набор.

Region

Country

Year

Month

Total Sales

NULL

NULL

NULL

NULL

966221.9606

NULL

NULL

2006

NULL

966221.9606

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

NULL

NULL

NULL

966221.9606

Europe

NULL

2006

NULL

966221.9606

Europe

NULL

2006

7

109936.0248

Europe

NULL

2006

8

296651.4808

Europe

NULL

2006

9

184477.7563

Europe

NULL

2006

10

62792.5455

Europe

NULL

2006

11

213238.0125

Europe

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

Europe

FR

2006

NULL

966221.9606

Europe

FR

2006

7

109936.0248

Europe

FR

2006

8

296651.4808

Europe

FR

2006

9

184477.7563

Europe

FR

2006

10

62792.5455

Europe

FR

2006

11

213238.0125

Europe

FR

2006

12

99126.1407

Д. Использование оператора GROUP BY CUBE

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

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer AS C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY CUBE(
    T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Spa and Exercise Outfitters

287

27731.551

NULL

NULL

Spa and Exercise Outfitters

290

208479.3505

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

NULL

Versatile Sporting Goods Company

287

729.6344

NULL

NULL

Versatile Sporting Goods Company

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

DE

NULL

287

729.6344

NULL

DE

NULL

288

17073.0655

NULL

DE

Versatile Sporting Goods Company

NULL

17802.6999

NULL

DE

Versatile Sporting Goods Company

287

729.6344

NULL

DE

Versatile Sporting Goods Company

288

17073.0655

NULL

FR

NULL

NULL

236210.9015

NULL

FR

NULL

287

27731.551

NULL

FR

NULL

290

208479.3505

NULL

FR

Spa and Exercise Outfitters

NULL

236210.9015

NULL

FR

Spa and Exercise Outfitters

287

27731.551

NULL

FR

Spa and Exercise Outfitters

290

208479.3505

Europe

NULL

NULL

NULL

254013.6014

Europe

NULL

NULL

287

28461.1854

Europe

NULL

NULL

288

17073.0655

Europe

NULL

NULL

290

208479.3505

Europe

NULL

Spa and Exercise Outfitters

NULL

236210.9015

Europe

NULL

Spa and Exercise Outfitters

287

27731.551

Europe

NULL

Spa and Exercise Outfitters

290

208479.3505

Europe

NULL

Versatile Sporting Goods Company

NULL

17802.6999

Europe

NULL

Versatile Sporting Goods Company

287

729.6344

Europe

NULL

Versatile Sporting Goods Company

288

17073.0655

Europe

DE

NULL

NULL

17802.6999

Europe

DE

NULL

287

729.6344

Europe

DE

NULL

288

17073.0655

Europe

DE

Versatile Sporting Goods Company

NULL

17802.6999

Europe

DE

Versatile Sporting Goods Company

287

729.6344

Europe

DE

Versatile Sporting Goods Company

288

17073.0655

Europe

FR

NULL

NULL

236210.9015

Europe

FR

NULL

287

27731.551

Europe

FR

NULL

290

208479.3505

Europe

FR

Spa and Exercise Outfitters

NULL

236210.9015

Europe

FR

Spa and Exercise Outfitters

287

27731.551

Europe

FR

Spa and Exercise Outfitters

290

208479.3505

Е. Использование оператора CUBE с составными элементами

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

Этот оператор обрабатывает сгруппированные столбцы (T.[Group], T.CountryRegionCode) и (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) как один столбец.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
GROUP BY CUBE(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Ниже приводится результирующий набор.

Region

Country

Year

Month

Total Sales

NULL

NULL

NULL

NULL

966221.9606

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

Europe

FR

2006

7

109936.0248

Europe

FR

2006

8

296651.4808

Europe

FR

2006

9

184477.7563

Europe

FR

2006

10

62792.5455

Europe

FR

2006

11

213238.0125

Europe

FR

2006

12

99126.1407

Ж. Совместное использование предложения GROUP BY и оператора GROUPING SETS

В следующем примере оператор GROUPING SETS имеет четыре группирования — по одному для каждого столбца в списке инструкции SELECT. Оператор возвращает по одной строке для каждого уникального значения в столбцах Region, Country, Store, и SalesPersonID .

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityId 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS
    (T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID)
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014

З. Использование оператора GROUPING SETS с составными элементами

В следующем примере список оператора GROUPING SETS состоит из двух составных элементов — (T.[Group], T.CountryRegionCode) и (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Каждый составной элемент рассматривается как один столбец.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,DATEPART(yyyy,OrderDate) AS 'Year'
    ,DATEPART(mm,OrderDate) AS 'Month'
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND DATEPART(yyyy,OrderDate) = '2006'
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)))
ORDER BY T.[Group], T.CountryRegionCode
    ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);

Ниже приводится результирующий набор.

Region

Country

Year

Month

Total Sales

NULL

NULL

2006

7

109936.0248

NULL

NULL

2006

8

296651.4808

NULL

NULL

2006

9

184477.7563

NULL

NULL

2006

10

62792.5455

NULL

NULL

2006

11

213238.0125

NULL

NULL

2006

12

99126.1407

Europe

FR

NULL

NULL

966221.9606

И. Совместное использование предложения GROUP BY и нескольких операторов GROUPING SETS

В следующем примере в списке оператора GROUPING SETS находится пять элементов. В результирующем наборе будет по одной строке для каждого из следующих элементов.

  • Каждое уникальное сочетание значений в столбцах Region и Country.

  • Каждое уникальное значение в столбце Store.

  • Каждое уникальное сочетание значений в столбцах SalesPersonID и Region.

  • Каждое уникальное значение в столбце SalesPersonID.

  • Общий итог.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    (T.[Group], T.CountryRegionCode)
    ,(S.Name)
    ,(H.SalesPersonID,T.[Group])
    ,(H.SalesPersonID)
    ,())
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

Europe

NULL

NULL

287

28461.1854

Europe

NULL

NULL

288

17073.0655

Europe

NULL

NULL

290

208479.3505

Europe

DE

NULL

NULL

17802.6999

Europe

FR

NULL

NULL

236210.9015

К. Использование оператора GROUPING SETS с ключевым словом ROLLUP в качестве части списка предложения GROUP BY

В следующем примере в список оператора GROUPING SETS входят группирования для столбцов T.[Group] и T.CountryRegionCode и результат оператора ROLLUP для столбцов S.Name и H.SalesPersonID.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
   ,ROLLUP(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Spa and Exercise Outfitters

287

27731.551

NULL

NULL

Spa and Exercise Outfitters

290

208479.3505

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

NULL

Versatile Sporting Goods Company

287

729.6344

NULL

NULL

Versatile Sporting Goods Company

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014

Л. Использование оператора GROUPING SETS с ключевым словом CUBE в качестве части списка оператора GROUP BY

В следующем примере в список оператора GROUPING SETS входят группирования для столбцов T.[Group] и T.CountryRegionCode и результат оператора CUBE для столбцов S.Name и H.SalesPersonID.

USE AdventureWorks2008R2;
GO
SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
    ,S.Name AS N'Store', H.SalesPersonID
    ,SUM(TotalDue) AS N'Total Sales'
FROM Sales.Customer C
    INNER JOIN Sales.Store AS S
        ON C.StoreID  = S.BusinessEntityID 
    INNER JOIN Sales.SalesTerritory AS T
        ON C.TerritoryID  = T.TerritoryID 
    INNER JOIN Sales.SalesOrderHeader AS H
        ON C.CustomerID = H.CustomerID
WHERE T.[Group] = N'Europe'
    AND T.CountryRegionCode IN(N'DE', N'FR')
    AND H.SalesPersonID IN(287, 288, 290)
    AND SUBSTRING(S.Name,1,4)IN(N'Vers', N'Spa ')
GROUP BY GROUPING SETS(
    T.[Group], T.CountryRegionCode
    ,CUBE(S.Name, H.SalesPersonID))
ORDER BY T.[Group], T.CountryRegionCode, S.Name, H.SalesPersonID;

Ниже приводится результирующий набор.

Region

Country

Store

SalesPersonID

Total Sales

NULL

NULL

NULL

NULL

254013.6014

NULL

NULL

NULL

287

28461.1854

NULL

NULL

NULL

288

17073.0655

NULL

NULL

NULL

290

208479.3505

NULL

NULL

Spa and Exercise Outfitters

NULL

236210.9015

NULL

NULL

Spa and Exercise Outfitters

287

27731.551

NULL

NULL

Spa and Exercise Outfitters

290

208479.3505

NULL

NULL

Versatile Sporting Goods Company

NULL

17802.6999

NULL

NULL

Versatile Sporting Goods Company

287

729.6344

NULL

NULL

Versatile Sporting Goods Company

288

17073.0655

NULL

DE

NULL

NULL

17802.6999

NULL

FR

NULL

NULL

236210.9015

Europe

NULL

NULL

NULL

254013.6014