Группировать по параметрам выделенных пулов SQL в Azure Synapse Analytics

В этой статье приведены советы по реализации групп по параметрам в выделенных пулах SQL.

Что делает GROUP BY?

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

• GROUP BY с ROLLUP
• НАБОРЫ ГРУППИРОВКИ
• GROUP BY с CUBE

Параметры свертки и группирования наборов

Самый простой вариант — использовать 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 для уровней агрегирования, которые вы хотите просмотреть.

Параметры куба

Можно создать конструкцию GROUP BY WITH 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;

На следующем рисунке показаны результаты CTAS:

Второй шаг — указать целевую таблицу для хранения промежуточных результатов:

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
;

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

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

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