Grupuj według opcji dedykowanych pul SQL w usłudze Azure Synapse Analytics

W tym artykule znajdziesz porady dotyczące implementowania grupowania według opcji w dedykowanych pulach SQL.

Co robi klauzula GROUP BY?

Klauzula GROUP BY T-SQL agreguje dane do zestawu sumarycznych wierszy. Funkcja GROUP BY ma pewne opcje, których dedykowana pula SQL nie obsługuje. Te opcje mają obejścia, które są następujące:

• GROUP BY with ROLLUP
• GROUPING SETS
• GROUP BY with CUBE

Rollup and grouping sets options

The simplest option here is to use UNION ALL to perform the rollup rather than relying on the explicit syntax. Wynik jest dokładnie taki sam.

Poniższy przykład z użyciem instrukcji GROUP BY z opcją 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]
                )
;

Przy użyciu funkcji ROLLUP powyższy przykład żąda następujących agregacji:

• Kraj i region
• Kraj
• Suma końcowa

Aby zastąpić metodę ROLLUP i zwrócić te same wyniki, możesz użyć funkcji UNION ALL i jawnie określić wymagane agregacje:

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;

Aby zastąpić ZESTAWY GRUPOWANIA, stosuje się przykładową zasadę. Musisz utworzyć tylko sekcje UNION ALL dla poziomów agregacji, które chcesz zobaczyć.

Cube options

Istnieje możliwość utworzenia modułu GROUP BY WITH CUBE przy użyciu podejścia UNION ALL. Problem polega na tym, że kod może szybko stać się kłopotliwy i nieporęczny. Aby rozwiązać ten problem, możesz użyć tego bardziej zaawansowanego podejścia.

W poprzednim przykładzie pierwszym krokiem jest zdefiniowanie kostki, która definiuje wszystkie poziomy agregacji, które chcemy utworzyć.

Take note of the CROSS JOIN of the two derived tables since this generates all the levels for us. Pozostała część kodu służy do formatowania:

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;

Na poniższej ilustracji przedstawiono wyniki CTAS:

Drugim krokiem jest określenie tabeli docelowej do przechowywania wyników tymczasowych:

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

The third step is to loop over our cube of columns performing the aggregation. Zapytanie zostanie uruchomione raz dla każdego wiersza w tabeli tymczasowej #Cube. Wyniki są przechowywane w tabeli #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

Lastly, you can return the results by reading from the #Results temporary table:

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

Dzieląc kod na sekcje i generując konstrukcję pętli, kod staje się bardziej zarządzany i możliwy do utrzymania.

Następne kroki

Aby uzyskać więcej porad dotyczących programowania, zobacz Omówienie programowania.