Quand utiliser les ensembles de groupage, CUBE et ROLLUP
j'ai récemment appris à grouper des SETS, CUBE et ROLLUP pour définir des sets de groupage multiples dans sql server.
ce que je demande, c'est dans quelles circonstances utilisons-nous ces caractéristiques ? Quels sont les avantages de les utiliser?
SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY GROUPING SETS ( ( shipperid, YEAR(shippeddate) ), ( shipperid ), ( YEAR(shippeddate) ), ( ) );
SELECT shipperid, YEAR(shippeddate) AS shipyear, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY CUBE( shipperid, YEAR(shippeddate) );
SELECT shipcountry, shipregion, shipcity, COUNT(*) AS numorders
FROM Sales.Orders
GROUP BY ROLLUP( shipcountry, shipregion, shipcity );
3 réponses
tout d'Abord, pour ceux qui n'ont pas déjà lu sur le sujet:
cela étant dit, Ne pensez pas à ces options de regroupement comme des façons d'obtenir un ensemble de résultats. ce sont des outils de performance.
prenons ROLLUP un exemple simple.
je peux utiliser la requête suivante pour obtenir le nombre d'enregistrements pour chaque valeur de GrpCol.
SELECT GrpCol, count(*) AS cnt
FROM dbo.MyTable
GROUP BY GrpCol
Et je peux utiliser la requête suivante pour sommairement "roll up" le nombre de TOUS les records.
SELECT NULL, count(*) AS cnt
FROM dbo.MyTable
Et j'ai pu UNION ALL le dessus de deux requêtes pour obtenir les mêmes résultats que je pourrais faire si j'avais écrit la première requête avec l' ROLLUP clause (c'est pourquoi j'y mets le NULL).
il pourrait en fait être plus pratique pour moi d'exécuter ceci comme deux requêtes différentes parce qu'alors j'ai les résultats groupés séparés de mes totaux. Pourquoi j'aurais envie de ma finale totale mélangée à droite dans le reste de ces résultats? La réponse est que faire les deux ensemble en utilisant le ROLLUP l'article est plus efficace. SQL Server utilisera un plan d'exécution qui calcule toutes les agrégations en un seul passage. Comparez cela à l' UNION ALL exemple qui fournirait exactement les mêmes résultats mais utiliserait un plan d'exécution moins efficace (deux scans de table au lieu d'un).
Imaginer un exemple extrême dans lequel vous travaillez sur un ensemble de données afin de grand que chacun d'analyse des données prend une heure entière. Vous devez fournir des totaux sur pratiquement chaque dimension possible (façon de trancher) que les données chaque jour. Aha! Je parie qu'une de ces options de regroupement est exactement ce dont vous avez besoin. Si vous sauvegardez les résultats de ce scan dans un schéma spécial, vous pourrez alors exécuter des rapports pour le reste de la journée.
donc je dis en gros que vous travaillez sur un projet d'entrepôt de données. Pour le reste d'entre nous, c' la plupart tombe dans la "bonne chose à savoir" de la catégorie.
CUBE est la même que celle de GROUPING SETS avec toutes les combinaisons possibles.
donc ceci (en utilisant CUBE)
GROUP BY CUBE (C1, C2, C3, ..., Cn-2, Cn-1, Cn)
est la même (à l'aide de GROUPING SETS)
GROUP BY GROUPING SETS (
(C1, C2, C3, ..., Cn-2, Cn-1, Cn) -- All dimensions are included.
,( , C2, C3, ..., Cn-2, Cn-1, Cn) -- n-1 dimensions are included.
,(C1, C3, ..., Cn-2, Cn-1, Cn)
…
,(C1, C2, C3, ..., Cn-2, Cn-1,)
,(C3, ..., Cn-2, Cn-1, Cn) -- n-2 dimensions included
,(C1 ..., Cn-2, Cn-1, Cn)
…
,(C1, C2) -- 2 dimensions are included.
,…
,(C1, Cn)
,…
,(Cn-1, Cn)
,…
,(C1) -- 1 dimension included
,(C2)
,…
,(Cn-1)
,(Cn)
,() ) -- Grand total, 0 dimension is included.
Alors, si vous n'avez pas vraiment besoin de toutes les combinaisons, vous devez utiliser GROUPING SETS plutôt que CUBE
les opérateurs ROLLUP et CUBE génèrent les mêmes ensembles de résultats et effectuer certains des mêmes calculs que les applications OLAP. cube opérateur génère un résultat qui peut être utilisé pour la totalisation croisée rapport. Une opération ROLLUP peut calculer l'équivalent D'un OLAP dimension ou hiérarchie.
Regardez ici pour voir grouper des ensembles D'équivalents
UPDATE
je pense qu'un exemple pourrait aider ici. Supposons que vous ayez un tableau du nombre D'ovnis aperçus par pays et par sexe, comme ci-dessous:
╔═════════╦═══════╦═════════╗
║ COUNTRY ║ GENDER║ #SIGHTS ║
╠═════════╬═══════╬═════════╣
║ USA ║ F ║ 450 ║
║ USA ║ M ║ 1500 ║
║ ITALY ║ F ║ 704 ║
║ ITALY ║ M ║ 720 ║
║ SWEDEN ║ F ║ 317 ║
║ SWEDEN ║ M ║ 310 ║
║ BRAZIL ║ F ║ 144 ║
║ BRAZIL ║ M ║ 159 ║
╚═════════╩═══════╩═════════╝
Alors, si vous voulez savoir les totaux pour chaque pays, par sexe et grand total seulement, vous devez utiliser GROUPING SETS
select Country, Gender, sum(Number_Of_Sights)
from Table1
group by GROUPING SETS((Country), (Gender), ())
order by Country, Gender
Pour obtenir le même résultat avec GROUP BY, vous pouvez utiliser UNION ALL comme:
select Country, NULL Gender, sum(Number_Of_Sights)
from Table1
GROUP BY Country
UNION ALL
select NULL Country, Gender, sum(Number_Of_Sights)
from Table1
GROUP BY GENDER
UNION ALL
SELECT NULL Country, NULL Gender, sum(Number_Of_Sights)
FROM TABLE1
ORDER BY COUNTRY, GENDER
mais il n'est pas possible d'obtenir le même résultat avec CUBE, puisqu'il retournera toutes les possibilités.
Maintenant, si vous voulez connaître toutes les combinaisons possibles, alors vous devez utiliser CUBE
je trouve qu'ils sont bons quand vous produisez un rapport et le résultat n'est pas quelque chose qui peut être enroulé dans le client.
par exemple, si vous faites quelque chose avec COUNT(DISTINCT...) ensuite le résultat dans un plus grand groupe n'est pas nécessairement la même valeur que la somme des parties. Par exemple, pendant deux jours individuels, vous pourriez avoir 1500 visiteurs et 2000 visiteurs, mais le total pourrait être n'importe où entre 2000 et 3500, selon le chevauchement. C'est agréable de faire ça chez le client., mais parce que le client ne peut pas dire ce qu'est le chevauchement, vous pouvez utiliser GROUPING SETS pour fournir la réponse (et ensuite gérer cette rangée supplémentaire venant à travers dans le rapport).