Opzioni Group by in SQL Data WarehouseGroup by options in SQL Data Warehouse

La clausola GROUP BY viene usata per aggregare i dati a un set di righe di riepilogo.The GROUP BY clause is used to aggregate data to a summary set of rows. Include inoltre alcune opzioni che ne estendono la funzionalità che devono essere evitate in quanto non sono supportate direttamente SQL Data Warehouse di Azure.It also has a few options that extend it's functionality that need to be worked around as they are not directly supported by Azure SQL Data Warehouse.

Queste opzioni sonoThese options are

  • GROUP BY con ROLLUPGROUP BY with ROLLUP
  • GROUPING SETSGROUPING SETS
  • GROUP BY con CUBEGROUP BY with CUBE

Opzioni di rollup e raggruppamento di setRollup and grouping sets options

L'opzione più semplice consiste nell'utilizzare UNION ALL invece di eseguire il rollup anziché utilizzare la sintassi esplicita.The simplest option here is to use UNION ALL instead to perform the rollup rather than relying on the explicit syntax. Il risultato è esattamente lo stessoThe result is exactly the same

Di seguito è riportato un esempio di istruzione group by utilizzando l’opzione ROLLUP :Below is an example of a group by statement using the ROLLUP option:

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

Usando ROLLUP sono state richieste le aggregazioni seguenti:By using ROLLUP we have requested the following aggregations:

  • Paese e areaCountry and Region
  • PaeseCountry
  • Grand TotalGrand Total

Per sostituire questo valore è necessario utilizzare UNION ALL; specificando le aggregazioni necessarie in modo esplicito per restituire gli stessi risultati:To replace this you will need to use UNION ALL; specifying the aggregations required explicitly to return the same results:

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;

Per GROUPING SETS è necessario adottare la stessa entità e creare solo sezioni UNION ALL per i livelli di aggregazione che si desidera visualizzare.For GROUPING SETS all we need to do is adopt the same principal but only create UNION ALL sections for the aggregation levels we want to see

Opzioni CubeCube options

È possibile creare un GROUP BY WITH CUBE utilizzando l'approccio UNION ALL.It is possible to create a GROUP BY WITH CUBE using the UNION ALL approach. Il problema è che il codice può risultare complesso e difficile da gestire.The problem is that the code can quickly become cumbersome and unwieldy. Per risolvere questo problema è possibile utilizzare questo approccio avanzato.To mitigate this you can use this more advanced approach.

Utilizzare l'esempio precedente.Let's use the example above.

Il primo passaggio consiste nel definire il cubo che definisce tutti i livelli di aggregazione che si desidera creare.The first step is to define the 'cube' that defines all the levels of aggregation that we want to create. È importante tenere conto del CROSS JOIN delle due tabelle derivate.It is important to take note of the CROSS JOIN of the two derived tables. In tal modo tutti i livelli vengono generati automaticamente.This generates all the levels for us. Il resto del codice è disponibile per la formattazione.The rest of the code is really there for formatting.

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;

Di seguito sono illustrati i risultati del CTAS:The results of the CTAS can be seen below:

Il secondo passaggio consiste nel specificare una tabella di destinazione per archiviare i risultati temporanei:The second step is to specify a target table to store interim results:

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

Il terzo passaggio consiste nell’eseguire il ciclo del cubo delle colonne per eseguire l'aggregazione.The third step is to loop over our cube of columns performing the aggregation. La query verrà eseguita una volta per ogni riga nella tabella temporanea #Cube e i risultati verranno archiviati nella tabella temporanea #ResultsThe query will run once for every row in the #Cube temporary table and store the results in the #Results temp table

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

Infine è possibile restituire i risultati semplicemente leggendo dalla tabella temporanea #ResultsLastly we can return the results by simply reading from the #Results temporary table

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

Suddividendo il codice in sezioni e generando un costrutto di ciclo il codice diventa più gestibile e gestibile e sostenibile.By breaking the code up into sections and generating a looping construct the code becomes more manageable and maintainable.

Passaggi successiviNext steps

Per altri suggerimenti sullo sviluppo, vedere la panoramica dello sviluppo.For more development tips, see development overview.