Çeyreklik satışları sütunlara çevir
Görev tanımı
Problem
Bir sales(region, quarter, amount) tablosu satış başına bir satır tutuyor; quarter 1'den 4'e bir sayı. Çeyrekleri sütunlara (column) çeviren bir sorgu yaz: bölge başına bir satır, region, q1, q2, q3 ve q4 sütunlarıyla; her biri o bölgenin o çeyrekteki toplamını, hiç satış yapmadıysa 0 tutsun. Bölgeye göre sırala.
Örnekler
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)]
Neden: her satış 3. çeyreğe düşüyor, diğer üç sütun yine 0 olarak görünüyor
Input: sales = []
Output: []
Neden: edge case: satış yoksa bölge de satır da yok
İpuçları
0 / 3
GROUP BY region istediğin bölge başına bir satırı verir. Zor kısım, her biri satırların yalnız bir kısmını görmesi gereken dört farklı toplamın o tek satırda olması.
WHERE yardımcı olamaz, çünkü bir satırı her sütundan aynı anda kaldırır. Yalnız tek bir sütuna uygulanan bir filtreye ihtiyacın var.
SUM'ın içine CASE WHEN quarter = 1 THEN amount ELSE 0 END yaz ve 2, 3 ve 4. çeyrekler için tekrarla. ELSE 0 önemli: onsuz satışı olmayan bir çeyrek NULL'a toplanır.
Kısıtlar
- quarter is a number from 1 to 4
Çözüm
Bu, koşullu aggregation ile bir pivot. GROUP BY region her bölgeyi tek bir satıra indirir ve her çıktı sütunu, bir satırın miktarını yalnız o sütunun çeyreğine aitse geçiren, değilse 0 katan bir CASE ifadesi üzerinde bir SUM'dır. CASE sütun başına bir filtredir: WHERE quarter = 1 diğer çeyreklerin satırlarını her sütun için atardı, CASE ise her sütunun kendi satırlarını tutmasını sağlar. ELSE 0 bilinçlidir, çünkü yalnız NULL'lar üzerindeki SUM 0 değil NULL'dır. Bazı veritabanlarında bir PIVOT anahtar kelimesi ya da FILTER (WHERE …) clause'u var, ama bir aggregate'in (toplama fonksiyonu) içindeki CASE her yerde çalışır. Sorgu tabloyu bir kez okur ve gruplar; hashing ile O(n).
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([])) # -> []Kodda değil de fikirde mi takıldın? WHERE ve filtreleme bunu anlatıyor.