Group By et relation d'unicité [Résolu]

[ZeGuizmo]

Bonsoir à tous,

Je bute sur un problème qui me parait pourtant très simple mais rien à faire
Mes recherches se sont montrées totalement infructueuses.

Prenons un exemple fictif :

Une table composée de quatre colonnes :
id (cle primaire)
date (timestamp)
user_id (fk)
value (double)

Une relation d'unicité est définie entre les clés date et user_id, car à une meme date un utilisateur ne peut pas enregistrer deux valeurs.

Mon but est de récupérer la dernière valeur enregistrée pour chaque utilisateur. J'entends par dernière valeur enregistrée, celle qui maximise la colonne date, et non pas le plus gros id (rien n'est enregistré dans l'ordre chronologique).

Pour cela je pensais faire une requete du style :

Code :

1
2
3
SELECT user_id, max(date), value
FROM TABLE
GROUP BY user_id;

Le problème c'est que postgresql ne semble pas tenir compte de l'unicité déclarée entre user_id et date, donc il rale sur le fait que value n'est pas dans le GROUP BY.

La requete suivante passe à merveille :

Code :

1
2
3
SELECT user_id, max(date)
FROM TABLE
GROUP BY user_id;

Mais elle ne renvoie pas la valeur qui correspond à user_id et max(date).

Je ne comprends pas pourquoi, alors que le couple user_id et max(date) forme une clé unique qui me retourne à coup sur la valeur, je suis incapable en une seule requête de récupérer la valeur en question. J'ai tendance à accuser pgsql de ne pas comprendre que l'unicité entre le champ date et le champ user_id lui permet de récupérer le champ value sans ambiguité, mais je procède surement de la mauvaise façon.

J'ai tenté des trucs a base de subqueries, sans grand résultats.

Avez vous une idée du problème ? Et si c'est connu (ce que je n'ai pas reussi a mettre en évidence avec mes recherches), comment contourner ?

Je sèche complètement

Merci et bonne fin de journée,

[estofilo]

C'est en effet très simple

Code :

1
2
3
4
5
SELECT user_id,date,value FROM TABLE
WHERE (user_id,date) IN (
 SELECT user_id, max(date) 
 FROM TABLE 
 GROUP BY user_id);

[ZeGuizmo]

Merci pour cette réponse.

Je ne connaissais pas le IN couplé à la subquerie, c'est très malin ! J'avais essayé avec des where, mais on ne peut retourner qu'une valeur à la fois.

Par contre je trouve cela très peu élégant, en fait surtout parceque mon cas réel est beeeaaaaauuucoup plus alambiqué que le simple cas que je présente, il y a des joins un peu partout, les conditions de jointures sont compliquées, etc. J'ai un peu le sentiment de taper deux fois la requete pour un résultat qui me parait tellement facile a récupérer ... si le moteur de bdd se rendait compte que dans le group by il n'a pas besoin de spécifier le champ value puisque la clause d'unicité lui interdit d'en avoir plusieurs !

Dans tous les cas, merci, maintenant au moins je récupère le résultat sans passer par le niveau applicatif. Si par contre quelqu'un sait pourquoi la clause d'unicité n'est pas explorée lors d'un group by qu'il n'hésite pas à me le dire, ou à m'expliquer ou je me trompe dans mon raisonnement.

Bonne soirée,

Guiz

[estofilo]

La contrainte d'unicité fait que le SGBD sait qu'à un couple (id,date) correspond une seule ligne de la table, mais pas que la même propriété est vraie pour un couple résultant d'un calcul: (id,max(date)).

[skuatamad]

si le moteur de bdd se rendait compte que dans le group by il n'a pas besoin de spécifier le champ value puisque la clause d'unicité lui interdit d'en avoir plusieurs !

C'est purement sémentique, toutes colonnes présentent dans le SELECT et non agrégées doient apparaître dans la clause GROUP BY.
MySql est laxiste sur ce point (mais pas seulement pour des colonnes uniques) et peut être d'autre SGBD "à 2 balles", mais là dessus PostgreSQL est très normatif et renvoie une erreur indépendamment de la clause unique.

J'ai un peu le sentiment de taper deux fois la requete pour un résultat qui me parait tellement facile a récupérer

Sans connaître la requête en question (ni la version de PG) difficile d'apporter une réponse précise mais regarde peut être du côté des CTE, pour factoriser certaines sous requêtes.

Si tu as une version récente de PostgreSQL, les fonctions analytiques (ou de fenêtrage) permette d'éviter les utilisations massives de GROUP BY + sous requête

Par exemple ta requête de base pourraît s'écrire :

Code :

1
2
3
SELECT user_id,value, 
       max(date) over (partiton BY user_id) AS max_dt
  FROM TABLE

[ZeGuizmo]

Envoyé par estofilo

La contrainte d'unicité fait que le SGBD sait qu'à un couple (id,date) correspond une seule ligne de la table, mais pas que la même propriété est vraie pour un couple résultant d'un calcul: (id,max(date)).

MAX ne modifie en rien la valeur, le rang retourné est bien un rang existant et le SGBD est en mesure de le savoir (pour cette fonction, AVG aurait été un cas différent !)

Envoyé par skuatamad

C'est purement sémentique, toutes colonnes présentent dans le SELECT et non agrégées doient apparaître dans la clause GROUP BY.
MySql est laxiste sur ce point (mais pas seulement pour des colonnes uniques) et peut être d'autre SGBD "à 2 balles", mais là dessus PostgreSQL est très normatif et renvoie une erreur indépendamment de la clause unique.

Je trouve cela fort dommage ! C'est un peu ce que j'avais compris des discussions que j'ai glanées ça et là mais je n'ai jamais eu de confirmation si claire. Un jour viendra ou les SGBD seront encore plus intelligents

Merci pour les fonctions analytiques, je ne connaissais pas du tout ce concept, je vais pouvoir approfondir ce point et surement réaliser ce que je veux.

Bonne soirée et merci encore a vous deux !

[edit :] et pour les CTE j'ai déjà regardé (sans savoir que ca s'appelait des CTE ) et je n'ai pas trouvé mon bonheur.

[SQLpro]

Envoyé par skuatamad

Par exemple ta requête de base pourraît s'écrire :

Code :

pas bon, il faudrait écrire par exemple :

Code :

1
2
3
4
5
6
7
WITH 
T AS (SELECT user_id, value, date 
             max(date) over (partiton BY user_id) AS max_date
      FROM TABLE)
SELECT user_id, value
FROM   T 
WHERE  date = max_date

A +

[ZeGuizmo]

Excellent merci !

[skuatamad]

C'est vrai qu'en filtrant ça correspond mieux merci d'avoir corrigé