Birden fazla kullanılan e-postalar
Görev tanımı
Problem
Bir account(id, email) tablosu kayıt başına bir satır tutuyor ve benzersizlik kontrolü olmayan bir kayıt formu bazı e-postaların birkaç kez girmesine izin vermiş. Birden fazla hesapta görünen her e-postayı, onu kullanan hesap sayısıyla birlikte email ve uses sütunları (column) olarak, e-postaya göre sıralı döndüren bir sorgu yaz. E-postalar tam olarak saklandıkları gibi karşılaştırılıyor.
Örnekler
Input: account = [(1, "a@x.io"), (2, "b@x.io"), (3, "a@x.io"), (4, "c@x.io"), (5, "b@x.io"), (6, "a@x.io")]
Output: [("a@x.io", 3), ("b@x.io", 2)]
Neden: c@x.io bir kez kullanılmış, yani tekrar değil
Input: account = [(1, "a@x.io"), (2, "b@x.io")]
Output: []
Neden: edge case: hiçbir e-posta tekrar etmiyor, yani hiç satır gelmiyor
İpuçları
0 / 3
Bir e-postayı paylaşan satırların birlikte sayılması gerekiyor; yani satırların gruplandığı şey e-posta.
WHERE gruplamadan önce çalışır ve bir sayıyı göremez. Başka bir clause grupları oluşturulduktan sonra bütün olarak filtreler.
GROUP BY email yap, email ve COUNT(*) seç, HAVING COUNT(*) > 1 ile grupları tut ve sıra tanımlı olsun diye ORDER BY email ile bitir.
Kısıtlar
- emails are compared exactly as stored
Çözüm
GROUP BY email, bir e-postayı paylaşan her satır kümesini (set) tek bir gruba katlar, COUNT(*) da içindeki satırları sayar. Filtrenin bu katlamadan sonra çalışması gerekir, çünkü tek bir satır kaç başka satırın e-postasını paylaştığını bilmez; HAVING tam olarak bunun için var: WHERE satırları gruplamadan önce, HAVING grupları ondan sonra filtreler. ORDER BY olmadan bir veritabanı grupları herhangi bir sırada döndürebilir, bu yüzden sıralama cevabın parçasıdır. Alışılmış devam sorusu fazla hesapları silip e-posta başına en eskisini tutmaktır: DELETE FROM account WHERE id NOT IN (SELECT MIN(id) FROM account GROUP BY email). Gruplama hashing ile O(n), sıralamayla O(n log n) tutar; email üzerinde bir indeks de veritabanının grupları zaten sıralı okumasını sağlar.
import sqlite3
QUERY = """
SELECT email, COUNT(*) AS uses
FROM account
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email
"""
def run(accounts):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE account (id INTEGER PRIMARY KEY, email TEXT)")
db.executemany("INSERT INTO account VALUES (?, ?)", accounts)
return db.execute(QUERY).fetchall()
print(run([(1, "a@x.io"), (2, "b@x.io"), (3, "a@x.io"), (4, "c@x.io"), (5, "b@x.io"), (6, "a@x.io")])) # -> [('a@x.io', 3), ('b@x.io', 2)]
print(run([(1, "a@x.io"), (2, "b@x.io")])) # -> []Kodda değil de fikirde mi takıldın? Aggregation ve GROUP BY bunu anlatıyor.