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

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

Co robi FUNKCJA 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:

• GRUPOWANIE WEDŁUG za pomocą FUNKCJI ROLLUP
• ZESTAWY GRUPOWANIA
• GRUPOWANIE WEDŁUG ZA POMOCĄ MODUŁU

Opcje zestawów zestawień i grupowania

Najprostszą opcją jest użycie funkcji UNION ALL do wykonania zestawienia, a nie polegania na jawnej składni. Wynik jest dokładnie taki sam.

W poniższym przykładzie przy użyciu 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ć.

Opcje modułu

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 modułu definiującego wszystkie poziomy agregacji, które chcemy utworzyć.

Zanotuj element CROSS JOIN dwóch tabel pochodnych, ponieważ spowoduje to wygenerowanie wszystkich poziomów dla nas. 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
)
;

Trzecim krokiem jest zapętlenie modułu kolumn wykonujących agregację. Zapytanie będzie uruchamiane 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

Na koniec możesz zwrócić wyniki, odczytując z tabeli tymczasowej #Results:

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

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

Następne kroki

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