GROUP BY cümlesinde görünmeli veya bir toplama işlevinde kullanılmalıdır

Bu arayan 'makerar&#39 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.

a_horse_with_no_name
Edited question 2013ööp9öö9 Kasım 2013 в 9:09
Programlama
sql group-by aggregate-functions postgresql-9.1
2013ööp50öö1 Ekim 2013 в 1:50
21 görünümler
Çözüm

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...

Sebas
Edited answer 2015ööp34öö6 Kasım 2015 в 6:34
Yorumlar (7)

Postgres'te, özel DISTINCT ON (expression) sözdizimini de kullanabilirsiniz:

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;
Yorumlar (8)
 
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.

zero323
Edited answer 2013ösp52ös22 Kasım 2013 в 10:52
Yorumlar (0)