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

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

后续步骤

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