Il y a quelques jours, alors que j’analysais un écart lors d’une recette, j’ai été confronté à une cause a priori étonnante.
Exemple
Données
Scripts SQL Server
J’ai 2 magasins de fruit, « Magasin_A » et « Magasin_B » , pour lesquels je souhaite consolider les ventes du 2 janvier 2023 :
Pour compter le nombre total de ventes réalisées pour chaque fruit sur cette journée, on construit donc une requête dans laquelle on :
- « concatène » dans un seul dataset les deux datasets d’origine (Magasin_A et Magasin_B) avec un « UNION«
- encapsule le résultat de cet « UNION » dans une sous-requête
- ajoute une agrégation ( « SUM » / « GROUP BY » )
| Fruit | Date | Quantite_agg |
|---|---|---|
| Pêche | 2023-01-02 | 22 |
| Poire | 2023-01-02 | 9 |
| Pomme | 2023-01-02 | 21 |
Problème
On m’interpelle alors :
« 22 pêches vendues ?
Probablement un contrôleur de gestion
Alors qu’on en a vendu
12 dans le Magasin_A et
19 dans le Magasin_B ?
Tu l’as fait avec les pieds, ton calcul ?! »
Effectivement, il semblerait bien que j’ai perdu des données en route… Mais où ?
Si on fait le test de retirer l’agrégation, on constate déjà le problème dans le résultat de la requête :
| Fruit | Date | Quantite |
|---|---|---|
| Pêche | 2023-01-02 | 3 |
| Pêche | 2023-01-02 | 9 |
| Pêche | 2023-01-02 | 10 |
| Poire | 2023-01-02 | 2 |
| Poire | 2023-01-02 | 7 |
| Pomme | 2023-01-02 | 1 |
| Pomme | 2023-01-02 | 3 |
| Pomme | 2023-01-02 | 4 |
| Pomme | 2023-01-02 | 5 |
| Pomme | 2023-01-02 | 8 |
Là où je m’attendrais à avoir 12 lignes (6 par magasin), la requête sans agrégation ne retourne que 10 lignes. Egalement, chose curieuse, mes lignes semblent triées par fruit.
BUG ?
UNION et UNION ALL
Points communs
« UNION » et « UNION ALL » permettent tous deux de « concaténer » des datasets dans un seul jeu de données, et ont un fonctionnement et des contraintes similaires :
- Il faut le même nombre de colonnes dans chacun des datasets qu’on veut concaténer
- Les colonnes doivent être dans la même ordre dans l’ensemble des datasets (l’alias est obligatoire dans le premier dataset, mais est inutile dans les suivants : seul l’ordre des colonnes compte)
- Les colonnes à concaténer doivent contenir des données de types similaires
Différences
- « UNION« applique un tri et un « DISTINCT » avant de renvoyer le résultat, et retournera donc uniquement des enregistrements uniques
- « UNION ALL » retourne tous les enregistrements, y-compris les doublons.
A noter : soyez vigilants, l’opération de suppression des enregistrements en double est réalisée avant les éventuelles agrégations, ce qui peut être une cause d’erreurs pas forcément flagrantes.
Retour sur l’exemple
Explication
En fait, ce comportement est dû au fait que j’utilise la commande « UNION » . Celle-ci applique implicitement un « DISTINCT » sur son résultat (et le trie) avant de réaliser l’agrégation ( « SUM » ) demandée.
Dans mon exemple, si je regarde mes deux datasets, je retrouve :
- Deux fois la valeur « Pêche » avec la quantité « 9«
- Deux fois la valeur « Pomme » avec la quantité « 3«
En fait, c’est la commande « UNION ALL » qui me permet d’arriver au résultat souhaité, en indiquant explicitement que je souhaite que ma concaténation retourne la totalité des lignes des datasets que je veux fusionner.
Dans la plupart des cas, on peut utiliser indifféremment l’une ou l’autre de ces instructions.
D’ailleurs, maintenant que vous avez compris le comportement de « UNION« , si je reprends mon jeu de données d’exemple et que j’y ajoute une colonne indiquant quel magasin a vendu quel fruit, les lignes indiquant « 9 pêches » sont désormais uniques, et le résultat du nombre de pêches est donc correct (il ne l’est toujours pas pour les pommes, car le doublon vient d’un seul dataset) :
| Fruit | Date | Quantite_agg |
|---|---|---|
| Pêche | 2023-01-02 | 31 |
| Poire | 2023-01-02 | 9 |
| Pomme | 2023-01-02 | 21 |
Dans quel cas utiliser « UNION » ? Dans quel cas utiliser « UNION ALL » ?
Quel est l’intérêt d’avoir créé deux opérateurs aux fonctionnalités si proches ? Bonne question !
Gardez en tête que certes, l’opération de suppression des lignes en double est le comportement par défaut de « UNION« , mais que celle-ci peut s’avérer gourmande en ressources de la base de données.
L’étape de tri puis de suppression des doublons implique une analyse de chaque enregistrement de la table. « UNION » sera donc généralement moins performant que « UNION ALL » au niveau du temps d’exécution, notamment sur de gros volumes de données.
Evidemment, si vous sous savez déjà que tous les enregistrements de vos datasets sont uniques (performance, surtout pour les gros volumes de données), utilisez « UNION ALL » sans hésiter !
Exception 1 : prenez également en considération la manière dont vous comptez exploiter le jeu de résultat : le volume de données à traiter peut être beaucoup plus important avec la clause « UNION ALL » s’il y a beaucoup de doublons, et donc retarder les étapes suivantes.
Exception 2 : vous avez un enjeu de transfert réseau, ou d’écriture dans un fichier par exemple, pour lequel le gain à transférer/écrire des lignes uniques est supérieur au temps gagné à utiliser « UNION ALL«
Dans un environnement décisionnel, « UNION » sera souvent utilisé pour manipuler des référentiels (« tables de dimension« ), là où « UNION ALL » sera utilisé pour les « tables de faits« .