Opties groeperen op voor toegewezen SQL-pools in Azure Synapse Analytics

In dit artikel vindt u tips voor het implementeren van opties voor groeperen op in toegewezen SQL-pools.

Wat doet GROUP BY?

Met de GROUP BY T-SQL-component worden gegevens samengevoegd tot een samenvattingsset rijen. GROUP BY heeft een aantal opties die de toegewezen SQL-pool niet ondersteunt. Deze opties hebben tijdelijke oplossingen, die als volgt zijn:

• GROUP BY met ROLLUP
• GROEPERINGSSETS
• 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 deze geavanceerdere benadering gebruiken om dit probleem te verhelpen.

Met behulp van het vorige voorbeeld is de eerste stap het definiëren van de 'kubus' die alle aggregatieniveaus definieert die we willen maken.

Noteer de CROSS JOIN van de twee afgeleide tabellen, omdat hiermee alle niveaus voor ons 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 de CTAS:

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 doorlopen van de kubus met kolommen die de aggregatie uitvoeren. De query wordt één keer 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 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 Overzicht van ontwikkeling voor meer tips voor ontwikkeling.