musi pojawić się w klauzuli GROUP BY lub być użyty w funkcji agregującej

W Postgres można również użyć specjalnego DISTINCT ON (expression) składnia:

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;

Problem z określeniem pól niezagregowanych i niezagregowanych w selektach group by polega na tym, że silnik nie ma możliwości poznania pola rekordu, które powinien zwrócić w tym przypadku. Najpierw? Ostatni? Zwykle nie ma zapisu, który w naturalny sposób odpowiada zagregowanemu wynikowi (min i max są wyjątkami).

Istnieje jednak obejście: zagreguj Wymagane pole. W posgres powinno to działać:

SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;

Zauważ, że tworzy to tablicę wszystkich wnames, uporządkowane przez avg i zwraca pierwszy element (tablice w postgres są oparte na 1).

Dla mnie nie chodzi o "powszechny problem agregacji", ale o niepoprawne zapytanie SQL. Jedna poprawna odpowiedź dla "wybierz maksymalną wartość avg dla każdego cname..."is

SELECT cname, MAX(avg) FROM makerar GROUP BY cname;

Wynik będzie:

 cname  |      MAX(avg)
--------+---------------------
 canada | 2.0000000000000000
 spain  | 5.0000000000000000

Ten wynik ogólnie odpowiada na pytanie "jaki jest najlepszy wynik dla każdej grupy?". Widzimy, że najlepszy wynik dla Hiszpanii to 5, a dla Kanady najlepszy wynik to 2. To prawda i nie ma błędu. Jeśli chcemy wyświetlić wmname Również, możemy musisz odpowiedzieć na pytanie: "Jaka jest reguła aby wybrać wmname z wynikowego zestawu?"Zmieńmy trochę dane wejściowe, aby wyjaśnić błąd:

  cname | wmname |        avg           
--------+--------+-----------------------
 spain  | zoro   |  1.0000000000000000
 spain  | luffy  |  5.0000000000000000
 spain  | usopp  |  5.0000000000000000

Jakiego wyniku oczekujesz po uruchomieniu tego zapytania: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname;? Powinno być spain+luffy Czy spain+usopp? Dlaczego? Nie jest określone W zapytaniu jak wybrać "lepsze" wmname Jeśli kilka jest odpowiednich, więc wynik również nie jest określony. Dlatego interpreter SQL zwraca błąd-zapytanie nie jest zgadza się.

Innymi słowy, nie ma poprawnej odpowiedzi na pytanie "Kto jest najlepszy w grupie spain?". Luffy nie jest lepszy od Usoppa, ponieważ usopp ma ten sam "wynik".

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;

Za pomocą rank() funkcja okna :

SELECT cname, wmname, avg
FROM (
    SELECT cname, wmname, avg, rank() 
    OVER (PARTITION BY cname ORDER BY avg DESC)
    FROM makerar) t
WHERE rank = 1;

Uwaga

Każda z nich zachowa wiele wartości maksymalnych na Grupę. Jeśli chcesz tylko jeden rekord na Grupę, nawet jeśli jest więcej niż jeden rekord z avg równym max, powinieneś sprawdzić odpowiedź @ypercube.

To wydaje się działać również

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )

Ostatnio napotkałem ten problem, próbując policzyć za pomocą case when i stwierdziłem, że zmiana kolejności instrukcji which i count rozwiązuje problem:

SELECT date(dateday) as pick_day,
COUNT(CASE WHEN (apples = 'TRUE' OR oranges 'TRUE') THEN fruit END)  AS fruit_counter

FROM pickings

GROUP BY 1

Zamiast używać-w tym drugim, gdzie mam błędy, że jabłka i pomarańcze powinny pojawić się w funkcjach zbiorczych

CASE WHEN ((apples = 'TRUE' OR oranges 'TRUE') THEN COUNT(*) END) END AS fruit_counter