Hiç sipariş vermeyen müşteriler
Görev tanımı
Problem
İki tablo var: customer(id, name) ve orders(id, customer_id); orders.customer_id, customer.id'yi gösteriyor. Misafir siparişleri NULL bir customer_id ile saklanıyor. Hiç sipariş vermemiş müşterilerin adlarını tek bir name sütunu (column) olarak, ada göre sıralı döndüren bir sorgu yaz.
Örnekler
Input: customer = [(1, "Ana"), (2, "Ben"), (3, "Cy")]
orders = [(10, 1), (11, 1), (12, 3)]
Output: [("Ben",)]
Neden: Ana'nın iki, Cy'nin bir siparişi var
Input: customer = [(1, "Ana"), (2, "Ben")]
orders = [(10, 1), (11, None)]
Output: [("Ben",)]
Neden: 11 numaralı sipariş bir misafir siparişiydi ve kimseye ait değil
Input: customer = [(1, "Ana"), (2, "Ben")]
orders = []
Output: [("Ana",), ("Ben",)]
Neden: edge case: hiç sipariş yokken her müşteri uyuyor
İpuçları
0 / 3
Bir inner join yalnız eşleşen bir siparişi olan müşterileri tutar; bu da istenenin tam tersi. Hiçbir şey eşleşmese bile bir müşteriyi hangi join tutar?
Bir LEFT JOIN her müşteriyi tutar ve eşleşme yokken sipariş sütunlarını NULL ile doldurur. Sinyal bu NULL'lar.
LEFT JOIN orders ON orders.customer_id = customer.id yap, sonra WHERE orders.id IS NULL olan satırları tut. NULL tutan bir sütun üzerinde NOT IN'e dikkat et: hiç satır döndürmez.
Kısıtlar
- guest orders have a NULL customer_id
Çözüm
Bir LEFT JOIN her müşteriyi tutar ve her birini siparişleriyle eşler; siparişi olmayan bir müşteri NULL'lardan oluşan tek bir satırla eşlenir. Gerçek bir siparişte asla NULL olamayacak o.id gibi bir sütunu test etmek tam olarak o müşterileri seçer. Bu şekle anti-join denir ve NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) aynı şeyi ifade eder. Cazip görünen WHERE id NOT IN (SELECT customer_id FROM orders) burada yanlıştır: misafir siparişi o listeye bir NULL koyar, x NOT IN (..., NULL) asla doğru olmaz ve sorgu hiçbir şey döndürmez. orders.customer_id üzerinde bir indeksle her müşteri bir indeks aramasına mal olur; n müşteri ve m sipariş için O(n log m).
import sqlite3
QUERY = """
SELECT c.name
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name
"""
def run(customers, orders):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE customer (id INTEGER PRIMARY KEY, name TEXT)")
db.execute("CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER)")
db.executemany("INSERT INTO customer VALUES (?, ?)", customers)
db.executemany("INSERT INTO orders VALUES (?, ?)", orders)
return db.execute(QUERY).fetchall()
print(run([(1, "Ana"), (2, "Ben"), (3, "Cy")], [(10, 1), (11, 1), (12, 3)])) # -> [('Ben',)]
print(run([(1, "Ana"), (2, "Ben")], [(10, 1), (11, None)])) # -> [('Ben',)]
print(run([(1, "Ana"), (2, "Ben")], [])) # -> [('Ana',), ('Ben',)]Kodda değil de fikirde mi takıldın? INNER ve OUTER JOIN'ler bunu anlatıyor.