Должен появиться в предложении GROUP BY или использоваться в агрегатной функции

Ответ 1

Да, это общая проблема агрегации. Перед SQL3 (1999) выбранные поля должны появиться в предложении GROUP BY [*].

Чтобы обойти эту проблему, вы должны вычислить агрегат в подзапросе и затем присоединить его к себе, чтобы получить дополнительные столбцы, которые вам нужно показать:

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

Но вы также можете использовать функции окна, которые выглядят проще:

SELECT cname, wmname, MAX(avg) OVER (PARTITION BY cname) AS mx
FROM makerar
;

Единственное, что с этим методом состоит в том, что он отобразит все записи (функции окна не группируются). Но он покажет правильную (то есть максимальную на уровне cname) MAX для страны в каждой строке, так что это зависит от вас:

 cname  | wmname |          mx           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | luffy  |     5.0000000000000000
 spain  | usopp  |     5.0000000000000000

Решение, возможно менее изящное, чтобы показать только те кортежи (cname, wmname), соответствующие максимальному значению, это:

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

[*]: Интересно, что, несмотря на то, что spec-тип позволяет выбирать негрупповые поля, основным двигателям, похоже, это не очень нравится. Oracle и SQLServer просто не позволяют этого вообще. Mysql используется для его разрешения по умолчанию, но теперь, начиная с 5.7, администратор должен включить эту опцию (ONLY_FULL_GROUP_BY) вручную в конфигурации сервера, чтобы эта функция поддерживалась...

Ответ 2

В Postgres вы также можете использовать специальный синтаксис DISTINCT ON (expression):

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

Ответ 3

Проблема с указанием несгруппированных и неагрегированных полей в group by селекторов состоит в том, что движок не может знать, какое поле записи оно должно вернуть в этом случае. Это первое? Это последний? Обычно нет записи, которая естественно соответствует агрегированному результату (min и max являются исключениями).

Тем не менее, есть обходной путь: объедините обязательное поле. В posgres это должно работать:

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

Обратите внимание, что это создает массив всех wnames, упорядоченных по avg, и возвращает первый элемент (массивы в postgres основаны на 1).

Ответ 4

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() функция окна:

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

Примечание

Любой из них сохранит несколько максимальных значений для каждой группы. Если вам нужна только одна запись на группу, даже если есть более одной записи с avg равным max, вы должны проверить ответ @ypercube.

Ответ 5

Для меня это не "общая проблема агрегации", а просто неправильный запрос SQL. Единственный правильный ответ для "выберите максимальное среднее значение для каждого имени..."

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

Результатом будет:

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

Этот результат в целом отвечает на вопрос "Каков наилучший результат для каждой группы?". Мы видим, что лучший результат для Испании - 5, а для Канады - 2. Это правда, и ошибки нет. Если нам нужно также отобразить wmname, мы должны ответить на вопрос: "Какое ПРАВИЛО выбрать wmname из полученного набора?" Позвольте немного изменить входные данные, чтобы уточнить ошибку:

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

Какой результат вы ожидаете при выполнении этого запроса: SELECT cname, wmname, MAX(avg) FROM makerar GROUP BY cname; ? Должно ли это быть spain+luffy или spain+usopp? Зачем? В запросе не определено, как выбрать "лучшее" имя wmname, если подходит несколько, поэтому результат также не определяется. Поэтому интерпретатор SQL возвращает ошибку - запрос неверный.

Другими словами, нет правильного ответа на вопрос "Кто лучший в spain?". Луффи не лучше, чем usopp, потому что у usopp такой же "счет".

Ответ 6

Недавно я столкнулся с этой проблемой при попытке подсчета с помощью case when и обнаружил, что изменение порядка операторов which и count устраняет проблему:

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

Вместо использования - во втором, где я получил ошибки, что яблоки и апельсины должны появляться в совокупных функциях

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

Ответ 7

Это тоже работает

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