Gruppera efter alternativ för dedikerade SQL-pooler i Azure Synapse Analytics

I den här artikeln hittar du tips för att implementera alternativ för gruppera efter i dedikerade SQL-pooler.

Vad gör GROUP BY?

GROUP BY T-SQL-satsen aggregerar data till en sammanfattningsuppsättning rader. GROUP BY har vissa alternativ som den dedikerade SQL-poolen inte stöder. De här alternativen har lösningar som är följande:

• GRUPPERA EFTER med SAMMANSLAGNING
• GRUPPERINGSUPPSÄTTNINGAR
• GRUPPERA EFTER med KUB

Alternativ för sammanslagning och grupperingsuppsättningar

Det enklaste alternativet här är att använda UNION ALL för att utföra sammanslagningen i stället för att förlita sig på den explicita syntaxen. Resultatet är exakt detsamma.

Följande exempel använder GROUP BY-instruktionen med rollup-alternativet:

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

Med hjälp av ROLLUP begär föregående exempel följande sammansättningar:

• Land och region
• Land
• Totalsumma

Om du vill ersätta ROLLUP och returnera samma resultat kan du använda UNION ALL och uttryckligen ange de nödvändiga aggregeringarna:

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 att ersätta GROUPING SETS gäller exempelprincipen. Du behöver bara skapa UNION ALL-avsnitt för de aggregeringsnivåer som du vill se.

Kubalternativ

Det går att skapa en GROUP BY WITH CUBE med union all-metoden. Problemet är att koden snabbt kan bli besvärlig och svårhanterlig. Du kan åtgärda det här problemet genom att använda den här mer avancerade metoden.

I föregående exempel är det första steget att definiera kuben som definierar alla aggregeringsnivåer som vi vill skapa.

Anteckna CROSS JOIN för de två härledda tabellerna eftersom detta genererar alla nivåer åt oss. Resten av koden finns där för formatering:

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;

Följande bild visar resultatet av CTAS:

Det andra steget är att ange en måltabell för lagring av delårsresultat:

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

Det tredje steget är att loopa över vår kub med kolumner som utför aggregeringen. Frågan körs en gång för varje rad i den #Cube temporära tabellen. Resultaten lagras i den #Results temporära tabellen:

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

Slutligen kan du returnera resultatet genom att läsa från den temporära tabellen #Results:

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

Genom att dela upp koden i avsnitt och generera en loopkonstruktion blir koden mer hanterbar och underhållsbar.

Nästa steg

Fler utvecklingstips finns i Utvecklingsöversikt.