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 z ROLLUP
• ZESTAWY GRUPOWANIA
• GRUPUJ WEDŁUG KOSTKI

Opcje agregacji i zestawów 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.

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ć.

Opcje kostki

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ć.

Zwróć uwagę na operację CROSS JOIN dwóch tabel pochodnych, ponieważ generuje ona wszystkie poziomy. 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 zakładać pętlę na naszych sześcianie kolumn i wykonywać agregację. 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

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 zarządzany i możliwy do utrzymania.

Następne kroki

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