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.
Could you hire me? Contact me if you like what I’ve done in this article and think I can create value for your company with my skills.