problemet med SUM(distinkt)
vi lærte tidligere, at vi kan bruge COUNT(Distinct) til at tælle kolonner fra den duplikerede tabel, så hvad med SUM(Distinct)? Det ser ud til, at det burde gøre tricket, da vi kun ønsker at opsummere forskellige forsendelsesomkostningsværdier, ikke alle duplikater. Lad os prøve det:
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 synes at have løst vores problem: når vi ser tilbage på vores Ordretabel, kan vi se, at TotalShipping-omkostningerne pr.
men vent … Det er faktisk forkert!
det er her mange mennesker har problemer. Ja, dataene ser korrekte ud. Og for denne lille prøve sker det bare tilfældigt at være korrekt. Men SUM(DISTINCT) fungerer nøjagtigt det samme som COUNT (DISTINCT): det får simpelthen alle de værdier, der er berettigede til at blive opsummeret, eliminerer alle duplikatværdier og tilføjer derefter resultaterne. Men det er at fjerne dublerede værdier, ikke duplikere rækker baseret på nogle primære nøgle kolonne! Det er ligeglad med, at forsendelsesomkostninger 40 tilhørte orderID #1, og at forsendelsesomkostninger 30 tilhørte OrderID #2; Det adskiller dem simpelthen ikke på den måde.
udtrykket SUM (Distinct ShippingCost) vurderes grundlæggende som dette:
- efter tilslutning fra ordrer til OrderDetails har hver gruppe følgende Forsendelsesomkostningsværdier:
Customer ABC: 40,40,30,30,25
Customer DEF: 10 - da DISTINCT blev bedt om, eliminerer det duplikatværdier fra disse lister:
Customer ABC: 40,40,30,30,25
Customer DEF: 10 - og nu kan det evaluere summen() ved at tilføje de resterende værdier:
kunde ABC: 40+30+25 = 95
kunde DEF: 10 = 10
hvis du ikke får konceptet, kan du stadig ikke se problemet. Faktisk, på dette tidspunkt, mange mennesker gør aldrig. De ser, at SUM returnerer enorme tal, der ikke kan være rigtige, så de tilpasser det og prøver SUM(distinkt), og værdierne ser meget mere rimelige ud, og de kan endda oprindeligt binde perfekt ud, så det går til produktion. Endnu, KVL er forkert; det er at stole på, at i øjeblikket ikke to ordrer for en kunde har samme forsendelsesomkostninger.
lad os demonstrere ved at tilføje en anden ordre:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
løb, der blot tilføjer en anden ordre til kunde DEF, forsendelsesomkostninger på $10, med en OrderDetail vare til $125. Lad os nu udføre det samme valg igen for at se, hvordan denne nye ordre påvirkede vores resultater:
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)
kolonnerne ItemCount, OrderAmount og OrderCount ser godt ud. Men TotalShipping-omkostningerne for DEF viser stadig $10! Hvad skete der!?
kan du finde ud af det? Husk, hvordan SUM (Distinct) fungerer! Det tager bare forskellige værdier, der overføres til funktionen og eliminerer duplikater. Begge ordrer til DEF havde en forsendelsesomkostninger på $10, og SUM(Distinct ShippingCost) er ligeglad med, at de to $10-værdier er for forskellige ordrer, det ved bare, at 10 duplikeres for kunden, så det bruger kun 10 en gang til at beregne summen. Således returnerer den en værdi på 10 som den samlede forsendelsesomkostning for disse to ordrer, selvom den skal være 10+10=20. Vores resultat er nu forkert. Den lange og korte af det er dette: Brug aldrig SUM (Distinct) ! Det giver normalt ikke logisk mening i de fleste situationer; der kan være en tid og et sted for det, men det er bestemt ikke her.
opsummering af afledte tabeller
så hvordan løser vi dette? Nå, som mange problemer med KVL, er svaret simpelt: gør det et trin ad gangen, prøv ikke at slutte alle tabellerne sammen og bare tilføje SUM() og gruppere efter og adskille næsten tilfældigt, indtil tingene fungerer; nedbryde det logisk trin for trin.
så før vi bekymrer os om totaler pr.kunde, lad os gå tilbage og fokusere på at returnere totaler pr. ordre. Hvis vi først kan returnere totaler pr. ordre, kan vi blot opsummere disse ordretotaler efter kunde, og vi får de resultater, vi har brug for. Lad os opsummere tabellen OrderDetails for at returnere 1 række pr. ordre med Varetællingen og det samlede ordrebeløb:
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)
Nice og enkel, let at kontrollere, tingene ser godt ud. Fordi vi grupperer på OrderID, kan vi sige, at disse resultater har en virtuel primær nøgle til OrderID-det vil sige, at der aldrig vil være duplikatrækker for den samme rækkefølge. Faktisk er her en anden grundlæggende regel at altid huske:
den virtuelle primære nøgle til en udvalgt med en gruppe efter klausul vil altid være de udtryk, der er angivet i gruppen af.
vi kan nu tage denne erklæring og disse resultater og indkapsle dem i deres egen afledte tabel. Hvis vi tilmelder os fra tabellen ordrer til det forrige valg som en afledt tabel, 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)
lad os undersøge disse resultater. Der er ingen dublerede rækker eller værdier overalt; der er nøjagtigt en række pr. Dette skyldes, at vores afledte tabel har en virtuel primær nøgle til OrderID, så sammenføjning fra ordrer til vores afledte tabel vil aldrig producere dubletter. Dette er en meget nyttig og enkel teknik til at undgå dubletter, når du relaterer en overordnet tabel til en underordnet tabel: opsummer barnetabellen med forældrenes primære nøgle først i en afledt tabel, og sæt den derefter sammen med den overordnede tabel. Den overordnede tabels rækker vil så aldrig blive duplikeret og kan opsummeres nøjagtigt.
nu har vi vores samlede ItemCount per ordre, samt vores samlede Ordreamount per ordre. Og vi kan se, at hvis vi opsummerer disse resultater, vil vores shippingcost-kolonne være fint, da det aldrig duplikeres. Intet behov for distinkt. Faktisk kan vi endda bruge en regelmæssig optælling (*) udtryk for at få det samlede antal ordrer per kunde!
så vi kan blot tilføje “gruppe efter kunde” til den forrige KVL, beregne, hvad vi har brug for med aggregeringsfunktioner, og fjerne eventuelle kolonner (som OrderID), som vi ikke opsummerer. Du kan også bemærke, at på dette tidspunkt er det samlede ItemCount per kunde ikke længere et count(*) udtryk; det er en simpel SUM () af ItemCount-værdien, der returneres fra vores afledte tabel.
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øgte vores data, overvejede logisk konsekvenserne af vores sammenføjninger, brød problemet ned i mindre dele og endte med en ret simpel løsning, som vi ved vil være hurtig, effektiv og præcis.
tilføjelse af flere tabeller en opsummeret vælg
for at afslutte tingene, Antag, at vores skema også har en tabel 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å at returnere hver kundes navn, by og stat i vores tidligere resultater. En måde at gøre dette på er blot at tilføje tabellen kunder til vores eksisterende joinforbindelse og derefter tilføje kundekolonnerne til SELECT-klausulen. Medmindre du også tilføjer alle kundekolonnerne til gruppen, får du en fejlmeddelelse, der angiver, at du enten skal gruppere eller opsummere alle kolonner, du vil have vist. Vi forsøger ikke at beregne en optælling() eller en SUM() af navn, by og stat, så det giver ikke mening at pakke disse kolonner i et samlet udtryk. Så det ser ud til, at vi skal tilføje dem alle til vores gruppe efter klausul for at få de resultater, vi har brug for:
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 set fungerer det, men det virker fjollet at liste alle disse kundekolonner i gruppen efter … Når alt kommer til alt grupperer vi bare på kunden, ikke på hver af kundens attributter, ikke?
det interessante er, at løsningen er noget, vi allerede har talt om, og den samme teknik gælder: Da kunden har en en-til-mange relation til ordrer, ved vi, at sammenføjning af kunder til ordrer vil resultere i duplikatrækker pr.kunde, og dermed duplikeres alle kolonner i Kundetabellen i resultaterne. Du bemærker måske, at dette er nøjagtigt det samme scenario, der gælder, når du tilslutter ordrer til OrderDetails. Så vi håndterer denne situation på samme måde! Vi opsummerer blot vores ordrer efter kunde først, i en afledt tabel, og derefter slutter vi disse resultater til Kundetabellen. Det betyder, at ingen kolonner fra Kundetabellen overhovedet bliver dupliceret, og det er ikke nødvendigt at tilføje dem alle til vores gruppe ved udtryk. Dette holder vores kvalitetssikring ren, organiseret og logisk lyd.
så vores endelige resultater ser nu sådan ud:
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)
konklusion
jeg håber, at denne todelt serie hjælper lidt med din forståelse af gruppe efter forespørgsler. Det er vigtigt at identificere og forstå, hvad den virtuelle primære nøgle i et resultatsæt er, når du tilmelder dig flere tabeller, og at genkende, hvilke rækker der duplikeres eller ej. Husk desuden, at tælling(distinkt) kan være nyttigt, men SUM (distinkt) bør meget sjældent, hvis nogensinde, bruges.
generelt, hvis du finder ud af, at værdier, du har brug for at opsummere (), er blevet duplikeret, skal du opsummere tabellen, der forårsager disse dubletter separat, og slutte den til som en afledt tabel. Dette giver dig også mulighed for at opdele dit problem i mindre trin og teste og validere resultaterne af hvert trin, mens du går.
GROUP BY er en meget kraftfuld funktion, men er også misforstået og misbrugt, og den nemmeste måde at udnytte det på er at omhyggeligt bygge din enhed fra mindre, enklere dele til større, mere komplicerede løsninger.