Problemet MED SUM(Distinkt)
vi har tidligere lært at vi kan bruke COUNT (Distinkt) til å telle kolonner fra det dupliserte tabellen, så HVA MED SUM(Distinkt)? Det virker som det burde gjøre trikset, siden vi bare vil summere forskjellige fraktkostnadsverdier, ikke alle duplikatene. La oss gi det et forsøk:
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)
Og der er den! Vi ser ut til å ha løst vårt problem: når vi ser tilbake til Ordretabellen, kan Vi se At TotalShipping-kostnaden per Kunde nå ser riktig ut.
men vent … Det er faktisk feil!
dette er hvor mange mennesker har problemer. Ja, dataene ser riktig ut. Og for denne lille prøven skjer det bare tilfeldig å være riktig. MEN SUM (DISTINKT) fungerer nøyaktig det SAMME SOM COUNT (DISTINKT): det får bare alle verdiene som er kvalifisert til å bli summert, eliminerer alle dupliserte verdier, og legger deretter sammen resultatene. Men det eliminerer dupliserte verdier, ikke dupliserte rader basert på noen primærnøkkelkolonne! Det bryr seg ikke om at fraktkostnaden 40 tilhørte orderID # 1 og at fraktkostnaden 30 tilhørte OrderID # 2; det skiller dem ganske enkelt ikke på den måten.
uttrykket SUM (Distinkt ShippingCost) er i utgangspunktet evaluert som dette:
- Etter Å Ha Blitt Med Fra Ordre Til OrderDetails, har hver gruppe Følgende Fraktkostnadsverdier:
Kunde ABC: 40,40,30,30,30,25
Kunde DEF: 10 - SIDEN DISTINCT ble bedt Om, eliminerer det dupliserte verdier fra disse listene:
Kunde ABC: 40,40,30,30,30,25
Kunde DEF: 10 - og nå kan det evaluere SUMMEN () ved å legge opp de resterende verdiene:
Kunde ABC: 40+30+25 = 95
Customer DEF: 10 = 10
hvis du ikke får konseptet, kan du fortsatt ikke se problemet. Faktisk, på dette punktet, mange mennesker aldri gjør. DE ser AT SUMMEN (x) returnerer store tall som ikke kan være riktig, så de justerer DET OG prøver SUM (DISTINKT x), og verdiene ser mye mer fornuftig ut, og de kan til og med i utgangspunktet knytte seg perfekt, så til produksjon går det. LIKEVEL ER SQL feil; det er avhengig av det faktum at i dag ikke to bestillinger for en kunde har samme fraktkostnad.
la oss demonstrere ved å legge til en annen ordre:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
Kjører som bare legger til en Annen Ordre For Customer DEF, fraktkostnad på $10, med En OrderDetail-vare for $125. Nå, la oss utføre det SAMME VALGET igjen for å se hvordan denne nye Ordren påvirket resultatene våre:
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)
kolonnene ItemCount, OrderAmount og OrderCount ser bra ut. Men TotalShipping kostnaden FOR DEF viser fortsatt $10! Hva skjedde!?
Kan du finne ut det? Husk HVORDAN SUM (Distinkt) fungerer! Det tar bare distinkte verdier gått til funksjonen og eliminerer duplikater. BEGGE ordrene FOR DEF hadde en fraktkostnad på $10, OG SUM (Distinct ShippingCost) bryr seg ikke om at de to $10 verdiene er for forskjellige Ordrer, det vet bare at 10 er duplisert For Kunden, så det bruker bare 10 en gang for å beregne SUMMEN. Dermed returnerer den en verdi på 10 som den totale fraktkostnaden for de to ordrene, selv om den skal være 10 + 10=20. Vårt resultat er nå feil. Den lange og korte av det er dette: bruk ALDRI SUM (Distinkt) ! Det gir vanligvis ikke logisk mening i de fleste situasjoner; det kan være tid og sted for det, men det er definitivt ikke her.
Oppsummerer Avledede Tabeller
Så hvordan løser vi dette? Vel, som mange SQL-problemer, er svaret enkelt: Gjør det ett skritt om gangen, ikke prøv å bli med alle tabellene sammen og bare legg TIL SUM () OG GRUPPE ETTER og DISTINKT nesten tilfeldig til ting fungerer; bryte det ned logisk trinnvis.
Så, før du bekymrer deg for totaler per Kunde, la oss gå tilbake og fokusere på å returnere totaler per Ordre. Hvis vi kan returnere totaler per Ordre først, så kan vi bare oppsummere Disse ordretotalene Av Kunden, og vi får de resultatene vi trenger. La oss oppsummere OrderDetails-tabellen for å returnere 1 rad per Ordre, Med ItemCount og total Ordrebeløp:
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)
Fin og enkel, lett å verifisere, ting ser bra ut. Fordi vi grupperer På OrderID, kan vi si at disse resultatene har en virtuell primærnøkkel Av OrderID – det vil si at det aldri vil bli dupliserte rader for samme Rekkefølge. Faktisk, her er en annen grunnleggende regel å alltid huske:
den virtuelle primærnøkkelen TIL EN SELECT MED EN GROUP BY-setning vil alltid være uttrykkene som er angitt I GROUP BY.
Vi kan nå ta DEN SQL-setningen og disse resultatene og innkapsle dem i sin egen avledede tabell. Hvis Vi blir Med Fra Ordertabellen til forrige VALG som et avledet bord, får vi:
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)
la oss undersøke disse resultatene. Det er ingen dupliserte rader eller verdier hvor som helst; det er nøyaktig en rad per Ordre. Dette skyldes at vår avledede tabell har en virtuell primærnøkkel Av OrderID, så det å bli Med Fra Ordrer til vår avledede tabell vil aldri produsere duplikater. Dette er en veldig nyttig og enkel teknikk for å unngå duplikater når du knytter en overordnet tabell til en underordnet tabell: oppsummer den underordnede tabellen med foreldrenes primærnøkkel først i en avledet tabell, og legg den deretter sammen med den overordnede tabellen. Den overordnede tabellens rader vil da aldri bli duplisert og kan oppsummeres nøyaktig.
Nå har vi vår totale ItemCount per ordre, samt vår totale Ordreamount per ordre. Og vi kan se at hvis vi summere disse resultatene opp, Vår ShippingCost kolonnen vil bli bra, siden det er aldri duplisert. Ingen behov for distinkte. Faktisk kan vi også bruke en vanlig TELLE ( * ) uttrykk for å få det totale antall bestillinger per kunde!
så kan vi bare legge til «GRUPPE Etter Kunde» til forrige SQL, beregne hva vi trenger med aggregatfunksjoner, og fjern eventuelle kolonner (som OrderID) som vi ikke vil oppsummere. Du kan også legge merke til at på dette punktet, det totale ItemCount per Kunde er ikke lenger EN COUNT (*) uttrykk; det er en enkel SUM() Av ItemCount-verdien returnert fra vår avledede tabell.
her er resultatet:
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)
og der har du det! Vi undersøkte våre data, logisk vurdert implikasjonene av VÅRE SAMMENFØYNINGER, brøt problemet ned i mindre deler, og endte opp med en ganske enkel løsning som vi vet vil være rask, effektiv og nøyaktig.
Legge Til Flere Tabeller En Oppsummert VELG
for å fullføre ting, anta at vårt skjema også har en Tabell Over Kunder:
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'
… og vi ønsker også å returnere hver kundenes navn, by og stat i våre tidligere resultater. En måte å gjøre dette på er å bare legge Til Kundetabellen i vår eksisterende sammenføyning, og deretter legge til kundekolonnene I select-klausulen. Med mindre du legger til alle kundekolonnene I GRUPPEN BY, får du imidlertid en feilmelding som angir at du må gruppere eller oppsummere alle kolonnene du vil vise. Vi prøver ikke å beregne EN TELLE () ELLER EN SUM () Av Navn, By og Stat, så det er ikke fornuftig å pakke disse kolonnene i et samlet uttrykk. Så, det ser ut til at vi må legge dem alle TIL VÅR GRUPPE etter klausul for å få de resultatene vi trenger:
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)
Teknisk fungerer det, Men det virker dumt å liste alle disse kundekolonnene I GRUPPEN ETTER … Tross alt, vi er bare gruppering På Kunden, ikke på hver av kundens attributter,rett?
det som er interessant er at løsningen er noe vi allerede snakket om, og den samme teknikken gjelder: Siden Kunden har en en-til-mange-relasjon med Ordrer, vet Vi at å bli Med Kunder Til Ordrer vil resultere i dupliserte rader per Kunde, og dermed blir alle kolonnene i Kundetabellen duplisert i resultatene. Du kan legge merke til at dette er nøyaktig det samme scenariet som gjelder når du blir Med Ordre Til OrderDetails. Så, vi håndterer denne situasjonen på samme måte! Vi oppsummerer Bare Våre Bestillinger Av Kunden først, i en avledet tabell, og så blir vi med disse resultatene Til Kundetabellen. Dette betyr at ingen kolonner Fra Kundetabellen vil bli duplisert i det hele tatt, og det er ikke nødvendig å legge dem alle til I VÅR GRUPPE etter uttrykk. Dette holder VÅR SQL ren, organisert og logisk lyd.
så, våre endelige resultater ser nå slik ut:
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)
Konklusjon
jeg håper denne todelte serien hjelper litt med din forståelse AV GRUPPE ved spørsmål. Det er viktig å identifisere og forstå hva den virtuelle primærnøkkelen til et resultatsett er når du blir med i flere tabeller, og å gjenkjenne hvilke rader som dupliseres eller ikke. I tillegg må du huske AT COUNT (Distinct) kan være nyttig, MEN SUM (Distinct) bør svært sjelden, om noensinne, brukes.
generelt, hvis du finner at verdiene du TRENGER Å SUMMERE() har blitt duplisert, oppsummer tabellen som forårsaker disse duplikatene separat og bli med som en avledet tabell. Dette vil også tillate deg å bryte ned problemet ditt i mindre trinn og teste og validere resultatene av hvert trinn mens du går.
GROUP BY ER en veldig kraftig funksjon, men er også misforstått og misbrukt, og den enkleste måten å utnytte det er å nøye bygge SQL fra mindre, enklere deler til større, mer kompliserte løsninger.