problemet med SUM(Distinct)
vi lärde oss tidigare att vi kan använda COUNT(Distinct) för att räkna kolumner från det duplicerade bordet, så hur är det med SUM(Distinct)? Det verkar som om det borde göra tricket, eftersom vi bara vill summera olika fraktkostnadsvärden, inte alla dubbletter. Låt oss ge det ett försö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)
och där är det! Vi verkar ha löst vårt problem: när vi ser tillbaka till vår Ordertabell kan vi se att den totala fraktkostnaden per kund nu ser korrekt ut.
men vänta … Det är faktiskt fel!
det är här många människor har problem. Ja, uppgifterna ser korrekta ut. Och för det här lilla provet råkar det bara slumpmässigt vara korrekt. Men SUM(DISTINCT) fungerar exakt samma som COUNT (DISTINCT): det blir helt enkelt alla värden som är berättigade att summeras, eliminerar alla dubbla värden och lägger sedan till resultaten. Men det eliminerar dubbla värden, inte dubbla rader baserat på någon primär nyckelkolumn! Det bryr sig inte om att fraktkostnaden 40 tillhörde orderID #1 och att fraktkostnaden 30 tillhörde OrderID #2; Det skiljer dem helt enkelt inte på det sättet.
uttryckssumman (distinkt ShippingCost) utvärderas i princip så här:
- efter att ha gått från order till OrderDetails har varje grupp följande Fraktkostnadsvärden:
kund ABC: 40,40,30,30,30,25
kund DEF: 10 - eftersom DISTINCT begärdes eliminerar det dubbla värden från dessa listor:
kund ABC: 40,40,30,30,30,25
kund Def: 10 - och nu kan det utvärdera summan () genom att lägga till de återstående värdena:
kund ABC: 40+30+25 = 95
kund DEF: 10 = 10
om du inte får konceptet kanske du fortfarande inte ser problemet. Faktum är att många människor aldrig gör det. De ser att SUM(x) returnerar stora tal som inte kan vara rätt, så de tweak det och försöker SUM (distinkt x), och värdena ser mycket mer rimliga ut, och de kan till och med initialt knyta ut perfekt, så av till produktion går det. Ändå är SQL felaktig; det är beroende av det faktum att för närvarande inga två beställningar för en kund har samma fraktkostnad.
låt oss visa genom att lägga till en annan ordning:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
Running som helt enkelt lägger till en annan Order för kund DEF, fraktkostnad på $10, med en OrderDetail-artikel för $125. Låt oss nu utföra samma val igen för att se hur denna nya Order påverkade våra resultat:
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)
kolumnerna ItemCount, Ordermount och OrderCount ser bra ut. Men den totala fraktkostnaden för DEF visar fortfarande $10! Vad hände!?
kan du räkna ut det? Kom ihåg hur SUM (distinkt) fungerar! Det tar bara distinkta värden som skickas till funktionen och eliminerar dubbletter. Båda beställningarna för DEF hade en fraktkostnad på $10, och SUM(Distinct ShippingCost) bryr sig inte om att de två $10-värdena är för olika beställningar, det vet bara att 10 dupliceras för kunden, så det använder bara 10 en gång för att beräkna summan. Således returnerar det ett värde på 10 som den totala fraktkostnaden för dessa två beställningar, även om det borde vara 10+10=20. Vårt resultat är nu fel. Den långa och korta av det är detta: använd aldrig SUM (distinkt) ! Det brukar inte vara logiskt i de flesta situationer; det kan finnas en tid och plats för det, men det är definitivt inte här.
sammanfattar härledda tabeller
så, hur fixar vi det här? Tja, som många SQL-problem är svaret enkelt: gör det ett steg i taget, försök inte gå med i alla tabellerna tillsammans och lägg bara till SUM() och gruppera och skilja nästan slumpmässigt tills sakerna fungerar; bryta ner det logiskt steg för steg.
så innan du oroar dig för totaler per kund, låt oss gå tillbaka och fokusera på att returnera totaler per beställning. Om vi kan returnera summor per Order först, då kan vi helt enkelt sammanfatta dessa order summor av kunden och vi får de resultat vi behöver. Låt oss sammanfatta tabellen OrderDetails för att returnera 1 rad per beställning, med Objekträkningen och det totala orderbeloppet:
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)
trevligt och enkelt, lätt att verifiera, saker ser bra ut. Eftersom vi grupperar på OrderID kan vi säga att dessa resultat har en virtuell primärnyckel för OrderID-det vill säga det kommer aldrig att finnas dubbla rader för samma ordning. Faktum är att här är en annan grundläggande regel att alltid komma ihåg:
den virtuella primärnyckeln för en SELECT med en GROUP BY-sats kommer alltid att vara de uttryck som anges i GROUP BY.
vi kan nu ta det SQL-uttalandet och dessa resultat och inkapsla dem i sin egen härledda tabell. Om vi går från tabellen order till föregående Välj som en härledd tabell 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)
låt oss undersöka dessa resultat. Det finns inga dubbla rader eller värden någonstans; det finns exakt en rad per Order. Detta beror på att vårt härledda bord har en virtuell primärnyckel för OrderID, så att gå från order till vårt härledda bord kommer aldrig att producera dubbletter. Detta är en mycket användbar och enkel teknik för att undvika dubbletter när du relaterar en överordnad tabell till ett barnbord: sammanfatta barntabellen med förälderns primära nyckel först i en härledd tabell och anslut sedan den till överordnad tabell. Den överordnade tabellens rader kommer då aldrig att dupliceras och kan sammanfattas korrekt.
nu har vi vårt totala antal artiklar per beställning, liksom vårt totala antal beställningar per beställning. Och vi kan se att om vi summerar dessa resultat kommer vår ShippingCost-kolumn att bli bra, eftersom den aldrig dupliceras. Inget behov av distinkt. Faktum är att vi till och med kan använda ett vanligt RÄKNEUTTRYCK(*) för att få det totala antalet beställningar per kund!
så vi kan helt enkelt lägga till ”GROUP BY Customer” till föregående SQL, beräkna vad vi behöver med aggregerade funktioner och ta bort alla kolumner (som OrderID) som vi inte kommer att sammanfatta. Du kanske också märker att den totala ItemCount per kund vid denna tidpunkt inte längre är ett RÄKNEUTTRYCK ( * ); det är en enkel summa() av ItemCount-värdet som returneras från vår härledda tabell.
här är 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)
och där har du det! Vi undersökte våra data, logiskt betraktade konsekvenserna av våra kopplingar, bröt problemet i mindre delar och slutade med en ganska enkel lösning som vi vet kommer att vara snabb, effektiv och korrekt.
lägga till fler tabeller en sammanfattad välj
för att avsluta saker, anta att vårt schema också har en tabell med 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'
… och vi vill också returnera varje kunds namn, stad och stat i våra tidigare resultat. Ett sätt att göra detta är att helt enkelt lägga till tabellen Kunder i vår befintliga koppling och sedan lägga till kundkolumnerna I select-klausulen. Men om du inte lägger till alla kundkolumner i gruppen med, får du ett felmeddelande som indikerar att du måste antingen gruppera eller sammanfatta alla kolumner du vill visa. Vi försöker inte beräkna en räkning() eller en summa () av namn, stad och stat, så det är inte meningsfullt att sätta in dessa kolumner i ett aggregerat uttryck. Så det verkar som om vi måste lägga till dem alla i vår grupp genom klausul för att få de resultat vi behöver:
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)
tekniskt fungerar det, men det verkar dumt att lista alla dessa kundkolumner i gruppen efter … När allt kommer omkring grupperar vi bara på kunden, inte på var och en av kundens attribut, eller hur?
det som är intressant är att lösningen är något vi redan pratat om och samma teknik gäller: Eftersom kunden har en en-till-många relation med order, vi vet att gå kunder till order kommer att resultera i dubbla rader per kund, och därmed alla kolumner i Kundtabellen dupliceras i resultaten. Du kanske märker att detta är exakt samma scenario som gäller när du ansluter order till OrderDetails. Så vi hanterar denna situation på samma sätt! Vi sammanfattar helt enkelt våra beställningar efter kund först, i en härledd tabell, och sedan går vi med i dessa resultat till Kundtabellen. Det betyder att inga kolumner från Kundtabellen kommer att vara dupicated alls, och det finns inget behov av att lägga till dem alla i vår grupp genom uttryck. Detta håller vår SQL ren, organiserad och logiskt ljud.
så, våra slutliga resultat ser nu ut så här:
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)
slutsats
jag hoppas att den här tvådelsserien hjälper lite med din förståelse av GROUP BY queries. Det är viktigt att identifiera och förstå vad den virtuella primärnyckeln för en resultatuppsättning är när du går med i flera tabeller och att känna igen vilka rader som dupliceras eller inte. Dessutom, kom ihåg att räkna(distinkt) kan vara användbart, men summa(distinkt) bör mycket sällan, om någonsin, användas.
i allmänhet, om du upptäcker att värden du behöver summera() har duplicerats, sammanfatta tabellen som orsakar dessa dubbletter separat och gå med i den som en härledd tabell. Detta gör att du också kan dela upp ditt problem i mindre steg och testa och validera resultaten av varje steg när du går.
GROUP BY är en mycket kraftfull funktion, men är också missförstådd och missbrukad, och det enklaste sättet att utnyttja det är att noggrant bygga din SQL från mindre, enklare delar till större, mer komplicerade lösningar.