ongelma summan(erillisellä)
kanssa opimme aiemmin, että voimme laskea sarakkeita kahdennetusta taulukosta, joten entä summa(erillisellä)? Näyttää siltä, että pitäisi tepsiä, koska haluamme vain summa eri toimituskulut arvot, ei kaikki kaksoiskappaleet. Kokeillaan.:
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)
ja siinä se on! Olemme ratkaisseet ongelmamme.: tarkastellessamme Tilaustaulukkoamme voimme nähdä, että Kokonaishipping-hinta asiakasta kohti näyttää nyt oikealta.
mutta odota … Se on itse asiassa väärin!
tässä kohtaa monilla on ongelmia. Kyllä, tiedot näyttävät oikeilta. Ja, tämä pieni otos, se vain sattumanvaraisesti sattuu olemaan oikein. Mutta summa(erillinen) toimii täsmälleen sama kuin COUNT (erillinen): se yksinkertaisesti saa kaikki arvot voidaan summata, poistaa kaikki päällekkäiset arvot, ja sitten yhteen tulokset. Mutta se poistaa päällekkäisiä arvoja, ei päällekkäisiä rivejä, jotka perustuvat johonkin ensisijaiseen avainsarakkeeseen! Se ei välitä, että toimituskulut 40 kuului orderID #1 ja että toimituskulut 30 kuului OrderID #2; se ei yksinkertaisesti erota niitä siten.
lausekkeen summa (erillinen Lähetyskustannus) arvioidaan periaatteessa näin:
- liityttyään tilauksista Tilauspyyntöihin, jokaisella ryhmällä on seuraavat toimituskulut:
asiakkaan ABC: 40,40,30,30,30,25
asiakkaan DEF: 10 - koska erillistä pyydettiin, se poistaa päällekkäiset arvot kyseisistä luetteloista:
asiakkaan ABC: 40,40,30,30,30,25
asiakkaan DEF: 10 - Ja nyt se voi arvioida summan() laskemalla yhteen jäljellä olevat arvot:
asiakkaan ABC: 40+30+25 = 95
asiakas DEF: 10 = 10
jos et ymmärrä konseptia, et silti välttämättä näe ongelmaa. Itse asiassa, tässä vaiheessa, monet ihmiset eivät koskaan. He näkevät, että summa (x) palauttaa valtavia numeroita, jotka eivät voi olla oikeassa, joten he nipistää sitä ja yrittää summa(erillinen x), ja arvot näyttävät paljon järkevämmiltä, ja he saattavat jopa aluksi sitoa täydellisesti, joten pois tuotantoon se menee. Silti SQL on virheellinen; se perustuu siihen, että tällä hetkellä ei ole kahta tilausta asiakkaalle on sama toimituskulut.
osoitetaan lisäämällä toinen järjestys:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
käynnissä, että yksinkertaisesti lisää toisen tilauksen asiakkaan DEF, toimituskulut $10, yhdellä Tilataksa kohde $125. Nyt, suoritetaan sama valitse uudelleen nähdä, miten tämä uusi järjestys vaikuttaa tuloksiin:
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)
ItemCount, OrderAmount ja OrderCount sarakkeet näyttävät hyvältä. Mutta DEF: n TotalShipping-hinta näyttää edelleen 10 dollaria! Mitä tapahtui??
saatko sen selville? Muista, miten summa (erillinen) toimii! Se vain vie erillisiä arvoja siirretään toiminto ja poistaa päällekkäisyyksiä. Molemmat tilaukset DEF oli toimituskulut $10, ja summa(erillinen ShippingCost) ei välitä, että kaksi $10 arvot ovat eri tilauksia, se vain tietää, että 10 on päällekkäinen asiakkaalle, joten se käyttää vain 10 kerran laskea summa. Näin ollen se palauttaa arvon 10 näiden kahden tilauksen kokonaislähetyskustannuksena, vaikka sen pitäisi olla 10+10=20. Tuloksemme on nyt väärä. Pitkä ja lyhyt se on tämä: Älä koskaan käytä summa (erillinen) ! Se ei yleensä ole loogista useimmissa tilanteissa; sille voi olla aikansa ja paikkansa, mutta se ei todellakaan ole täällä.
Yhteenveto johdetuista taulukoista
Joten, miten tämä korjataan? No, kuten monet SQL ongelmia, vastaus on yksinkertainen: tee se askel kerrallaan, älä yritä liittyä kaikki taulukot yhteen ja vain lisätä summa() ja ryhmä ja erilliset lähes satunnaisesti, kunnes asiat toimivat; murtaa se alas loogisesti askel askeleelta.
joten ennen kuin murehditaan kokonaissummia per asiakas, otetaan askel taaksepäin ja keskitytään palauttamaan kokonaissummat per tilaus. Jos voimme palauttaa summat tilauskohtaisesti ensin, sitten voimme yksinkertaisesti tiivistää ne Tilaussummat asiakkaan ja meillä on tulokset tarvitsemme. Tehdään yhteenveto Tilaustiedotteet taulukko palauttaa 1 rivi per tilaus, jossa ItemCount ja koko tilauksen määrä:
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)
mukava ja yksinkertainen, helppo tarkistaa, asiat näyttävät hyvältä. Koska ryhmittelemme Orderidiin, voimme sanoa, että näillä tuloksilla on virtuaalinen ensisijainen avain Orderidiin-eli ei tule koskaan olemaan päällekkäisiä rivejä samaan järjestykseen. Itse asiassa, tässä on toinen perussääntö aina muistaa:
ryhmä lausekkeelta varustetun Selectin virtuaalinen ensisijainen avain ovat aina lausekkeet, jotka lausutaan ryhmässä.
voimme nyt ottaa tuon SQL-lausekkeen ja nuo tulokset ja kapseloida ne omaan johdettuun taulukkoonsa. Jos liitymme Tilaustaulukosta edelliseen SELECT-taulukkoon johdettuna taulukkona, saamme:
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)
katsotaanpa tuloksia. Ei ole päällekkäisiä rivejä tai arvoja missään; on täsmälleen yksi rivi per tilaus. Tämä johtuu siitä, että meidän johdettu taulukko on virtuaalinen ensisijainen avain OrderID, joten liittyminen tilauksia meidän johdettu taulukko ei koskaan tuota kaksoiskappaleita. Tämä on erittäin hyödyllinen ja yksinkertainen tekniikka päällekkäisyyksien välttämiseksi, kun vanhempainpöytä liitetään lapsen taulukkoon: summaa lapsi taulukko vanhemman ensisijaisella avaimella ensin johdettuun taulukkoon ja liitä se sitten vanhempainpöytään. Emotaulukon rivejä ei sitten koskaan monisteta, ja ne voidaan tiivistää tarkasti.
nyt meillä on tilauskohtainen Kokonaissummamäärä sekä tilauskohtainen Kokonaissummamäärä. Ja voimme nähdä, että jos laskemme nämä tulokset yhteen, Lähetyskustannussarakkeemme on kunnossa, koska sitä ei koskaan toisteta. Ei tarvitse erottautua. Itse asiassa voimme jopa käyttää säännöllistä COUNT (*) lauseke saada kokonaismäärä tilauksia per asiakas!
voimme siis yksinkertaisesti lisätä edelliseen SQL: ään ”asiakasryhmittäin”, laskea yhteenlasketuilla funktioilla, ja poistaa kaikki sarakkeet (kuten OrderID), joita emme summaa. Saatat myös huomata, että tässä vaiheessa asiakasmäärää ei enää lasketa (*).; se on johdetusta taulukostamme palautetun ItemCount-arvon yksinkertainen summa ().
tässä tulos:
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)
ja siinä se on! Tutkimme tietojamme, loogisesti pohdimme LIITTYMIEMME vaikutuksia, hajosimme ongelman pienempiin osiin ja päädyimme melko yksinkertaiseen ratkaisuun, jonka tiedämme olevan nopea, tehokas ja tarkka.
lisäämällä lisää taulukoita tiivistetty SELECT
viimeistelemään asioita, oletetaan, että skeemallamme on myös taulukko asiakkaista:
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'
… haluamme myös palauttaa jokaisen asiakkaan nimen, kaupungin ja valtion aiemmissa tuloksissamme. Yksi tapa tehdä tämä on yksinkertaisesti lisätä asiakkaiden taulukko olemassa join, ja sitten lisätä asiakaspalstat valitse lauseke. Kuitenkin, ellet lisää kaikkia asiakaspalstoja ryhmään samoin, saat virheilmoituksen, joka osoittaa, että sinun täytyy joko ryhmitellä tai tiivistää kaikki sarakkeet haluat näyttää. Emme yritä laskea nimen, kaupungin ja valtion määrää() tai summaa (), joten ei ole järkevää kääriä noita sarakkeita yhteenlaskettuun ilmaisuun. Näyttää siis siltä, että meidän on lisättävä ne kaikki ryhmäämme lausekkeittain, jotta saamme tarvittavat tulokset.:
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)
teknisesti se toimii ,mutta tuntuu hölmöltä luetella kaikki ne asiakaspalstat ryhmässä mukaan… Loppujen lopuksi ryhmittelemme vain asiakkaan, emme jokaisen asiakkaan ominaisuuksien mukaan, eikö niin?
mielenkiintoista on, että ratkaisusta on jo puhuttu ja sama tekniikka pätee: Koska asiakkaalla on yksi-to-many-suhde tilauksiin, tiedämme, että asiakkaiden liittyminen tilauksiin johtaa kahteen riviin asiakasta kohden, ja siten kaikki asiakastaulukon sarakkeet ovat päällekkäisiä tuloksissa. Saatat huomata, että tämä on täsmälleen sama skenaario, jota sovelletaan liittyessään tilauksia Tilausdetails. Hoidamme tilanteen samalla tavalla! Me yksinkertaisesti tiivistää tilauksia asiakkaan ensin, johdetussa taulukossa, ja sitten liitymme nämä tulokset asiakkaan pöytään. Tämä tarkoittaa sitä, että Asiakaspöydästä ei tule lainkaan kolumneja, eikä niitä kaikkia tarvitse lisätä ilmaisulla ryhmäämme. Tämä pitää SQL puhdas, järjestetty, ja loogisesti ääni.
joten lopputulokset näyttävät nyt tältä:
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)
johtopäätös
toivon, että tämä kaksiosainen sarja auttaa hieman ymmärtämään ryhmä kyselyiden avulla. On tärkeää tunnistaa ja ymmärtää, mikä on tulosjoukon virtuaalinen ensisijainen avain, kun liityt useisiin pöytiin, ja tunnistaa, mitkä rivit ovat päällekkäisiä tai eivät. Muista lisäksi, että laskenta(erillinen) voi olla hyödyllinen, mutta summa(erillinen) pitäisi hyvin harvoin, jos koskaan, käyttää.
Yleensä, Jos huomaat, että summattavia arvoja() on monistettu, summaa taulukko, joka aiheuttaa nämä päällekkäisyydet erikseen ja yhdistä se johdettuna taulukkona. Näin voit myös jakaa ongelmasi pienempiin vaiheisiin ja testata ja vahvistaa kunkin vaiheen tulokset mennessäsi.
GROUP BY on erittäin tehokas ominaisuus, mutta se on myös väärinymmärretty ja väärinkäytetty, ja helpoin tapa hyödyntää sitä on rakentaa SQL huolellisesti pienemmistä, yksinkertaisemmista osista suurempiin, monimutkaisempiin ratkaisuihin.