Vendas trimestrais como colunas
A missão
Problema
Uma tabela sales(region, quarter, amount) guarda uma linha por venda, com quarter sendo um número de 1 a 4. Escreva uma consulta (query) que transforme os trimestres em colunas: uma linha por região, com as colunas region, q1, q2, q3 e q4, cada uma com o total da região naquele trimestre, ou 0 quando a região não vendeu nada nele. Ordene por região.
Exemplos
Input: sales = [("east", 1, 100), ("east", 2, 50), ("west", 1, 70), ("east", 1, 30), ("west", 4, 20)]
Output: [("east", 130, 50, 0, 0), ("west", 70, 0, 0, 20)]
Input: sales = [("north", 3, 5), ("north", 3, 5), ("north", 3, 5)]
Output: [("north", 0, 0, 15, 0)]
Por quê: toda venda cai no trimestre 3, e as outras três colunas ainda aparecem como 0
Input: sales = []
Output: []
Por quê: edge case: sem vendas, não há regiões nem linhas
Dicas
0 / 3
GROUP BY region dá exatamente a linha por região que você quer. A parte difícil são quatro totais diferentes nessa mesma linha, cada um enxergando só algumas das linhas.
O WHERE não ajuda, porque ele remove uma linha de todas as colunas ao mesmo tempo. Você precisa de um filtro que valha para uma coluna só.
Dentro do SUM, escreva CASE WHEN quarter = 1 THEN amount ELSE 0 END e repita para os trimestres 2, 3 e 4. O ELSE 0 importa: sem ele, um trimestre sem vendas soma NULL.
Restrições
- quarter is a number from 1 to 4
Solução
Isto é um pivot feito com agregação condicional. GROUP BY region reduz cada região a uma linha, e cada coluna de saída é um SUM sobre uma expressão CASE que deixa passar o valor da linha só quando ela pertence ao trimestre daquela coluna e contribui com 0 nos outros casos. O CASE é um filtro por coluna: WHERE quarter = 1 jogaria fora as linhas dos outros trimestres para todas as colunas, enquanto o CASE permite que cada coluna mantenha as próprias linhas. O ELSE 0 é proposital, porque o SUM sobre nada além de NULLs é NULL, não 0. Alguns bancos têm a palavra-chave PIVOT ou a cláusula FILTER (WHERE …), mas CASE dentro de uma agregação funciona em qualquer lugar. A consulta lê a tabela uma vez e a agrupa, O(n) com hashing.
import sqlite3
QUERY = """
SELECT region,
SUM(CASE WHEN quarter = 1 THEN amount ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 2 THEN amount ELSE 0 END) AS q2,
SUM(CASE WHEN quarter = 3 THEN amount ELSE 0 END) AS q3,
SUM(CASE WHEN quarter = 4 THEN amount ELSE 0 END) AS q4
FROM sales
GROUP BY region
ORDER BY region
"""
def run(rows):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE sales (region TEXT, quarter INTEGER, amount INTEGER)")
db.executemany("INSERT INTO sales VALUES (?, ?, ?)", rows)
return db.execute(QUERY).fetchall()
print(run([("east", 1, 100), ("east", 2, 50), ("west", 1, 70), ("east", 1, 30), ("west", 4, 20)])) # -> [('east', 130, 50, 0, 0), ('west', 70, 0, 0, 20)]
print(run([("north", 3, 5), ("north", 3, 5), ("north", 3, 5)])) # -> [('north', 0, 0, 15, 0)]
print(run([])) # -> []Travou na ideia, e não no código? WHERE e filtragem explica isso.