Synapse SQL 中的 GROUP BY 選項

Synapse SQL 允許透過實作不同的 GROUP BY 選項來開發解決方案。

GROUP BY 的功能

GROUP BY T-SQL 子句將資料彙整成一組摘要列。

無伺服器 SQL 池不支援 GROUP BY 選項。 專用 SQL 池支援有限數量的 GROUP BY 選項。

專用 SQL 池中支援的 GROUP BY 選項

GROUP BY 有一些專用 SQL 池不支援的選項。 這些選項有解決方法，如下：

  • 搭配 ROLLUP 的 GROUP BY
  • GROUPING SETS
  • 使用 CUBE 進行分組

Rollup 和 grouping sets 選項

這裡最簡單的選擇是使用 UNION ALL 來執行彙整，而非依賴明確語法。 結果完全一樣

以下範例使用 GROUP BY 陳述句搭配 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]
                )
;

透過使用 ROLLUP，前述範例請求以下聚合：

  • 國家與地區
  • 國家
  • 總計

若要取代 ROLLUP 並回傳相同結果，你可以使用 UNION ALL 並明確指定所需的聚合：

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;

要取代 GROUPING SETS，則採用範例原理。 你只需要為你想看到的聚合層級建立 UNION ALL 區段。

方塊選項

可以使用 UNION ALL 方法來建立 GROUP BY WITH CUBE。 問題在於程式碼很快就會變得繁瑣且難以駕馭。 為了減輕這個問題，你可以使用這個更進階的方法。

第一步是定義定義我們想要建立的所有聚合層級的「立方體」。 請注意兩個導出表的交叉連接，因為它會產生所有層級。 剩下的程式碼是用來格式化的。

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;

下圖顯示 CREATE TABLE AS SELECT 的結果：

依立方體分組

第二步是指定一個目標表來儲存中期結果：

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

第三個步驟是逐一走訪資料行的 Cube，並執行彙總。 查詢會對 #Cube 暫存資料表中的每一列執行一次。 結果儲存在 #Results 臨時表中：

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

最後，你可以從 #Results 暫存表中讀取結果：

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

透過將程式碼拆分成多個區段並產生迴圈結構，程式碼變得更易管理且易於維護。

下一步

更多開發建議，請參閱 開發概覽。

  • Last updated on