PostgreSQL MAX и GROUP BY

У меня есть таблица с id, year и count.

Я хочу получить MAX(count) для каждого id и сохранить year, когда это произойдет, поэтому я делаю этот запрос:

SELECT id, year, MAX(count)
FROM table
GROUP BY id;

К сожалению, это дает мне ошибку:

ОШИБКА: столбец "table.year" должен появиться в предложении GROUP BY или быть используется в агрегатной функции

Итак, я пытаюсь:

SELECT id, year, MAX(count)
FROM table
GROUP BY id, year;

Но тогда он не делает MAX(count), он просто показывает таблицу как есть. Я полагаю, что при группировке year и id он получает максимум для id этого конкретного года.

Итак, как я могу написать этот запрос? Я хочу получить id MAX(count) и год, когда это произойдет.

Ответ 1

select *
from (
  select id, 
         year,
         thing,
         max(thing) over (partition by id) as max_thing
  from the_table
) t
where thing = max_thing

или

select t1.id,
       t1.year,
       t1.thing
from the_table t1
where t1.thing = (select max(t2.thing) 
                  from the_table t2
                  where t2.id = t1.id);

или

select t1.id,
       t1.year,
       t1.thing
from the_table t1
  join ( 
    select id, max(t2.thing) as max_thing
    from the_table t2
    group by id
  ) t on t.id = t1.id and t.max_thing = t1.thing

или (то же, что и предыдущее с другой нотацией)

with max_stuff as (
  select id, max(t2.thing) as max_thing
  from the_table t2
  group by id
) 
select t1.id, 
       t1.year,
       t1.thing
from the_table t1
  join max_stuff t2 
    on t1.id = t2.id 
   and t1.thing = t2.max_thing

Ответ 2

Самый короткий (и, возможно, самый быстрый) запрос будет с DISTINCT ON, расширением PostgreSQL для SQL стандартное предложение DISTINCT:

SELECT DISTINCT ON (1)
       id, count, year
FROM   tbl
ORDER  BY 1, 2 DESC, 3;

Числа являются порядковыми позициями в списке SELECT, вы также можете указать:

SELECT DISTINCT ON (id)
       id, count, year
FROM   tbl
ORDER  BY id, count DESC, year;

Результат упорядочивается id, что может быть или не приветствоваться. Это лучше, чем "undefined" в любом случае.

Он также разрывает связи (когда несколько лет имеют одинаковый максимальный счет) в четко определенном ключе: выберите самый ранний год. Если вам все равно, отпустите year из ORDER BY. Или выберите последний год с помощью year DESC.

Больше объяснений, ссылок, эталонных тестов, возможно, более быстрых решений в этом тесно связанном ответе:

Выберите первую строку в каждой группе GROUP BY?

Помимо этого: в запросе реальной жизни вы не будете использовать некоторые имена столбцов. id является ненасытным анти-шаблоном для имени столбца, count является зарезервированным словом в стандартном SQL и count() агрегатная функция в Postgres.