Soci (Soczó Zsolt) szakmai blogja

2008.04.10.

SQL Server 2008 újdonságok 35. – GROUPING SETS

Filed under: Adatbázisok,SQL Server,SQL Server 2008,Szakmai élet — Soczó Zsolt @ 09:20

A GROUP BY egyféle szempont, akárhány oszlop vagy kifejezés alapján csoportosít, aztán aggregáló függvényekkel trancsírozzuk a többi oszlop adatait. Időnként azonban többféle szempont szerint is kellene csoportosítani. Pl. eladások év alapján, eladások év és termék alapján, eladások csak úgy, összesen, stb. Ezt meg lehet tenni több lekérdezés UNION ALL-jával, amelyek eleje majdnem ugyanaz, csak a group by-ok mások.
Valami hasonlót csinált már az SQL aszem 7-ben bevezetett CUBE és ROLLUP záradék a group by után. Ezekbe be volt építve, hogy ne csak a megjelölt oszlopok szerint csoportosítsanak és aggregáljanak, hanem egyre több csoportosító oszlopot elhagyva egyre durvább, egyre nagyobb átfogással számoljanak, lsd. az első rész eladásos példája.

No, az új grouping set záradék a group by után arra szolgál, hogy mi, explicit megadhassunk több csoportosító feltételt is, így többféle dimenzió mentén aggregálhassunk. Azaz, ha akarunk eljuthatunk a cube illetve rollup-ig is, de kihagyhatunk bizonyos szempontokat is. Ez nem más tehát, mint egy átmenet a sima egyes group by és a mindenféle kombinációban aggregáló cube között.

Egy példa mindent megmagyaráz:

SELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F
INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey
INNER JOIN dbo.DimSalesTerritory T ON
F.SalesTerritoryKey = T.SalesTerritoryKey
GROUP BY GROUPING SETS (
(CalendarYear, CalendarQuarter, SalesTerritoryCountry),
(CalendarYear, CalendarQuarter),
(SalesTerritoryCountry),
())
ORDER BY CalendarYear, CalendarQuarter, SalesTerritoryCountry

(A táblázat a webes kiadásban csak görgetéssel látszik teljes egészében, az rssben szerintem teljes szélességében látszani fog.)

CalendarYear CalendarQuarter SalesTerritoryCountry          SalesAmount
------------ --------------- ------------------------------ ---------------------
NULL         NULL            NULL                           80450596.9823
NULL         NULL            Australia                      1594335.3767
NULL         NULL            Canada                         14377925.5965
NULL         NULL            France                         4607537.935
NULL         NULL            Germany                        1983988.0373
NULL         NULL            United Kingdom                 4279008.8266
NULL         NULL            United States                  53607801.2102
2001         3               NULL                           3193633.9687
2001         3               Canada                         637982.8445
2001         3               United States                  2555651.1242
2001         4               NULL                           4871801.3366
2001         4               Canada                         875376.6118
2001         4               United States                  3996424.7248
2002         1               NULL                           4069186.0383
2002         1               Canada                         775755.4784
2002         1               United States                  3293430.5599
2002         2               NULL                           4153820.4239
2002         2               Canada                         790691.8802
2002         2               United States                  3363128.5437
2002         3               NULL                           8880239.4384
2002         3               Canada                         1860334.8668
2002         3               France                         523999.2337
2002         3               United Kingdom                 471783.2311
2002         3               United States                  6024122.1068
2002         4               NULL                           7041183.7534
2002         4               Canada                         1396216.9767
2002         4               France                         333123.9447
2002         4               United Kingdom                 369974.5296
2002         4               United States                  4941868.3024
2003         1               NULL                           5266343.505
2003         1               Canada                         1008618.4806
2003         1               France                         238772.5213
2003         1               United Kingdom                 273906.5922
2003         1               United States                  3745045.9109
2003         2               NULL                           6733903.8214
2003         2               Canada                         1349998.8179
2003         2               France                         332124.6758
2003         2               United Kingdom                 290827.6064
2003         2               United States                  4760952.7213
2003         3               NULL                           10926196.0872
2003         3               Australia                      450884.4054
2003         3               Canada                         1744784.0586
2003         3               France                         957497.1842
2003         3               Germany                        560152.5008
2003         3               United Kingdom                 862226.626
2003         3               United States                  6350651.3122
2003         4               NULL                           9276226.0116
2003         4               Australia                      396546.5586
2003         4               Canada                         1547904.0683
2003         4               France                         845409.6541
2003         4               Germany                        538714.1841
2003         4               United Kingdom                 733185.0082
2003         4               United States                  5214466.5383
2004         1               NULL                           7102685.111
2004         1               Australia                      340479.429
2004         1               Canada                         1024145.7867
2004         1               France                         597367.5743
2004         1               Germany                        393901.2514
2004         1               United Kingdom                 542463.7975
2004         1               United States                  4204327.2721
2004         2               NULL                           8935377.4868
2004         2               Australia                      406424.9837
2004         2               Canada                         1366115.726
2004         2               France                         779243.1469
2004         2               Germany                        491220.101
2004         2               United Kingdom                 734641.4356
2004         2               United States                  5157732.0936

Ennyi az egész, elég egyszerű.

Egyébként nem csak egyszerűbb a szintax, hanem gyorsabb is a group by groping settel, mert egy menetben csinálja meg az összes aggregálást, és fel tudja használni a finomabb felbontású aggregált eredményeket a durvábban. Pl.


--Alap
--Cost: 1.92, IO: 1613
SELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F
INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey
INNER JOIN dbo.DimSalesTerritory T ON
F.SalesTerritoryKey = T.SalesTerritoryKey
GROUP BY GROUPING SETS (
(CalendarYear, CalendarQuarter, SalesTerritoryCountry),
(CalendarYear, CalendarQuarter),
(SalesTerritoryCountry),
())

--Darabonkéti
--Cost: 5.87, IO: 6821
SELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F
INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey
INNER JOIN dbo.DimSalesTerritory T ON
F.SalesTerritoryKey = T.SalesTerritoryKey
GROUP BY CalendarYear, CalendarQuarter, SalesTerritoryCountry

UNION ALL

SELECT D.CalendarYear, D.CalendarQuarter, NULL, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F
INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey
INNER JOIN dbo.DimSalesTerritory T ON
F.SalesTerritoryKey = T.SalesTerritoryKey
GROUP BY CalendarYear, CalendarQuarter

UNION ALL

SELECT NULL, NULL, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F
INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey
INNER JOIN dbo.DimSalesTerritory T ON
F.SalesTerritoryKey = T.SalesTerritoryKey
GROUP BY SalesTerritoryCountry

UNION ALL

SELECT NULL, NULL, NULL, SUM(F.SalesAmount) AS SalesAmount
FROM dbo.FactResellerSales F
INNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey
INNER JOIN dbo.DimSalesTerritory T ON
F.SalesTerritoryKey = T.SalesTerritoryKey

Jelentős IO és költség különbség van, ez plusz öröm, a tömör formátum mellett.

No Comments

No comments yet.

RSS feed for comments on this post.

Sorry, the comment form is closed at this time.

Powered by WordPress