Das Problem mit SUM(Distinct)
Wir haben zuvor gelernt, dass wir COUNT(Distinct) verwenden können, um Spalten aus der duplizierten Tabelle zu zählen, also was ist mit SUM(Distinct)? Es scheint, dass dies den Trick machen sollte, da wir nur unterschiedliche Versandkostenwerte summieren möchten, nicht alle Duplikate. Probieren wir es aus:
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)
Und da ist es! Wir scheinen unser Problem gelöst zu haben: wenn wir auf unsere Bestellungstabelle zurückblicken, können wir sehen, dass die Gesamtversandkosten pro Kunde jetzt korrekt aussehen.
Aber warte … Es ist eigentlich falsch!
Hier haben viele Menschen Probleme. Ja, die Daten sehen korrekt aus. Und für diese kleine Stichprobe ist es zufällig richtig. SUM(DISTINCT) funktioniert jedoch genauso wie COUNT(DISTINCT): Es werden einfach alle Werte abgerufen, die summiert werden können, alle doppelten Werte entfernt und dann die Ergebnisse addiert. Aber es beseitigt doppelte Werte, keine doppelten Zeilen basierend auf einer Primärschlüsselspalte! Es ist egal, dass die Versandkosten 40 zu OrderID # 1 gehörten und die Versandkosten 30 zu OrderID # 2; Es trennt sie einfach nicht auf diese Weise.
Der Ausdruck SUM(Distinct ShippingCost) wird grundsätzlich wie folgt ausgewertet:
- Nach dem Beitritt von Orders zu OrderDetails hat jede Gruppe die folgenden Versandkostenwerte:
Customer ABC: 40,40,30,30,30,25
Customer DEF: 10 - Da DISTINCT angefordert wurde, werden doppelte Werte aus diesen Listen entfernt:
Customer ABC: 40,40,30,30,30,25
Customer DEF: 10 - Und jetzt kann die SUMME () ausgewertet werden, indem die verbleibenden Werte addiert werden:
Customer ABC: 40+30+25 = 95
Kunden DEF: 10 = 10
Wenn Sie das Konzept nicht verstehen, sehen Sie das Problem möglicherweise immer noch nicht. Tatsächlich tun dies zu diesem Zeitpunkt viele Menschen nie. Sie sehen, dass SUM(x) große Zahlen zurückgibt, die nicht richtig sein können, also optimieren sie es und versuchen SUM(DISTINCT x) , und die Werte sehen viel vernünftiger aus, und sie könnten sogar anfänglich perfekt binden, also ab in die Produktion geht es. Die SQL ist jedoch falsch; es basiert auf der Tatsache, dass derzeit keine zwei Bestellungen für einen Kunden die gleichen Versandkosten haben.
Lassen Sie uns demonstrieren, indem wir eine weitere Bestellung hinzufügen:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
Wenn Sie das ausführen, wird einfach eine weitere Bestellung für den Kunden DEF mit Versandkosten von 10 USD und einem OrderDetail-Artikel für 125 USD hinzugefügt. Führen wir nun dieselbe AUSWAHL erneut aus, um zu sehen, wie sich diese neue Reihenfolge auf unsere Ergebnisse ausgewirkt hat:
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)
Die Spalten ItemCount, OrderAmount und OrderCount sehen gut aus. Aber die TotalShipping kosten für DEF zeigt immer noch $10! Was ist passiert!?
Können Sie es herausfinden? Denken Sie daran, wie SUM(Distinct) funktioniert! Es werden nur eindeutige Werte an die Funktion übergeben und Duplikate beseitigt. Beide Bestellungen für DEF hatten Versandkosten von 10 USD, und SUM(Distinct ShippingCost) ist es egal, dass die beiden 10 USD Werte für verschiedene Bestellungen gelten, es weiß nur, dass die 10 für den Kunden dupliziert werden, sodass die 10 nur einmal verwendet werden, um die SUMME zu berechnen. Daher wird ein Wert von 10 als Gesamtversandkosten für diese beiden Bestellungen zurückgegeben, obwohl er 10 + 10 = 20 betragen sollte. Unser Ergebnis ist jetzt falsch. Die lange und kurze davon ist dies: Verwenden Sie niemals SUM(Distinct) ! Es macht normalerweise in den meisten Situationen keinen logischen Sinn; es mag eine Zeit und einen Ort dafür geben, aber es ist definitiv nicht hier.
Zusammenfassung abgeleiteter Tabellen
Wie beheben wir das? Nun, wie bei vielen SQL-Problemen ist die Antwort einfach: Machen Sie es Schritt für Schritt, versuchen Sie nicht, alle Tabellen miteinander zu verbinden, und fügen Sie einfach SUM() und GROUP BY und DISTINCT fast zufällig hinzu, bis die Dinge funktionieren.
Bevor wir uns also Gedanken über die Gesamtsummen pro Kunde machen, sollten wir einen Schritt zurücktreten und uns auf die Rückgabe der Gesamtsummen pro Bestellung konzentrieren. Wenn wir zuerst Summen pro Bestellung zurückgeben können, können wir diese Bestellsummen einfach nach Kunden zusammenfassen und erhalten die Ergebnisse, die wir benötigen. Fassen wir die Tabelle OrderDetails zusammen, um 1 Zeile pro Bestellung mit dem ItemCount und dem Gesamtbestellbetrag zurückzugeben:
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)
Schön und einfach, leicht zu überprüfen, die Dinge sehen gut aus. Da wir nach OrderID gruppieren, können wir sagen, dass diese Ergebnisse einen virtuellen Primärschlüssel von OrderID – das heißt, es wird niemals doppelte Zeilen für dieselbe Reihenfolge geben. Eigentlich, Hier ist eine weitere Grundregel, an die Sie sich immer erinnern sollten:
Der virtuelle Primärschlüssel einer SELECT mit einer GROUP BY-Klausel sind immer die Ausdrücke, die in der GROUP BY angegeben sind.
Wir können diese SQL-Anweisung und diese Ergebnisse jetzt in einer eigenen abgeleiteten Tabelle kapseln. Wenn wir von der Tabelle Orders zur vorherigen SELECT als abgeleitete Tabelle joinen, erhalten wir:
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)
Lassen Sie uns diese Ergebnisse untersuchen. Es gibt nirgendwo doppelte Zeilen oder Werte; Es gibt genau eine Zeile pro Bestellung. Dies liegt daran, dass unsere abgeleitete Tabelle einen virtuellen Primärschlüssel von OrderID , sodass das Verknüpfen von Orders mit unserer abgeleiteten Tabelle niemals Duplikate erzeugt. Dies ist eine sehr nützliche und einfache Technik, um Duplikate zu vermeiden, wenn eine übergeordnete Tabelle mit einer untergeordneten Tabelle verknüpft wird: Fassen Sie die untergeordnete Tabelle zuerst nach dem Primärschlüssel der übergeordneten Tabelle in einer abgeleiteten Tabelle zusammen und verbinden Sie sie dann mit der übergeordneten Tabelle. Die Zeilen der übergeordneten Tabelle werden dann nie dupliziert und können genau zusammengefasst werden.
Jetzt haben wir unsere gesamte Artikelanzahl pro Bestellung sowie unseren gesamten Bestellbetrag pro Bestellung. Und wir können sehen, dass, wenn wir diese Ergebnisse zusammenfassen, unsere Versandkostenspalte in Ordnung ist, da sie niemals dupliziert wird. Keine Notwendigkeit für distinct. Tatsächlich können wir sogar einen regulären COUNT(*) -Ausdruck verwenden, um die Gesamtzahl der Bestellungen pro Kunde zu ermitteln!
Wir können also einfach „GROUP BY Customer“ zum vorherigen SQL hinzufügen, berechnen, was wir mit Aggregatfunktionen benötigen, und alle Spalten (wie OrderID) entfernen, die wir nicht zusammenfassen werden. Möglicherweise stellen Sie auch fest, dass der gesamte Artikelzähler pro Kunde zu diesem Zeitpunkt kein COUNT(*) -Ausdruck mehr ist; es ist eine einfache Summe () des ItemCount-Werts, der aus unserer abgeleiteten Tabelle zurückgegeben wird.
Hier ist das Ergebnis:
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)
Und da hast du es! Wir haben unsere Daten untersucht, die Auswirkungen unserer VERKNÜPFUNGEN logisch betrachtet, das Problem in kleinere Teile zerlegt und eine ziemlich einfache Lösung gefunden, von der wir wissen, dass sie schnell, effizient und genau ist.
Hinzufügen weiterer Tabellen a> SELECT
Angenommen, unser Schema enthält auch eine Tabelle mit Kunden:
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'
… und wir möchten auch den Namen, die Stadt und das Bundesland jedes Kunden in unseren vorherigen Ergebnissen zurückgeben. Eine Möglichkeit, dies zu tun, besteht darin, einfach die Customers-Tabelle zu unserem vorhandenen Join hinzuzufügen und dann die customer-Spalten zur SELECT-Klausel hinzuzufügen. Wenn Sie jedoch nicht auch alle Kundenspalten zur GRUPPE NACH hinzufügen, erhalten Sie eine Fehlermeldung, die angibt, dass Sie alle Spalten, die Sie anzeigen möchten, entweder gruppieren oder zusammenfassen müssen. Wir versuchen nicht, eine COUNT() oder eine SUM() von Name, City und State zu berechnen, daher ist es nicht sinnvoll, diese Spalten in einen Aggregatausdruck einzuschließen. Es scheint also, dass wir sie alle zu unserer GROUP BY Klausel hinzufügen müssen, um die Ergebnisse zu erhalten, die wir benötigen:
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)
Technisch funktioniert das, aber es scheint albern, alle diese Kundenspalten in der GROUP BY aufzulisten… Schließlich gruppieren wir nur nach Kunden, nicht nach den Attributen des Kunden, oder?
Interessant ist, dass wir bereits über die Lösung gesprochen haben und dieselbe Technik angewendet wird: Da der Kunde eine Eins-zu-Viele-Beziehung zu Bestellungen hat, wissen wir, dass das Verbinden von Kunden mit Bestellungen zu doppelten Zeilen pro Kunde führt und daher alle Spalten in der Kundentabelle in den Ergebnissen dupliziert werden. Möglicherweise stellen Sie fest, dass dies genau das gleiche Szenario ist, das beim Verknüpfen von Bestellungen mit OrderDetails gilt. Also gehen wir mit dieser Situation genauso um! Wir fassen unsere Bestellungen einfach zuerst nach Kunden in einer abgeleiteten Tabelle zusammen und verbinden diese Ergebnisse dann mit der Kundentabelle. Dies bedeutet, dass überhaupt keine Spalten aus der Kundentabelle dupliziert werden und nicht alle zu unserem Ausdruck GROUP BY hinzugefügt werden müssen. Dies hält unsere SQL sauber, organisiert und logisch solide.
Unsere Endergebnisse sehen also jetzt so aus:
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)
Fazit
Ich hoffe, diese zweiteilige Serie hilft ein wenig bei Ihrem Verständnis von GROUP BY queries. Es ist wichtig, den virtuellen Primärschlüssel einer Ergebnismenge zu identifizieren und zu verstehen, wenn Sie mehrere Tabellen verknüpfen, und zu erkennen, welche Zeilen dupliziert werden oder nicht. Denken Sie außerdem daran, dass COUNT(Distinct) nützlich sein kann, SUM(Distinct) jedoch sehr selten, wenn überhaupt, verwendet werden sollte.
Wenn Sie feststellen, dass Werte, die Sie SUM() müssen, dupliziert wurden, fassen Sie die Tabelle, die diese Duplikate verursacht, separat zusammen und fügen Sie sie als abgeleitete Tabelle hinzu. Auf diese Weise können Sie Ihr Problem auch in kleinere Schritte aufteilen und die Ergebnisse jedes Schritts testen und validieren.
GROUP BY ist eine sehr leistungsstarke Funktion, wird aber auch missverstanden und missbraucht, und der einfachste Weg, sie zu nutzen, besteht darin, Ihr SQL sorgfältig aus kleineren, einfacheren Teilen in größere, kompliziertere Lösungen umzuwandeln.