Il problema con SUM(Distinct)
In precedenza abbiamo appreso che possiamo usare COUNT(Distinct) per contare le colonne dalla tabella duplicata, quindi per quanto riguarda SUM(Distinct)? Sembra che dovrebbe fare il trucco, dal momento che vogliamo solo sommare i valori dei costi di spedizione distinti, non tutti i duplicati. Facciamo un tentativo:
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)
Ed eccolo qui! Sembra che abbiamo risolto il nostro problema: guardando indietro alla nostra tabella degli ordini, possiamo vedere che il costo di TotalShipping per cliente ora sembra corretto.
Ma aspetta … In realtà è sbagliato!
Questo è dove molte persone hanno problemi. Sì, i dati sembrano corretti. E, per questo piccolo campione, casualmente sembra essere corretto. Ma SUM(DISTINCT) funziona esattamente come COUNT (DISTINCT): ottiene semplicemente tutti i valori idonei per essere sommati, elimina tutti i valori duplicati e quindi aggiunge i risultati. Ma sta eliminando i valori duplicati, non duplica le righe in base a qualche colonna di chiave primaria! Non importa che il costo di spedizione 40 appartenesse a orderID #1 e che il costo di spedizione 30 appartenesse a OrderID #2; semplicemente non li separa in questo modo.
L’espressione SOMMA(Distinti ShippingCost) è, fondamentalmente, valutati come questo:
- Dopo l’Adesione da Ordini di OrderDetails, ogni gruppo ha il seguente costo di trasporto valori:
ABC Clienti: 40,40,30,30,30,25
Cliente DEF: 10 - Dal DISTINTO è stato chiesto, elimina i valori duplicati da quegli elenchi:
ABC Clienti: 40,40,30,30,30,25
Cliente DEF: 10 - E ora può valutare la SOMMA () sommando i valori rimanenti:
Cliente ABC: 40+30+25 = 95
Cliente DEF: 10 = 10
Se non si ottiene il concetto, si potrebbe ancora non vedere il problema. In realtà, a questo punto, molte persone non lo fanno mai. Vedono che SUM(x) restituisce numeri enormi che non possono essere giusti, quindi lo modificano e provano SUM (DISTINCT x), ei valori sembrano molto più ragionevoli, e potrebbero anche inizialmente legarsi perfettamente, quindi va alla produzione. Tuttavia, l’SQL non è corretto; si basa sul fatto che attualmente non ci sono due ordini per un cliente hanno lo stesso costo di spedizione.
Dimostriamo aggiungendo un altro ordine:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
Esecuzione che aggiunge semplicemente un altro ordine per il cliente DEF, costo di spedizione di $10, con un articolo OrderDetail per $125. Ora, eseguiamo di nuovo la stessa SELEZIONE per vedere come questo nuovo Ordine ha influenzato i nostri risultati:
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)
Le colonne ItemCount, OrderAmount e OrderCount sembrano grandi. Ma il costo TotalShipping per DEF mostra ancora $10! Cos’è successo!?
Riesci a capirlo? Ricorda come funziona la SOMMA(Distinta)! Prende solo valori distinti passati alla funzione ed elimina i duplicati. Entrambi gli ordini per DEF avevano un costo di spedizione di $10, e SUM(Distinct ShippingCost) non si preoccupa che i due valori di $10 siano per ordini diversi, sa solo che il 10 è duplicato per il Cliente, quindi usa solo il 10 una volta per calcolare la SOMMA. Pertanto, restituisce un valore di 10 come costo di spedizione totale per questi due ordini, anche se dovrebbe essere 10+10=20. Il nostro risultato è ora sbagliato. Il lungo e il corto è questo: non usare mai SUM (Distinct) ! Di solito non ha senso logico nella maggior parte delle situazioni; ci può essere un tempo e un luogo per esso, ma non è sicuramente qui.
Riepilogo delle tabelle derivate
Quindi, come possiamo risolvere questo problema? Bene, come molti problemi SQL, la risposta è semplice: fallo un passo alla volta, non provare a unire tutte le tabelle insieme e basta aggiungere SUM() e GROUP BY e DISTINCT quasi casualmente fino a quando le cose non funzionano; scomponilo logicamente passo dopo passo.
Quindi, prima di preoccuparti dei totali per cliente, facciamo un passo indietro e concentriamoci sulla restituzione dei totali per ordine. Se possiamo restituire prima i totali per ordine, possiamo semplicemente riassumere i totali degli ordini per cliente e avremo i risultati di cui abbiamo bisogno. Riassumiamo la tabella OrderDetails per restituire 1 riga per ordine, con ItemCount e l’importo totale dell’ordine:
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)
Bello e semplice, facile da verificare, le cose sembrano buone. Poiché stiamo raggruppando su OrderID, possiamo dire che questi risultati hanno una chiave primaria virtuale di OrderID, ovvero non ci saranno mai righe duplicate per lo stesso Ordine. In effetti, ecco un’altra regola di base da ricordare sempre:
La chiave primaria virtuale di una SELEZIONE con una clausola GROUP BY saranno sempre le espressioni indicate nel GROUP BY.
Ora possiamo prendere quell’istruzione SQL e quei risultati e incapsularli nella propria tabella derivata. Se ci uniamo dalla tabella Ordini alla SELEZIONE precedente come tabella derivata, otteniamo:
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)
Esaminiamo quei risultati. Non ci sono righe o valori duplicati da nessuna parte; c’è esattamente una riga per Ordine. Questo perché la nostra tabella derivata ha una chiave primaria virtuale di OrderID, quindi l’unione dagli Ordini alla nostra tabella derivata non produrrà mai duplicati. Questa è una tecnica molto utile e semplice per evitare duplicati quando si collega una tabella padre a una tabella figlio: riassumere la tabella figlio con la chiave primaria del genitore prima in una tabella derivata e quindi unirla alla tabella genitore. Le righe della tabella padre non verranno mai duplicate e possono essere riassunte in modo accurato.
Ora abbiamo il nostro totale ItemCount per ordine, così come il nostro totale OrderAmount per ordine. E possiamo vedere che se sommiamo questi risultati, la nostra colonna ShippingCost andrà bene, poiché non viene mai duplicata. Non c’è bisogno di distinguished. In effetti, possiamo persino utilizzare un’espressione COUNT(*) regolare per ottenere il numero totale di ordini per cliente!
Quindi, possiamo semplicemente aggiungere “GROUP BY Customer” al precedente SQL, calcolare ciò di cui abbiamo bisogno con le funzioni aggregate e rimuovere qualsiasi colonna (come OrderID) che non riassumeremo. Si potrebbe anche notare che a questo punto, l’ItemCount totale per cliente non è più un’espressione COUNT(*) ; è una semplice SOMMA () del valore ItemCount restituito dalla nostra tabella derivata.
Ecco il risultato:
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)
E il gioco è fatto! Abbiamo esaminato i nostri dati, logicamente considerato le implicazioni dei nostri JOIN, rotto il problema in parti più piccole, e si è conclusa con una soluzione abbastanza semplice che sappiamo sarà veloce, efficiente e preciso.
Aggiunta di più tabelle Una SELEZIONE riassunta
Per completare le cose, supponiamo che il nostro schema abbia anche una tabella di clienti:
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'
… e desideriamo anche restituire il nome, la città e lo stato di ogni cliente nei nostri risultati precedenti. Un modo per farlo è semplicemente aggiungere la tabella Clienti al nostro join esistente e quindi aggiungere le colonne cliente alla clausola SELECT. Tuttavia, a meno che non si aggiungano anche tutte le colonne del cliente al GRUPPO, verrà visualizzato un messaggio di errore che indica che è necessario raggruppare o riepilogare tutte le colonne che si desidera visualizzare. Non stiamo cercando di calcolare un COUNT() o una SUM() di Nome, Città e Stato, quindi non ha senso avvolgere quelle colonne in un’espressione aggregata. Quindi, sembra che dobbiamo aggiungerli tutti al nostro GRUPPO PER clausola per ottenere i risultati di cui abbiamo bisogno:
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)
Tecnicamente, funziona, ma sembra sciocco elencare tutte quelle colonne cliente nel GRUPPO PER… Dopotutto, stiamo solo raggruppando il cliente, non su ciascuno degli attributi del cliente, giusto?
Ciò che è interessante è che la soluzione è qualcosa di cui abbiamo già parlato e si applica la stessa tecnica: Poiché il cliente ha una relazione uno-a-molti con gli ordini, sappiamo che unire i clienti agli ordini genererà righe duplicate per cliente e quindi tutte le colonne nella tabella Cliente vengono duplicate nei risultati. Si potrebbe notare che questo è esattamente lo stesso scenario che si applica quando si uniscono gli ordini a OrderDetails. Quindi, gestiamo questa situazione allo stesso modo! Riassumiamo semplicemente i nostri ordini per cliente prima, in una tabella derivata, e poi uniamo quei risultati alla tabella Cliente. Ciò significa che nessuna colonna della tabella Cliente verrà duplicata e non è necessario aggiungerle tutte al nostro GRUPPO PER espressione. Questo mantiene il nostro SQL pulito, organizzato e logicamente sano.
Quindi, i nostri risultati finali ora assomigliano a questo:
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)
Conclusione
Spero che questa serie in due parti aiuti un po ‘ con la tua comprensione delle query GROUP BY. È fondamentale identificare e capire quale sia la chiave primaria virtuale di un set di risultati quando si uniscono più tabelle e riconoscere quali righe sono duplicate o meno. Inoltre, ricorda che COUNT(Distinct) può essere utile, ma SUM (Distinct) dovrebbe essere usato molto raramente, se mai.
In generale, se si scopre che i valori necessari per SUM() sono stati duplicati, riassumere la tabella che causa tali duplicati separatamente e unirla come tabella derivata. Questo vi permetterà anche di abbattere il problema in piccoli passi e testare e convalidare i risultati di ogni passo, come si va.
GROUP BY è una funzionalità molto potente, ma viene anche fraintesa e abusata, e il modo più semplice per sfruttarla è costruire attentamente il tuo SQL da parti più piccole e più semplici in soluzioni più grandi e complicate.