El problema con SUM (Distinto)
Anteriormente aprendimos que podemos usar COUNT(Distinto) para contar columnas de la tabla duplicada, así que ¿qué pasa con SUM(Distinto)? Parece que eso debería hacer el truco, ya que solo queremos sumar valores de costos de envío distintos, no todos los duplicados. Vamos a intentarlo.:
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)
¡Y ahí está! Parece que hemos resuelto nuestro problema: mirando hacia atrás a nuestra tabla de pedidos, podemos ver que el costo total de envío por cliente ahora parece correcto.
Pero espere … ¡En realidad está mal!
aquí es donde muchas personas tienen problemas. Sí, los datos parecen correctos. Y, para esta pequeña muestra, resulta que al azar es correcta. Pero SUM(DISTINTO) funciona exactamente igual que COUNT (DISTINTO): Simplemente obtiene todos los valores elegibles para ser sumados, elimina todos los valores duplicados y luego suma los resultados. ¡Pero está eliminando valores duplicados, no filas duplicadas basadas en alguna columna de clave primaria! No importa que el costo de envío 40 pertenezca al OrderID #1 y que el costo de envío 30 pertenezca al OrderID # 2; simplemente no los separa de esa manera.
La expresión SUM(Distinct ShippingCost) se evalúa básicamente de la siguiente manera:
- Después de unirse de los Pedidos a los detalles del pedido, cada grupo tiene los siguientes valores de costo de envío:
Cliente ABC: 40,40,30,30,30,25
Cliente DEF: 10 - Desde que se solicitó DISTINCT, elimina los valores duplicados de esas listas:
Cliente ABC: 40,40,30,30,30,25
Cliente DEF: 10 - Y ahora puede evaluar la SUMA () sumando los valores restantes:
ABC del cliente: 40+30+25 = 95
DEF del cliente: 10 = 10
Si no estás entendiendo el concepto, es posible que aún no veas el problema. De hecho, en este punto, muchas personas nunca lo hacen. Ven que SUM(x) devuelve números enormes que no pueden ser correctos, por lo que lo retocan y prueban SUM (DISTINTO x), y los valores parecen mucho más razonables, e incluso podrían vincularse perfectamente al principio, así que se va a la producción. Sin embargo, el SQL es incorrecto; se basa en el hecho de que actualmente no hay dos pedidos para un cliente que tengan el mismo costo de envío.
Vamos a demostrar añadiendo otro orden:
insert into Orders values (5, 'DEF', '2007-01-04', 10)insert into OrderDetails values (9, 5, 'Item J', 125)
Ejecución que simplemente agrega otro pedido para el cliente DEF, costo de envío de $10, con un artículo detallado de pedido por $125. Ahora, ejecutemos esa misma SELECCIÓN de nuevo para ver cómo este nuevo orden afectó a nuestros 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)
Las columnas itemCount, OrderAmount y OrderCount se ven geniales. ¡Pero el costo total de envío para DEF todavía muestra 1 10! ¡Qué pasó!?
¿Puedes averiguarlo? ¡Recuerda cómo funciona SUM (Distinto)! Solo toma valores distintos pasados a la función y elimina duplicados. Ambos pedidos para DEF tenían un costo de envío de $10, y a SUM(Distinct ShippingCost) no le importa que los dos valores de DE 10 sean para pedidos diferentes, solo sabe que el 10 está duplicado para el Cliente, por lo que solo usa el 10 una vez para calcular la SUMA. Por lo tanto, devuelve un valor de 10 como el costo total de envío para esos dos pedidos, aunque debería ser 10+10=20. Nuestro resultado ahora es erróneo. A largo y a corto plazo, es esto: ¡Nunca use SUMA (Distinta)! Por lo general, no tiene sentido lógico en la mayoría de las situaciones; puede haber un momento y lugar para ello, pero definitivamente no está aquí.
Resumiendo Tablas derivadas
Entonces, ¿cómo solucionamos esto? Bueno, como muchos problemas SQL, la respuesta es simple: Hazlo paso a paso, no intentes unir todas las tablas y solo agrega SUM() y AGRUPA POR y DISTINGUE casi aleatoriamente hasta que las cosas funcionen; divídelo lógicamente paso a paso.
Así que, antes de preocuparnos por los totales por Cliente, retrocedamos y centrémonos en devolver los totales por pedido. Si podemos devolver los totales por Pedido primero, entonces simplemente podemos resumir los totales de los pedidos por Cliente y tendremos los resultados que necesitamos. Resumamos la tabla OrderDetails para devolver 1 fila por Pedido, con el itemCount y el importe total del pedido:
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)
Agradable y simple, fácil de verificar, las cosas se ven bien. Debido a que estamos agrupando en OrderID, podemos decir que estos resultados tienen una clave primaria virtual de OrderID, es decir, nunca habrá filas duplicadas para el mismo orden. De hecho, aquí hay otra regla básica para recordar siempre:
La clave primaria virtual de un SELECT con una cláusula GROUP BY siempre serán las expresiones indicadas en el GROUP BY.
Ahora podemos tomar esa instrucción SQL y esos resultados y encapsularlos en su propia tabla derivada. Si unimos de la tabla Orders a la anterior SELECT como tabla derivada, obtenemos:
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)
Examinemos esos resultados. No hay filas o valores duplicados en ningún lugar; hay exactamente una fila por orden. Esto se debe a que nuestra tabla derivada tiene una clave primaria virtual de OrderID, por lo que la unión de órdenes a nuestra tabla derivada nunca producirá duplicados. Esta es una técnica muy útil y sencilla para evitar duplicados al relacionar una tabla principal con una tabla secundaria: resumir la tabla secundaria por la clave primaria de la tabla primaria primero en una tabla derivada y luego unirla a la tabla principal. Las filas de la tabla principal nunca se duplicarán y se pueden resumir con precisión.
Ahora tenemos nuestro total de artículos por pedido, así como nuestro total de pedidos por pedido. Y podemos ver que si sumamos estos resultados, nuestra columna ShippingCost estará bien, ya que nunca se duplica. No hay necesidad de diferenciarse. De hecho, incluso podemos usar una expresión de CONTEO regular(*) para obtener el número total de pedidos por cliente.
Por lo tanto, simplemente podemos agregar «AGRUPAR POR cliente» al SQL anterior, calcular lo que necesitamos con funciones de agregado y eliminar cualquier columna (como OrderID) que no resumiremos. También puede notar que en este punto, el total itemCount por cliente ya no es una expresión COUNT ( * ); es una SUMA simple () del valor itemCount devuelto de nuestra tabla derivada.
Aquí está el 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)
Y ahí lo tienen! Examinamos nuestros datos, consideramos lógicamente las implicaciones de nuestras UNIONES, dividimos el problema en partes más pequeñas y terminamos con una solución bastante simple que sabemos que será rápida, eficiente y precisa.
Agregar más tablas una SELECCIÓN resumida
Para terminar, supongamos que nuestro esquema también tiene una tabla 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'
… y también queremos devolver el nombre, la ciudad y el estado de cada cliente en nuestros resultados anteriores. Una forma de hacerlo es simplemente agregar la tabla Clientes a nuestra unión existente y, a continuación, agregar las columnas clientes a la cláusula SELECT. Sin embargo, a menos que también agregue todas las columnas de clientes al GRUPO, recibirá un mensaje de error que indica que necesita agrupar o resumir todas las columnas que desea mostrar. No estamos tratando de calcular un COUNT() o una SUMA() de Nombre, Ciudad y Estado, por lo que no tiene sentido envolver esas columnas en una expresión agregada. Por lo tanto, parece que debemos agregarlos todos a nuestra cláusula GROUP BY para obtener los resultados que necesitamos:
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)
Técnicamente, eso funciona, pero parece tonto enumerar todas esas columnas de clientes en el GRUPO POR … Después de todo, solo estamos agrupando en el Cliente, no en cada uno de los atributos del cliente, ¿verdad?
Lo interesante es que la solución es algo de lo que ya hablamos y se aplica la misma técnica: Dado que el Cliente tiene una relación de uno a muchos con los pedidos, sabemos que unir Clientes a Pedidos dará como resultado filas duplicadas por Cliente, y por lo tanto todas las columnas de la tabla de clientes se duplican en los resultados. Es posible que note que este es exactamente el mismo escenario que se aplica al unir Órdenes a los detalles del pedido. Por lo tanto, manejamos esta situación de la misma manera! Simplemente resumimos nuestros pedidos por Cliente primero, en una tabla derivada, y luego unimos esos resultados a la tabla del Cliente. Esto significa que ninguna columna de la tabla de clientes se copiará en absoluto, y no hay necesidad de agregarlas todas a nuestro GRUPO POR expresión. Esto mantiene nuestro SQL limpio, organizado y lógicamente sólido.
Entonces, nuestros resultados finales ahora se ven así:
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)
Conclusión
Espero que esta serie de dos partes le ayude un poco a comprender el GRUPO POR consultas. Es vital identificar y comprender cuál es la clave primaria virtual de un conjunto de resultados cuando se unen varias tablas, y reconocer qué filas se duplican o no. Además, recuerde que CONTAR (Distinto) puede ser útil, pero la SUMA(Distinto) debe usarse muy raramente, si es que se usa alguna vez.
En general, si encuentra que los valores que necesita SUMAR () se han duplicado, resuma la tabla que causa esos duplicados por separado y únala como una tabla derivada. Esto también le permitirá desglosar su problema en pasos más pequeños y probar y validar los resultados de cada paso a medida que avanza.
GROUP BY es una característica muy poderosa, pero también se malinterpreta y se abusa de ella, y la forma más fácil de aprovecharla es construir cuidadosamente su SQL de partes más pequeñas y simples a soluciones más grandes y complicadas.