[Casse-tête] MySQL, agrégation, maximum

16 mars 2012, 04:14

Amateurs de casse-tête, bonjour

Voici une colle qu'on m'a posé récemment. Le sujet, en apparence simple, m'a intéressé (mes recherches ne m'ont pas permis de trancher définitivement, en tout cas pas avec le résultat que j'imaginais), alors je soumets la question à votre sagacité

(à défaut de me servir dans l'immédiat, ça pourra toujours être utile à d'autres qui tomberaient plus tard sur le sujet).

On utilise MySQL 5, et voici une table de test et son jeu de données :

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | NO   |     | NULL    |                |
| dept  | varchar(255) | NO   |     | NULL    |                |
| age   | int(11)      | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

+----+-----------+------+-----+
| id | name      | dept | age |
+----+-----------+------+-----+
|  1 | Alexandre | DIR  |  50 |
|  2 | David     | IT   |  30 |
|  3 | Pierre    | IT   |  24 |
|  4 | Maxime    | IT   |  40 |
|  5 | Julie     | DIR  |  29 |
|  6 | Arnaud    | SAL  |  25 |
+----+-----------+------+-----+

On a donc un identifiant, des prénoms, des départements et des âges. Voici le code pour créer tout cela :

CREATE TABLE IF NOT EXISTS `latable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `dept` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `latable` (`id`, `name`, `dept`, `age`) VALUES
(1, 'Alexandre', 'DIR', 50),
(2, 'David', 'IT', 30),
(3, 'Pierre', 'IT', 24),
(4, 'Maxime', 'IT', 40),
(5, 'Julie', 'DIR', 29),
(6, 'Arnaud', 'SAL', 25);

On cherche à savoir, pour l'ensemble des départements, qui est la personne la plus âgée de chaque département, avec une requête SQL.
Il y a dans ces données trois départements différents, donc la requête doit ramener trois résultats (un pour chaque, le ou la plus âgé(e)) :

+----+-----------+------+-----+
| id | name      | dept | age |
+----+-----------+------+-----+
|  4 | Maxime    | IT   |  40 |
|  1 | Alexandre | DIR  |  50 |
|  6 | Arnaud    | SAL  |  25 |
+----+-----------+------+-----+

Simple non ?

Eh bien, à vous de juger.

l'intérêt du problème est à la fois de faire une liste des solutions possibles, mais aussi de critiquer ces solutions pour choisir "LA solution parfaite", si elle existe, ou sinon de passer en revue les inconvénients de chacune. On s'intéresse principalement à la performance et la généricité de la requête à produire.

17 mars 2012, 15:58

Yop,

en simple je ferais ça :

select id, name, dept, max(age) as age from latable group by dept order by age;

Code : Tout sélectionner

+----+-----------+------+------+
| id | name      | dept | age  |
+----+-----------+------+------+
|  6 | Arnaud    | SAL  |   25 |
|  2 | David     | IT   |   40 |
|  1 | Alexandre | DIR  |   50 |
+----+-----------+------+------+
3 rows in set (0.00 sec)

Edit : hum en fait, j'ai regard que les ages, mais les données a coté sont pas bonne

(par rapport a ton résultat)
va falloir que je regarde un peu mieux, réponse trop rapide :/

@+

17 mars 2012, 16:15

Voila qui est mieux

j'avoue que je sais pas pourquoi le max dans la requete (principale) ne prend pas les bonnes infos :s

select * from latable where age in(select max(age) from latable group by dept);

Code : Tout sélectionner

+----+-----------+------+-----+
| id | name      | dept | age |
+----+-----------+------+-----+
|  1 | Alexandre | DIR  |  50 |
|  4 | Maxime    | IT   |  40 |
|  6 | Arnaud    | SAL  |  25 |
+----+-----------+------+-----+
3 rows in set (0.00 sec)

@+

18 mars 2012, 11:34

C'est pas mal du tout tout ça moogli, merci

Tu as bien vu le piège : curieusement (moi non plus, je ne m'attendais pas à tomber dans une impasse sur cette question si on veut s'éviter l'imbrication), la requête imbriquée que tu proposes semble être la meilleure solution. Elle est générique, et moyennant l'ajout d'une clause ORDER BY on pourrait même gérer les cas d'ex-aequo (sachant qu'il n'y en a pas dans les données fournies).

Il resterait toutefois à se poser la question des performances de cette requête imbriquée sur une table plus consistante...

De mon côté, pressé de trouver une solution à cette énigme en quelques minutes (et déterminé que j'étais à m'épargner la requête imbriquée) j'étais parti sur une union :

(SELECT * FROM `latable` WHERE dept='IT' ORDER BY age DESC LIMIT 1)
    -> UNION
    -> (SELECT * FROM `latable` WHERE dept='DIR' ORDER BY age DESC LIMIT 1)
    -> UNION
    -> (SELECT * FROM `latable` WHERE dept='SAL' ORDER BY age DESC LIMIT 1);

Cela évite d'une part la requête imbriquée, et s'éclate facilement en plusieurs petites sous-requêtes (il suffit de fragmenter les UNION en requêtes indépendantes). En revanche cela impose de passer auparavant par un

SELECT distinct(dept) FROM latable;

Pour obtenir la liste des départements et construire les requêtes. Donc finalement même si la requête imbriquée est contournée, on se retrouve avec un traitement dans un langage tiers (ou une procédure) à effectuer. Quid des performances par rapport à la requête imbriquée ?

Enfin, y aurait-il une troisième piste exploitant une (ou plusieurs) jointure(s) intra-table ? Cette solution serait-elle compétitive en performances, lisibilité et généricité ?

18 mars 2012, 21:02

J'avoue que j'utilise rarement l'union, c'est pas le truc auquel je pense (je sais pas pourquoi mais pour moi s'est pas naturelle

).

Et comme tu l'indique la liste d'union oblige à modifier la requête si les départements évoluent, donc pas top.
En auto jointure je vois pas trop :/

Après je sèche :/

@+

20 mars 2012, 09:47

Commentaire linguistique:
On cherche à savoir, pour l'ensemble des départements, qui est la personne la plus âgée de chaque département, avec une seule requête SQL.

20 mars 2012, 11:37

J'ai un petit doute sur la solution proposée.

Rajoutez Paul (25 ans) au département DIR pour voir si la requête fonctionne toujours.

20 mars 2012, 12:23

Eventuellement on peut créer une vue : select dept , max(age) from latable as age group by dept
Ensuite, il faut faite une jointure sur la vue et la table avec l'age et le département.

Pour info, les colonnes sélectionnées qui ne participent pas à la clause group by doivent être utilisé avec une fonction de type max, min... Ca marche quand même avec mysql, mais c'est un effet de bord que tout les sgbd ne tolèrent pas.

20 mars 2012, 15:08

SELECT * FROM latable lt1 WHERE age =(SELECT max(age) FROM latable lt2 where lt2.dept=lt1.dept)

20 mars 2012, 19:57

Bonjour,

Très bon sujet.
Je vais voir dès demain comment DB2 (Express & DB2 Zos) se comporte

Bonne soirée.

21 mars 2012, 01:59

Commentaire linguistique:
On cherche à savoir, pour l'ensemble des départements, qui est la personne la plus âgée de chaque département, avec une seule requête SQL.

Tout à fait, mais... C'est ce que j'avais écrit en premier, avant de m'auto-corriger. Le pourquoi est en filigrane dans la question et les réponses

: Il semble, sauf avis contraire, qu'il n'y ait pas de solution avec une unique requête, au sens strict ! (en excluant de fait les imbrications et unions).

J'avoue que j'utilise rarement l'union, c'est pas le truc auquel je pense (je sais pas pourquoi mais pour moi s'est pas naturelle ).

Pareil pour moi avec les requêtes imbriquées, moogli

(surtout dans le contexte un peu spécial où on m'a présenté ce "challenge").

Et comme tu l'indique la liste d'union oblige à modifier la requête si les départements évoluent, donc pas top.

Tout à fait... Je me demande si une procédure stockée permettrait de résoudre cet inconvénient (j'avoue ne pas avoir creusé davantage). Et de retomber +/- sur nos pattes par rapport au prérequis : une requête (ou commande).

SELECT * FROM latable lt1 WHERE age =(SELECT max(age) FROM latable lt2 where lt2.dept=lt1.dept)

Bravo, Myrina, c'est bien vu, tu as géré le problème des ex-aequo =D>

Eventuellement on peut créer une vue : select dept , max(age) from latable as age group by dept
Ensuite, il faut faite une jointure sur la vue et la table avec l'age et le département.

Pour info, les colonnes sélectionnées qui ne participent pas à la clause group by doivent être utilisé avec une fonction de type max, min... Ca marche quand même avec mysql, mais c'est un effet de bord que tout les sgbd ne tolèrent pas.

Je crois bien avoir entendu cela de la bouche de mon professeur d'analyse il y a fort longtemps... Mais je ne m'en souvenais plus. Merci pour le rappel Mazarini

21 mars 2012, 10:58

La remarque sur les max/min obligatoire ou la solution avec la vue ?

21 mars 2012, 21:17

La remarque sur les max/min obligatoire ou la solution avec la vue ?

La première remarque

Pour la vue, c'est, finalement, à peu près la même chose que la requête imbriquée, mais c'est bien aussi.

Merci à tous pour vos réponses.

[Casse-tête] MySQL, agrégation, maximum

Qui est en ligne