Opções GROUP BY no Synapse SQL
O Synapse SQL permite desenvolver soluções ao implementar diferentes opções GROUP BY.
O que faz o GROUP BY
A cláusula GROUP BY T-SQL agrega dados a um conjunto de linhas de resumo.
O conjunto de SQL sem servidor não suporta opções GROUP BY. O conjunto de SQL dedicado suporta um número limitado de opções GROUP BY.
Opções GROUP BY suportadas no conjunto de SQL dedicado
GROUP BY tem algumas opções que o conjunto de SQL dedicado não suporta. Estas opções têm soluções, que são as seguintes:
- AGRUPAR POR com ROLLUP
- CONJUNTOS DE AGRUPAMENTO
- AGRUPAR POR com CUBO
Opções de conjuntos de rollup e agrupamento
A opção mais simples aqui é utilizar UNION ALL para executar o rollup em vez de depender da sintaxe explícita. O resultado é exatamente o mesmo
O exemplo seguinte utiliza a instrução GROUP BY com a opção 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]
)
;
Ao utilizar ROLLUP, o exemplo anterior pede as seguintes agregações:
- País e Região
- País
- Total Geral
Para substituir ROLLUP e devolver os mesmos resultados, pode utilizar UNION ALL e especificar explicitamente as agregações necessárias:
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;
Para substituir CONJUNTOS DE AGRUPAMENTO, aplica-se o princípio de exemplo. Só precisa de criar secções UNION ALL para os níveis de agregação que pretende ver.
Opções de cubo
É possível criar um GRUPO POR COM CUBO com a abordagem UNION ALL. O problema é que o código pode rapidamente tornar-se pesado e desajeitado. Para mitigar este problema, pode utilizar esta abordagem mais avançada.
O primeiro passo é definir o "cubo" que define todos os níveis de agregação que queremos criar. Tome nota da ASSOCIAção CRUZADA das duas tabelas derivadas à medida que gera todos os níveis. O resto do código está lá para formatação.
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;
A imagem seguinte mostra os resultados de CREATE TABLE AS SELECT:
O segundo passo é especificar uma tabela de destino para armazenar resultados provisórios:
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
)
;
O terceiro passo é fazer um ciclo sobre o cubo de colunas que efetuam a agregação. A consulta será executada uma vez para cada linha na #Cube tabela temporária. Os resultados são armazenados na tabela temporária #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
Por último, pode devolver os resultados ao ler a partir da tabela temporária #Results:
SELECT *
FROM #Results
ORDER BY 1,2,3
;
Ao dividir o código em secções e gerar uma construção em loop, o código torna-se mais gerível e manejável.
Passos seguintes
Para obter mais sugestões de desenvolvimento, veja Descrição geral do desenvolvimento.