Opciones DE GROUP BY en Synapse SQL

Synapse SQL permite desarrollar soluciones mediante la implementación de diferentes opciones de GROUP BY.

Qué hace GROUP BY

La cláusula GROUP BY T-SQL agrega datos a un conjunto de filas de resumen.

El grupo de SQL sin servidor no admite las opciones GROUP BY. El grupo de SQL dedicado admite un número limitado de opciones de GROUP BY.

Opciones de GROUP BY admitidas en el grupo de SQL dedicado

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 de acumulación y agrupación de conjuntos

La opción más sencilla aquí es usar UNION ALL para ejecutar el paquete acumulativo en lugar de confiar en 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.

El primer paso es definir el "cubo" que define todos los niveles de agregación que queremos crear. Tenga en cuenta el CROSS JOIN de las dos tablas derivadas, ya 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 CREATE TABLE AS SELECT:

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 consiste en 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, puede devolver los resultados leyendo desde 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.