Synapse SQL 中的 GROUP BY 选项

Tip

Microsoft Fabric Data Warehouse是数据湖基础上的企业规模关系仓库，具有未来就绪的体系结构、内置 AI 和新功能。 如果不熟悉数据仓库，请从Fabric Data Warehouse开始。 现有的指定 SQL 池工作负荷可以升级到 Fabric，以跨数据科学、实时分析和报告访问新功能。

• 启动Fabric免费试用版。
• Fabric 数据仓库迁移助手。

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
• 分组设置
• 将 GROUP BY 与 CUBE 配合使用

汇总和分组集选项

此处最简单的选项是使用 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 部分。

多维数据集选项

可以使用 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 的结果：

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

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
;

通过将代码分解为节并生成循环构造，代码将变得更加可管理且可维护。

后续步骤

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