Exportar (0) Imprimir
Expandir todo
Personas que lo han encontrado útil: 2 de 2 - Valorar este tema

Utilizar GROUP BY con ROLLUP, CUBE y GROUPING SETS

Los operadores ROLLUP, CUBE y GROUPING SETS son extensiones de la cláusula GROUP BY. Los operadores ROLLUP, CUBE y GROUPING SETS pueden generar el mismo conjunto de resultados que al utilizar UNION ALL para combinar consultas de agrupación única; aunque utilizar uno de los operadores GROUP BY suele ser más eficaz.

El operador GROUPING SETS puede generar el mismo conjunto de resultados que el generado por medio de un operador GROUP BY, ROLLUP o CUBE simple. Cuando no se requieren todas las agrupaciones que se generan utilizando un operador ROLLUP o CUBE completo, se puede utilizar GROUPING SETS para especificar sólo las agrupaciones que se deseen. La lista GROUPING SETS puede contener agrupaciones duplicadas, y al utilizar GROUPING SETS con ROLLUP y CUBE, podría generar agrupaciones duplicadas. Las agrupaciones duplicadas se retienen igual que si se utilizara UNION ALL.

NotaNota

CUBE, ROLLUP y GROUPING SETS no admiten la función CHECKSUM_AGG.

Los grupos de columnas que están entre paréntesis internos en la lista GROUPING SETS son tratadas como un único conjunto. Por ejemplo, en la cláusula GROUP BY GROUPING SETS ((Colum1, Column2), Column3, Column4), Column1 y Column2 se tratan como una columna. Para obtener un ejemplo de cómo utilizar GROUPING SETS con elementos compuestos, vea el ejemplo H posterior en este tema.

Cuando la lista GROUPING SETS contiene varios conjuntos entre paréntesis internos, separados por comas, el resultado de los conjuntos se concatena. El conjunto de resultados es el producto cruzado o producto cartesiano de los conjuntos de agrupamiento. Para obtener un ejemplo de cómo utilizar GROUP BY con operaciones ROLLUP concatenadas, vea el ejemplo D más adelante en este tema.

Las consultas que usan los operadores ROLLUP y CUBE generan algunos de los conjuntos de resultados y realizan algunos de los cálculos que lleva a cabo las aplicaciones OLAP. El operador CUBE genera un conjunto de resultados que se puede utilizar en los informes de tabulación cruzada. Una operación ROLLUP puede calcular el equivalente de una dimensión o jerarquía OLAP.

Por ejemplo, dada una dimensión de tiempo con los niveles o atributos año, mes y día, la siguiente operación ROLLUP genera las agrupaciones siguientes.

Operación

Agrupaciones

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

year, month, day

year, month

year

()

Dada una dimensión de ubicación con los niveles región y ciudad concatenados con los niveles de dimensión de tiempo año, mes y día, la siguiente operación ROLLUP genera las agrupaciones siguientes.

Operación

Agrupaciones

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

()

Una operación CUBE de los mismos niveles desde las dimensiones de ubicación y tiempo da como resultado las agrupaciones siguientes.

Operación

Grouping

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

()

En los conjuntos de resultados generados por los operadores GROUP BY, NULL tiene los usos siguientes:

  • Si una columna de agrupamiento contiene varios valores NULL, todos ellos se consideran equivalentes y se colocan en un grupo NULL individual.

  • Cuando se agrega una columna de forma consecutiva, el valor de la columna se muestra como NULL.

El ejemplo siguiente utiliza la función GROUPING para mostrar los dos usos de NULL. UNKNOWN reemplaza a NULL en filas en donde los valores nulos en una columna han sido agrupados. ALL reemplaza a NULL en una columna en donde NULL indica que se ha incluido una columna en una agregación.

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);

El conjunto de resultados es el siguiente.

Store

SalesYear

SalesMonth

Count

Desconocido

Desconocido

Desconocido

1

Desconocido

Desconocido

January

1

Desconocido

Desconocido

ALL

2

Desconocido

2002

Desconocido

1

Desconocido

2002

ALL

1

Desconocido

ALL

ALL

3

Active Cycling

Desconocido

Desconocido

1

Active Cycling

Desconocido

January

2

Active Cycling

Desconocido

ALL

3

Active Cycling

2002

Desconocido

1

Active Cycling

2002

ALL

1

Active Cycling

2003

Desconocido

1

Active Cycling

2003

February

1

Active Cycling

2003

ALL

2

Active Cycling

ALL

ALL

6

Mountain Bike Store

Desconocido

Desconocido

1

Mountain Bike Store

Desconocido

ALL

1

Mountain Bike Store

2002

Desconocido

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

Los ejemplos de esta sección utilizan la función de agregado SUM para que se puedan comparar los conjuntos de resultados. También se podrían utilizar las otras funciones de agregado para calcular resúmenes diferentes.

A. Usar un GROUP BY simple

En el ejemplo siguiente, el GROUP BY simple devuelve un conjunto de resultados para comparar con los conjuntos de resultados de los ejemplos B a K. Estos ejemplos utilizan los operadores GROUP BY con la misma instrucción 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;

El conjunto de resultados es el siguiente.

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

B. Usar GROUP BY ROLLUP

En el ejemplo siguiente, el operador ROLLUP devuelve un conjunto de resultados que contiene las agrupaciones siguientes:

  • Region, Country, Store y SalesPersonID.

  • Region, Country y Store

  • Region y Country

  • Region

  • total general

El número de agrupaciones generadas por ROLLUP es igual al número de columnas en la lista ROLLUP más una agrupación de total general. El número de combinaciones únicas de valores en las columnas de la agrupación determina el número de filas en esa agrupación.

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;

El conjunto de resultados es el siguiente.

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

C. Usar GROUP BY ROLLUP con el orden de las columnas invertido

En el ejemplo siguiente, el operador ROLLUP devuelve un conjunto de resultados que contiene las agrupaciones siguientes:

  • SalesPersonID, Store, Country y Region.

  • SalesPersonID, Store y Country

  • SalesPersonID y Store

  • SalesPersonID

  • total general

Las columnas de la lista ROLLUP son las mismas que las del ejemplo B, pero están en el orden inverso. Las columnas se resumen de derecha a izquierda; por consiguiente, el orden afecta a las agrupaciones. El número de filas en el conjunto de resultados podría variar en función del orden de las columnas.

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];

El conjunto de resultados es el siguiente.

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

D. Usar GROUP BY en operaciones ROLLUP concatenadas

En este ejemplo se obtiene el producto cruzado de las dos operaciones 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);

El conjunto de resultados es el siguiente.

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

E. Usar GROUP BY CUBE

En el ejemplo siguiente, el operador CUBE devuelve un conjunto de resultados que tiene una agrupación para todas las combinaciones de columnas posibles de la lista CUBE y una agrupación de total general.

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;

El conjunto de resultados es el siguiente.

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

F. Usar CUBE con elementos compuestos

En el ejemplo siguiente, el operador CUBE devuelve un conjunto de resultados que tiene una agrupación para todas las posibles combinaciones de columnas en la lista CUBE y una agrupación de total general.

El operador procesa cada una de las columnas agrupadas (T.[Group], T.CountryRegionCode) y (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)) como una sola columna.

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);

El conjunto de resultados es el siguiente.

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

G. Usar GROUP BY con GROUPING SETS

En el ejemplo siguiente, el operador GROUPING SETS tiene cuatro agrupaciones, una para cada columna de la lista SELECT. El operador devuelve una fila por cada valor único en las columnas Region, Country, Store, y 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;

El conjunto de resultados es el siguiente.

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

H. Usar GROUPING SETS con elementos compuestos

En el ejemplo siguiente, la lista GROUPING SETS contiene dos elementos compuestos, (T.[Group], T.CountryRegionCode) y (DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate)). Cada elemento compuesto se trata como una columna.

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);

El conjunto de resultados es el siguiente.

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

I. Usar GROUP BY con múltiples GROUPING SETS

En el ejemplo siguiente, la lista GROUPING SETS tiene cinco elementos. El conjunto de resultados tiene una fila para los elementos siguientes:

  • Cada combinación única de valores en las columnas Region y Country

  • Cada valor único en la columna Store

  • Cada combinación única de valores en las columnas SalesPersonID y Region

  • Cada valor único en la columna SalesPersonID

  • Un total general

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;

El conjunto de resultados es el siguiente.

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

J. Usar GROUPING SETS con un ROLLUP de parte de la lista GROUP BY

En el ejemplo siguiente, la lista GROUPING SETS incluye agrupaciones para las columnas T.[Group] y T.CountryRegionCode y un ROLLUP de las columnas S.Name y 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;

El conjunto de resultados es el siguiente.

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

K. Usar GROUPING SETS con un CUBE de parte de la lista GROUP BY

En el ejemplo siguiente, la lista GROUPING SETS incluye agrupaciones para las columnas T.[Group] y T.CountryRegionCode y un CUBE de las columnas S.Name y 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;

El conjunto de resultados es el siguiente.

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

¿Te ha resultado útil?
(Caracteres restantes: 1500)
Gracias por sus comentarios

Adiciones de comunidad

AGREGAR
Mostrar:
© 2014 Microsoft. Reservados todos los derechos.