Параметры GROUP BY в Synapse SQL

Статья
06/01/2023

Synapse SQL позволяет разрабатывать решения путем реализации различных параметров GROUP BY.

Действие GROUP BY

Предложение T-SQL GROUP BY используется для объединения данных в сводный набор строк.

Бессерверный пул SQL не поддерживает параметры GROUP BY. Выделенный пул SQL поддерживает ограниченное число параметров GROUP BY.

Параметры GROUP BY, поддерживаемые в выделенном пуле SQL

Выделенный пул SQL не поддерживает некоторые параметры GROUP BY. Для этих параметров есть обходные решения:

GROUP BY с ROLLUP;
GROUPING SETS
GROUP BY с 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, приведенный выше пример запрашивает следующие объединения.

Страна и регион
Country
Grand Total

Чтобы получить те же результаты без 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

Предложение GROUP BY с параметром CUBE можно составить, используя оператор UNION ALL. Проблема в том, что в этом случае код может быстро разрастись и стать слишком громоздким. В этом случае можно прибегнуть к более современному методу.

Для начала нужно задать «куб», определяющий все уровни группирования данных, которые нам нужно создать. Обратите внимание на применение оператора CROSS JOIN для двух производных таблиц, поскольку он создает все уровни. Остальная часть кода нужна для форматирования.

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. Результаты сохраняются во временной таблице #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
;

Если разбить этот код на разделы и создать циклическую конструкцию, он станет более управляем и удобен в обслуживании.

Дальнейшие действия

Дополнительные советы по разработке приведены в обзоре разработки.

Поделиться через