Gruppieren nach Optionen für dedizierte SQL-Pools in Azure Synapse Analytics

In diesem Artikel finden Sie Tipps für die Implementierung von Gruppen nach Optionen in dedizierten SQL-Pools.

Was macht GROUP BY?

Die GROUP BY T-SQL-Klausel aggregiert Daten zu einem Sammelsatz von Zeilen. 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.

Im vorherigen Beispiel wird im ersten Schritt der "Cube" definiert, der alle Aggregationsebenen definiert, die wir erstellen möchten.

Beachten Sie die CROSS JOIN der beiden abgeleiteten Tabellen, da dadurch alle Ebenen erstellt werden. 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 des CTAS:

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 unser Aggregat von Spalten zu iterieren, um die Aggregation durchzuführen. Die Abfrage wird einmal für jede Zeile in der #Cube temporären Tabelle ausgeführt. Die 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.