Le problème avec SUM(Distinct)
Nous avons précédemment appris que nous pouvons utiliser COUNT(Distinct) pour compter les colonnes de la table dupliquée, alors qu’en est-il de SUM(Distinct)? Il semble que cela devrait faire l’affaire, car nous voulons seulement additionner des valeurs de frais de port distinctes, pas tous les doublons. Essayons-le:
select o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, count(distinct o.OrderID) as OrderCount, sum(distinct o.ShippingCost) as TotalShippingfrom Orders oinner join OrderDetails od on o.OrderID = od.OrderIDgroup by o.CustomerCustomer ItemCount OrderAmount OrderCount TotalShipping ---------- ----------- --------------------- ----------- --------------------- ABC 6 725.0000 3 95.0000DEF 2 350.0000 1 10.0000(2 row(s) affected)
Et voilà ! Nous semblons avoir résolu notre problème: en regardant notre tableau des commandes, nous pouvons voir que le coût total d’expédition par client semble maintenant correct.
Mais attendez… C’est en fait faux!
C’est là que beaucoup de gens ont des problèmes. Oui, les données semblent correctes. Et, pour ce petit échantillon, il se trouve que c’est juste au hasard. Mais SUM (DISTINCT) fonctionne exactement de la même manière que COUNT (DISTINCT): Il obtient simplement toutes les valeurs éligibles à la somme, élimine toutes les valeurs en double, puis additionne les résultats. Mais cela élimine les valeurs en double, pas les lignes en double basées sur une colonne de clé primaire! Peu importe que les frais d’expédition 40 appartiennent à orderID # 1 et que les frais d’expédition 30 appartiennent à OrderID # 2; cela ne les sépare tout simplement pas de cette façon.
La SOMME de l’expression (Frais d’expédition distincts) est fondamentalement évaluée comme ceci:
- Après avoir rejoint d’une Commande à l’autre, chaque groupe a les valeurs de frais de port suivantes:
Client ABC: 40,40,30,30,30,25
Client DEF: 10 - Étant donné que DISTINCT a été demandé, il élimine les valeurs en double de ces listes:
Client ABC: 40,40,30,30,30,25
Client DEF: 40,40,30,30,25
Client DEF: 10 - Et maintenant il peut évaluer la SUM() en additionnant les valeurs restantes:
Customer ABC: 40+30+25 = 95
DEF Client: 10 = 10
Si vous n’obtenez pas le concept, vous pourriez toujours ne pas voir le problème. En fait, à ce stade, beaucoup de gens ne le font jamais. Ils voient que SUM(x) renvoie des nombres énormes qui ne peuvent pas être corrects, alors ils le modifient et essaient SUM (x DISTINCT), et les valeurs semblent beaucoup plus raisonnables, et elles pourraient même s’attacher parfaitement au départ, alors c’est parti pour la production. Pourtant, le SQL est incorrect; il s’appuie sur le fait qu’actuellement, aucune commande pour un client n’a le même coût d’expédition.
Démontrons en ajoutant un autre ordre:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
L’exécution qui ajoute simplement une autre commande pour le client DEF, coût d’expédition de 10 $, avec un article de détail de commande pour 125 $. Maintenant, exécutons à nouveau cette même SÉLECTION pour voir comment cette nouvelle commande a affecté nos résultats:
select o.Customer, count(*) as ItemCount, sum(od.Amount) as OrderAmount, count(distinct o.OrderID) as OrderCount, sum(distinct o.ShippingCost) as TotalShippingfrom Orders oinner join OrderDetails od on o.OrderID = od.OrderIDgroup by CustomerCustomer ItemCount OrderAmount OrderCount TotalShipping ---------- ----------- --------------------- ----------- --------------------- ABC 6 725.0000 3 95.0000DEF 3 475.0000 2 10.0000(2 row(s) affected)
Les colonnes ItemCount, OrderAmount et OrderCount ont fière allure. Mais le coût total d’expédition pour DEF affiche toujours 10 $! Que s’est-il passé !?
Pouvez-vous le comprendre? Rappelez-vous comment SUM (Distinct) fonctionne! Il prend simplement des valeurs distinctes transmises à la fonction et élimine les doublons. Les deux commandes pour DEF avaient un coût d’expédition de 10 $, et SUM (Frais d’expédition distincts) ne se soucie pas que les deux valeurs de 10 are soient pour des Commandes différentes, il sait juste que le 10 est dupliqué pour le Client, il n’utilise donc le 10 qu’une seule fois pour calculer la SOMME. Ainsi, il renvoie une valeur de 10 comme coût total d’expédition pour ces deux commandes, même s’il devrait être de 10 + 10 = 20. Notre résultat est maintenant faux. Le long et le court est le suivant: N’utilisez jamais SUM (Distinct)! Cela n’a généralement pas de sens logique dans la plupart des situations; il peut y avoir un moment et un lieu pour cela, mais ce n’est certainement pas ici.
Résumer les tables dérivées
Alors, comment résoudre ce problème? Eh bien, comme beaucoup de problèmes SQL, la réponse est simple: Faites-le une étape à la fois, n’essayez pas de joindre toutes les tables ensemble et ajoutez simplement SUM() et GROUPEZ PAR et DISTINCT presque au hasard jusqu’à ce que les choses fonctionnent; décomposez-le logiquement étape par étape.
Donc, avant de nous soucier des totaux par client, prenons du recul et concentrons-nous sur le retour des totaux par Commande. Si nous pouvons d’abord retourner les totaux par Commande, nous pouvons simplement résumer ces totaux de commande par client et nous aurons les résultats dont nous avons besoin. Résumons le tableau OrderDetails pour renvoyer 1 ligne par Commande, avec le ItemCount et le montant total de la Commande:
select orderID, count(*) as ItemCount, sum(Amount) as OrderAmountfrom orderDetailsgroup by orderIDorderID ItemCount OrderAmount ----------- ----------- --------------------- 1 2 250.00002 3 375.00003 1 100.00004 2 350.00005 1 125.0000(5 row(s) affected)
Agréable et simple, facile à vérifier, les choses ont l’air bien. Parce que nous regroupons sur OrderID, nous pouvons dire que ces résultats ont une clé primaire virtuelle de OrderID – c’est-à-dire qu’il n’y aura jamais de lignes en double pour le même ordre. En fait, voici une autre règle de base à toujours retenir:
La clé primaire virtuelle d’un SELECT avec une clause GROUP BY sera toujours les expressions indiquées dans le GROUP BY.
Nous pouvons maintenant prendre cette instruction SQL et ces résultats et les encapsuler dans leur propre table dérivée. Si nous joignons la table Orders à la SÉLECTION précédente en tant que table dérivée, nous obtenons:
select o.orderID, o.Customer, o.ShippingCost, d.ItemCount, d.OrderAmountfrom orders oinner join( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID) d on o.orderID = d.orderIDorderID Customer ShippingCost ItemCount OrderAmount ----------- ---------- --------------------- ----------- --------------------- 1 ABC 40.0000 2 250.00002 ABC 30.0000 3 375.00003 ABC 25.0000 1 100.00004 DEF 10.0000 2 350.00005 DEF 10.0000 1 125.0000(5 row(s) affected)
Examinons ces résultats. Il n’y a aucune ligne ou valeur en double nulle part ; il y a exactement une ligne par commande. En effet, notre table dérivée a une clé primaire virtuelle de OrderID, donc la jonction des commandes à notre table dérivée ne produira jamais de doublons. C’est une technique très utile et simple pour éviter les doublons lors de la relation entre une table parent et une table enfant : résumez d’abord la table enfant par la clé primaire du parent dans une table dérivée, puis joignez-la à la table parent. Les lignes de la table parente ne seront alors jamais dupliquées et pourront être résumées avec précision.
Maintenant, nous avons notre total ItemCount par commande, ainsi que notre total OrderAmount par commande. Et nous pouvons voir que si nous additionnons ces résultats, notre colonne ShippingCost ira bien, car elle n’est jamais dupliquée. Pas besoin de distinct. En fait, nous pouvons même utiliser une expression régulière COUNT (*) pour obtenir le nombre total de commandes par client!
Ainsi, nous pouvons simplement ajouter « GROUP BY Customer » au SQL précédent, calculer ce dont nous avons besoin avec des fonctions d’agrégation et supprimer toutes les colonnes (comme OrderID) que nous ne résumerons pas. Vous remarquerez peut-être également qu’à ce stade, le total ItemCount par client n’est plus une expression COUNT(*); c’est une simple SOMME () de la valeur ItemCount renvoyée par notre table dérivée.
Voici le résultat:
select o.Customer, count(*) as OrderCount, sum(o.ShippingCost) as ShippingTotal, sum(d.ItemCount) as ItemCount, sum(d.OrderAmount) as OrderAmountfrom orders oinner join( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID) d on o.orderID = d.orderIDgroup by o.customerCustomer OrderCount ShippingTotal ItemCount OrderAmount ---------- ----------- --------------------- ----------- --------------------- ABC 3 95.0000 6 725.0000DEF 2 20.0000 3 475.0000(2 row(s) affected)
Et voilà, vous l’avez ! Nous avons examiné nos données, examiné logiquement les implications de nos JOINTURES, divisé le problème en parties plus petites et nous avons abouti à une solution assez simple qui, nous le savons, sera rapide, efficace et précise.
Ajout de tables supplémentaires UNE SÉLECTION résumée
Pour terminer les choses, supposons que notre schéma comporte également une table de clients:
Create table Customers(Customer varchar(10) primary key,CustomerName varchar(100) not null,City varchar(100) not null,State varchar(2) not null)insert into Customersselect 'ABC','ABC Corporation','Boston','MA' union allselect 'DEF','The DEF Foundation','New York City','NY'
… et nous souhaitons également renvoyer le nom, la ville et l’état de chaque client dans nos résultats précédents. Une façon de le faire consiste simplement à ajouter la table Customers à notre jointure existante, puis à ajouter les colonnes customer à la clause SELECT. Cependant, à moins que vous n’ajoutiez également toutes les colonnes client au GROUPE, vous obtiendrez un message d’erreur indiquant que vous devez regrouper ou résumer toutes les colonnes que vous souhaitez afficher. Nous n’essayons pas de calculer un COUNT() ou une SUM() de Nom, de ville et d’État, il n’est donc pas logique d’envelopper ces colonnes dans une expression agrégée. Il semble donc que nous devons tous les ajouter à notre clause GROUP BY pour obtenir les résultats dont nous avons besoin:
select o.Customer, c.customerName, c.City, c.State, count(*) as OrderCount, sum(o.ShippingCost) as ShippingTotal, sum(d.ItemCount) as ItemCount, sum(d.OrderAmount) as OrderAmountfrom orders oinner join( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID) d on o.orderID = d.orderIDinner join customers c on o.customer = c.customergroup by o.customer, c.customerName, c.City, c.StateCustomer customerName City State OrderCount ShippingTotal ItemCount OrderAmount---------- -------------------- --------------- ----- ----------- ------------- --------- -----------ABC ABC Corporation Boston MA 3 95.0000 6 725.0000DEF The DEF Foundation New York City NY 2 20.0000 3 475.0000(2 row(s) affected)
Techniquement, cela fonctionne, mais il semble idiot de répertorier toutes ces colonnes de clients dans le GROUPE PAR… Après tout, nous ne faisons que nous regrouper sur le client, pas sur chacun des attributs du client, n’est-ce pas?
Ce qui est intéressant, c’est que la solution est quelque chose dont nous avons déjà parlé et la même technique s’applique: Étant donné que le client a une relation un à plusieurs avec les Commandes, nous savons que la jonction des Clients aux Commandes entraînera des lignes en double par Client, et donc toutes les colonnes de la table des clients sont dupliquées dans les résultats. Vous remarquerez peut-être que c’est exactement le même scénario qui s’applique lors de la jonction des commandes à OrderDetails. Donc, nous gérons cette situation de la même manière! Nous résumons simplement nos Commandes par client d’abord, dans une table dérivée, puis nous joignons ces résultats à la table Client. Cela signifie qu’aucune colonne de la table Customer ne sera dupliquée et qu’il n’est pas nécessaire de toutes les ajouter à notre GROUPE PAR expression. Cela permet de garder notre SQL propre, organisé et logiquement sain.
Donc, nos résultats finaux ressemblent maintenant à ceci:
select c.Customer, c.customerName, c.City, c.State, o.OrderCount, o.ShippingTotal, o.ItemCount, o.OrderAmountfrom( select o.customer, count(*) as OrderCount, sum(o.ShippingCost) as ShippingTotal, sum(d.ItemCount) as ItemCount, sum(d.OrderAmount) as OrderAmount from orders o inner join ( select orderID, count(*) as ItemCount, sum(Amount) as OrderAmount from orderDetails group by orderID ) d on o.orderID = d.orderID group by o.customer) oinner join customers c on o.customer = c.customerCustomer customerName City State OrderCount ShippingTotal ItemCount OrderAmount---------- -------------------- --------------- ----- ----------- ------------- --------- -----------ABC ABC Corporation Boston MA 3 95.0000 6 725.0000DEF The DEF Foundation New York City NY 2 20.0000 3 475.0000(2 row(s) affected)
Conclusion
J’espère que cette série en deux parties vous aidera un peu à comprendre les requêtes GROUP BY. Il est essentiel d’identifier et de comprendre quelle est la clé primaire virtuelle d’un ensemble de résultats lorsque vous joignez plusieurs tables, et de reconnaître les lignes dupliquées ou non. De plus, rappelez-vous que COUNT (Distinct) peut être utile, mais SUM (Distinct) devrait très rarement, voire jamais, être utilisé.
En général, si vous trouvez que les valeurs que vous devez SUM() ont été dupliquées, résumez la table à l’origine de ces doublons séparément et joignez-la en tant que table dérivée. Cela vous permettra également de décomposer votre problème en étapes plus petites et de tester et de valider les résultats de chaque étape au fur et à mesure.
GROUP BY est une fonctionnalité très puissante, mais elle est également mal comprise et abusée, et le moyen le plus simple de l’exploiter est de construire soigneusement votre SQL à partir de pièces plus petites et plus simples en solutions plus grandes et plus compliquées.