Discussion :

[kaboume]

Bonjour à tous,

J'ai besoin de votre aide pour construire et optimiser une requête SQL.

Je vous explique : j'ai une table qui liste des articles et une autre qui contient l'historique de changement de statut de mes articles.
J'ai besoin en une seule requête de récupérer la liste des articles avec pour chacun d'entre eux son dernier statut et la date de son dernier statut.

Je vous ai modelisé les tables pour faciliter la compréhension :



J'ai d'abord pensé à faire une requête du genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT art_nom, max(sta_date), sta_code
FROM article A INNER JOIN statut S ON A.art_id = S.art_id

mais le code statut retourné n'est pas forcément celui correspondant à la max(sta_date).

Est-ce que quelqu'un aurait une idée ?
Merci d'avance.

[fab256]

Moi je pensais a cette solution

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT art_nom, sta_date, sta_code
FROM 
(
SELECT art_id, max(sta_date) Last_Modif
FROM statut
GROUP BY art_id
) B
INNER JOIN statut  C ON (B.art_id=C.art_id and sta_date=Last_Modif)
INNER JOIN article D ON (B.art_id=D.art_id)

[kaboume]

Bonjour fab256,

Merci pour ta réponse.

Penses-tu que c'est la seule solution car j'ai un peu peur des performances... en sachant que pour un article, je peux avoir jusqu'à 10 statuts d'historique ?

[tfc3146]

Bonjour,

Essaye cela peut-être (non testé), je ne sais pas si les temps de réponses sont mieux :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
SELECT A.art_nom, S.sta_code, S.sta_date
FROM ARTICLE A
JOIN STATUT S ON A.art_id=S.art_id
WHERE sta_date = (SELECT MAX(sta_date)
                           FROM STATUT S2
                           WHERE S2.art_id=S.art_id)

[fab256]

Effectivement la requette de tfc3146 est beaucoup plus performante , j'ai fait un petit test sur l'exemple cité plus haut ca prends 0.0011 sec alors que la solution que j'ai proposé prends 0.0393 sec.

[kaboume]

Je vous remercie pour vos réponses.
Je vais donc opter pour la solution de tfc3146.

Si jamais une autre personne trouve encore mieux, je suis preneur.

Merci encore.

[kaboume]

Bonjour,

Je relance ce post car je rencontre un problème lorsque les deux derniers statuts de mon article ont la même date.
Dans ce cas là, la requête de tfc3146 me renvoie deux lignes de résultats pour un même article.

Est-ce qu'il y a moyen de modifier la requête pour ne récupérer, pour un article, que le statut ayant la max(date) et dont l'id du statut (sta_id) est le plus élevé ? cela me permettrait de n'avoir qu'un statut par article et donc qu'une ligne par article...

Merci à vous.

[punkoff]

bonjour,

lisez ceci : http://sqlpro.developpez.com/cours/sqlaz/ensembles/
En particulier la partie sur le group by

[kaboume]

Bonjour,

Merci de votre réponse.

J'ai effectivement pensé à utiliser un group by mais dans ce cas-là je ne vois vraiment pas dans quelle mesure il peut m'être utile.

[punkoff]

oui effectivement j’ai peut être un peu lu trop vite la demande.

Que lest votre sgbd ?

Ensuite est-ce que vouloir la ligne avec l'id max est pertinent (c'est une pk donc ..) ? Fonctionnellement ceci vous convient ?

edit: surtout au vu de votre exemple, pour un même article :
sta_id : 7, date : 12-02-2011
sta_id : 8, date : 11-02-2011

donc prendre le statut avec le sta_id le plus élevé correspond-il vraiment au résultat voulu ?

[tfc3146]

Et quelque chose du genre ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT A.art_nom, S.sta_code,S.sta_id
FROM ARTICLE A
JOIN STATUT S ON A.art_id=S.art_id
WHERE S.sta_date=(SELECT MAX(S2.sta_date)
                  FROM STATUT S2
                  WHERE S2.art_id=S.art_id)
GROUP BY A.art_nom,S.sta_code
HAVING S.sta_id = (SELECT MAX(S3.sta_id)
		   WHERE s3.art_id=S.art_id
		   AND s3.sta_date=s2.sta_date)

[fab256]

Une petite modification sur la requete que j'avais proposé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT art_nom, sta_date, sta_code
FROM 
(
SELECT art_id, max(sta_date) Last_Modif, max(sta_id) Last_Id
FROM statut
GROUP BY art_id
) B
INNER JOIN statut  C ON (B.art_id=C.art_id and sta_date=Last_Modif and sta_id=Last_Id)
INNER JOIN article D ON (B.art_id=D.art_id)

[punkoff]

Une petite modification sur la requete que j'avais proposé

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT art_nom, sta_date, sta_code
FROM 
(
SELECT art_id, max(sta_date) Last_Modif, max(sta_id) Last_Id
FROM statut
GROUP BY art_id
) B
INNER JOIN statut  C ON (B.art_id=C.art_id and sta_date=Last_Modif and sta_id=Last_Id)
INNER JOIN article D ON (B.art_id=D.art_id)

bonjour,

cette requête ne marchera pas.

le sta_id le plus élevé pour un article n'est pas forcément celui qui à la date la plus grande.

[kaboume]

Bonjour,

Merci pour vos réponses.

@punkoff : je suis sous MySQL

@punkoff : fonctionnellement, c'est effectivement ce que je souhaite à savoir dans le cas où un article possède ses deux derniers statuts à la même date, prendre le statut qui a été inséré en dernier donc avec le sta_id le plus grand.

@tfc3146 : merci, je vais partir sur ta solution. J'ai quand même peur de perfs étant donné que cela oblige à faire une sous-requête supplémentaire... mais si j'ai pas le choix....

Merci encore.

[punkoff]

la seule solution sans sous-requete serai d'avoir les fonctions de fenêtrage.

Mais c'est foutu pour MySql de ce côté là.


et est-ce que quelque-chose dans ce genre là fonctionne ? (éviterai une 2eme sous-requete )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
select art_nom, sta_code, sta_date
from article art
inner join statut sta1 on sta1.art_id = art.art_id
where sta1.sta_id = (select sta2.sta_id
				from statut sta2
				where sta2.art_id = sta1.art_id
				order by sta2.sta_date desc, sta2.sta_id desc
				TOP 1);

[kaboume]

@punkoff : je viens de tester et ta requête fonctionne bien.

Entre ces deux requêtes, laquelle est la plus performante ?

[punkoff]

je ne saurai répondre, il faudrai regarder les plans d'execution et les comparer (je n'ai pas mySql d'installé)

Niveau indexation pour la 2eme requête ça serait :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
create index idx_test on statut (art_id, sta_date desc, sta_id desc)

Et pareil comparez les plans avant et après le test de cet index.

[tfc3146]

ORDER BY c'est lourd. Peut-être que si ta table n'est pas très grosse, tu ne verras pas senseiblement la différence en tant de réponse, mais là l'ORDER BY ne s'impose pas => il vaut mieux utiliser MAX.

[punkoff]

ORDER BY c'est lourd. Peut-être que si ta table n'est pas très grosse, tu ne verras pas senseiblement la différence en tant de réponse, mais là l'ORDER BY ne s'impose pas => il vaut mieux utiliser MAX.

Même avec l'index déjà trié ..?

[Waldar]

Essayez aussi ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT A.art_nom, S1.sta_code, S1.sta_date
  FROM ARTICLE A
       INNER JOIN STATUT S1
         ON S1.art_id = A.art_id
       INNER JOIN ( select max(S3.STA_ID) as STA_ID_MAX
                      FROM STATUT S3
                           INNER JOIN ( select ART_ID, MAX(STA_DATE) as STA_DATE_MAX
                                          FROM STATUT
                                      group by art_id) S4
                            ON S4.art_id       = S3.art_id
                           AND S4.STA_DATE_MAX = S3.STA_DATE
                  group by ART_ID) S2
         ON S2.STA_ID_MAX = S1.STA_ID

C'est vraiment moche le SQL chez MySQL !