Dados · SQL
Por que o total duplica depois de um JOIN no SQL?
Um pedido de R$ 30 foi enviado em 2 pacotes: quantas linhas ele dá no JOIN entre pedidos e pacotes?
E o que SUM(amount) devolve nessas linhas?
De um pedido em dois pacotes a um total certo, em cinco passos:
Por que o mesmo valor aparece várias vezes depois de um JOIN?
Um pedido de R$ 75 enviado em 3 pacotes aparece 3 vezes no JOIN, e cada uma dessas 3 linhas traz R$ 75.
Vamos pegar três pedidos e os pacotes deles.
SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
| id | amount |
|---|---|
| 101 | R$ 40 |
| 102 | R$ 25 |
| 103 | R$ 60 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 102 |
| 503 | 102 |
| 504 | 103 |
| orders.id | amount |
|---|---|
| 101 | R$ 40 |
| 102 | R$ 25 |
| 102 | R$ 25 |
| 103 | R$ 60 |
Quais pacotes têm o número do pedido 102? Toque neles na tabela.
Você procura 102 na coluna
order_id.
SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 35 102 R$ 80 103 R$ 15 104 R$ 50 parcels id order_id 501 101 502 102 503 102 504 102 505 103 506 104 SELECT orders.id, amount FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 20 102 R$ 70 103 R$ 45 104 R$ 60 parcels id order_id 501 104 502 102 503 101 504 101 505 104 506 104
Por que o SUM dá um total grande demais depois de um JOIN?
Se o pedido de R$ 75 aparece 3 vezes no JOIN, SUM(amount) soma esse valor 3 vezes: ele pesa R$ 225 no total, em vez de R$ 75.
Vamos pegar as mesmas tabelas, com SUM.
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
| id | amount |
|---|---|
| 101 | R$ 40 |
| 102 | R$ 25 |
| 103 | R$ 60 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 102 |
| 503 | 102 |
| 504 | 103 |
| orders.id | amount |
|---|---|
| 101 | R$ 40 |
| 102 | R$ 25 |
| 102 | R$ 25 |
| 103 | R$ 60 |
| SUM(amount) |
|---|
| R$ 150 |
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 50 102 R$ 15 103 R$ 70 parcels id order_id 501 101 502 102 503 102 504 102 505 103 SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 35 102 R$ 80 103 R$ 20 104 R$ 45 parcels id order_id 501 101 502 101 503 102 504 104
Como contar os pedidos sem duplicar depois de um JOIN?
Se 12 pedidos enviados dão 20 linhas no JOIN, COUNT(*) devolve 20 e COUNT(DISTINCT orders.id) devolve 12.
Vamos pegar três pedidos, dois deles enviados em vários pacotes.
SELECT COUNT(*),
COUNT(DISTINCT orders.id)
FROM orders
JOIN parcels
ON parcels.order_id = orders.id| id | amount |
|---|---|
| 101 | R$ 40 |
| 102 | R$ 25 |
| 103 | R$ 60 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 101 |
| 503 | 102 |
| 504 | 103 |
| 505 | 103 |
| 506 | 103 |
| orders.id | amount |
|---|---|
| 101 | R$ 40 |
| 101 | R$ 40 |
| 102 | R$ 25 |
| 103 | R$ 60 |
| 103 | R$ 60 |
| 103 | R$ 60 |
| COUNT(*) | COUNT(DISTINCT orders.id) |
|---|---|
| 6 | 3 |
SELECT COUNT(*) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 15 102 R$ 50 103 R$ 35 104 R$ 70 parcels id order_id 501 101 502 102 503 102 504 102 505 103 506 104 507 104 SELECT COUNT(DISTINCT orders.id) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 80 102 R$ 20 103 R$ 45 104 R$ 60 parcels id order_id 501 101 502 103 503 101 504 104 505 104
Como corrigir um SUM inflado por um JOIN?
Você reduz a tabela parcels a uma linha por pedido antes do JOIN: o pedido de R$ 90 enviado em 4 pacotes entra uma vez só na soma.
Vamos pegar três pedidos, dois deles de R$ 40.
SELECT SUM(amount)
FROM orders
JOIN (SELECT DISTINCT order_id
FROM parcels) AS shipped
ON shipped.order_id = orders.id| id | amount |
|---|---|
| 101 | R$ 40 |
| 102 | R$ 25 |
| 103 | R$ 40 |
| id | order_id |
|---|---|
| 501 | 101 |
| 502 | 101 |
| 503 | 102 |
| 504 | 103 |
| order_id |
|---|
| 101 |
| 102 |
| 103 |
| SUM(amount) |
|---|
| R$ 105 |
SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
resultado SUM(DISTINCT amount) R$ 65
SELECT SUM(amount) FROM orders JOIN (SELECT DISTINCT order_id FROM parcels) AS shipped ON shipped.order_id = orders.idorders id amount 101 R$ 35 102 R$ 50 103 R$ 20 104 R$ 70 parcels id order_id 501 101 502 101 503 103 504 104 505 104 506 104 SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 45 102 R$ 15 103 R$ 45 104 R$ 80 parcels id order_id 501 103 502 102 503 102 504 101 505 103 506 104
Como achar rápido um total duplicado por um JOIN?
Você ganha velocidade quando conta primeiro os pacotes de cada pedido: é o número de vezes que o valor dele entra no JOIN com parcels.
SELECT SUM(amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 25 102 R$ 60 103 R$ 15 104 R$ 80 parcels id order_id 501 101 502 102 503 102 504 103 SELECT COUNT(DISTINCT orders.id) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 20 102 R$ 45 103 R$ 35 104 R$ 50 parcels id order_id 501 104 502 102 503 101 504 101 505 101 SELECT SUM(DISTINCT amount) FROM orders JOIN parcels ON parcels.order_id = orders.id
orders id amount 101 R$ 70 102 R$ 25 103 R$ 70 104 R$ 15 parcels id order_id 501 103 502 102 503 101 504 101 SELECT SUM(amount) FROM orders JOIN (SELECT DISTINCT order_id FROM parcels) AS shipped ON shipped.order_id = orders.idorders id amount 101 R$ 40 102 R$ 80 103 R$ 50 104 R$ 35 parcels id order_id 501 101 502 102 503 102 504 102 505 104 506 104