Menu

PostgreSQL DISTINCT ON: Her Gruptan Tek Satır

SELECT DISTINCT ON (key) ... ORDER BY key, sort her grubun ilk satırını tutar; örneğin her müşterinin en son siparişini. Yalnızca PostgreSQL'de vardır.

Bu sayfada çalıştırılabilir editörler var. Düzenle, çalıştır ve sonucu anında gör.

SELECT DISTINCT ON (expr) ..., expr'in her farklı değeri için bir satır döndürür: o grubun ORDER BY sırasına göre ilk satırını. Önce grup anahtarına, sonra "ilk"in neye göre belirleneceğine göre sırala. Her müşterinin en son siparişi için bu ORDER BY customer, created_at DESC olur.

Üç satır döner: asha için 2, ben için 4 ve chen için 5 numaralı sipariş; yani her müşterinin en son siparişi. DISTINCT ON PostgreSQL'e özgü bir eklentidir. Diğer veritabanları aşağıda gösterilen pencere fonksiyonu biçimine ihtiyaç duyar.

ORDER BY hangi satırın kalacağını nasıl belirler

PostgreSQL satırları sıralar, sonra DISTINCT ON değeri her değiştiğinde ilk satırı tutar. Yani:

  • ORDER BY, DISTINCT ON ifadeleriyle başlamalıdır; yoksa PostgreSQL "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" hatası verir.
  • Bu ifadelerden sonra gelen bir ORDER BY yoksa hangi satırın kalacağı öngörülemez ve çalıştırmalar arasında değişebilir.

created_at DESC yerine total DESC yazarsan aynı sorgu her müşterinin en büyük siparişini döndürür:

Artık asha'nın satırı 1 numaralı sipariş (40), ben'inki 3 numaralı sipariş (60).

Eşitlikleri bilinçli olarak bozmak

İki satır sıralama sütununda eşit olduğunda hangisi de kazanabilir. Sonucun her seferinde aynı olması için en sona birincil anahtar gibi benzersiz bir sıralama anahtarı ekle:

2 ve 3 numaralı satırların ikisi de 95 puana sahip. Son anahtar olan id, 2 numaralı satırı kazanan yapar.

Birden fazla sütuna göre gruplamak

Her kombinasyon için bir satır almak için birkaç ifade listele:

Her sensör ve ölçüm türü çifti en son okumasını tutar: s1 humidity 40, s1 temp 22, s2 temp 19.

ROW_NUMBER ile taşınabilir sürüm

ROW_NUMBER(), her bölümün (partition) içindeki satırları numaralandırır. 1 numarayı tutmak DISTINCT ON ile aynı sonucu verir ve MySQL 8, SQL Server ve SQLite'ta da çalışır:

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

Grup başına ilk n satıra ihtiyacın olduğunda (rn <= 3) ya da sorgunun başka bir veritabanında çalışması gerektiğinde bu biçimi kullan. PARTITION BY pencere fonksiyonları sayfasında anlatılıyor.

Hızlandırmak

DISTINCT ON girdiyi sıralar, bu yüzden büyük bir tabloda ona ORDER BY ile eşleşen bir indeks ver:

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

PostgreSQL böylece satırları sıralamak yerine sırayla okuyabilir. Az sayıda grup ve grup başına çok satır olduğunda, aynı indeksten müşteri başına LIMIT 1 alan bir LATERAL join genellikle daha az satır okur. LATERAL join'ler sayfasında gösteriliyor. İkisini kendi verinde EXPLAIN ANALYZE ile karşılaştır.

Sıkça Sorulan Sorular

DISTINCT ile DISTINCT ON arasındaki fark nedir?

DISTINCT, seçilen her sütunda aynı olan satırları kaldırır. DISTINCT ON (a) ise a'nın her değeri için bir satır tutar; diğer sütunlar da o grupta sıralamada ilk gelen satırdan gelir.

DISTINCT ON standart SQL mi?

Hayır. PostgreSQL'e özgü bir eklentidir, bu yüzden MySQL ya da SQL Server'da çalışmaz. Taşınabilir sürümü, bir alt sorgu içinde ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) kullanıp satır numarası 1 olanları filtrelemektir.

Neden "SELECT DISTINCT ON expressions must match initial ORDER BY expressions" hatası alıyorum?

ORDER BY, DISTINCT ON ile aynı ifadelerle, aynı sırayla başlamalıdır. Tek başına ORDER BY created_at DESC değil, DISTINCT ON (customer) ... ORDER BY customer, created_at DESC yaz.

DISTINCT ON her gruptan ilk 3 satırı döndürebilir mi?

Hayır, her gruptan tam olarak bir satır döndürür. İlk n satır için satırları ROW_NUMBER() ile numaralandırıp rn <= 3 olanları tut ya da LIMIT 3 içeren bir LATERAL alt sorgu kullan.

Coddy programlama dilleri çizimi

Coddy ile kodlamayı öğren

Başla