Écrire des requêtes qui spécifient plusieurs regroupements avec des jeux de regroupements

  • 7 minutes

Vous utilisez la clause GROUP BY dans une instruction SELECT en Transact-SQL pour organiser les lignes en groupes, en général pour permettre la prise en charge des agrégations. Toutefois, si vous devez effectuer un regroupement en fonction de différents attributs en même temps, par exemple pour créer des rapports à différents niveaux, vous avez normalement besoin de plusieurs requêtes combinées avec UNION ALL. Au lieu de cela, si vous devez créer des agrégats de plusieurs regroupements dans la même requête, vous pouvez utiliser la sous-clause GROUPING SETS de la clause GROUP BY dans Transact-SQL. GROUPING SETS constitue une alternative à l’utilisation de UNION ALL pour combiner les résultats de plusieurs requêtes individuelles, chacune avec sa propre clause GROUP BY.

Utiliser la sous-clause GROUPING SETS

Pour utiliser GROUPING SETS, vous spécifiez les combinaisons d’attributs à regrouper, comme dans l’exemple de syntaxe suivant :

SELECT <column list with aggregate(s)>
FROM <source>
GROUP BY 
GROUPING SETS(
    (<column_name>),--one or more columns
    (<column_name>),--one or more columns
    () -- empty parentheses if aggregating all rows
        );

Imaginons par exemple que vous souhaitez utiliser GROUPING SETS pour l’agrégation des colonnes Category et Cust d’une table Sales.CategorySales, en plus de la notation des parenthèses vides pour agréger toutes les lignes :

SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY 
    GROUPING SETS((Category),(Cust),())
ORDER BY Category, Cust;

Le résultat peut ressembler à ce qui suit :

Catégorie Cust Quantité totale
ZÉRO ZÉRO 999
ZÉRO 1 80
ZÉRO 2 12
ZÉRO 3 154
ZÉRO 4 241
ZÉRO 5 512
Boissons ZÉRO 513
Condiments ZÉRO 114
Confiseries ZÉRO 372

Notez la présence de valeurs NULL dans les résultats. Des valeurs NULL peuvent être retournées parce qu’une valeur NULL a été stockée dans la source sous-jacente, ou parce qu’il s’agit d’un espace réservé dans une ligne générée comme un résultat d’agrégation. Par exemple, dans les résultats précédents, la première ligne affiche NULL, NULL, 999. Il s’agit d’une ligne de total général. La valeur NULL dans les colonnes Category et Cust est un espace réservé, car aucune Category ni aucun Cust ne participe à l’agrégation.

Conseil

Si vous souhaitez savoir si une valeur NULL marque un espace réservé ou vient de données sous-jacentes, vous pouvez utiliser GROUPING_ID. Pour plus d’informations, consultez la page de référence sur GROUPING_ID.

Utiliser les sous-clauses CUBE et ROLLUP

Comme GROUPING SETS, les sous-clauses CUBE et ROLLUP permettent également plusieurs regroupements pour l’agrégation des données. Toutefois, CUBE et ROLLUP n’exigent pas que vous spécifiiez chaque ensemble d’attributs à regrouper. Au lieu de cela, étant donné un ensemble de colonnes, CUBE détermine toutes les combinaisons et tous les regroupements de sortie possibles. ROLLUP crée des combinaisons, en supposant que les colonnes d’entrée représentent une hiérarchie. Par conséquent, CUBE et ROLLUP peuvent être considérés comme des raccourcis vers GROUPING SETS.

Pour utiliser CUBE, ajoutez le mot clé CUBE à la clause GROUP BY et fournissez une liste de colonnes à regrouper. Par exemple, pour regrouper sur toutes les combinaisons de colonnes Category et Cust, utilisez la syntaxe suivante dans votre requête :

SELECT Category, Cust, SUM(Qty) AS TotalQty
FROM Sales.CategorySales
GROUP BY CUBE(Category,Cust);

Cela génère des regroupements pour les combinaisons suivantes : (Category, Cust), (Cust, Category), (Cust), (Category) et l’agrégat sur tous les éléments vides () :

Catégorie Cust Quantité totale
Boissons 1 36
Condiments 1 44
ZÉRO 1 80
Boissons 2 5
Confiseries 2 7
ZÉRO 2 12
Boissons 3 105
Condiments 3 4
Confiseries 3 45
ZÉRO 3 154
...
ZÉRO ZÉRO 999
Boissons ZÉRO 513
Condiments ZÉRO 114
Confiseries ZÉRO 372

Pour utiliser ROLLUP, ajoutez le mot clé ROLLUP à la clause GROUP BY et fournissez une liste de colonnes à regrouper. Par exemple, pour regrouper sur les combinaisons de colonnes Category, Subcategory et Product, utilisez la syntaxe suivante dans votre requête :

SELECT Category, Subcategory, Product, SUM(Qty) AS TotalQty
FROM Sales.ProductSales
GROUP BY ROLLUP(Category,Subcategory, Product);

Cela génère des regroupements pour les combinaisons suivantes : (Category, Subcategory, Product), (Category, Subcategory), (Category) et l’agrégat sur tous les éléments vides (). L’ordre dans lequel les colonnes sont fournies est important : ROLLUP part du principe que les colonnes sont répertoriées dans un ordre qui exprime une hiérarchie. Il fournit des sous-totaux pour chaque regroupement, ainsi qu’un total général pour tous les regroupements à la fin.