problema cu SUM(Distinct)
am învățat anterior că putem folosi COUNT(Distinct) pentru a număra coloane din tabelul duplicat, deci ce zici de SUM(Distinct)? Se pare că ar trebui să facă trucul, deoarece vrem doar să însumăm valori distincte ale costurilor de expediere, nu toate duplicatele. Să încercăm:
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 iată-l! Se pare că ne-am rezolvat problema: privind înapoi la tabelul nostru de comenzi, putem vedea că costul TotalShipping pe client pare acum corect.
dar așteptați … Este de fapt greșit!
acest lucru este în cazul în care mulți oameni au probleme. Da, datele arată corect. Și, pentru acest mic eșantion, se întâmplă la întâmplare să fie corect. Dar SUM(DISTINCT) funcționează exact la fel ca COUNT (DISTINCT): pur și simplu obține toate valorile eligibile pentru a fi însumate, elimină toate valorile duplicate și apoi adaugă rezultatele. Dar elimină valorile duplicate, nu rândurile duplicate bazate pe o coloană cheie primară! Nu-i pasă că costul de expediere 40 a aparținut orderID #1 și că costul de expediere 30 a aparținut OrderID #2; pur și simplu nu le separă în acest fel.
suma expresiei (Distinct ShippingCost) este practic evaluată astfel:
- după aderarea la comenzi la OrderDetails, fiecare grup are următoarele valori de cost de transport maritim:
client ABC: 40,40,30,30,30,25
client Def: 10 - deoarece DISTINCT a fost cerut, se elimină valorile duplicat din aceste liste:
client ABC: 40,40,30,30,30,25
client Def: 10 - și acum se poate evalua suma () prin adăugarea la valorile rămase:
client ABC: 40+30+25 = 95
DEF Client: 10 = 10
dacă nu primiți conceptul, este posibil să nu vedeți problema. De fapt, în acest moment, mulți oameni nu o fac niciodată. Ei văd că SUM(x) returnează numere uriașe care nu pot fi corecte, așa că o Modifică și încearcă SUM (X DISTINCT), iar valorile arată mult mai rezonabile și s-ar putea chiar să se lege inițial perfect, așa că pleacă la producție. Cu toate acestea, SQL este incorectă; se bazează pe faptul că în prezent nu există două comenzi pentru un client care să aibă același cost de expediere.
să demonstrăm adăugând o altă comandă:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
rularea care adaugă pur și simplu o altă comandă pentru client DEF, costul de transport maritim de $10, cu un element OrderDetail pentru $125. Acum, să executăm același SELECT din nou pentru a vedea cum această nouă comandă a afectat rezultatele noastre:
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)
coloanele ItemCount, OrderAmount și OrderCount arată grozav. Dar costul TotalShipping pentru DEF arată încă $10! Ce s-a întâmplat!?
îți dai seama? Amintiți-vă cum funcționează suma(distinctă)! Este nevoie doar de valori distincte transmise funcției și elimină duplicatele. Ambele comenzi pentru DEF au avut un cost de expediere de 10 USD, iar SUM(Distinct ShippingCost) nu-i pasă că cele două valori de 10 USD sunt pentru comenzi diferite, știe doar că 10 este duplicat pentru client, deci folosește doar 10 o dată pentru a calcula suma. Astfel, returnează o valoare de 10 ca cost total de expediere pentru aceste două comenzi, chiar dacă ar trebui să fie 10+10=20. Rezultatul nostru este acum greșit. Lung și scurt de ea este aceasta: nu utilizați niciodată suma(Distinct) ! De obicei, nu are sens logic în majoritatea situațiilor; s-ar putea să existe un timp și un loc pentru asta, dar cu siguranță nu este aici.
rezumând tabele derivate
Deci, cum rezolvăm acest lucru? Ei bine, la fel ca multe probleme SQL, răspunsul este simplu: faceți-l pas cu pas, nu încercați să vă alăturați tuturor tabelelor împreună și doar adăugați SUM() și GROUP BY și DISTINCT aproape aleatoriu până când lucrurile funcționează; descompuneți-l logic pas cu pas.
deci, înainte de a vă îngrijora de totalurile pe client, să facem un pas înapoi și să ne concentrăm pe returnarea totalurilor pe comandă. Dacă putem returna mai întâi totalurile pe comandă, atunci putem rezuma pur și simplu acele totaluri ale comenzilor de către Client și vom avea rezultatele de care avem nevoie. Să rezumăm tabelul OrderDetails pentru a returna 1 rând pe Comandă, cu ItemCount și suma totală a Comenzii:
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)
frumos și simplu, ușor de verificat, lucrurile arată bine. Deoarece ne grupăm pe OrderID, putem spune că aceste rezultate au o cheie primară virtuală a OrderID-adică nu vor exista niciodată rânduri duplicate pentru aceeași ordine. De fapt, iată o altă regulă de bază de reținut întotdeauna:
cheia primară virtuală a unui SELECT cu o clauză GROUP BY va fi întotdeauna expresiile enunțate în GROUP BY.
putem acum să luăm acea instrucțiune SQL și acele rezultate și să le încapsulăm în propriul tabel derivat. Dacă ne alăturăm din tabelul Comenzi la selecția anterioară ca tabel derivat, obținem:
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)
să examinăm aceste rezultate. Nu există rânduri sau valori duplicate nicăieri; există exact un rând pe comandă. Acest lucru se datorează faptului că tabelul nostru derivat are o cheie primară virtuală a OrderID, astfel încât aderarea de la comenzi la tabelul nostru derivat nu va produce niciodată duplicate. Aceasta este o tehnică foarte utilă și simplă pentru a evita duplicatele atunci când raportați un tabel părinte la un tabel copil: rezumați tabelul copil după cheia primară a părintelui mai întâi într-un tabel derivat și apoi alăturați-l la tabelul părinte. Rândurile tabelului părinte nu vor fi niciodată duplicate și pot fi rezumate cu exactitate.
acum avem Numărul total de articole pe comandă, precum și numărul total de comenzi pe comandă. Și putem vedea că dacă rezumăm aceste rezultate, coloana noastră de costuri va fi bine, deoarece nu este niciodată duplicată. Nu este nevoie de distincții. De fapt, putem folosi chiar și un număr regulat(*) expresie pentru a obține numărul total de comenzi pe client!
Deci, putem pur și simplu să adăugăm „grup după Client” la SQL-ul anterior, să calculăm ce avem nevoie cu funcții agregate și să eliminăm orice coloane (cum ar fi OrderID) pe care nu le vom rezuma. S-ar putea observa, de asemenea, că în acest moment, ItemCount totală pe client nu mai este o COUNT(*) Expresie; este o sumă simplă () a valorii ItemCount returnate din tabelul nostru derivat.
iată rezultatul:
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 nu – l ai! Ne-am examinat datele, am analizat logic implicațiile asociațiilor noastre, am împărțit problema în părți mai mici și am ajuns la o soluție destul de simplă, despre care știm că va fi rapidă, eficientă și precisă.
adăugarea mai multor tabele a rezumat selectați
pentru a termina lucrurile, să presupunem că schema noastră are, de asemenea, un tabel de clienți:
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'
… și dorim să returnăm, de asemenea, numele, orașul și statul fiecărui client în rezultatele noastre anterioare. O modalitate de a face acest lucru este să adăugați pur și simplu tabelul clienți la asocierea noastră existentă, apoi să adăugați coloanele client la clauza SELECT. Cu toate acestea, cu excepția cazului în care adăugați și toate coloanele clientului la grup, veți primi un mesaj de eroare care indică faptul că trebuie să grupați sau să rezumați toate coloanele pe care doriți să le afișați. Nu încercăm să calculăm un număr () sau o sumă () de nume, oraș și stat, deci nu are sens să înfășurăm acele coloane într-o expresie agregată. Deci, se pare că trebuie să le adăugăm pe toate la clauza noastră GROUP BY pentru a obține rezultatele de care avem nevoie:
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)
din punct de vedere tehnic, funcționează, dar pare o prostie să enumerăm toate coloanele clienților din grup … La urma urmei, ne grupăm doar pe client, nu pe fiecare dintre atributele Clientului, nu?
ceea ce este interesant este că soluția este ceva despre care am vorbit deja și se aplică aceeași tehnică: Deoarece clientul are o relație unu-la-mulți cu comenzile, știm că aderarea clienților la comenzi va avea ca rezultat rânduri duplicate pe client și, astfel, toate coloanele din tabelul clientului sunt duplicate în rezultate. S-ar putea să observați că acesta este exact același scenariu care se aplică atunci când vă alăturați Orders to OrderDetails. Deci, ne ocupăm de această situație în același mod! Noi pur și simplu rezuma comenzile noastre de client în primul rând, într-un tabel derivat, și apoi ne-am alătura aceste rezultate la tabelul de client. Acest lucru înseamnă că nici o coloană din tabelul de client va fi dupicat la toate, și nu este nevoie să le adăugați la grupul nostru de Expresie. Acest lucru menține SQL nostru curat, organizat, și logic sunet.
deci, rezultatele noastre finale arată astfel:
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)
concluzie
sper că această serie de două părți vă ajută puțin la înțelegerea grupului prin interogări. Este vital să identificați și să înțelegeți care este cheia primară virtuală a unui set de rezultate atunci când vă alăturați mai multor tabele și să recunoașteți ce rânduri sunt duplicate sau nu. În plus, amintiți-vă că numărul(Distinct) poate fi util, dar suma(distinctă) ar trebui foarte rar, dacă vreodată, să fie utilizată.
în general, dacă găsiți că valorile pe care trebuie să le însumați() au fost duplicate, rezumați tabelul care cauzează aceste duplicate separat și alăturați-l ca un tabel derivat. Acest lucru vă va permite, de asemenea, să vă descompuneți problema în pași mai mici și să testați și să validați rezultatele fiecărui pas pe măsură ce mergeți.
GROUP BY este o caracteristică foarte puternică, dar este, de asemenea, înțeleasă greșit și abuzată, iar cel mai simplu mod de a o folosi este să vă construiți cu atenție SQL-ul din părți mai mici și mai simple în soluții mai mari și mai complicate.