GROUP BY-alternativ i Synapse SQL

Synapse SQL gör det möjligt att utveckla lösningar genom att implementera olika GROUP BY-alternativ.

Vad GROUP BY gör

GROUP BY T-SQL-satsen aggregerar data till en sammanfattningsuppsättning rader.

Serverlös SQL-pool stöder inte GROUP BY-alternativ. Dedikerad SQL-pool stöder ett begränsat antal GROUP BY-alternativ.

GROUP BY-alternativ som stöds i en dedikerad SQL-pool

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 köra sammanslagningen i stället för att förlita sig på den explicita syntaxen. Resultatet är exakt samma

I följande exempel används GROUP BY-instruktionen med alternativet ROLLUP:

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.

Det första steget är att definiera kuben som definierar alla aggregeringsnivåer som vi vill skapa. Anteckna CROSS JOIN för de två härledda tabellerna när den genererar alla nivåer. 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 CREATE TABLE AS SELECT:

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 kuben 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.