Options GROUP BY dans Synapse SQL

Synapse SQL permet de développer des solutions en implémentant différentes options GROUP BY.

Que fait GROUP BY

La clause GROUP BY T-SQL agrège les données à un ensemble de lignes récapitulative.

Le pool SQL serverless ne prend pas en charge les options GROUP BY. Le pool SQL dédié prend en charge un nombre limité d’options GROUP BY.

Options GROUP BY prises en charge dans le pool SQL dédié

GROUP BY a certaines options que le pool SQL dédié ne prend pas en charge. Ces options ont des solutions de contournement, qui sont les suivantes :

• GROUP BY avec ROLLUP
• ENSEMBLES DE REGROUPEMENTS
• GROUP BY avec CUBE

Options de cumul et de regroupement

L'option la plus simple ici consiste à utiliser UNION ALL pour exécuter le cumul plutôt que de s'appuyer sur la syntaxe spécifiée. Le résultat est exactement le même

L’exemple suivant utilise l’instruction GROUP BY avec l’option 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]
                )
;

En utilisant ROLLUP, l’exemple précédent demande les agrégations suivantes :

• Pays et région
• Pays
• Total général

Pour remplacer ROLLUP et retourner les mêmes résultats, vous pouvez utiliser UNION ALL et spécifier explicitement les agrégations requises :

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;

Pour remplacer GROUPING SETS, le principe d'échantillonnage s’applique. Vous devez uniquement créer des sections UNION ALL pour les niveaux d’agrégation que vous souhaitez voir.

Options de cube

Il est possible de créer un GROUPE BY WITH CUBE à l’aide de l’approche UNION ALL. Le problème est que le code peut rapidement devenir fastidieux et difficile. Pour atténuer ce problème, vous pouvez utiliser cette approche plus avancée.

La première étape consiste à définir le « cube » qui définit tous les niveaux d’agrégation que nous voulons créer. Notez la jointure CROSS des deux tables dérivées, car elle génère tous les niveaux. Le reste du code est là pour la mise en forme.

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;

L’image suivante montre les résultats de CREATE TABLE AS SELECT :

La deuxième étape consiste à spécifier une table cible pour stocker les résultats intermédiaires :

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

La troisième étape consiste à faire une boucle sur le cube de colonnes pour réaliser l'agrégation. La requête s’exécute une fois pour chaque ligne de la table temporaire #Cube. Les résultats sont stockés dans la table temporaire #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

Enfin, vous pouvez retourner les résultats en lisant à partir de la table temporaire #Results :

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

En décomposant le code en sections et en générant une structure de boucle, le code devient plus gérable et facile à maintenir.

Étapes suivantes

Pour obtenir des conseils supplémentaires, consultez la vue d’ensemble du développement.