Opcje GROUP BY w usłudze Synapse SQL
Usługa Synapse SQL umożliwia tworzenie rozwiązań przez zaimplementowanie różnych opcji GROUP BY.
Co robi FUNKCJA GROUP BY
Klauzula GROUP BY T-SQL agreguje dane do zestawu sumarycznych wierszy.
Bezserwerowa pula SQL nie obsługuje opcji GROUP BY. Dedykowana pula SQL obsługuje ograniczoną liczbę opcji GROUP BY.
Opcje GROUP BY obsługiwane w dedykowanej puli SQL
Funkcja GROUP BY ma pewne opcje, których dedykowana pula SQL nie obsługuje. Te opcje mają obejścia, które są następujące:
- GRUPOWANIE WEDŁUG za pomocą FUNKCJI ROLLUP
- ZESTAWY GRUPOWANIA
- GRUPOWANIE WEDŁUG ZA POMOCĄ MODUŁU
Opcje zestawów zestawień i grupowania
Najprostszą opcją jest użycie funkcji UNION ALL do wykonania zestawienia, a nie polegania na jawnej składni. Wynik jest dokładnie taki sam
W poniższym przykładzie użyto instrukcji GROUP BY z opcją ROLLUP:
SELECT [SalesTerritoryCountry]
, [SalesTerritoryRegion]
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey
GROUP BY ROLLUP (
[SalesTerritoryCountry]
, [SalesTerritoryRegion]
)
;
Przy użyciu funkcji ROLLUP powyższy przykład żąda następujących agregacji:
- Kraj i region
- Kraj
- Suma końcowa
Aby zastąpić metodę ROLLUP i zwrócić te same wyniki, możesz użyć funkcji UNION ALL i jawnie określić wymagane agregacje:
SELECT [SalesTerritoryCountry]
, [SalesTerritoryRegion]
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey
GROUP BY
[SalesTerritoryCountry]
, [SalesTerritoryRegion]
UNION ALL
SELECT [SalesTerritoryCountry]
, NULL
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey
GROUP BY
[SalesTerritoryCountry]
UNION ALL
SELECT NULL
, NULL
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t ON s.SalesTerritoryKey = t.SalesTerritoryKey;
Aby zastąpić ZESTAWY GRUPOWANIA, stosuje się przykładową zasadę. Musisz utworzyć tylko sekcje UNION ALL dla poziomów agregacji, które chcesz zobaczyć.
Opcje modułu
Istnieje możliwość utworzenia modułu GROUP BY WITH CUBE przy użyciu podejścia UNION ALL. Problem polega na tym, że kod może szybko stać się kłopotliwy i nieporęczny. Aby rozwiązać ten problem, możesz użyć tego bardziej zaawansowanego podejścia.
Pierwszym krokiem jest zdefiniowanie modułu definiującego wszystkie poziomy agregacji, które chcemy utworzyć. Zanotuj element CROSS JOIN dwóch tabel pochodnych, ponieważ generuje wszystkie poziomy. Pozostała część kodu służy do formatowania.
CREATE TABLE #Cube
WITH
( DISTRIBUTION = ROUND_ROBIN
, LOCATION = USER_DB
)
AS
WITH GrpCube AS
(SELECT CAST(ISNULL(Country,'NULL')+','+ISNULL(Region,'NULL') AS NVARCHAR(50)) as 'Cols'
, CAST(ISNULL(Country+',','')+ISNULL(Region,'') AS NVARCHAR(50)) as 'GroupBy'
, ROW_NUMBER() OVER (ORDER BY Country) as 'Seq'
FROM ( SELECT 'SalesTerritoryCountry' as Country
UNION ALL
SELECT NULL
) c
CROSS JOIN ( SELECT 'SalesTerritoryRegion' as Region
UNION ALL
SELECT NULL
) r
)
SELECT Cols
, CASE WHEN SUBSTRING(GroupBy,LEN(GroupBy),1) = ','
THEN SUBSTRING(GroupBy,1,LEN(GroupBy)-1)
ELSE GroupBy
END AS GroupBy --Remove Trailing Comma
,Seq
FROM GrpCube;
Na poniższej ilustracji przedstawiono wyniki polecenia CREATE TABLE AS SELECT:
Drugim krokiem jest określenie tabeli docelowej do przechowywania wyników tymczasowych:
DECLARE
@SQL NVARCHAR(4000)
,@Columns NVARCHAR(4000)
,@GroupBy NVARCHAR(4000)
,@i INT = 1
,@nbr INT = 0
;
CREATE TABLE #Results
(
[SalesTerritoryCountry] NVARCHAR(50)
,[SalesTerritoryRegion] NVARCHAR(50)
,[TotalSalesAmount] MONEY
)
WITH
( DISTRIBUTION = ROUND_ROBIN
, LOCATION = USER_DB
)
;
Trzecim krokiem jest zapętlenie modułu kolumn wykonujących agregację. Zapytanie będzie uruchamiane raz dla każdego wiersza w tabeli tymczasowej #Cube. Wyniki są przechowywane w tabeli #Results temp:
SET @nbr =(SELECT MAX(Seq) FROM #Cube);
WHILE @i<=@nbr
BEGIN
SET @Columns = (SELECT Cols FROM #Cube where seq = @i);
SET @GroupBy = (SELECT GroupBy FROM #Cube where seq = @i);
SET @SQL ='INSERT INTO #Results
SELECT '+@Columns+'
, SUM(SalesAmount) AS TotalSalesAmount
FROM dbo.factInternetSales s
JOIN dbo.DimSalesTerritory t
ON s.SalesTerritoryKey = t.SalesTerritoryKey
'+CASE WHEN @GroupBy <>''
THEN 'GROUP BY '+@GroupBy ELSE '' END
EXEC sp_executesql @SQL;
SET @i +=1;
END
Na koniec możesz zwrócić wyniki, odczytując z tabeli tymczasowej #Results:
SELECT *
FROM #Results
ORDER BY 1,2,3
;
Dzieląc kod na sekcje i generując konstrukcję pętli, kod staje się bardziej czytelny i możliwy do utrzymania.
Następne kroki
Aby uzyskać więcej porad dotyczących programowania, zobacz Omówienie programowania.