GROUP BY opties in Synapse SQL

Met Synapse SQL kunt u oplossingen ontwikkelen door verschillende GROUP BY-opties te implementeren.

Wat GROUP BY doet

Met de GROUP BY T-SQL-component worden gegevens samengevoegd tot een samenvattingsset rijen.

Serverloze SQL-pool biedt geen ondersteuning voor GROUP BY-opties. Toegewezen SQL-pool ondersteunt een beperkt aantal GROUP BY-opties.

GROUP BY-opties die worden ondersteund in een toegewezen SQL-pool

GROUP BY heeft enkele opties die toegewezen SQL-pool niet ondersteunt. Deze opties hebben tijdelijke oplossingen, die als volgt zijn:

• GROUP BY met ROLLUP
• GROEPERINGSSETS
• GROEPEREN OP met KUBUS

Opties voor samenvouwen en groeperen

De eenvoudigste optie hier is om UNION ALL te gebruiken om het samenvouwen uit te voeren in plaats van te vertrouwen op de expliciete syntaxis. Het resultaat is precies hetzelfde

In het volgende voorbeeld wordt de GROUP BY-instructie gebruikt met de optie 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]
                )
;

Door ROLLUP te gebruiken, vraagt het voorgaande voorbeeld de volgende aggregaties aan:

• Land en regio
• Land/regio
• Eindtotaal

Als u ROLLUP wilt vervangen en dezelfde resultaten wilt retourneren, kunt u UNION ALL gebruiken en expliciet de vereiste aggregaties opgeven:

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;

Het voorbeeldprincipe is van toepassing om GROUPING SETS te vervangen. U hoeft alleen UNION ALL-secties te maken voor de aggregatieniveaus die u wilt zien.

Kubus opties

Het is mogelijk om een GROUP BY WITH CUBE te maken met behulp van de BENADERING UNION ALL. Het probleem is dat de code snel omslachtig en onhandig kan worden. U kunt dit probleem oplossen door deze geavanceerdere benadering te gebruiken.

De eerste stap is het definiëren van de 'kubus' die alle aggregatieniveaus definieert die we willen maken. Let op de CROSS JOIN van de twee afgeleide tabellen omdat deze alle niveaus genereert. De rest van de code is beschikbaar voor opmaak.

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;

In de volgende afbeelding ziet u de resultaten van CREATE TABLE AS SELECT:

De tweede stap is het opgeven van een doeltabel voor het opslaan van tussentijdse resultaten:

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

De derde stap bestaat uit het doorlopen van de kubus met kolommen die de aggregatie uitvoeren. De query wordt eenmaal uitgevoerd voor elke rij in de #Cube tijdelijke tabel. Resultaten worden opgeslagen in de #Results tijdelijke tabel:

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

Ten slotte kunt u de resultaten retourneren door de #Results tijdelijke tabel te lezen:

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

Door de code op te delen in secties en een lusconstructie te genereren, wordt de code beter beheersbaar en onderhoudbaar.

Volgende stappen

Zie ontwikkelingsoverzicht voor meer ontwikkelingstips.