У меня есть таблица, которая выглядит следующим абонента 'makerar'
cname | wmname | avg
--------+-------------+------------------------
canada | zoro | 2.0000000000000000
spain | luffy | 1.00000000000000000000
spain | usopp | 5.0000000000000000
И я хочу выбрать максимальное AVG для каждого типа CNAME.
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
Как я могу идти о фиксации этой проблемы?
Примечание: эта таблица-это представление, созданное из предыдущей операции.
Да, это общая проблема агрегирования. Перед [SQL3 (1999)][1], некоторые поля должны отображаться в группе
п.[*].
Чтобы обойти эту проблему, необходимо рассчитать совокупные в суб-запроса и затем присоединить его с собой, чтобы сделать дополнительные столбцы, вы'd нужен, чтобы показать:
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
;
Единственная вещь, с помощью этого метода заключается в том, что он будет показывать все записи (окно функции не группа). Но он будет показывать правильно (т. е. с превышением нормы в имя
уровень) Макс
для страны в каждом ряду, так что's до вас:
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
[1]: http://web.cecs.pdx.edu/~лен/sql1999.формат PDF
[*]: Достаточно интересно, хотя в спецификации вроде как позволяет выбрать не-сгруппированных полей, крупных двигателей, похоже, не очень нравится. Oracle и SQLServer просто Дон'т допустить этого вообще. В MySQL используется, чтобы разрешить его по умолчанию, но теперь с 5.7 нужд администратора, чтобы включить этот параметр (ONLY_FULL_GROUP_BY
) вручную в конфигурации сервера для этой функции...
В Postgres, вы также можете использовать специальные четкая на (выражение)
синтаксис:
SELECT DISTINCT ON (cname)
cname, wmname, avg
FROM
makerar
ORDER BY
cname, avg DESC ;
Проблема с указанием номера группируются и неагрегатная поля в группе
выбирает заключается в том, что двигатель не имеет возможности узнать, какая запись's на поле он должен вернуться в этом случае. Это первый? Это в последний раз? Обычно нет записи, что, естественно, соответствует агрегированный результат ("мин" и "Макс" и исключения).
Однако, есть обходной путь: Обязательное поле агрегированы, а также. В posgres, это должно работать:
SELECT cname, (array_agg(wmname ORDER BY avg DESC))[1], MAX(avg)
FROM makerar GROUP BY cname;
Обратите внимание, что это создает массив всех wnames, заказал на AVG, и возвращает первый элемент (массивы в Postgres-это с 1).
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;
Используя ранг()
окно:
SELECT cname, wmname, avg
FROM (
SELECT cname, wmname, avg, rank()
OVER (PARTITION BY cname ORDER BY avg DESC)
FROM makerar) t
WHERE rank = 1;
Примечание
Либо один будет сохранить несколько максимальных значений в группе. Если вы хотите только одну запись в группе, даже если есть более одной записи с СР равным Max, вы должны проверить @ypercube'ы ответ.
Для меня, речь идет не о том, что "общие проблемы на" агрегации;, но просто неправильный SQL-запрос. Один правильный ответ и"выберите максимальное AVG для каждого типа CNAME..." это
SELECT cname, MAX(avg) FROM makerar GROUP BY cname;
Результат будет:
cname | MAX(avg)
--------+---------------------
canada | 2.0000000000000000
spain | 5.0000000000000000
Этот результат в целом отвечает на вопрос "что является лучшим результатом для каждой группы?&и". Мы видим, что лучший результат для Испании-это 5 и для Канады лучшим результатом является 2. Это правда, и ошибки нет. Если нам нужно вывести wmname также, мы должны ответить на вопрос: "Что это правила чтобы выбрать wmname из результирующего набора?" Давайте's поменять входные данные немного прояснить ошибку:
cname | wmname | avg
--------+--------+-----------------------
spain | zoro | 1.0000000000000000
spain | luffy | 5.0000000000000000
spain | usopp | 5.0000000000000000
Какой результат вы ожидаете на запуск этого запроса: выбрать CNAME, wmname, максимум(в среднем) от группы makerar на запись CNAME;
? Она должна быть Испания+Луффи " или " Испания+Усопп
? Почему? Это не определена на запрос Как выбрать шампунь&; лучше" по wmname если подходит, поэтому результат тоже не определено. Что's, почему интерпретатор SQL возвращает ошибку - запрос не является правильным.
Другими словами, нет правильного ответа на вопрос ", который является лучшим в группе Испания`?&и". Луффи не лучше, чем Усопп, Усопп, потому что имеет тот же "на результат" по.
Я недавно столкнулся с такой проблемой, когда пытаюсь считать с помощью случае
, и обнаружили, что изменение порядка что
и 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
Это похоже на работу
SELECT *
FROM makerar m1
WHERE m1.avg = (SELECT MAX(avg)
FROM makerar m2
WHERE m1.cname = m2.cname
)