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

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

Что делает GROUP BY

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

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

Параметры GROUP BY, поддерживаемые в выделенном пуле 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;

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

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

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

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