GROUP BY-Optionen in Synapse SQL

Synapse SQL ermöglicht die Entwicklung von Lösungen, indem verschiedene GROUP BY-Optionen implementiert werden.

Funktionsweise von GROUP BY

Die GROUP BY T-SQL-Klausel aggregiert Daten zu einem Sammelsatz von Zeilen.

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

GROUP BY-Optionen, die im dedizierten SQL-Pool unterstützt werden

GROUP BY verfügt über einige Optionen, die vom dedizierten SQL-Pool nicht unterstützt werden. Diese Optionen haben Lösungen, die wie folgt lauten:

• GRUPPIEREN NACH mit ROLLUP
• GRUPPIERUNGSMENGEN
• GRUPPIEREN NACH MIT CUBE

Rollup- und Gruppierungssets-Optionen

Die einfachste Option besteht darin, UNION ALL zum Ausführen des Rollups zu verwenden, anstatt auf die explizite Syntax zu vertrauen. Das Ergebnis ist genau dasselbe

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 fordert das vorangehende Beispiel die folgenden Aggregationen an:

• Land und Region
• Staat
• Gesamtsumme

Um ROLLUP zu ersetzen und dieselben 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;

Zum Ersetzen von GROUPING SETS gilt das Stichprobenprinzip. Sie müssen nur UNION ALL-Abschnitte für die Aggregationsebenen erstellen, die Sie anzeigen möchten.

Cubeoptionen

Es ist möglich, mithilfe des UNION ALL-Ansatzes eine GROUP BY WITH CUBE zu erstellen. Das Problem besteht darin, dass der Code schnell umständlich und unübersichtlich werden kann. Um dieses Problem zu beheben, können Sie diesen erweiterten Ansatz verwenden.

Der erste Schritt besteht darin, den "Cube" zu definieren, der alle Aggregationsebenen definiert, die wir erstellen möchten. Notieren Sie sich die CROSS JOIN der beiden abgeleiteten Tabellen, da sie alle Ebenen generiert. Der rest des Codes ist für die Formatierung vorhanden.

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:

Der zweite Schritt besteht darin, eine Zieltabelle zum Speichern von Zwischenergebnissen anzugeben:

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 besteht darin, über den Würfel der Spalten zu iterieren und die Aggregation durchzuführen. Die Abfrage wird einmal für jede Zeile in der #Cube temporären Tabelle ausgeführt. Ergebnisse werden in der #Results temporären Tabelle 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

Schließlich können Sie die Ergebnisse zurückgeben, indem Sie aus der #Results temporären Tabelle lesen:

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

Durch das Aufteilen des Codes in Abschnitte und Generieren eines Schleifenkonstrukts wird der Code besser verwaltbar und wartbar.

Nächste Schritte

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