Problém s SUM(Distinct)
již dříve Jsme se dozvěděli, že můžeme použít COUNT(Distinct) count sloupce z duplicitní tabulky, takže to, co o SUM(Distinct)? Zdá se, že by to mělo stačit, protože chceme pouze shrnout odlišné hodnoty nákladů na dopravu, ne všechny duplikáty. Pojďme to zkusit:
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)
a tady to je! Zdá se, že jsme vyřešili náš problém: když se podíváme zpět na naši tabulku objednávek, vidíme, že celková cena na zákazníka nyní vypadá správně.
ale počkejte … Je to vlastně špatně!
to je místo, kde mnoho lidí má problémy. Ano, data vypadají správně. A, pro tento malý vzorek, je to náhodně správné. Ale SUM(DISTINCT) funguje úplně stejně jako COUNT(DISTINCT): je To prostě dostane všechny hodnoty způsobilé být sečteny, odstraňuje všechny duplicitní hodnoty, a pak sečte výsledky. Ale eliminuje duplicitní hodnoty, ne duplicitní řádky založené na nějakém sloupci primárního klíče! Nezáleží na tom, že náklady na dopravu 40 patřily orderID #1 a že náklady na dopravu 30 patřily OrderID #2; jednoduše je tak neodděluje.
výraz SUM(Distinct ShippingCost) je v zásadě hodnocena jako je tento:
- Po Vstupu z Objednávky OrderDetails, každá skupina má následující Přepravní náklady hodnoty:
Zákazník ABC: 40,40,30,30,30,25
Zákazník DEF: 10 - Protože ZŘETELNÝ byl požádán, eliminuje duplicitní hodnoty z těchto seznamů:
Zákazník ABC: 40,40,30,30,30,25
Zákazník DEF: 10 - A teď to můžete posoudit SOUČET() sečtením zbývajících hodnot:
Zákazník ABC: 40+30+25 = 95
Zákazník DEF: 10 = 10
Pokud si nejste dostat koncept, stále nemusí vidět problém. Ve skutečnosti, v tomto bodě, mnoho lidí nikdy dělat. Vidí, že SUM(x) vrací obrovské množství, že nemohou mít pravdu, tak se to vyladit a zkusit SUM(DISTINCT x) a hodnoty vypadají mnohem rozumnější, a oni by mohli dokonce zpočátku kravatu dokonale, takže z výroby to jde. Přesto je SQL nesprávný; spoléhá se na skutečnost, že v současné době žádné dvě objednávky pro zákazníka nemají stejné náklady na dopravu.
ukážeme přidáním další objednávky:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
běh, který jednoduše přidá další objednávku pro zákazníka DEF, náklady na dopravu $ 10, s jednou objednávkou pro $ 125. Nyní provedeme stejný výběr znovu, abychom zjistili, jak tato nová objednávka ovlivnila naše výsledky:
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)
sloupce ItemCount, OrderAmount a OrderCount vypadají skvěle. Ale TotalShipping náklady na DEF stále ukazuje $ 10! Co se stalo!?
můžete na to přijít? Pamatujte, jak SUM (odlišný) funguje! To prostě trvá odlišné hodnoty předané funkci a eliminuje duplikáty. Oba příkazy na DEF měl přepravní náklady $10, a SUM(Distinct ShippingCost) jedno, dvě $10 hodnoty jsou pro různé Příkazy, prostě ví, že 10 je duplicitní pro Zákazníka, tak to používá pouze 10 jednou vypočítat SOUČET. Vrací tedy hodnotu 10 jako celkové náklady na dopravu pro tyto dvě objednávky, i když by to mělo být 10 + 10=20. Náš výsledek je nyní špatný. Dlouhé a krátké z toho je toto: nikdy nepoužívejte součet (odlišný)! Ve většině situací to obvykle nedává logický smysl; může na to být čas a místo, ale rozhodně to není tady.
shrnutí odvozených tabulek
jak to tedy opravíme? No, stejně jako mnoho SQL problémy, odpověď je jednoduchá: Udělat jeden krok v době, nesnažte se připojit všechny stoly k sobě a stačí přidat SUM() a GROUP BY a DISTINCT téměř náhodně, dokud věci fungují, rozebrat to logicky krok za krokem.
takže předtím, než se obáváte o součty na zákazníka, ustoupíme a zaměříme se na vrácení součtů na objednávku. Pokud můžeme nejprve vrátit součty na objednávku, pak můžeme jednoduše shrnout tyto součty objednávek podle zákazníka a budeme mít výsledky, které potřebujeme. Pojďme shrnout OrderDetails tabulka vrátí 1 řádek na Objednávku, s ItemCount a celková Částka Objednávky:
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)
Pěkné a jednoduché, snadné ověřit, že vše vypadá v pořádku. Protože se seskupujeme na OrderID, můžeme říci, že tyto výsledky mají virtuální primární klíč OrderID – to znamená, že nikdy nebudou duplicitní řádky pro stejné pořadí. Ve skutečnosti je zde další základní pravidlo, které si musíte vždy pamatovat:
virtuální primární klíč SELECT s klauzulí GROUP BY budou vždy výrazy uvedené ve skupině BY.
Nyní můžeme vzít tento příkaz SQL a tyto výsledky a zapouzdřit je do vlastní odvozené tabulky. Pokud se připojíme z tabulky příkazů k předchozímu výběru jako odvozená tabulka, dostaneme:
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)
podívejme se na ty výsledky. Nikde nejsou žádné duplicitní řádky nebo hodnoty; na objednávku je přesně jeden řádek. Je to proto, že naše odvozená tabulka má virtuální primární klíč OrderID, takže spojení z objednávek do naší odvozené tabulky nikdy nevytvoří duplikáty. To je velmi užitečné a jednoduché techniky, aby se zabránilo duplikáty, když o nadřazené tabulky do podřízené tabulky: shrnout podřízené tabulky pomocí nadřazené primární klíč první v odvozené tabulky, a pak jej připojit k nadřazené tabulky. Řádky nadřazené tabulky pak nebudou nikdy duplikovány a lze je přesně shrnout.
nyní máme náš celkový ItemCount na objednávku, stejně jako náš celkový OrderAmount na objednávku. A vidíme, že pokud tyto výsledky shrneme, náš sloupec ShippingCost bude v pořádku, protože nikdy není duplikován. Není třeba rozlišovat. Ve skutečnosti můžeme dokonce použít běžný počet ( * ) výraz pro získání celkového počtu objednávek na zákazníka!
Takže, můžeme jednoduše přidat „SKUPINY Zákazníků“ na předchozí SQL, spočítat, co potřebujeme s agregační funkce, a odstraňte všechny sloupce (jako Kódobjednávky), že nebudeme shrnující. Můžete si také všimnout, že v tomto okamžiku již celkový ItemCount na zákazníka není výrazem COUNT(*) ; jedná se o jednoduchý součet () hodnoty ItemCount vrácené z naší odvozené tabulky.
zde je výsledek:
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)
a tady to máte! Zkoumali jsme naše data, logicky za důsledky našeho SPOJENÍ, zlomil si problém na menší části, a skončil s poměrně jednoduché řešení, které víme, že bude rychlý, efektivní a přesné.
přidání dalších tabulek souhrnný výběr
Chcete-li dokončit věci, předpokládejme, že naše schéma má také tabulku zákazníků:
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'
… a chceme také vrátit jméno každého zákazníka, město a stát v našich předchozích výsledcích. Jedním ze způsobů, jak toho dosáhnout, je jednoduše přidat tabulku zákazníků do našeho stávajícího spojení a poté přidat sloupce zákazníků do klauzule SELECT. Pokud však do skupiny nepřidáte také všechny sloupce zákazníků, zobrazí se chybová zpráva, že je třeba seskupit nebo shrnout všechny sloupce, které chcete zobrazit. Nesnažíme se vypočítat počet () nebo součet () jména, města a státu, takže nemá smysl tyto sloupce zabalit do souhrnného výrazu. Takže, zdá se, že musíme přidat je všechny do naší klauzule GROUP BY, aby si výsledky, potřebujeme:
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)
Technicky to funguje, ale zdá se, hloupé, aby seznam všech těch zákazníků sloupce v GROUP BY … Koneckonců, jsme jen seskupení na zákazníka, ne na každém z atributů zákazníka, že jo?
zajímavé je, že řešení je něco, o čem jsme již mluvili a platí stejná technika: Od té doby má Zákazník one-to-many vztah s Příkazy, víme, že spojení Zákazníky, aby Objednávky bude mít za následek duplicitní řádky na Zákazníka, a proto všechny sloupce v tabulce Zákazník, jsou duplikovány ve výsledcích. Můžete si všimnout, že se jedná o přesně stejný scénář, který platí při spojování objednávek do OrderDetails. Takže tuto situaci řešíme stejným způsobem! Jednoduše shrneme naše objednávky podle zákazníka nejprve v odvozené tabulce a poté tyto výsledky připojíme k tabulce zákazníků. To znamená, že žádné sloupce z tabulky zákazníků nebudou duplikovány vůbec a není třeba je všechny přidávat do naší skupiny výrazem. To udržuje náš SQL čistý, organizovaný a logicky zdravý.
Takže naše konečné výsledky nyní vypadat takto:
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)
Závěr
doufám, že tohle dvoudílné série pomůže trochu s vaší pochopení SKUPINĚ dotazy. Je důležité identifikovat a pochopit, jaký je virtuální primární klíč sady výsledků, když se připojíte k více tabulkám, a rozpoznat, které řádky jsou duplikovány nebo ne. Kromě toho si pamatujte, že počet (odlišný) může být užitečný, ale součet(odlišný) by měl být použit velmi zřídka, pokud vůbec.
obecně platí, že pokud zjistíte, že hodnoty, které potřebujete, aby SUM() byly duplicitní, shrnují tabulky způsobuje tyto duplikáty samostatně a připojit to jako odvozené tabulky. To vám také umožní rozdělit váš problém na menší kroky a otestovat a ověřit výsledky každého kroku.
GROUP BY je velmi výkonná funkce, ale je také nepochopena a zneužívána a nejjednodušší způsob, jak ji využít, je pečlivě sestavit SQL z menších, jednodušších částí do větších a složitějších řešení.