GROUP BY-opties in Synapse SQL

Artikel
06/01/2023

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 toegewezen SQL-pool

GROUP BY heeft een aantal opties die niet worden ondersteund door een toegewezen SQL-pool. Deze opties hebben tijdelijke oplossingen, die als volgt zijn:

GROUP BY met ROLLUP
GROEPEERSETS
GROUP BY met KUBUS

Opties voor rollup en groeperingssets

De eenvoudigste optie hier is om UNION ALL te gebruiken om het samengetelde 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]
                )
;

Met behulp van ROLLUP 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;

Ter vervanging van GROUPING SETS is het voorbeeldprincipe van toepassing. U hoeft alleen UNION ALL-secties te maken voor de aggregatieniveaus die u wilt zien.

Kubusopties

Het is mogelijk om een GROUP BY WITH CUBE te maken met behulp van de UNION ALL-benadering. 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. Noteer de CROSS JOIN van de twee afgeleide tabellen terwijl alle niveaus worden gegenereerd. 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:

Groeperen op kubus

De tweede stap bestaat uit 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 herhalen van de kubus met kolommen die de aggregatie uitvoeren. De query wordt eenmaal uitgevoerd voor elke rij in de #Cube tijdelijke tabel. De 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 te lezen in de #Results tijdelijke tabel:

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 Overzicht van ontwikkeling voor meer tips voor ontwikkeling.

Delen via