a SUM(Distinct) problémája
korábban megtudtuk, hogy a COUNT(Distinct) segítségével oszlopokat számolhatunk a duplikált táblából, tehát mi a helyzet a SUM(Distinct)? Úgy tűnik, hogy ennek meg kell tennie a trükköt, mivel csak különálló szállítási költségértékeket akarunk összegezni, nem az összes másolatot. Próbáljuk meg:
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)
és itt is van! Úgy tűnik, megoldottuk a problémánkat: visszatekintve a rendelési táblázatunkra, láthatjuk, hogy az egy ügyfélre jutó teljes szállítási költség most helyesnek tűnik.
de várj… Ez valójában rossz!
ez az, ahol sok embernek problémái vannak. Igen, az adatok helyesnek tűnnek. És ehhez a kis mintához véletlenül pont ez a helyes. De a SUM(DISTINCT) pontosan ugyanúgy működik, mint a COUNT (DISTINCT): egyszerűen megkapja az összes összegezhető értéket, kiküszöböli az összes ismétlődő értéket, majd összeadja az eredményeket. De kiküszöböli az ismétlődő értékeket, nem pedig az elsődleges kulcs oszlop alapján ismétlődő sorokat! Nem érdekli, hogy a szállítási költség 40 tartozott orderID # 1 és a szállítási költség 30 tartozott OrderID #2; egyszerűen nem választja el őket, hogy így.
a kifejezés összeg (Distinct ShippingCost) alapvetően értékelik, mint ez:
- Miután csatlakozott a megrendelések OrderDetails, minden csoport a következő szállítási költség értékek:
Customer ABC: 40,40,30,30,30,25
Customer DEF: 10 - mivel DISTINCT kértek, ez kiküszöböli ismétlődő értékek e listák:
Customer ABC: 40,40,30,30,30,25
Customer DEF:: 10 - és most ki tudja értékelni az összeget () a fennmaradó értékek összeadásával:
ügyfél ABC: 40+30+25 = 95
ügyfél DEF: 10 = 10
ha nem kapja meg a koncepciót, akkor még mindig nem látja a problémát. Valójában ezen a ponton sokan soha nem teszik meg. Látják, hogy a SUM (x) hatalmas számokat ad vissza, amelyek nem lehetnek helyesek, ezért módosítják, és megpróbálják a SUM(DISTINCT x) értéket, és az értékek sokkal ésszerűbbnek tűnnek, és talán még kezdetben is tökéletesen kötődnek, így a termeléshez megy. Még, az SQL helytelen; arra a tényre támaszkodik, hogy jelenleg nincs két megrendelés az ügyfél számára azonos szállítási költséggel.
mutassuk be egy másik sorrend hozzáadásával:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
futás, hogy egyszerűen hozzáad egy másik érdekében az ügyfél DEF, szállítási költség $10, egy OrderDetail tétel $ 125. Most hajtsuk végre újra ugyanazt a választást, hogy lássuk, hogyan befolyásolta ez az új sorrend az eredményeinket:
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)
az ItemCount, OrderAmount és OrderCount oszlopok jól néznek ki. De a DEF teljes szállítási költsége továbbra is 10 dollárt mutat! Mi történt!?
meg tudod találni? Ne feledje, hogyan működik a Sum (Distinct)! Csak külön értékeket ad át a függvénynek, és kiküszöböli a duplikációkat. A DEF mindkét megrendelésének szállítási költsége 10 dollár volt, és a SUM(Distinct ShippingCost) nem érdekli, hogy a két 10 dolláros érték különböző megrendelésekre vonatkozik, csak tudja, hogy a 10 duplikált az ügyfél számára, ezért csak egyszer használja a 10-et az összeg kiszámításához. Így 10 értéket ad vissza e két megrendelés teljes szállítási költségeként, annak ellenére, hogy 10+10=20-nak kell lennie. Az eredményünk most rossz. A hosszú és rövid a következő: soha ne használjon SUM(Distinct) ! A legtöbb helyzetben általában nincs logikus értelme; lehet, hogy van rá idő és hely, de biztosan nincs itt.
a származtatott táblázatok összefoglalása
Szóval, hogyan oldjuk meg ezt? Nos, mint sok SQL probléma, a válasz egyszerű: csináld lépésről lépésre, ne próbáld össze az összes táblát, és csak add hozzá a SUM () – t, és csoportosítsd és KÜLÖNBÖZTESD meg szinte véletlenszerűen, amíg a dolgok működnek; logikusan bontsd le lépésről lépésre.
tehát, mielőtt aggódnánk az egy ügyfélre jutó összegek miatt, lépjünk vissza, és összpontosítsunk a Rendelésenkénti összegek visszatérítésére. Ha először vissza tudjuk adni a Rendelésenkénti összegeket, akkor egyszerűen összefoglalhatjuk ezeket a rendelési összegeket az ügyfél szerint, és megkapjuk a szükséges eredményeket. Összegezzük az OrderDetails táblázatot, hogy rendelésenként 1 sort adjon vissza, az ItemCount-tal és a teljes rendelési összeggel:
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)
szép és egyszerű, könnyen ellenőrizhető, a dolgok jól néznek ki. Mivel az OrderID-en csoportosulunk, azt mondhatjuk, hogy ezeknek az eredményeknek van egy virtuális elsődleges kulcsa az OrderID-nek-vagyis soha nem lesznek ismétlődő sorok ugyanarra a sorrendre. Valójában itt van egy másik alapvető szabály, amelyet mindig emlékezni kell:
a GROUP BY záradékkal rendelkező SELECT virtuális elsődleges kulcsa mindig a GROUP BY kifejezésben megadott kifejezések lesznek.
ezt az SQL utasítást és az eredményeket a saját származtatott táblázatukba foglalhatjuk. Ha a rendelések táblából az előző SELECT-hez származtatott táblaként csatlakozunk, akkor:
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)
vizsgáljuk meg ezeket az eredményeket. Nincsenek ismétlődő sorok vagy értékek sehol; rendelésenként pontosan egy sor van. Ez azért van, mert a származtatott táblánknak van egy virtuális elsődleges kulcsa OrderID, így a megrendelésekből a származtatott táblánkba való csatlakozás soha nem fog duplikátumokat előállítani. Ez egy nagyon hasznos és egyszerű módszer a duplikációk elkerülésére, amikor egy szülőtáblát egy gyermektáblához kapcsolunk: először a szülő elsődleges kulcsával foglalja össze a gyermektáblát egy származtatott táblában, majd csatlakoztassa a szülőtáblához. A szülő tábla sorai ezután soha nem duplikálódnak, és pontosan összefoglalhatók.
most már a teljes ItemCount rendelésenként, valamint a teljes OrderAmount rendelésenként. És láthatjuk, hogy ha ezeket az eredményeket összegezzük, a ShippingCost oszlopunk rendben lesz, mivel soha nem duplikálódik. Nincs szükség különállásra. Sőt, mi is használja a rendszeres COUNT (*) kifejezés, hogy az összes megrendelések száma Egy ügyfél!
tehát egyszerűen hozzáadhatjuk a “GROUP by Customer” – t az előző SQL-hez, kiszámolhatjuk, hogy mire van szükségünk az összesített függvényekkel, és eltávolíthatunk minden olyan oszlopot (például az OrderID-t), amelyet nem fogunk összefoglalni. Azt is észreveheti, hogy ezen a ponton az Ügyfélenkénti teljes tételszám már nem számít ( * ) kifejezésnek; ez egy egyszerű összeg() az itemcount érték vissza a származtatott tábla.
itt az eredmény:
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)
és itt is van! Megvizsgáltuk az adatainkat, logikusan mérlegeltük a csatlakozások következményeit, kisebb részekre bontottuk a problémát, és egy meglehetősen egyszerű megoldáshoz jutottunk, amelyről tudjuk, hogy gyors, hatékony és pontos lesz.
további táblázatok hozzáadása a Összefoglalás
a dolgok befejezéséhez tegyük fel, hogy a sémánknak van egy táblázata az ügyfelekről:
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'
… és szeretnénk visszaadni minden ügyfél nevét, városát és államát a korábbi eredményeinkben. Ennek egyik módja az, hogy egyszerűen hozzáadja az ügyfelek táblát a meglévő join-hoz, majd hozzáadja az ügyfél oszlopokat a SELECT záradékhoz. Ha azonban nem adja hozzá az összes ügyféloszlopot a csoporthoz, akkor hibaüzenet jelenik meg, amely jelzi, hogy csoportosítania kell vagy össze kell foglalnia az összes megjeleníteni kívánt oszlopot. Nem próbáljuk kiszámítani a név, város és állam számát() vagy összegét (), ezért nincs értelme ezeket az oszlopokat összesített kifejezésbe csomagolni. Tehát úgy tűnik, hogy mindet hozzá kell adnunk a csoportunkhoz záradék szerint, hogy megkapjuk a szükséges eredményeket:
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)
technikailag ez működik, de butaságnak tűnik felsorolni a csoport összes ügyféloszlopát … Végül is csak az Ügyfélre csoportosítunk, nem pedig az ügyfél minden tulajdonságára, igaz?
az az érdekes, hogy a megoldás olyasmi, amiről már beszéltünk, és ugyanaz a technika érvényes: Mivel az ügyfél egy a sokhoz kapcsolatban áll a megrendelésekkel, tudjuk, hogy az ügyfelek megrendelésekhez való csatlakozása ügyfélenként ismétlődő sorokat eredményez, így az Ügyféltábla összes oszlopa megismétlődik az eredményekben. Észreveheti, hogy ez pontosan ugyanaz a forgatókönyv, amely akkor érvényes, amikor a rendeléseket az OrderDetails-hez csatlakoztatja. Tehát ugyanúgy kezeljük ezt a helyzetet! Először egyszerűen összefoglaljuk megrendeléseinket ügyfélenként, egy származtatott táblázatban, majd ezeket az eredményeket összekapcsoljuk az Ügyféltáblával. Ez azt jelenti, hogy az Ügyféltáblázatból egyetlen oszlop sem lesz becsapva, és nincs szükség arra, hogy mindegyiket kifejezéssel adjuk hozzá a csoportunkhoz. Ez tisztán, szervezetten és logikusan tartja az SQL-t.
tehát a végső eredményeink most így néznek ki:
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)
következtetés
remélem, hogy ez a két részből álló sorozat segít egy kicsit a GROUP BY lekérdezések megértésében. Létfontosságú azonosítani és megérteni, hogy mi az eredményhalmaz virtuális elsődleges kulcsa, amikor több táblát csatlakoztat, és felismerni, hogy mely sorok vannak duplikálva vagy sem. Ezenkívül ne feledje, hogy a COUNT(Distinct) hasznos lehet, de az SUM (Distinct) nagyon ritkán, ha valaha is használható.
általában, ha úgy találja, hogy a SUM() értékeket meg kell duplikálni, külön foglalja össze a duplikációkat okozó táblázatot, és származtatott táblaként csatlakozzon hozzá. Ez azt is lehetővé teszi, hogy a problémát kisebb lépésekre bontsa, és tesztelje és érvényesítse az egyes lépések eredményeit.
a GROUP BY egy nagyon hatékony funkció, de félreértik és visszaélnek vele, és a legegyszerűbb módja annak, hogy kihasználja, ha gondosan építi az SQL-t kisebb, egyszerűbb részekből nagyobb, bonyolultabb megoldásokba.