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

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

Vad gör GROUP BY?

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

• GRUPPERA EFTER med ROLLUP
• GRUPPERINGSSTÄLLNINGAR
• GRUPPERA EFTER med CUBE

Alternativ för sammanslagning och grupperingsuppsätt

Det enklaste alternativet här är att använda UNION ALL för att utföra sammanställningen 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;

Om du vill 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 är möjligt att skapa en GROUP BY WITH CUBE med union all-metoden. Problemet är att koden snabbt kan bli besvärlig och svårhanterlig. För att åtgärda det här problemet kan du använda den här mer avancerade metoden.

Med det föregående exemplet är det första steget att definiera den kub som definierar alla aggregeringsnivåer som vi vill skapa.

Notera "CROSS JOIN" för de två härledda tabellerna eftersom detta genererar alla nivåer för vår del. 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 interimsresultat:

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. Resultatet lagras i tabellen #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

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

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.