requète avec min()

Version imprimable

Voir 40 message(s) de cette discussion en une page

20/10/2010, 10h02
fey

oui forcément comme cela marche j'y avais pas pensé. :oops:
merci ;)

mais cela m'intéresse quand même de voir comment on fait avec la fonction min() et la fameuse jointure de la table sur elle meme ^^

Citation:

mais cela m'intéresse quand même de voir comment on fait avec la fonction min() et la fameuse jointure de la table sur elle meme ^^

Peut être quelque chose comme :
Code:

1 2 3 4 5 6 7 8 9 SELECT min(cp2.quantite) AS quant, cp1.moisnum, cp1.annees FROM cop_releves_p1 AS cp1 INNER JOIN cop_releves_p2 AS cp2 ON cp1.id_element = cp2.id_element GROUP BY id_machine HAVING cp1.id_machine LIKE "2090repro" AND cp2.id_nom_element = 99 ORDER BY cp1.annees ASC, cp1.moisnum ASC
Sans aucune certitude. ;)

20/10/2010, 10h17
RunCodePhp

Petite parenthèse quand même.
Il serait intéressant de changer leur nom, car sans avoir leur structure sous les yeux cop_releves_p1 et cop_releves_p2 ça sous entend quelles auraient la même structure, et non pas 2 tables liées, complémentaires.

C'est pas pratique car pas du tout intuitif, vois tu.
Enfin, je dis ça, chacun fait comme il veut ;)
20/10/2010, 10h18
fey

Code:

#1054 - Champ 'cp2.id_nom_element' inconnu dans having clause

alors qu'apriori c'est le bon nom

C'est le regroupement qui ne serait pas cohérent peut être.

En le mettant dans le WHERE comme condition générale peut être :
Code:

1 2 3 4 5 6 7 8 SELECT min(cp2.quantite) AS quant, cp1.moisnum, cp1.annees FROM cop_releves_p1 AS cp1 INNER JOIN cop_releves_p2 AS cp2 ON cp1.id_element = cp2.id_element WHERE cp2.id_nom_element = 99 GROUP BY id_machine HAVING cp1.id_machine LIKE "2090repro" ORDER BY cp1.annees ASC, cp1.moisnum ASC

Bonjour,

et comme ca ?

Code:

1
2
3
4
5
6
7
8
9
 
SELECT min(quantite) AS quant, annees, moisnum  FROM cop_releves_p2 AS cp2
INNER JOIN (
      SELECT annees, moisnum, id_element, id_machine FROM cop_releves_p1
      ORDER BY 1, 2 LIMIT 1
) as cp1
ON cp1.id_element = cp2.id_element 
WHERE cp1.id_machine = "2090repro" 
AND cp2.id_nom_element = 99

20/10/2010, 10h37
fey

ouais mais la je récupère le mauvais résultat pour l'année et le mois :cry:

Il faut que le regroupement s'applique sur : cp1.id_machine = "2090repro", donc que cette condition soit dans le GROUP BY, car la fonction MIN() s'appliquera selon le regroupement.
En tout cas c'est la base des regroupements tel COUNT(), MAX(), etc ...

Il faut peut être rajouter les autres champs dans le GROUP BY pour que ça s'applique sur l'ensemble des données qu'on souhaite obtenir :
Code:

1 2 3 4 5 6 7 8 SELECT min(cp2.quantite) AS quant, cp1.moisnum, cp1.annees FROM cop_releves_p1 AS cp1 INNER JOIN cop_releves_p2 AS cp2 ON cp1.id_element = cp2.id_element WHERE cp2.id_nom_element = 99 GROUP BY cp1.moisnum, cp1.annees, cp1.id_machine HAVING cp1.id_machine LIKE "2090repro" ORDER BY cp1.annees ASC, cp1.moisnum ASC
Mais là je dis peut être une bêtise.
Code:

1 2 3 4 5 6 7 SELECT min(cp2.quantite) AS quant, cp1.moisnum, cp1.annees FROM cop_releves_p1 AS cp1 INNER JOIN cop_releves_p2 AS cp2 ON cp1.id_element = cp2.id_element WHERE cp2.id_nom_element = 99 GROUP BY id_machine HAVING cp1.id_machine LIKE "2090repro" ORDER BY cp1.annees ASC, cp1.moisnum ASC
Mais ceci, ça donne quoi ?

Citation:

Envoyé par fey

ouais mais la je récupère le mauvais résultat pour l'année et le mois :cry:

Moi ca me semble bon

min(quantite) = 120459 pour id_nom_element=99 et id_machine="2090repro"

Pour cet enregistrement, tu vois 2090repro20081 en id_element quand tu match dans cp1, tu tombes sur le moisnum=1 et annees=2008 ce que ma requete ramène...

PS: différence entre la colonne mois et moisnum...?

EDIT; juste une petite amélioration ;)
Code:

1 2 3 4 5 6 7 8 9 SELECT min(quantite) AS quant, annees, moisnum FROM cop_releves_p2 AS cp2 INNER JOIN ( SELECT annees, moisnum, id_element, id_machine FROM cop_releves_p1 WHERE id_machine = "2090repro" ORDER BY 1, 2 LIMIT 1 ) AS cp1 ON cp1.id_element = cp2.id_element WHERE cp2.id_nom_element = 99

20/10/2010, 11h02
fey

Madfrix

oui effectivement avec ta requête cela fonctionne nickel ;)

je ne savais pas qu'on pouvait faire comme cela direct après une jointure

merci à vous tous pour votre aide :ccool:
20/10/2010, 11h05
Madfrix

Il suffit de créer un sous ensemble où tu tries selon tes contraintes et de considérer cet ensemble comme une table. Ensuite, une jointure et le tour est joué ;)

Penses au tag :resolu:

@+
20/10/2010, 11h08
fey

juste une petite question subsidiaire

Code:

ORDER BY 1, 2

cela correspond à quoi le 1 et 2 ?
20/10/2010, 11h09
Madfrix

C'est juste un raccouci pour désigner annees, moisnum ;)

4 correspondrait à id_machine ici

tu peux mettre la valeur littérale si tu veux
20/10/2010, 13h10
fey

merci beaucoup

Il me semble avoir compris un peu mieux le MIN() et le GROUP BY, comment les regroupement sont effectués.

Apparemment, quand on fait un MIN(), le regroupement est effectué en 1er.
Cependant, le traitement du MIN() est indépendant des autres champs comme "annees" et "moisnum" ici, ce qui fait que les données ne correspondent pas (à part une pure coïncidence).

Concrètement, le MIN() sans GROUP BY va faire qu'on obtienne qu'1 seule ligne, mais le MIN() correspondra bien à la valeur la plus petite, à un ID unique, mais pour les 2 autres données, la Bdd va récupérer la 1ère valeur parmi le regroupement, donc pas forcément à celle de la quantité la plus petite (du min), à part une coïncidence.

Il ne faudrait pas faire de regroupement normalement, en tout cas, pas sur le champ id_machine.
Il faudrait plutôt que la requête puisse récupérer l'ensemble des machines donc des IDs, dont les id_machine valant "2090repro".
Ce sera le ORDER BY + le LIMIT 1 qui permettra d'avoir les 3 valeurs concordante.

La requête serait du genre :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT cp2.id, cp1.id_machine, MIN(cp2.quantite) AS quant, cp1.moisnum, cp1.annees FROM cop_releves_p1 AS cp1 INNER JOIN cop_releves_p2 AS cp2 ON cp1.id_element = cp2.id_element WHERE cp2.id_nom_element = 99 GROUP BY cp2.id HAVING cp1.id_machine = "2090repro" ORDER BY cp2.quantite ASC, cp1.annees ASC, cp1.moisnum ASC LIMIT 1 // Ou : ORDER BY quant (tout seul devrait être tout aussi bien).
Il faut rajouter les champ "id" et "id_machine" dans le SELECT pour y faire référence dans le GROUP BY, sinon on obtient le même type d'erreur qu'auparavant (unknow ... blabla ...).

Je ne sais si ceci fonctionne, mais de mon coté j'ai effectué quelques essais sur 2 tables qui théoriquement seraient similaires à la situation, et ça à l'air concluant.

20/10/2010, 15h39
Madfrix
Testé sur ma base de test ca a l'air de marcher en effet ;)

Citation:

Envoyé par RunCodePhp

Apparemment, quand on fait un MIN(), le regroupement est effectué en 1er.
Cependant, le traitement du MIN() est indépendant des autres champs comme "annees" et "moisnum" ici, ce qui fait que les données ne correspondent pas (à part une pure coïncidence).

En effet, c'est toujours le même principe avec les GROUP BY. Imagine que tu as 3 champs heure et minutes et secondes si tu fais un
Code:

1 2 SELECT * FROM maTable GROUP BY heure
Tu perdras de l'information sur les champs minutes et secondes

un :
Code:

1 2 SELECT * FROM maTable GROUP BY heure, minutes
te fera perdre de l'information uniquement sur les secondes

et un :
Code:

1 2 SELECT * FROM maTable GROUP BY heure, minutes, secondes
ne te fera rien perdre mais tu ne groupes rien (cas idiot)

Donc il faut quand tu perds des lignes, que la seule ligne correspondant au secondes ramenée pour une heure et une minute corresponde à celle que tu veuilles donc c'est effectivement rarement le cas...

C'est pour ca qu'il faut donc effectivement introduire un ORDER BY afin de s'arranger pour que la seule ligne ramenée corresponde à la seconde désirée dans mon cas de figure
20/10/2010, 16h02
RunCodePhp

Citation:

Envoyé par Madfrix

Testé sur ma base de test ca a l'air de marcher en effet ;)

Merci, c'est bon à savoir :ccool:

C'est tout à fait ça que j'ai remarqué au niveau du comportant des regroupement.
C'est un peu déroutant, mais on y arrive.

En tout cas, sur une requête simple sur 2 tables comme ici, qui plus est, on connait le résultat à l'avance, ça va.
Mais quand les choses sont plus complexes, quasi imprévisibles, hummm, chaud les marrons quand même. :mrgreen:

Ce fût un bon petit exercice. :D

Je viens de faire un test pour me persuader qu'on avait raison :D

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE TABLE `test` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `heure` int(11) NOT NULL,
 `minutes` int(11) NOT NULL,
 `secondes` int(11) NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
 
INSERT INTO `test` (`id`, `heure`, `minutes`, `secondes`) VALUES
(1, 10, 20, 1),
(2, 10, 20, 2),
(3, 10, 20, 3);

j'ai testé les requetes suivantes :

Code:

1
2
3
4
5
 
SELECT * FROM test
GROUP BY heure, minutes
 
// retour : 1 10 20 1

Code:

1
2
3
4
5
6
 
SELECT * FROM test
GROUP BY heure, minutes
ORDER BY secondes DESC
 
// retour : 1 10 20 1

Donc le group by ramène la premiere ligne trouvée pour les champs non groupés même s'il y a un order car l'order se fait sur le groupement déjà effectué.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT a.id, a.heure, a.minutes, a.secondes
FROM test a
INNER JOIN (
                SELECT *
                FROM test
                ORDER BY secondes DESC
                LIMIT 1
               )b 
ON a.id = b.id
 
// retour : 3 10 20 3

;)

Citation:

Donc le group by ramène la premiere ligne trouvée pour les champs non groupés même s'il y a un order car l'order se fait sur le groupement déjà effectué.

Figure toi que j'avais remarqué la même chose, et j'avais beau changé de champ dans le ORDER BY comme un fou furieux, rien à faire. :aie:

La question que je me pose toujours, c'est sur quoi se base MySQL pour trier les lignes regroupées ?
Apparemment ça serait l'ordre d'enregistrement, donc indépendamment des valeurs, et même qu'il y est une clé primaire, index ou pas (enfin, visiblement).

Ca n'a pas l'air comme ça, mais ça peu déboucher sur des résultats incohérents si on y fait pas gaffe.

J'ai repris tes donnée, en les mettant un peu dans le désordre, comme ceci :
Code:

1 2 3 4 5 6 7 8 9 10 INSERT INTO `test` (`id`, `heure`, `minutes`, `secondes`) VALUES (2, 10, 20, 2), (1, 10, 20, 1), (3, 10, 20, 3); // Avec : SELECT * FROM test GROUP BY heure, minutes ORDER BY secondes DESC
Donc la même requête retourne toujours la 1ère ligne enregistrée, soit :
2 10 20 2

Je me dis malgrès tout, que si on utilise un GROUP BY, c'est que le but serait d'exploiter une fonction de regroupent (comme min, max, count, etc ...), sinon, il vaudrait mieux l'éviter et agir sur des critères annexes.

Pour les fonctions de regroupements théoriquement, je dis bien théoriquement, l'ordre n'a pas d'importance, car au bout ça sera qu'une seule valeurs parmi le regroupement :
La plus petite, la plus grande, le nombre total, la somme, etc ...

Enfin, c'est ce que j'en déduis :P

20/10/2010, 17h13
Madfrix
Citation:

Envoyé par RunCodePhp

La question que je me pose toujours, c'est sur quoi se base MySQL pour trier les lignes regroupées ?

En fait dans ce cas ci, vu que le GROUP BY ramène une seule ligne, le tri ne change pas.

J'ai réinséré les tuples
4 11 20 2
5 11 20 3
6 11 20 1

et avec la requete :
Code:

1 2 3 4 5 SELECT id, heure, minutes, secondes FROM test GROUP BY heure, minutes ORDER BY secondes DESC
j'obtiens :
4 11 20 2
1 10 20 1

tout ca pour dire que le order ordonne bien sur le group by et que le group by retourne la premiere ligne rencontrée pour le groupement effectué

Voir 40 message(s) de cette discussion en une page