Başlıktan da anlaşılacağı gibi, bir GROUP BY
ile gruplandırılmış her satır kümesinin ilk satırını seçmek istiyorum.
Özellikle, aşağıdaki gibi görünen bir `satın alımlar' tablosuna sahipsem:
SELECT * FROM purchases;
Çıktım:
id | müşteri | toplam ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1
Her bir müşteri
tarafından yapılan en büyük satın alma işleminin (toplam
) id
sini sorgulamak istiyorum. Bunun gibi bir şey:
SELECT FIRST(id), customer, FIRST(total)
FROM purchases
GROUP BY customer
ORDER BY total DESC;
Beklenen Çıktı:
İLK(id) | müşteri | İLK(toplam) ----------+----------+------------- 1 | Joe | 5 2 | Sally | 3
PostgreSQL'de bu genellikle daha basit ve daha hızlıdır** (aşağıda daha fazla performans optimizasyonu):
SELECT DISTINCT ON (customer)
id, müşteri, toplam
ALIMLARDAN
ORDER BY müşteri, toplam DESC, id;
Veya çıktı sütunlarının sıra numaraları ile daha kısa (net olmasa da):
SELECT DISTINCT ON (2)
id, customer, total
FROM purchases
ORDER BY 2, 3 DESC, 1;
Eğer total
NULL olabilirse (her iki şekilde de zarar vermez, ancak mevcut indekslerle eşleşmek isteyeceksiniz):
...
ORDER BY customer, total DESC NULLS LAST, id;
###Başlıca noktalar
- [**`DISTINCT ON`**][1] standardın bir PostgreSQL uzantısıdır (tüm `SELECT` listesi üzerinde yalnızca `DISTINCT` tanımlanır).
- DISTINCT ON` cümlesinde herhangi bir sayıda ifadeyi listeleyin, birleştirilmiş satır değeri kopyaları tanımlar. [Kılavuz:][2]
> Açıkça görüldüğü üzere, iki satır en az
> bir sütun değeri. **Bu karşılaştırmada boş değerler eşit kabul edilir.**
Kalın vurgular bana aittir.
- DISTINCT ON` ifadesi **`ORDER BY`** ile birleştirilebilir. Baştaki ifadeler, baştaki `DISTINCT ON` ifadeleriyle aynı sırada eşleşmelidir. Her bir eşler grubundan belirli bir satırı seçmek için `ORDER BY` ifadesine *ek* ifadeler ekleyebilirsiniz. Beraberliği bozmak için son öğe olarak `id` ekledim:
*"En yüksek `toplam`ı paylaşan her gruptan en küçük `id`ye sahip satırı seçin."*
Sonuçları grup başına birinciyi belirleyen sıralama düzenine uymayan bir şekilde sıralamak için, yukarıdaki sorguyu başka bir `ORDER BY` ile bir dış sorguya yerleştirebilirsiniz. Örneğin:
- https://stackoverflow.com/questions/9795660/postgresql-distinct-on-with-different-order-by/9796104#9796104
- Eğer `total` NULL olabiliyorsa, *büyük olasılıkla* en büyük null olmayan değere sahip satırı istersiniz. Gösterildiği gibi **`NULLS LAST`** ekleyin. Ayrıntılar:
- https://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492
- `SELECT` listesi** herhangi bir şekilde `DISTINCT ON` veya `ORDER BY` içindeki ifadelerle kısıtlanmaz. (Yukarıdaki basit durumda gerekli değildir):
- DISTINCT ON` veya `ORDER BY` içindeki ifadelerden herhangi birini dahil etmek zorunda değilsiniz.
- SELECT` listesine başka herhangi bir ifadeyi dahil edebilirsiniz. Bu, çok daha karmaşık sorguları alt sorgular ve toplama / pencere işlevleriyle değiştirmek için çok önemlidir.
- Postgres 8.3 - 12 sürümleri ile test ettim. Ancak bu özellik en azından 7.1 sürümünden beri var, yani temelde her zaman.
##Index
Yukarıdaki sorgu için *mükemmel* dizin, eşleşen sırayla ve eşleşen sıralama düzeniyle üç sütunu da kapsayan bir [çok sütunlu dizin][3] olacaktır:
CREATE INDEX purchases_3c_idx ON purchases (customer, total DESC, id);
Çok özelleşmiş olabilir. Ancak belirli bir sorgu için okuma performansı çok önemliyse kullanın. Sorguda DESC NULLS LAST
varsa, sıralama düzeninin eşleşmesi ve dizinin uygulanabilir olması için dizinde de aynısını kullanın.
Her sorgu için özel indeksler oluşturmadan önce maliyet ve faydayı tartın. Yukarıdaki dizinin potansiyeli büyük ölçüde veri dağılımına bağlıdır. Dizin, önceden sıralanmış veriler sunduğu için kullanılır. Postgres 9.2 veya sonraki sürümlerde, dizin temel tablodan daha küçükse sorgu bir yalnızca dizin taramasından da yararlanabilir. Yine de dizinin tamamının taranması gerekir.
customer
sütununda yüksek kardinalite), bu çok verimlidir. Zaten sıralanmış çıktıya ihtiyacınız varsa daha da fazla. Müşteri başına satır sayısı arttıkça fayda azalır.work_mem
değerine sahip olursunuz. Ancak genellikle work_mem
i çok yüksek ayarlamanın olumsuz etkileri olabilir. Son derece büyük sorgular için SET LOCAL
seçeneğini düşünün. EXPLAIN ANALYZE` ile ne kadar ihtiyacınız olduğunu bulun. Sıralama adımında "Disk:" ifadesi daha fazlasına ihtiyaç olduğunu gösterir:customer
sütununda düşük kardinalite), loose index scan (a.k.a. "skip scan") (çok) daha verimli olacaktır, ancak bu'Postgres 12'ye kadar uygulanmamıştır. (Yalnızca dizin taramaları için bir uygulama Postgres 13 için geliştirilmektedir. Bakınız burada ve burada).
Şimdilik, bunun yerine geçecek daha hızlı sorgu teknikleri vardır. Özellikle de benzersiz müşterileri tutan ayrı bir tablonuz varsa, ki bu tipik kullanım durumudur. Ama eğer yoksa da:Burada artık güncelliğini yitirmiş basit bir kıyaslama vardı. Onu ayrı bir cevapta ayrıntılı kıyaslama ile değiştirdim.
WITH summary AS (
SELECT p.id,
p.customer,
p.total,
ROW_NUMBER() OVER(PARTITION BY p.customer
ORDER BY p.total DESC) AS rk
FROM PURCHASES p)
SELECT s.*
FROM summary s
WHERE s.rk = 1
Ancak eşitliği bozmak için mantık eklemeniz gerekir:
SELECT MIN(x.id), -- change to MAX if you want the highest
x.customer,
x.total
FROM PURCHASES x
JOIN (SELECT p.customer,
MAX(total) AS max_total
FROM PURCHASES p
GROUP BY p.customer) y ON y.customer = x.customer
AND y.max_total = x.total
GROUP BY x.customer, x.total
Erwin'in de belirttiği gibi, SubQ'ların varlığı nedeniyle çözüm çok verimli değildir
select * from purchases p1 where total in
(select max(total) from purchases where p1.customer=customer) order by total desc;