O Problema com a SOMA(Distintos)
Nós já aprendemos que podemos usar COUNT(Distinct) para contagem de colunas de duplicados da tabela, de modo que sobre SOMA(Distintas)? Parece que isso deve funcionar, uma vez que só queremos somar valores de custo de transporte distintos, e não todas as duplicações. Vamos tentar:
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)
E aqui está! Parece que resolvemos o nosso problema.: olhando para trás para a nossa tabela de Pedidos, podemos ver que o custo total de transporte por cliente agora parece correto.
mas espere … É realmente errado!
é aqui que muitas pessoas têm problemas. Sim, os dados parecem correctos. E, para esta pequena amostra, acontece aleatoriamente estar correta. Mas a soma(distinta) funciona exatamente como a contagem (distinta): ela simplesmente obtém todos os valores elegíveis para serem somados, elimina todos os valores duplicados e, em seguida, adiciona os resultados. Mas é eliminar valores duplicados, não duplicar linhas com base em alguma coluna de chave primária! Não importa que o custo de transporte 40 pertenceu a orderID #1 e que o custo de transporte 30 pertenceu a OrderID #2; simplesmente não os separa dessa forma.
A expressão de SOMA(Distinct ShippingCost) é basicamente avaliada como este:
- Depois de Aderir, a partir de Encomendas para OrderDetails, cada grupo tem o seguinte custo de Transporte de valores:
Cliente ABC: 40,40,30,30,30,25
Cliente DEF: 10 - Desde DISTINTAS foi pedido, ele elimina valores duplicados a partir dessas listas:
Cliente ABC: 40,40,30,30,30,25
Cliente DEF: 10 - E agora ele pode avaliar o valor da SOMA (), adicionando-se os valores restantes:
Cliente ABC: 40+30+25 = 95
Cliente DEF: 10 = 10
Se você não está recebendo o conceito, você ainda não pode ver o problema. Na verdade, neste momento, muitas pessoas nunca o fazem. Eles vêem que SUM (x) retorna números enormes que não podem estar certos, então eles ajustam-no e tentar SUM(distinto x), e os valores parecem muito mais razoáveis, e eles podem até mesmo inicialmente amarrar perfeitamente, então fora para a produção que vai. No entanto, o SQL está incorrecto; baseia-se no facto de actualmente não haver duas encomendas para um cliente com o mesmo custo de envio.
vamos demonstrar adicionando outra ordem:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
executando isso simplesmente adiciona outra ordem para DEF cliente, custo de envio de $ 10,com um item OrderDetail de $125. Agora, vamos executar essa mesma seleção novamente para ver como esta nova ordem afetou nossos resultados:
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)
as colunas ItemCount, OrderAmount e OrderCount estão ótimas. Mas o custo total da DEF ainda mostra $ 10! O que aconteceu!? Consegues descobrir? Lembre-se de como a SUM(distinta) funciona! Ele apenas toma valores distintos passados para a função e elimina duplicados. Ambas as encomendas para DEF tiveram um custo de envio de US $10, e SUM (ShippingCost distinto) não se importa que os dois valores de US $10 são para ordens diferentes, ele só sabe que os 10 são duplicados para o cliente, então ele só usa os 10 uma vez para calcular a soma. Assim, ele retorna um valor de 10 como o custo total de envio para essas duas encomendas, embora deva ser 10+10=20. O nosso resultado está agora errado. O longo e curto é este: nunca use SUM (distinto) ! Normalmente não faz sentido lógico na maioria das situações.; pode haver uma hora e um lugar para isso, mas definitivamente não está aqui.
resumindo tabelas derivadas
assim, como corrigimos isto? Bem, como muitos problemas SQL, a resposta é simples: faça-o um passo de cada vez, não tente juntar todas as tabelas e apenas adicionar soma() e grupo por e distinto quase aleatoriamente até que as coisas funcionem; quebre-o logicamente passo a passo.Antes de nos preocuparmos com os totais por cliente, recuemos e concentremo-nos em devolver os totais por encomenda. Se pudermos devolver os totais por ordem primeiro, então podemos simplesmente resumir esses totais por cliente e teremos os resultados que precisamos. Vamos resumir a tabela OrderDetails para devolver 1 linha por ordem, com o ItemCount e o valor total da ordem:
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)
agradável e simples, fácil de verificar, as coisas parecem boas. Porque estamos agrupando em OrderID, podemos dizer que esses resultados têm uma chave primária virtual de OrderID — ou seja, nunca haverá linhas duplicadas para a mesma ordem. Na verdade, aqui está outra regra básica para sempre lembrar:
a chave primária virtual de uma seleção com um grupo por cláusula será sempre as expressões indicadas no grupo por.
podemos agora tomar essa declaração SQL e esses resultados e encapsulá-los em sua própria tabela derivada. Se nos juntarmos da tabela de encomendas para a seleção anterior como uma tabela derivada, obteremos:
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)
vamos examinar esses resultados. Não existem linhas ou valores duplicados em qualquer lugar; existe exatamente uma linha por ordem. Isto é porque a nossa tabela derivada tem uma chave primária virtual de OrderID, então a junção de ordens para a nossa tabela derivada nunca produzirá duplicados. Esta é uma técnica muito útil e simples para evitar duplicados ao relacionar uma tabela-mãe com uma tabela-filha: resuma a tabela-filhos pela chave primária do Pai primeiro em uma tabela derivada, e depois junte-a à tabela-mãe. As linhas da tabela pai nunca serão duplicadas e podem ser resumidas com precisão.
agora temos o nosso total de ItemCount por ordem, bem como o nosso total de Order por ordem. E podemos ver que se somarmos esses resultados, nossa coluna de postagem estará bem, uma vez que nunca é duplicada. Não há necessidade de distinção. Na verdade, podemos até usar uma expressão de contagem regular (*) para obter o número total de encomendas por cliente!
assim, podemos simplesmente adicionar “grupo por cliente” ao SQL anterior, calcular o que precisamos com funções agregadas, e remover quaisquer colunas (como OrderID) que não vamos resumir. Você também pode notar que neste momento, o total de ItemCount por cliente não é mais uma expressão de contagem (*); é uma simples soma() do valor ItemCount retornado de nossa tabela derivada.
aqui está o resultado:
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 aí está! Examinamos nossos dados, logicamente considerados as implicações de nossas juntas, dividimos o problema em partes menores, e acabamos com uma solução bastante simples que sabemos que será rápida, eficiente e precisa.
adicionando mais tabelas a resumido selecione
para terminar as coisas, suponha que o nosso esquema também tem uma tabela de clientes:
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 também queremos devolver o nome de cada cliente, cidade e estado em nossos resultados anteriores. Uma maneira de fazer isso é simplesmente adicionar a tabela de clientes à nossa junção existente, e, em seguida, adicionar as colunas de clientes para a cláusula de seleção. No entanto, a menos que você adicione todas as colunas do cliente ao grupo também, você receberá uma mensagem de erro indicando que você precisa agrupar ou resumir todas as colunas que você deseja exibir. Não estamos tentando calcular uma contagem() ou uma soma() de Nome, Cidade e estado, então não faz sentido envolver essas colunas em uma expressão agregada. Por conseguinte, parece que temos de os acrescentar a todos ao nosso grupo, por cláusula, para obtermos os resultados de que necessitamos.:
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, isso funciona, mas parece tolice listar todas aquelas colunas de clientes no grupo … Afinal de contas, estamos apenas agrupando no cliente, não em cada um dos atributos do cliente, certo?
o que é interessante é que a solução é algo que já falamos e a mesma técnica se aplica: Uma vez que o cliente tem uma relação de um para muitos com as encomendas, sabemos que juntar os clientes às encomendas resultará em linhas duplicadas por cliente, e assim todas as colunas na tabela de clientes são duplicadas nos resultados. Você pode notar que este é exatamente o mesmo cenário que se aplica quando se junta ordens para encomendas de encomendas para o OrderDetails. Então, lidamos com esta situação da mesma maneira! Nós simplesmente resumimos nossas ordens pelo Cliente Primeiro, em uma tabela derivada, e então nós juntamos esses resultados à mesa do cliente. Isto significa que nenhuma coluna da Mesa Do Cliente será duplicada em tudo, e não há necessidade de adicioná-los todos ao nosso grupo por expressão. Isto mantém o nosso SQL limpo, organizado e logicamente sólido.Portanto, os nossos resultados finais agora parecem-se com isto.:
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)
conclusão
espero que esta série de duas partes ajude um pouco com a sua compreensão do grupo por consultas. É vital identificar e entender qual é a chave primária virtual de um conjunto de resultados quando você se junta a várias tabelas, e reconhecer quais as linhas são duplicadas ou não. Além disso, lembre-se que a contagem(distinta) pode ser útil, mas a soma(distinta) deve muito raramente, se alguma vez, ser usada.
em geral, se você descobrir que os valores que você precisa somar () foram duplicados, resuma a tabela fazendo esses duplicados separadamente e junte-os como uma tabela derivada. Isto também lhe permitirá quebrar o seu problema em passos menores e testar e validar os resultados de cada passo à medida que você vai.
grupo BY é um recurso muito poderoso, mas também é mal entendido e abusado, e a maneira mais fácil de alavancá-lo é cuidadosamente construir o seu SQL de partes menores, mais simples em soluções maiores e mais complicadas.