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

У меня есть таблица, которая выглядит как этот вызывающий "makerar"

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

И я хочу выбрать максимальное среднее значение для каждого имени.

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

но я получу ошибку,

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;

так что я делаю это

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

однако это не даст ожидаемых результатов, и ниже показан неправильный вывод.

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

Фактические результаты должны быть

 cname  | wmname |          max           
--------+--------+------------------------
 canada | zoro   |     2.0000000000000000
 spain  | usopp  |     5.0000000000000000

Как я могу решить эту проблему?

Примечание. Эта таблица представляет собой ВИД, созданный в результате предыдущей операции.

Источник

6 ответов

Решение

Да, это общая проблема агрегации. До 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

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

Источник

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

SELECT DISTINCT ON (cname) 
    cname, wmname, avg
FROM 
    makerar 
ORDER BY 
    cname, avg DESC ;
Источник

Проблема с указанием несгруппированных и неагрегированных полей в 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).

Источник

Для меня это не "общая проблема агрегации", а просто неправильный запрос 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 такой же" балл ".

Источник 
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.

Источник

Это похоже на работу

SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
                FROM makerar m2
                WHERE m1.cname = m2.cname
               )
Источник

Я недавно столкнулся с этой проблемой, когда пытаюсь считать, используя 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
Источник
Другие вопросы по тегам