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

Suggerimenti per l’implementazione delle opzioni group by in SQL Data Warehouse di Azure per lo sviluppo di soluzioni.Tips for implementing group by options in Azure SQL Data Warehouse for developing solutions.

Qual è la funzione di GROUP BY?What does GROUP BY do?

La clausola T-SQL GROUP BY viene usata per aggregare i dati in un set di righe di riepilogo.The GROUP BY T-SQL clause aggregates data to a summary set of rows. GROUP BY include alcune opzioni non supportate da SQL Data Warehouse.GROUP BY has some options that SQL Data Warehouse does not support. Per queste opzioni esistono soluzioni alternative.These options have workarounds.

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'usare UNION ALL per eseguire il rollup anziché basarsi sulla 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

Nell'esempio seguente viene usata l'istruzione GROUP BY con l'opzione ROLLUP:The following example using the GROUP BY statement with 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]
                )
;

Quando si usa ROLLUP, sono necessarie le aggregazioni seguenti per l'esempio precedente:By using ROLLUP, the preceding example requests the following aggregations:

  • Paese e area geograficaCountry and Region
  • PaeseCountry
  • Grand TotalGrand Total

Per sostituire ROLLUP e restituire gli stessi risultati, è possibile usare UNION ALL e specificare in modo esplicito le aggregazioni necessarie:To replace ROLLUP and return the same results, you can use UNION ALL and explicitly specify the required aggregations:

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 sostituire GROUPING SETS, si applica lo stesso principio.To replace GROUPING SETS, the sample principle applies. È sufficiente creare sezioni UNION ALL per i livelli di aggregazione che si vuole visualizzare.You only need to create UNION ALL sections for the aggregation levels you 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 usare questo approccio più 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 vengono illustrati i risultati dell'operazione CTAS:The following shows the results of the CTAS:

Raggruppare per cubo

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, you 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 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.