Bu arayan 'makerar' gibi görünen bir tablom var;
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Ve her bir cname için maksimum ortalamayı seçmek istiyorum.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
ama bir hata alacağım,
ERROR: column "makerar.wmname" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;
Bu yüzden bunu yapıyorum.
SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname, wmname;
ancak bu amaçlanan sonuçları vermeyecektir ve aşağıdaki hatalı çıktı gösterilmektedir.
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
Gerçek Sonuçlar şöyle olmalıdır
cname | wmname | max
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Bu sorunu nasıl çözebilirim?
Not: Bu tablo, önceki bir işlemden oluşturulan bir GÖRÜNÜM'dür.
Evet, bu yaygın bir toplama sorunudur. SQL3 (1999)]1 öncesinde, seçilen alanlar GROUP BY
cümlesinde görünmelidir[*].
Bu sorunu aşmak için, toplamı bir alt sorguda hesaplamalı ve ardından göstermeniz gereken ek sütunları elde etmek için kendisiyle birleştirmelisiniz:
SELECT m.cname, m.wmname, t.mx
FROM (
SELECT cname, MAX(avg) AS mx
FROM makerar
GROUP BY cname
) t JOIN makerar m ON m.cname = t.cname AND t.mx = m.avg
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
Ancak daha basit görünen pencere işlevlerini de kullanabilirsiniz:
SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;
Bu yöntemle ilgili tek şey, tüm kayıtları gösterecek olmasıdır (pencere işlevleri gruplama yapmaz). Ancak her satırdaki ülke için doğru (yani cname
düzeyinde maksimum) MAX
değerini gösterecektir, bu yüzden size kalmış:
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
Maksimum değerle eşleşen yalnızca (cname, wmname)
çiftlerini göstermek için tartışmalı bir şekilde daha az zarif olan çözüm:
SELECT DISTINCT /* distinct here matters, because maybe there are various tuples for the same max value */
m.cname, m.wmname, t.avg AS mx
FROM (
SELECT cname, wmname, avg, ROW_NUMBER() OVER (PARTITION BY avg DESC) AS rn
FROM makerar
) t JOIN makerar m ON m.cname = t.cname AND m.wmname = t.wmname AND t.rn = 1
;
cname | wmname | mx
--------+--------+------------------------
canada | zoro | 2.0000000000000000
spain | usopp | 5.0000000000000000
[*]: İlginçtir ki, spesifikasyon gruplanmamış alanların seçilmesine izin verse de, büyük motorlar bundan pek hoşlanmıyor gibi görünüyor. Oracle ve SQLServer buna hiç izin vermiyor. Mysql eskiden varsayılan olarak buna izin veriyordu, ancak şimdi 5.7'den beri bu özelliğin desteklenmesi için yöneticinin sunucu yapılandırmasında bu seçeneği (ONLY_FULL_GROUP_BY
) manuel olarak etkinleştirmesi gerekiyor...
Postgres'te, özel DISTINCT ON (expression)
sözdizimini de kullanabilirsiniz:
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
SELECT t1.cname, t1.wmname, t2.max
FROM makerar t1 JOIN (
SELECT cname, MAX(avg) max
FROM makerar
GROUP BY cname ) t2
ON t1.cname = t2.cname AND t1.avg = t2.max;
rank()` pencere işlevi kullanarak:
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
Not
Her ikisi de grup başına birden fazla maksimum değeri koruyacaktır. Avg değeri max değerine eşit olan birden fazla kayıt olsa bile grup başına yalnızca tek bir kayıt istiyorsanız @ypercube'un yanıtını kontrol etmelisiniz.