het probleem met Som(verschillend)
we hebben eerder geleerd dat we COUNT(verschillend) kunnen gebruiken om kolommen uit de gedupliceerde tabel te tellen, dus hoe zit het met Som (verschillend)? Het lijkt erop dat dat de truc zou moeten doen, omdat we alleen afzonderlijke verzendkosten waarden willen optellen, niet alle duplicaten. Laten we het eens proberen.:
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)
en daar is het! We lijken ons probleem opgelost te hebben.: terugkijkend naar onze Orderentabel, kunnen we zien dat de totale verzendkosten per klant er nu correct uitzien.
maar wacht … Het is eigenlijk verkeerd!
hier hebben veel mensen problemen. Ja, de gegevens lijken juist. En voor dit kleine monster is het toevallig juist. Maar SUM(DISTINCT) werkt precies hetzelfde als COUNT (DISTINCT): het krijgt gewoon alle waarden die in aanmerking komen om te worden opgeteld, elimineert alle dubbele waarden en telt vervolgens de resultaten op. Maar het elimineert dubbele waarden, geen dubbele rijen op basis van een primaire sleutelkolom! Het maakt niet uit dat verzendkosten 40 behoorde tot orderID #1 en dat verzendkosten 30 behoorde tot OrderID #2; het gewoon niet scheiden ze op die manier.
De uitdrukking BEDRAG(Eenmalig Verzendkosten) is in principe geëvalueerd zoals deze:
- Na het Samenvoegen van Orders OrderDetails, elke groep heeft de volgende Verzendkosten waarden:
Klant ABC: 40,40,30,30,30,25
Klant DEF: 10 - Sinds ONDERSCHEIDEN werd gevraagd voor het elimineert dubbele waarden van die lijsten:
Klant ABC: 40,40,30,30,30,25
Klant DEF: 10 - en nu kan het de som() evalueren door de resterende waarden op te tellen:
klant ABC: 40+30+25 = 95
Customer DEF: 10 = 10
als je het concept niet krijgt, zie je misschien nog steeds het probleem niet. In feite, op dit punt, veel mensen nooit doen. Ze zien dat SUM (x) enorme getallen geeft die niet goed kunnen zijn, dus ze tweaken het en proberen SUM(DISTINCT x), en de waarden zien er veel redelijker uit, en ze kunnen zelfs in eerste instantie perfect binden, dus op productie gaat het. Toch is de SQL onjuist; het is een beroep op het feit dat momenteel geen twee bestellingen voor een klant hebben dezelfde verzendkosten.
laten we demonstreren door een andere volgorde toe te voegen:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
Running dat voegt gewoon een andere bestelling voor de klant DEF, verzendkosten van $10, met een OrderDetail item voor $ 125. Nu, laten we diezelfde SELECT opnieuw uit te voeren om te zien hoe deze nieuwe volgorde onze resultaten beà nvloed:
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)
de Artikelcount, orderbedrag en OrderCount kolommen zien er geweldig uit. Maar de totale verzendkosten voor DEF toont nog steeds $10! Wat is er gebeurd!?
kunt u erachter komen? Weet hoe de som werkt. Het neemt gewoon verschillende waarden doorgegeven aan de functie en elimineert duplicaten. Beide bestellingen voor DEF had een verzendkosten van $10, en Som (verschillende verzendkosten) maakt het niet uit dat de twee $ 10 waarden zijn voor verschillende bestellingen, het weet gewoon dat de 10 wordt gedupliceerd voor de klant, dus het gebruikt alleen de 10 een keer om de som te berekenen. Dus, het retourneert een waarde van 10 als de totale verzendkosten voor die twee bestellingen, ook al moet het 10 + 10 = 20. Ons resultaat is nu verkeerd. De lange en korte daarvan is dit: gebruik nooit Som (onderscheiden)! Het is meestal niet logisch logisch in de meeste situaties; er kan een tijd en plaats voor zijn, maar het is zeker niet hier.
samenvattende afgeleide tabellen
dus, hoe lossen we dit op? Nou, net als veel SQL problemen, het antwoord is eenvoudig: Doe het een stap voor een, probeer niet om alle tabellen samen te voegen en gewoon optellen som() en groep door en verschillende bijna willekeurig totdat dingen werken; break het logisch stap voor stap.
dus, voordat we ons zorgen maken over totalen per klant, laten we een stap terug doen en ons richten op het retourneren van totalen per bestelling. Als we totalen per bestelling eerst kunnen retourneren, dan kunnen we die totalen per klant eenvoudig samenvatten en hebben we de resultaten die we nodig hebben. Laten we de tabel met OrderDetails samenvatten om 1 rij per bestelling te retourneren, met het ItemCount en het totale orderbedrag:
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)
mooi en eenvoudig, makkelijk te verifiëren, dingen zien er goed uit. Omdat we groeperen op OrderID, kunnen we zeggen dat deze resultaten een virtuele primaire sleutel van OrderID hebben — dat wil zeggen, Er zullen nooit dubbele rijen voor dezelfde orde zijn. In feite, hier is een andere basisregel om altijd te onthouden:
de virtuele primaire sleutel van een select met een groep door clausule zal altijd de uitdrukkingen zijn die in de groep door.
we kunnen nu dat SQL statement en die resultaten nemen en ze inkapselen in hun eigen afgeleide tabel. Als we van de Orderentabel naar de vorige SELECT als afgeleide tabel gaan, krijgen we:
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)
laten we die resultaten eens bekijken. Er zijn nergens dubbele rijen of waarden; er is precies één rij per bestelling. Dit komt omdat onze afgeleide tabel een virtuele primaire sleutel van OrderID heeft, dus het samenvoegen van Orders naar onze afgeleide tabel zal nooit duplicaten produceren. Dit is een zeer nuttige en eenvoudige techniek om duplicaten te voorkomen wanneer een oudertabel wordt gekoppeld aan een dochtertabel: vat de dochtertabel samen met de primaire sleutel van de ouder eerst in een afgeleide tabel, en voeg deze vervolgens toe aan de oudertabel. De rijen van de bovenliggende tabel zullen dan nooit worden gedupliceerd en kunnen nauwkeurig worden samengevat.
nu hebben we onze totale ItemCount per bestelling, evenals ons totale orderbedrag per bestelling. En we kunnen zien dat als we deze resultaten optellen, onze verzendkosten kolom zal goed zijn, omdat het nooit wordt gedupliceerd. Je hoeft niet apart te zijn. In feite kunnen we zelfs een reguliere telling(*) uitdrukking gebruiken om het totale aantal bestellingen per klant te krijgen!
dus we kunnen gewoon “GROUP BY Customer” toevoegen aan de vorige SQL, berekenen wat we nodig hebben met geaggregeerde functies, en kolommen verwijderen (zoals OrderID) die we niet zullen samenvatten. Je zou ook kunnen merken dat op dit punt, de totale ItemCount per klant is niet langer een telling (*) uitdrukking; het is een eenvoudige som () van de ItemCount waarde geretourneerd uit onze afgeleide tabel.
hier is het resultaat:
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)
en daar heb je het! We onderzochten onze gegevens, beschouwden logischerwijs de implicaties van onze JOINS, splitsten het probleem op in kleinere delen, en eindigden met een vrij eenvoudige oplossing waarvan we weten dat die snel, efficiënt en accuraat zal zijn.
meer tabellen toevoegen een samenvatting selecteren
om dingen af te maken, Stel dat ons schema ook een tabel met klanten heeft:
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'
… en we willen ook de naam, stad en staat van elke klant retourneren in onze vorige resultaten. Een manier om dit te doen is door simpelweg de klantentabel toe te voegen aan onze bestaande join, en vervolgens de klantkolommen toe te voegen aan de Select-clausule. Echter, tenzij u alle van de klant kolommen toe te voegen aan de groep door zo goed, krijgt u een foutmelding die aangeeft dat je nodig hebt om ofwel groeperen of samenvatten van alle kolommen die u wilt weergeven. We proberen niet om een telling() of een som() van naam, stad en staat te berekenen, dus het heeft geen zin om die kolommen in een geaggregeerde uitdrukking te wikkelen. Het lijkt er dus op dat we ze allemaal per clausule aan onze fractie moeten toevoegen om de resultaten te krijgen die we nodig hebben.:
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)
Technisch, dat werkt, maar het lijkt dom om een lijst van al die klant kolommen in de groep door … Immers, we zijn gewoon groeperen op de klant, niet op elk van de kenmerken van de klant, toch?
interessant is dat de oplossing iets is waar we al over gesproken hebben en dat dezelfde techniek van toepassing is: Omdat de klant een één-op-veel relatie heeft met bestellingen, weten we dat het aansluiten van klanten bij bestellingen zal resulteren in dubbele rijen per klant, en dus worden alle kolommen in de klantentabel gedupliceerd in de resultaten. Je zou kunnen merken dat dit precies hetzelfde scenario dat van toepassing is bij de toetreding tot Orders om OrderDetails. Dus, we behandelen deze situatie op dezelfde manier! We vatten onze bestellingen eerst samen Per Klant, in een afgeleide tabel, en dan voegen we die resultaten toe aan de klantentabel. Dit betekent dat er helemaal geen kolommen uit de klantentabel zullen worden dupicated, en het is niet nodig om ze allemaal toe te voegen aan onze groep door uitdrukking. Dit houdt onze SQL schoon, georganiseerd en logisch geluid.
onze eindresultaten zien er nu zo uit:
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)
conclusie
ik hoop dat deze tweedelige serie een beetje helpt met uw begrip van groep door queries. Het is van vitaal belang om te identificeren en te begrijpen wat de virtuele primaire sleutel van een resultaatset is wanneer u meerdere tabellen samenvoegt, en om te herkennen welke rijen worden gedupliceerd of niet. Bovendien, vergeet niet dat telling(verschillend) nuttig kan zijn, maar Som(verschillend) moet zeer zelden, indien ooit, worden gebruikt.
in het algemeen, als u merkt dat waarden die u moet optellen() zijn gedupliceerd, vat de tabel die deze duplicaten veroorzaakt apart samen en voeg deze toe als een afgeleide tabel. Dit zal u ook toestaan om uw probleem op te splitsen in kleinere stappen en testen en valideren van de resultaten van elke stap als je gaat.
groep door is een zeer krachtige functie, maar wordt ook verkeerd begrepen en misbruikt, en de makkelijkste manier om het te benutten is om zorgvuldig uw SQL te bouwen van kleinere, eenvoudigere delen in grotere, meer gecompliceerde oplossingen.