clause HAVING et MAX avec un GROUP BY

**EnRadeSurEclipse** · 03/02/2010, 20h18

Rien a faire ça rentre pas, je ne comprends pas comment on utilise cette fonction MAX
Je veux sur un GROUP BY récupérer la ligne de valeur maximale sur ce sous groupe, donc j'écris la requête suivante:

Code :

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

1
2
3
4
SELECT count(*), `NomAeroDep`,`NomAeroArr`, `HDepChemin`, `HArrChemin`,`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
group by c.`CodeAeroDep`, c.`CodeAeroArr`
having max(time_to_sec(c.`HDepChemin`))

voilà je veux le départ le plus tardif pour chaque couple origine-destination.
Et badaboum, rien n'y fait il me renvoie une ligne unique pour chaque O-D mais jamais celle de plus haute valeur de HdepChemin

Je m'arrache les cheveux.
Help please

**Antoun** · 03/02/2010, 21h20

d'après ce que tu dis, pas besoin de HAVING... tu veux le MAX, tu demandes le MAX :

Code :

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

1
2
3
SELECT `NomAeroDep`,`NomAeroArr`, MAX(`HDepChemin`), MAX(`HArrChemin`),`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
GROUP BY c.`CodeAeroDep`, c.`CodeAeroArr`

EDIT : Kris, t'es grillé !

**Maljuna Kris** · 03/02/2010, 21h23

Saluton,
Je ne suis vraiment pas sûr de la pertinence de la projection en fonction du GROUP BY.
Par contre HAVING max(time_to_sec(c.`HDepChemin`)) sera toujours vrai et n'a donc pas de sens.
Il faut comparer max(time_to_sec(c.`HDepChemin`)) avec une colonne dans la clause HAVING.

**EnRadeSurEclipse** · 03/02/2010, 23h00

Envoyé par Antoun

d'après ce que tu dis, pas besoin de HAVING... tu veux le MAX, tu demandes le MAX :

Code :

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

1
2
3
SELECT `NomAeroDep`,`NomAeroArr`, MAX(`HDepChemin`), MAX(`HArrChemin`),`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
GROUP BY c.`CodeAeroDep`, c.`CodeAeroArr`

ben oui Antoun, mais là je vais sortir les valeurs maximales sur deux colonnes, ce n'est pas ce que je veux, je veux la ligne contenant la valeur maximale sur une colonne, c'est à dire le trajet complet DONT l'heure de départ est maximale. Avec ce que tu propose je ais avoir l'heure max de départ et aussi une heure max d'arrivée qui pourra correspondre à une autre ligne.

**EnRadeSurEclipse** · 03/02/2010, 23h07

Envoyé par Maljuna Kris

Saluton,
Je ne suis vraiment pas sûr de la pertinence de la projection en fonction du GROUP BY.
Par contre HAVING max(time_to_sec(c.`HDepChemin`)) sera toujours vrai et n'a donc pas de sens.
Il faut comparer max(time_to_sec(c.`HDepChemin`)) avec une colonne dans la clause HAVING.

c'est pas totalement limpide pour moi ta réponse: j'ai tenté un :

Code :

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

c.`HDepChemin`=max(time_to_sec(c.`HDepChemin`))

et là il me sort que dalle. alors qu'avant il me sortait une liste, débile certes, mais une liste quand même.
Je vois pas du tout pourquoi tu dis que le HAVING max(time_to_sec(c.`HDepChemin`)) sera tuojours vrai.
Ma compréhension est que le HAVING agit à l'intérieur du GROUP BY, enfin c'est ce que j'aimerais qu'il fit.

**Antoun** · 03/02/2010, 23h12

Envoyé par EnRadeSurEclipse

Avec ce que tu propose je ais avoir l'heure max de départ et aussi une heure max d'arrivée qui pourra correspondre à une autre ligne.

Yep... tu ne parlais que de l'heure de départ et je ne savais pas quoi faire de l'autre... Maintenant, je crois comprendre que ce que tu veux ce sont les détails du dernier vol à partir. Cela demande une sous-requête, par exemple :

Code :

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

1
2
3
4
5
 
SELECT `NomAeroDep`,`NomAeroArr`, `HDepChemin`,   
  `HArrChemin`,`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
WHERE HDepChemin = (SELECT MAX(HDepChemin) FROM Chemins_HDepMin_1610 c2)

**EnRadeSurEclipse** · 03/02/2010, 23h33

Envoyé par Antoun

Yep... tu ne parlais que de l'heure de départ et je ne savais pas quoi faire de l'autre... Maintenant, je crois comprendre que ce que tu veux ce sont les détails du dernier vol à partir. Cela demande une sous-requête, par exemple :

Code :

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

1
2
3
4
5
 
SELECT `NomAeroDep`,`NomAeroArr`, `HDepChemin`,   
  `HArrChemin`,`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
WHERE HDepChemin = (SELECT MAX(HDepChemin) FROM Chemins_HDepMin_1610 c2)

drôle comme il est ardu d'expliquer simplement ma requête.
Ce que tu propose ici c'est de trouver le départ le plus tardif de toute ma table; moi je veux le départ le plus tardif de chaque couple origine-destination. Ce que je veux c'est supprimer des "presque" doublons d'OD, et à chaque fois n'en retenir qu'un, le plus tardif.

**Antoun** · 03/02/2010, 23h40

Envoyé par EnRadeSurEclipse

drôle comme il est ardu d'expliquer simplement ma requête.

oui. Une fois que tu as exprimé précisément ton besoin, tu as déjà 3/4 de ta requête.

Envoyé par EnRadeSurEclipse

Ce que tu propose ici c'est de trouver le départ le plus tardif de toute ma table; moi je veux le départ le plus tardif de chaque couple origine-destination. Ce que je veux c'est supprimer des "presque" doublons d'OD, et à chaque fois n'en retenir qu'un, le plus tardif.

OK :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT `NomAeroDep`,`NomAeroArr`, `HDepChemin`,   
  `HArrChemin`,`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
WHERE HDepChemin = (SELECT MAX(HDepChemin) 
  FROM Chemins_HDepMin_1610 c2
  WHERE c2.CodeAeroDep = c.CodeAeroDep AND c2.CodeAeroArr = c.CodeAeroArr
  )
 
-- ou encore :
 
SELECT c.NomAeroDep, c.NomAeroArr, c.HDepChemin,   
  c.HArrChemin,c.CodeAeroDep, c.CodeAeroArr
FROM Chemins_HDepMin_1610 c
  INNER JOIN (
    SELECT CodeAeroDep, CodeAeroArr, MAX(HDepChemin) AS DernierDep
    FROM Chemins_HDepMin_1610
    GROUP BY CodeAeroDep, CodeAeroArr
  ) c2 ON c.CodeAeroDep = c2.CodeAeroDep AND c.CodeAeroArr = c2.CodeAeroArr AND c.HDepChemin = c2.DernierDep

**EnRadeSurEclipse** · 03/02/2010, 23h53

Envoyé par Antoun

oui. Une fois que tu as exprimé précisément ton besoin, tu as déjà 3/4 de ta requête.

OK :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT `NomAeroDep`,`NomAeroArr`, `HDepChemin`,   
  `HArrChemin`,`CodeAeroDep`, `CodeAeroArr` 
FROM `Chemins_HDepMin_1610` c
WHERE HDepChemin = (SELECT MAX(HDepChemin) 
  FROM Chemins_HDepMin_1610 c2
  WHERE c2.CodeAeroDep = c.CodeAeroDep AND c2.CodeAeroArr = c.CodeAeroArr
  )
 
-- ou encore :
 
SELECT c.NomAeroDep, c.NomAeroArr, c.HDepChemin,   
  c.HArrChemin,c.CodeAeroDep, c.CodeAeroArr
FROM Chemins_HDepMin_1610 c
  INNER JOIN (
    SELECT CodeAeroDep, CodeAeroArr, MAX(HDepChemin) AS DernierDep
    FROM Chemins_HDepMin_1610 
    GROUP BY CodeAeroDep, CodeAeroArr
  ) c2 ON c.CodeAeroDep = c2.CodeAeroDep AND c.CodeAeroArr = c2.CodeAeroArr AND c.HDepChemin = c2.DernierDep

wah !!
ta première solution marche et je la comprends, absolument logique. Elle fait bien ce que je veux: bravo à toi. Donc tu confirme que je me suis totalement fourvoyé avec mon HAVING?
Jai plus de mal à comprendre la seconde. En particulier pourquoi mets tu un group by alors que je me figure qu'un where suffit.

En tout cas d'ores et déjà un ENORME merci !

**Antoun** · 04/02/2010, 00h17

Bon, un peu de théorie. La première utilise une sous-requête scalaire (une seule ligne, une seule colonne) et corrélée (la corrélation étant la condition WHERE reliant la sous-requête avec la requête principale).

La seconde utilise une "table virtuelle", càd une sous-requête dans le FROM. La table virtuelle calcule le départ le plus tardif pour chaque O/D ; ensuite, il n'y a plus qu'à jointurer avec ta table de base sur origine, destination et heure de départ.

MySQL a des performances consternantes avec les sous-requêtes corrélées. Donc sur un petit volume, la première version est préférable parce qu'elle est effectivement plus lisible et que la différence de perf ne sera pas trop sensible. Par contre, sur de gros volume, les problèmes de perf vont apparaître et tu devras passer à la seconde version.

Un peu de lecture complémentaire : http://sqlpro.developpez.com/cours/sqlaz/sousrequetes/

**EnRadeSurEclipse** · 04/02/2010, 00h45

j'aime la théorie présentée comme cela.
Limpide.
Merci de tes explications de qualité.
Et bonne nuit!!

**EnRadeSurEclipse** · 04/02/2010, 23h41

Bon j'ai bien réussi à faire tourner la requête sur une première table d'environ 10 000 enregistrements. Et puis voilà que je mance sur une table de l'ordre de 100 000 et patatras, ça ne veut plus marcher.
Comme cela revient à un

Code :

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

group by origine, destination

je sais compter combien je dois trouver d'enregistrements dans ma nouvelle table.

Bon la seconde requête avec le INNER JOIN récupère un message d'erreur, ou un retour sur la page de garde de phpmyadmin, signe de problème.

Donc ma question devient: comment optimiser cette requête pour qu'elle puisse passer? Je n'ai aucun index sur ces tables. Et si je créais une colonne suppléemntatin concaténant les id origine et id destination, ca ferait pas gagner un peu? ou encore je fais une première passe sur la moitié de la table en claquent un where bien senti (pour l'instant je vois pas trop comment découper proprement ma table en 2 ou 3)

Bon aide souhaitée, quoi.
Merci d'avance.

**Antoun** · 05/02/2010, 00h19

Peux-tu mettre ici le code de création de ta table ? Je regarderai ce WE ce qu'il est possible de faire.

**EnRadeSurEclipse** · 05/02/2010, 01h16

bon ben c'est du brut de décoffrage. Les deux tables ont des structures absolument identiques. J'ajoute qu'nesuite je réalise un simple JOIN et je peux savoir combien de temps est disponible à destination pour les allers-retours, c'est la finalité de ma démarche.
Merci infiniment de tes lumières.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
-- phpMyAdmin SQL Dump
-- version 3.1.5
-- http://www.phpmyadmin.net
--
-- Serveur: espon121.sql.free.fr
-- Généré le : Ven 05 Février 2010 à 01:36
-- Version du serveur: 5.0.83
-- Version de PHP: 5.2.9
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
 
--
-- Base de données: `espon121`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `Chemins_HArrMax_1220`
--
 
CREATE TABLE IF NOT EXISTS `Chemins_HArrMax_1220` (
  `NomAeroDep` text NOT NULL,
  `NomAeroArr` text NOT NULL,
  `HDepChemin` text NOT NULL,
  `HArrChemin` text NOT NULL,
  `HRetourMinimum` tinytext NOT NULL,
  `HDepRetour` tinytext NOT NULL,
  `HArrRetour` tinytext NOT NULL,
  `NbEscales` text NOT NULL,
  `SequenceModale` text NOT NULL,
  `Duree` text NOT NULL,
  `TpsCorres` text NOT NULL,
  `DistKm` text NOT NULL,
  `CodeVolInitial` text NOT NULL,
  `CodeVolFinal` text NOT NULL,
  `CodeAeroDep` text NOT NULL,
  `CodeAeroArr` text NOT NULL,
  `ModeChemin` text NOT NULL,
  `NomEscale1` text NOT NULL,
  `TempsCorres1` text NOT NULL,
  `CodeEscale1` text NOT NULL,
  `NomEscale2` text NOT NULL,
  `TempsCorres2` text NOT NULL,
  `CodeEscale2` text NOT NULL,
  `NomEscale3` text NOT NULL,
  `TempsCorres3` text NOT NULL,
  `CodeEscale3` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

**CinePhil** · 05/02/2010, 12h14

Je n'ai pas suivi la discussion en détail mais le problème vient sans doute en partie de ceci :

Envoyé par EnRadeSurEclipse

Je n'ai aucun index sur ces tables.

D'autre part, les colonnes de type Text ne peuvent, je crois, pas être indexées.
Il faudrait en changer le type de manière optimale.
Par exemple, une durée est un nombre, pas un texte !
Les codes sont peut-être des chaînes de caractères de longueur fixe, donc de type CHAR.

**EnRadeSurEclipse** · 05/02/2010, 23h55

en effet tout est texte dans ma table.
tu veux dire que simplement déclarer mes codes identifiants d'origine et destination qui sont des numériques en integer va faire avancer le schmilblick?
Ce qui me gêne dans ma table c'est que l'index doit être unique (non?) et que l'id de l'origine ou de la destination pris seul est tout sauf unique, donc il faudrait créer un concat des deux id et des heures pour retrouver un identifiant unique et encore sans garantie.
Donc pas très chaud pour me compliquer la vie en ce sens.
Mais si tu me dis que c'est la voie, pourquoi pas.
enfin en tous cas, je ne m'y lancerai qu'après une semaine de congés qui commence dans minutes environ.
Merci pour toutes les réponses jusque là.

**CinePhil** · 06/02/2010, 22h55

Envoyé par EnRadeSurEclipse

en effet tout est texte dans ma table.
tu veux dire que simplement déclarer mes codes identifiants d'origine et destination qui sont des numériques en integer va faire avancer le schmilblick?

Oui car tu vas pouvoir indexer ces colonnes et les temps de recherche vont être considérablement augmentés.

Ce qui me gêne dans ma table c'est que l'index doit être unique (non?)

Ne pas confondre index et identifiant !
L'identifiant doit être unique mais un index est là pour créer un ordre artificiel permettant au SGBD de trouver plus rapidement les données.

Je crois comprendre que ta table contient des informations sur les lignes aériennes.
Les colonnes 'NomAeroDep' et 'NomAeroArr' devraient être indexées car il est fort probable que la recherche se fera sur le nom de l'aéroport assez souvent.
Encore mieux : Il faudrait transformer cette table en base de données relationnelle en externalisant les noms des aéroports et en ne mettant dans cette table que les identifiant des aéroports, lesquels seront de type entier pour accélérer encore la recherche.
Il y a dans ta table redondance entre les colonnes 'NomAeroDep' et 'CodeAeroDep' d'une part, 'NomAeroArr' et 'CodeAeroArr' d'autre part. Ces couples de colonnes représentent la même information : l'aéroport de départ et l'aéroport d'arrivée. Donc deux colonnes suffisent : l'identifiant des aéroports de départ et d'arrivée.

Toutes les colonnes commençant par H semblent être des heures. Il faut utiliser le type TIME.
Les colonnes 'Duree' et 'TpsCorres' sont probablement des nombres décimaux.
'DistKm' est probablement un entier.
Les escales devraient figurer dans une autre table.

Bref, il faut MODELISER les données et construire une BDD normalisée !
Voir le cours sur la modélisation Merise si tu ne connais pas.

et que l'id de l'origine ou de la destination pris seul est tout sauf unique, donc il faudrait créer un concat des deux id et des heures pour retrouver un identifiant unique et encore sans garantie.

Que contient cette table ?
Des lignes aériennes ?
Il faudra donc un identifiant de ligne. Un simple entier suffit. Il sera auto-incrémenté.
Mais il faut d'abord déterminer l'ensemble minimum de colonnes qui distingue chaque ligne de la table.
A première vue, ça pourrait être {CodeAeroDep, CodeAeroArr, HDepChemin} par exemple.
Tu y associes les colonnes qui dépendent directement de cet ensemble et cela forme ton entité LigneAerienne.

Ensuite les règles de gestion...
1) Une ligne aérienne part d'un seul aéroport et arrive à un seul aéroport ; un aéroport peut être le point de départ ou le point d'arrivée de plusieurs lignes aériennes.

2) Une ligne aérienne faire escale par plusieurs aéroports et un aéroport peut être escale pour plusieurs lignes aériennes.

Le MCD :
LigneAerienne -1,1----Partir--------0,n- Aeroport
|---------------------1,1----Arriver----0,n------------|
|---------------------0,n----FaireEscale----0,n----|

Bon courage !

**EnRadeSurEclipse** · 19/02/2010, 00h10

bon, ça progresse, mais j'y suis pas encore

j'ai passé mes identifiants d'aéroports en integer, et comme par magie ils se sont gentiment mis en index, et puis j'ai réussi le gros insert into qui ne passait pas sur la plus petite des tables, comportant 40 000 lignes environ
Par contre je galère avec la plus grosse, environ 80 000 lignes ou je retombe sur une requête qui ne s'exécute pas jusqu'au bout et je sais, par un contrôle vie un group y, qu'il me manque des enregistrements.

a tout hasard je joins la dernière version de ma requête adaptée de ce qui m'a été proposé précédemment dans ce topic par Antoun, que son nom soit loué:

Code :

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

1
2
3
4
5
6
7
8
9
insert into Chemins_HDepMin_1510_sd
(`NomAeroDep`, `NomAeroArr`, `HDepChemin`, `HArrChemin`, `HRetourMinimum`, `HDepRetour`, `HArrRetour`, `NbEscales`, `SequenceModale`, `Duree`, `TpsCorres`, `DistKm`, `CodeVolInitial`, `CodeVolFinal`, `CodeAeroDep`, `CodeAeroArr`, `ModeChemin`, `NomEscale1`, `TempsCorres1`, `CodeEscale1`, `NomEscale2`, `TempsCorres2`, `CodeEscale2`, `NomEscale3`, `TempsCorres3`, `CodeEscale3`)
SELECT c.`NomAeroDep`, c.`NomAeroArr`, c.`HDepChemin`, c.`HArrChemin`, c.`HRetourMinimum`, c.`HDepRetour`, c.`HArrRetour`, c.`NbEscales`, c.`SequenceModale`, c.`Duree`, c.`TpsCorres`, c.`DistKm`, c.`CodeVolInitial`, c.`CodeVolFinal`, c.`CodeAeroDep`, c.`CodeAeroArr`, c.`ModeChemin`, c.`NomEscale1`, c.`TempsCorres1`, c.`CodeEscale1`, c.`NomEscale2`, c.`TempsCorres2`, c.`CodeEscale2`, c.`NomEscale3`, c.`TempsCorres3`, c.`CodeEscale3`
FROM Chemins_HDepMin_1510 c
  INNER JOIN (
    SELECT CodeAeroDep, CodeAeroArr, MAX(HDepChemin) AS DernierDep
    FROM Chemins_HDepMin_1510 
    GROUP BY CodeAeroDep, CodeAeroArr
  ) c2 ON c.CodeAeroDep = c2.CodeAeroDep AND c.CodeAeroArr = c2.CodeAeroArr AND c.HDepChemin = c2.DernierDep

OK Cinephil pour toutes les explications sur la structuration, mes données viennent comme cela, certes il y a de la redondance, mais je ne vois pas bien le bénéfice à tirer de ce travail de "modélisation" car a chaque fois je galère dans les update ensuite.
J'ajoute que mes données sont simplement des vols, avec éventuellement des étapes, mais basiquement on peut les assimiler à des vols, et mon group by des enregistrements sans doublons se fait juste sur les deux codes id d'aéroports de départ et d'arrivée

**EnRadeSurEclipse** · 19/02/2010, 00h36

euh, j'aime pas trop jouer avec le feu:
je modifie en live mes formats et j'ai modifié mes formats d'heure en TIME comme tu me l'as indiqué; eh ben à ce moment là mon insert into ne passe plus, il me signale un erreur de syntaxe au moment ou je lui demande mon HArrChemin

Et pire, je me remets en format texte et j'ai encore cette erreur de syntaxe

Code :

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

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into Chemins_HDepMin_1510_sd (`NomAeroDep`, `NomAeroArr`, `HDepChemin`, ' at line 4

alors que tout à l'heure ça passait!!!

alors là ça devient carrément galére !!!

**EnRadeSurEclipse** · 21/02/2010, 23h45

toujours rien:
j'ai enlevé tout le superflu de la table il ne me reste plus que 5 champs:
`HDepChemin`, `HArrChemin`, `CodeAeroDep`, `CodeAeroArr`, `id`
ces champs sont en numérique, id est un identifiant incrémenté.

Et là encore je ne parviens toujours pas à obtenir un bon résultat à la requête suivante:

Code :

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

1
2
3
4
5
6
7
8
9
INSERT INTO Chemins_HDepMin_1510_simplifié_sd
(`HDepChemin`, `HArrChemin`, `CodeAeroDep`, `CodeAeroArr`, `nbDoublons`, `id`)
SELECT c.`HDepChemin`, c.`HArrChemin`, c.`CodeAeroDep`, c.`CodeAeroArr`, c.`nbDoublons`, c.`id`
FROM Chemins_HDepMin_1510_simplifié c
  INNER JOIN (
    SELECT CodeAeroDep, CodeAeroArr, MAX(HDepChemin) AS DernierDep
    FROM Chemins_HDepMin_1510_simplifié 
    GROUP BY CodeAeroDep, CodeAeroArr
  ) c2 ON c.CodeAeroDep = c2.CodeAeroDep AND c.CodeAeroArr = c2.CodeAeroArr AND c.HDepChemin = c2.DernierDep

j'ai toujours droit à un "serveur shutdown in progress" et la procédure n'est pas exécutée complètement.

Vraiment là je ne vois plus quoi faire

clause HAVING et MAX avec un GROUP BY

Requêtes MySQL

Discussions similaires

Partager

Partager