GROUP BY-Optionen in Synapse SQL

Artikel
06/01/2023

Synapse SQL ermöglicht die Entwicklung von Lösungen durch Implementierung verschiedener GROUP BY-Optionen.

Funktionsweise von GROUP BY

Die T-SQL-Klausel GROUP BY fasst Daten zu einer Gruppe von Zeilen zusammen.

Der serverlose SQL-Pool unterstützt keine GROUP BY-Optionen. Der dedizierte SQL-Pool unterstützt eine begrenzte Anzahl von GROUP BY-Optionen.

Im dedizierten SQL-Pool unterstützte GROUP BY-Optionen

GROUP BY bietet einige Optionen, die der dedizierte SQL-Pool nicht unterstützt. Für diese Optionen gibt es jedoch Problemumgehungen, die wie folgt lauten:

GROUP BY mit ROLLUP
GROUPING SETS
GROUP BY mit CUBE

Rollup- und Grouping Set-Optionen

Die einfachste Lösung ist, anstelle der expliziten Syntax UNION ALL für die Ausführung des Rollups zu verwenden. Das Ergebnis ist identisch.

Im folgenden Beispiel wird die GROUP BY-Anweisung mit der ROLLUP-Option verwendet:

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

Mithilfe von ROLLUP werden im obigen Beispiel die folgenden Aggregationen angefordert:

Land und Region
Country
Grand Total

Um ROLLUP zu ersetzen und die gleichen Ergebnisse zurückzugeben, können Sie UNION ALL verwenden und explizit die erforderlichen Aggregationen angeben:

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;

Für den Ersatz von GROUPING SETS gelten die Beispielprinzipien. Sie müssen nur die UNION ALL-Abschnitte für die Aggregationsebenen erstellen, die angezeigt werden sollen.

Cube-Optionen

Es ist möglich, mit dem UNION ALL-Ansatz eine GROUP BY WITH CUBE-Klausel zu erstellen. Das Problem ist, dass der Code schnell unübersichtlich und schwerfällig werden kann. Dies lässt sich vermeiden, indem Sie diesen erweiterten Ansatz verwenden.

Der erste Schritt ist das Definieren des „Cubes“, mit dem alle Aggregationsebenen festgelegt werden, die erstellt werden sollen. Beachten Sie den CROSS JOIN der beiden abgeleiteten Tabellen, da dadurch alle Ebenen generiert werden. Der übrige Code dient nur der Formatierung.

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;

Die folgende Abbildung zeigt die Ergebnisse von CREATE TABLE AS SELECT:

GROUP BY mit CUBE

Der zweite Schritt ist das Angeben einer Zieltabelle zum Speichern von Zwischenergebnissen:

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

Der dritte Schritt ist das Durchführen einer Schleife für den Cube mit den Spalten, um die Aggregation durchzuführen. Die Abfrage wird für jede Zeile in der temporären Tabelle „#Cube“ einmal ausgeführt. Die Ergebnisse werden in der temporären Tabelle „#Results“ gespeichert:

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

Abschließend können Sie die Ergebnisse zurückgeben, indem Sie sie aus der temporären Tabelle „#Results“ auslesen:

SELECT *
FROM #Results
ORDER BY 1,2,3
;

Indem der Code in Abschnitte unterteilt und ein Schleifenkonstrukt generiert wird, lässt sich der Code leichter verwalten und instand halten.

Nächste Schritte

Weitere Hinweise zur Entwicklung finden Sie in der Entwicklungsübersicht.

Freigeben über