Opciones de agrupamiento para grupos de SQL dedicados en Azure Synapse Analytics

En este artículo, encontrará sugerencias para implementar grupos por opciones en grupos de SQL dedicados.

¿Qué hace GROUP BY?

La cláusula GROUP BY T-SQL agrega datos a un conjunto de filas de resumen. GROUP BY tiene algunas opciones que el grupo de SQL dedicado no admite. Estas opciones tienen soluciones alternativas, que son las siguientes:

• GROUP BY con ROLLUP
• CONJUNTOS DE AGRUPACIÓN
• GROUP BY con CUBE

Opciones Rollup y Grouping sets

Aquí, la opción más sencilla consiste en usar UNION ALL para realizar la acumulación en lugar de depender de la sintaxis explícita. El resultado es exactamente el mismo.

En el ejemplo siguiente se usa la instrucción GROUP BY con la opción 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]
                )
;

Mediante ROLLUP, en el ejemplo anterior se solicitan las agregaciones siguientes:

• País y región
• País
• Total general

Para reemplazar ROLLUP y devolver los mismos resultados, puede usar UNION ALL y especificar explícitamente las agregaciones necesarias:

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 reemplazar GROUPING SETS, se aplica el principio de ejemplo. Solo tiene que crear secciones UNION ALL para los niveles de agregación que desea ver.

Opciones de cubo

Es posible crear un GROUP BY WITH CUBE mediante el enfoque UNION ALL. El problema es que el código puede volverse complicado e ingobernable. Para mitigar este problema, puede usar este enfoque más avanzado.

Con el ejemplo anterior, el primer paso es definir el "cubo" que define todos los niveles de agregación que queremos crear.

Tome nota de la CROSS JOIN de las dos tablas derivadas, ya que esto es lo que genera todos los niveles. El resto del código está ahí para dar formato:

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;

En la imagen siguiente se muestran los resultados de CTAS:

El segundo paso consiste en especificar una tabla de destino para almacenar los resultados provisionales:

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

El tercer paso es recorrer en bucle el cubo de columnas que realiza la agregación. La consulta se ejecutará una vez para cada fila de la tabla temporal #Cube. Los resultados se almacenan en la tabla temporal de #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, se pueden devolver los resultados simplemente leyendo la tabla temporal #Results:

SELECT *
FROM #Results
ORDER BY 1,2,3
;

Al dividir el código en secciones y generar una construcción de bucle, el código se vuelve más fácil de administrar y mantener.

Pasos siguientes

Para obtener más sugerencias sobre desarrollo, vea la información general sobre desarrollo.