Problem z SUM(Distinct)
wcześniej dowiedzieliśmy się, że możemy użyć COUNT(Distinct) do zliczania kolumn z zduplikowanej tabeli, więc co z SUM(Distinct)? Wydaje się, że powinno to załatwić sprawę, ponieważ chcemy tylko zsumować różne wartości kosztów wysyłki, a nie wszystkie duplikaty. Spróbujmy:
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)
i oto jest! Wygląda na to, że rozwiązaliśmy nasz problem: patrząc wstecz na naszą tabelę zamówień, widzimy, że całkowity koszt wysyłki na klienta wygląda teraz poprawnie.
ale poczekaj … To jest naprawdę złe!
tutaj wiele osób ma problemy. Tak, dane wyglądają poprawnie. I dla tej małej próbki, to po prostu przypadkowo się zgadza. Ale SUM (DISTINCT) działa dokładnie tak samo jak COUNT (DISTINCT): po prostu pobiera wszystkie wartości kwalifikujące się do zsumowania, eliminuje wszystkie zduplikowane wartości, a następnie sumuje wyniki. Ale eliminuje zduplikowane wartości, a nie zduplikowane wiersze w oparciu o kolumnę klucza podstawowego! Nie obchodzi mnie, że koszt wysyłki 40 należał do orderID #1, a koszt wysyłki 30 należał do OrderID #2; po prostu nie dzieli ich w ten sposób.
suma wyrażeń (Distinct ShippingCost) jest w zasadzie obliczana w następujący sposób:
- po dołączeniu ze zleceń do OrderDetails każda grupa ma następujące wartości kosztów wysyłki:
customer ABC: 40,40,30,30,30,25
Customer DEF: 10 - ponieważ poproszono o DISTINCT, eliminuje on zduplikowane wartości z tych list:
Customer ABC: 40,40,30,30,30,25
Customer DEF: 10 - i teraz może obliczyć SUM () dodając pozostałe wartości:
ABC klienta: 40+30+25 = 95
Klient DEF: 10 = 10
jeśli nie rozumiesz koncepcji, nadal możesz nie zobaczyć problemu. W rzeczywistości, w tym momencie, wielu ludzi nigdy nie robi. Widzą, że SUM (x) zwraca ogromne liczby, które nie mogą być prawidłowe, więc poprawiają je i próbują SUM (DISTINCT x), a wartości wyglądają o wiele bardziej rozsądnie, a mogą nawet początkowo wiązać się idealnie, więc do produkcji idzie. Jednak SQL jest niepoprawny; polega na tym, że obecnie żadne dwa zamówienia dla klienta nie mają takich samych kosztów wysyłki.
zademonstrujmy dodając kolejne zlecenie:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
uruchomienie, które po prostu dodaje kolejne zamówienie dla klienta DEF, koszt wysyłki $ 10, z jednym zamówieniem za $125. Teraz wykonajmy ten sam wybór ponownie, aby zobaczyć, jak to nowe zamówienie wpłynęło na nasze wyniki:
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)
kolumny ItemCount, OrderAmount i OrderCount wyglądają świetnie. Ale całkowity koszt wysyłki dla DEF nadal pokazuje $10! Co się stało!?
Pamiętaj, jak działa SUM (Distinct)! Po prostu pobiera różne wartości przekazywane do funkcji i eliminuje duplikaty. Oba zamówienia dla DEF miały koszt wysyłki $10, a SUM (Distinct ShippingCost) nie obchodzi, że dwie wartości $10 są dla różnych zamówień, po prostu wie, że 10 jest duplikowane dla klienta, więc używa tylko 10 raz do obliczenia sumy. W ten sposób zwraca wartość 10 jako całkowity koszt wysyłki dla tych dwóch zamówień, mimo że powinien wynosić 10+10=20. Nasz wynik jest teraz zły. Długie i krótkie jest to: nigdy nie używaj SUM (Distinct) ! Zwykle nie ma to logicznego sensu w większości sytuacji; może i jest na to czas i miejsce, ale na pewno nie tutaj.
Podsumowując pochodne tabele
więc, jak to naprawić? Cóż, podobnie jak wiele problemów SQL, odpowiedź jest prosta: zrób to krok po kroku, nie próbuj łączyć wszystkich tabel razem i po prostu dodaj SUM() i grupuj według I rozróżniaj prawie losowo, aż wszystko zadziała; rozbij to logicznie krok po kroku.
więc zanim zaczniesz martwić się o sumy na klienta, cofnijmy się i skupmy się na zwracaniu kwot na zamówienie. Jeśli możemy najpierw zwrócić sumy na zamówienie, możemy po prostu podsumować te sumy zamówień według klienta i będziemy mieli potrzebne wyniki. Podsumujmy tabelę OrderDetails, aby zwrócić 1 wiersz na zamówienie, zliczeniem ItemCount i całkowitą kwotą zamówienia:
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)
ładne i proste, łatwe do zweryfikowania, wszystko wygląda dobrze. Ponieważ grupujemy na OrderID, możemy powiedzieć, że te wyniki mają wirtualny klucz podstawowy OrderID-to znaczy, że nigdy nie będzie duplikatów wierszy dla tej samej kolejności. W rzeczywistości, oto kolejna podstawowa zasada, którą należy zawsze pamiętać:
wirtualny klucz podstawowy SELECT z klauzulą GROUP BY zawsze będzie wyrażeniem zawartym w GROUP BY.
możemy teraz wziąć to polecenie SQL i te wyniki i zamknąć je w ich własnej tabeli pochodnej. Jeśli dołączymy z tabeli Orders do poprzedniego SELECT jako tabeli pochodnej, otrzymamy:
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)
zbadajmy te wyniki. Nigdzie nie ma duplikatów wierszy ani wartości; jest dokładnie jeden wiersz na zamówienie. Dzieje się tak, ponieważ nasza tabela pochodna ma wirtualny klucz podstawowy OrderID, więc połączenie z Orders do naszej tabeli pochodnej nigdy nie spowoduje duplikatów. Jest to bardzo użyteczna i prosta technika pozwalająca uniknąć duplikatów podczas powiązania tabeli nadrzędnej z tabelą podrzędną: podsumuj tabelę podrzędną za pomocą klucza podstawowego rodzica w tabeli pochodnej, a następnie dołącz ją do tabeli nadrzędnej. Wiersze tabeli nadrzędnej nigdy nie będą powielane i można je dokładnie podsumować.
teraz mamy naszą całkowitą liczbę elementów na zamówienie, a także naszą całkowitą liczbę zamówień na zamówienie. I widzimy, że jeśli zsumujemy te wyniki, nasza kolumna ShippingCost będzie w porządku, ponieważ nigdy nie jest powielana. Nie trzeba się wyróżniać. W rzeczywistości możemy nawet użyć zwykłego wyrażenia COUNT (*), aby uzyskać całkowitą liczbę zamówień na klienta!
możemy więc po prostu dodać „GROUP BY Customer” do poprzedniego SQL, obliczyć to, czego potrzebujemy za pomocą funkcji agregujących i usunąć wszelkie kolumny (takie jak OrderID), których nie będziemy podsumowywać. Możesz również zauważyć, że w tym momencie całkowita liczba elementów na klienta nie jest już wyrażeniem COUNT (*); jest to prosta suma () wartości ItemCount zwracanej z naszej tabeli pochodnej.
oto wynik:
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)
i oto jest! Zbadaliśmy nasze dane, logicznie rozważyliśmy konsekwencje naszych połączeń, podzieliliśmy problem na mniejsze części i skończyliśmy z dość prostym rozwiązaniem, które, jak wiemy, będzie szybkie, wydajne i dokładne.
dodawanie więcej tabel podsumowane wybierz
aby zakończyć rzeczy, Załóżmy, że nasz schemat ma również tabelę klientów:
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'
… chcemy również zwrócić nazwę, Miasto i stan każdego klienta w naszych poprzednich wynikach. Jednym ze sposobów, aby to zrobić, jest po prostu dodanie tabeli klientów do naszego istniejącego join, a następnie dodanie kolumn klienta do klauzuli SELECT. Jeśli jednak nie dodasz wszystkich kolumn klienta do grupy, pojawi się komunikat o błędzie informujący, że musisz grupować lub podsumowywać wszystkie kolumny, które chcesz wyświetlić. Nie próbujemy obliczać COUNT() ani SUM () nazwy, miasta i stanu, więc nie ma sensu owijać tych kolumn wyrażeniem zbiorczym. Wygląda więc na to, że musimy dodać je wszystkie do naszej klauzuli GROUP BY, aby uzyskać wyniki, których potrzebujemy:
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)
technicznie, to działa, ale wydaje się głupie, aby wymienić wszystkie te kolumny klientów w grupie przez … W końcu grupujemy się na kliencie, a nie na każdym z atrybutów klienta, prawda?
ciekawe jest to, że rozwiązanie jest czymś, o czym już rozmawialiśmy i ta sama technika dotyczy: Ponieważ klient ma relację jeden do wielu z zamówieniami, wiemy, że połączenie klientów z zamówieniami spowoduje duplikację wierszy na klienta, a zatem wszystkie kolumny w tabeli klientów są duplikowane w wynikach. Możesz zauważyć, że jest to dokładnie ten sam scenariusz, który ma zastosowanie przy łączeniu zamówień do OrderDetails. Więc radzimy sobie z tą sytuacją w ten sam sposób! Po prostu najpierw podsumowujemy nasze zamówienia według klienta w tabeli pochodnej, a następnie łączymy te wyniki z tabelą klienta. Oznacza to, że żadne kolumny z tabeli klienta nie będą w ogóle duplikowane i nie ma potrzeby dodawania ich wszystkich do naszej grupy poprzez wyrażenie. Dzięki temu nasz SQL jest czysty, zorganizowany i logicznie zdrowy.
więc nasze końcowe wyniki wyglądają teraz tak:
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)
wniosek
mam nadzieję, że ta dwuczęściowa seria pomoże Ci trochę zrozumieć grupę przez zapytania. Ważne jest, aby zidentyfikować i zrozumieć, czym jest wirtualny klucz podstawowy zestawu wyników, gdy dołączasz do wielu tabel, i rozpoznać, które wiersze są duplikowane, czy nie. Ponadto pamiętaj, że COUNT(Distinct) może być użyteczne, ale SUM(Distinct) powinno być bardzo rzadko, jeśli w ogóle, używane.
ogólnie rzecz biorąc, jeśli stwierdzisz, że wartości, których potrzebujesz do sum (), zostały zduplikowane, podsumuj tabelę powodującą te duplikaty osobno i dołącz ją jako tabelę pochodną. Pozwoli to również na rozbicie problemu na mniejsze kroki i przetestowanie i walidację wyników każdego kroku.
GROUP BY jest bardzo potężną funkcją, ale jest również źle rozumiana i nadużywana, a najprostszym sposobem na jej wykorzystanie jest staranne zbudowanie SQL z mniejszych, prostszych części w większe, bardziej skomplikowane rozwiązania.