Bertrand Fabre“J’ai besoin de calculer toutes les ventes sous diverses dimensions mais c’est long à écrire. Il n’y aurait pas une possibilité de tout faire d’un coup ?”
Sur BigQuery il y a la fonctionnalité GROUP BY CUBE() qui permet d’agréger les données sur plusieurs niveaux de granularité.
Chaque niveau aura son sous-total, selon la fonction d’agrégation choisie.
Pour résumer, GROUP BY CUBE() permet de calculer des statistiques pour chaque combinaison de dimension indiqué (ça paraît très clair dans ma tête😅).
Pour y voir plus clair voici un exemple de requête utilisant CUBE() :
WITH cte_sales AS (
SELECT 'A' AS product, 'North' AS area, 100 AS amount UNION ALL
SELECT 'A', 'South', 150 UNION ALL
SELECT 'B', 'North', 200 UNION ALL
SELECT 'B', 'South', 250
)
SELECT
product,
area,
SUM(amount) AS totalAmount
FROM cte_sales
GROUP BY CUBE(product, area)
;
Interprétation
On dit à la requête de faire un agrégat sur toutes les combinaisons possibles entre product et area (dans le GROUP BY CUBE()).
C’est ensuite dans le SELECT qu’on lui indique de quelle manière on veut faire ces agrégats : on veut en faire la somme du montant avec SUM(amount).
Dans notre exemple, cela permet de connaître, la somme des ventes par produit, par région, de manière distincte et de manière conjointe. De plus, on obtient aussi le grand total des ventes.
Résultats
product | area | totalAmount |
NULL | NULL | 700 |
A | NULL | 250 |
NULL | North | 300 |
A | North | 100 |
NULL | South | 400 |
A | South | 150 |
B | NULL | 450 |
B | North | 200 |
B | South | 250 |
Pour ceux qui auraient déjà lu un de mes posts sur le GROUP BY ROLLUP, il est possible que vous ressentiez une ressemblance.
La différence est que GROUP BY ROLLUP génère moins de combinaisons d’agrégats. En effet, il n’y aura pas d’agrégat seul pour le deuxième champ précisé.
☝️ Je ne l’ai pas montré dans l’exemple, mais il est possible d’utiliser plusieurs fonctions d’agrégats.
Merci de votre lecture !
