Synapse SQL 中的 GROUP BY 选项

2024-08-14

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 的 GROUP BY

Rollup 和 grouping sets 选项

此处最简单的选项是使用 UNION ALL 来执行汇总，而不是依赖显式语法。结果应完全相同

下面的示例使用了具有 ROLLUP 选项的 GROUP BY 语句：

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 部分。

Cube 选项

可以使用 UNION ALL 方法创建 GROUP BY WITH CUBE。问题在于，代码可能很快就会变得庞大且失控。若要缓解此问题，可以使用这种更高级的方法。

第一步是定义“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 的结果：

Group by cube

第二步是指定用于存储临时结果的目标表：

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
;

将代码拆分成不同的部分并生成循环构造以后，代码就会更易于管理和维护。

后续步骤

有关更多开发技巧，请参阅开发概述。

共用方式為