A GROUP BY egyf\u00e9le szempont, ak\u00e1rh\u00e1ny oszlop vagy kifejez\u00e9s alapj\u00e1n csoportos\u00edt, azt\u00e1n aggreg\u00e1l\u00f3 f\u00fcggv\u00e9nyekkel trancs\u00edrozzuk a t\u00f6bbi oszlop adatait. Id\u0151nk\u00e9nt azonban t\u00f6bbf\u00e9le szempont szerint is kellene csoportos\u00edtani. Pl. elad\u00e1sok \u00e9v alapj\u00e1n, elad\u00e1sok \u00e9v \u00e9s term\u00e9k alapj\u00e1n, elad\u00e1sok csak \u00fagy, \u00f6sszesen, stb. Ezt meg lehet tenni t\u00f6bb lek\u00e9rdez\u00e9s UNION ALL-j\u00e1val, amelyek eleje majdnem ugyanaz, csak a group by-ok m\u00e1sok.
\nValami hasonl\u00f3t csin\u00e1lt m\u00e1r az SQL aszem 7-ben bevezetett CUBE \u00e9s ROLLUP z\u00e1rad\u00e9k a group by ut\u00e1n. Ezekbe be volt \u00e9p\u00edtve, hogy ne csak a megjel\u00f6lt oszlopok szerint csoportos\u00edtsanak \u00e9s aggreg\u00e1ljanak, hanem egyre t\u00f6bb csoportos\u00edt\u00f3 oszlopot elhagyva egyre durv\u00e1bb, egyre nagyobb \u00e1tfog\u00e1ssal sz\u00e1moljanak, lsd. az els\u0151 r\u00e9sz elad\u00e1sos p\u00e9ld\u00e1ja.<\/p>\n
No, az \u00faj grouping set z\u00e1rad\u00e9k a group by ut\u00e1n arra szolg\u00e1l, hogy mi, explicit megadhassunk t\u00f6bb csoportos\u00edt\u00f3 felt\u00e9telt is, \u00edgy t\u00f6bbf\u00e9le dimenzi\u00f3 ment\u00e9n aggreg\u00e1lhassunk. Azaz, ha akarunk eljuthatunk a cube illetve rollup-ig is, de kihagyhatunk bizonyos szempontokat is. Ez nem m\u00e1s teh\u00e1t, mint egy \u00e1tmenet a sima egyes group by \u00e9s a mindenf\u00e9le kombin\u00e1ci\u00f3ban aggreg\u00e1l\u00f3 cube k\u00f6z\u00f6tt.<\/p>\n
Egy p\u00e9lda mindent megmagyar\u00e1z:<\/p>\n
\r\nSELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount\r\nFROM dbo.FactResellerSales F\r\nINNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey\r\nINNER JOIN dbo.DimSalesTerritory T ON\r\nF.SalesTerritoryKey = T.SalesTerritoryKey\r\nGROUP BY GROUPING SETS (\r\n(CalendarYear, CalendarQuarter, SalesTerritoryCountry),\r\n(CalendarYear, CalendarQuarter),\r\n(SalesTerritoryCountry),\r\n())\r\nORDER BY CalendarYear, CalendarQuarter, SalesTerritoryCountry\r\n<\/pre>\n(A t\u00e1bl\u00e1zat a webes kiad\u00e1sban csak g\u00f6rget\u00e9ssel l\u00e1tszik teljes eg\u00e9sz\u00e9ben, az rssben szerintem teljes sz\u00e9less\u00e9g\u00e9ben l\u00e1tszani fog.)<\/p>\n
\r\nCalendarYear CalendarQuarter SalesTerritoryCountry SalesAmount\r\n------------ --------------- ------------------------------ ---------------------\r\nNULL NULL NULL 80450596.9823\r\nNULL NULL Australia 1594335.3767\r\nNULL NULL Canada 14377925.5965\r\nNULL NULL France 4607537.935\r\nNULL NULL Germany 1983988.0373\r\nNULL NULL United Kingdom 4279008.8266\r\nNULL NULL United States 53607801.2102\r\n2001 3 NULL 3193633.9687\r\n2001 3 Canada 637982.8445\r\n2001 3 United States 2555651.1242\r\n2001 4 NULL 4871801.3366\r\n2001 4 Canada 875376.6118\r\n2001 4 United States 3996424.7248\r\n2002 1 NULL 4069186.0383\r\n2002 1 Canada 775755.4784\r\n2002 1 United States 3293430.5599\r\n2002 2 NULL 4153820.4239\r\n2002 2 Canada 790691.8802\r\n2002 2 United States 3363128.5437\r\n2002 3 NULL 8880239.4384\r\n2002 3 Canada 1860334.8668\r\n2002 3 France 523999.2337\r\n2002 3 United Kingdom 471783.2311\r\n2002 3 United States 6024122.1068\r\n2002 4 NULL 7041183.7534\r\n2002 4 Canada 1396216.9767\r\n2002 4 France 333123.9447\r\n2002 4 United Kingdom 369974.5296\r\n2002 4 United States 4941868.3024\r\n2003 1 NULL 5266343.505\r\n2003 1 Canada 1008618.4806\r\n2003 1 France 238772.5213\r\n2003 1 United Kingdom 273906.5922\r\n2003 1 United States 3745045.9109\r\n2003 2 NULL 6733903.8214\r\n2003 2 Canada 1349998.8179\r\n2003 2 France 332124.6758\r\n2003 2 United Kingdom 290827.6064\r\n2003 2 United States 4760952.7213\r\n2003 3 NULL 10926196.0872\r\n2003 3 Australia 450884.4054\r\n2003 3 Canada 1744784.0586\r\n2003 3 France 957497.1842\r\n2003 3 Germany 560152.5008\r\n2003 3 United Kingdom 862226.626\r\n2003 3 United States 6350651.3122\r\n2003 4 NULL 9276226.0116\r\n2003 4 Australia 396546.5586\r\n2003 4 Canada 1547904.0683\r\n2003 4 France 845409.6541\r\n2003 4 Germany 538714.1841\r\n2003 4 United Kingdom 733185.0082\r\n2003 4 United States 5214466.5383\r\n2004 1 NULL 7102685.111\r\n2004 1 Australia 340479.429\r\n2004 1 Canada 1024145.7867\r\n2004 1 France 597367.5743\r\n2004 1 Germany 393901.2514\r\n2004 1 United Kingdom 542463.7975\r\n2004 1 United States 4204327.2721\r\n2004 2 NULL 8935377.4868\r\n2004 2 Australia 406424.9837\r\n2004 2 Canada 1366115.726\r\n2004 2 France 779243.1469\r\n2004 2 Germany 491220.101\r\n2004 2 United Kingdom 734641.4356\r\n2004 2 United States 5157732.0936\r\n<\/pre>\nEnnyi az eg\u00e9sz, el\u00e9g egyszer\u0171.<\/p>\n
Egy\u00e9bk\u00e9nt nem csak egyszer\u0171bb a szintax, hanem gyorsabb is a group by groping settel, mert egy menetben csin\u00e1lja meg az \u00f6sszes aggreg\u00e1l\u00e1st, \u00e9s fel tudja haszn\u00e1lni a finomabb felbont\u00e1s\u00fa aggreg\u00e1lt eredm\u00e9nyeket a durv\u00e1bban. Pl.<\/p>\n
\r\n\r\n--Alap\r\n--Cost: 1.92, IO: 1613\r\nSELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount\r\nFROM dbo.FactResellerSales F\r\nINNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey\r\nINNER JOIN dbo.DimSalesTerritory T ON\r\nF.SalesTerritoryKey = T.SalesTerritoryKey\r\nGROUP BY GROUPING SETS (\r\n(CalendarYear, CalendarQuarter, SalesTerritoryCountry),\r\n(CalendarYear, CalendarQuarter),\r\n(SalesTerritoryCountry),\r\n())\r\n\r\n--Darabonk\u00e9ti\r\n--Cost: 5.87, IO: 6821\r\nSELECT D.CalendarYear, D.CalendarQuarter, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount\r\nFROM dbo.FactResellerSales F\r\nINNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey\r\nINNER JOIN dbo.DimSalesTerritory T ON\r\nF.SalesTerritoryKey = T.SalesTerritoryKey\r\nGROUP BY CalendarYear, CalendarQuarter, SalesTerritoryCountry\r\n\r\nUNION ALL\r\n\r\nSELECT D.CalendarYear, D.CalendarQuarter, NULL, SUM(F.SalesAmount) AS SalesAmount\r\nFROM dbo.FactResellerSales F\r\nINNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey\r\nINNER JOIN dbo.DimSalesTerritory T ON\r\nF.SalesTerritoryKey = T.SalesTerritoryKey\r\nGROUP BY CalendarYear, CalendarQuarter\r\n\r\nUNION ALL\r\n\r\nSELECT NULL, NULL, T.SalesTerritoryCountry, SUM(F.SalesAmount) AS SalesAmount\r\nFROM dbo.FactResellerSales F\r\nINNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey\r\nINNER JOIN dbo.DimSalesTerritory T ON\r\nF.SalesTerritoryKey = T.SalesTerritoryKey\r\nGROUP BY SalesTerritoryCountry\r\n\r\nUNION ALL\r\n\r\nSELECT NULL, NULL, NULL, SUM(F.SalesAmount) AS SalesAmount\r\nFROM dbo.FactResellerSales F\r\nINNER JOIN dbo.DimDate D ON F.OrderDateKey = D.DateKey\r\nINNER JOIN dbo.DimSalesTerritory T ON\r\nF.SalesTerritoryKey = T.SalesTerritoryKey\r\n<\/pre>\nJelent\u0151s IO \u00e9s k\u00f6lts\u00e9g k\u00fcl\u00f6nbs\u00e9g van, ez plusz \u00f6r\u00f6m, a t\u00f6m\u00f6r form\u00e1tum mellett.<\/p>\n","protected":false},"excerpt":{"rendered":"
A GROUP BY egyf\u00e9le szempont, ak\u00e1rh\u00e1ny oszlop vagy kifejez\u00e9s alapj\u00e1n csoportos\u00edt, azt\u00e1n aggreg\u00e1l\u00f3 f\u00fcggv\u00e9nyekkel trancs\u00edrozzuk a t\u00f6bbi oszlop adatait. Id\u0151nk\u00e9nt azonban t\u00f6bbf\u00e9le…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,58],"tags":[],"class_list":["post-499","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/499","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=499"}],"version-history":[{"count":0,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/499\/revisions"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=499"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=499"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=499"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}