PostgreSQL MAX et GROUP BY
j'ai une table avec id , year et count .
je veux obtenir le MAX(count) pour chaque id et garder le year quand il se produit, donc je fais cette requête:
SELECT id, year, MAX(count)
FROM table
GROUP BY id;
malheureusement, il me donne une erreur:
ERREUR: la colonne "de la table.année " doit apparaître dans le groupe par clause OU être utilisé dans une fonction agrégée
Donc j'essaie:
SELECT id, year, MAX(count)
FROM table
GROUP BY id, year;
mais alors, il ne fait pas MAX(count) , il montre juste la table telle qu'elle est. Je suppose que parce que lorsque groupant par year et id , il obtient le max pour le id de cette année spécifique.
Alors, comment puis-je écrire cette requête? Je veux avoir le id s MAX(count) et l'année où cela arrive.
2 réponses
select *
from (
select id,
year,
thing,
max(thing) over (partition by id) as max_thing
from the_table
) t
where thing = max_thing
ou:
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);
ou
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
ou (le même que le précédent avec une autre notation)
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
la requête la plus courte (et peut-être la plus rapide) serait avec DISTINCT ON , une extension PostgreSQL de la norme SQL DISTINCT clause:
SELECT DISTINCT ON (1)
id, count, year
FROM tbl
ORDER BY 1, 2 DESC, 3;
les chiffres renvoient aux positions ordinales de la liste SELECT . Vous pouvez épeler les noms de colonne pour plus de clarté:
SELECT DISTINCT ON (id)
id, count, year
FROM tbl
ORDER BY id, count DESC, year;
le résultat est commandé par id , qui peut ou non être le bienvenu. C'est mieux que "undefined" dans tous les cas.
il rompt également les liens (lorsque plusieurs années partagent le même nombre maximal) d'une manière bien définie: choisir l'année la plus ancienne. Si vous vous en fichez, supprimez year du ORDER BY . Ou choisissez la dernière année avec year DESC .
plus d'explications, des liens, un benchmark et peut-être des solutions plus rapides dans cette réponse étroitement liée:
mis à part: dans une requête de la vraie vie, vous n'utiliseriez pas certains des noms de colonne. id est un anti-motif non descriptif pour un nom de colonne, count est un mot réservé dans le SQL standard et une fonction agrégée dans Postgres.