Groeperen op opties voor toegewezen SQL-pools in Azure Synapse Analytics

In dit artikel vindt u tips voor het implementeren van groeperen op opties 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 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 CUBE

Opties voor samenvouwen en groeperen

De eenvoudigste optie 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-statement gebruikt met de ROLLUP-optie.

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.

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 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. De resultaten worden opgeslagen in de tabel #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

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.