Discussion :

[defcon_suny]

Bonjour,

Je vous expose mon problème. Voici un schéma simple d'une base de données. Elle comporte 3 tables.

adresse
id_adresse INTEGER PK NN UNSIGNED AI
rue VARCHAR(30)
numero VARCHAR(5)
code INTEGER UNSIGNED
localite VARCHAR(20)

personne
id_personne INTEGER PK NN UNSIGNED AI
nom VARCHAR(20)
prenom VARCHAR(20)

personne_has_adresse
fk_id_personne INTEGER PK NN UNSIGNED
fk_id_adresse INTEGER PK NN UNSIGNED
date_creation DATETIME
datet_fin DATETIME

En sachant qu'une personne peut avoir eu plusieurs adresses et qu'à une adresse on peut avoir plusieurs habitants.

J'ai du mal à créer une requête qui me permettrait de faire un DISTINCT sur les adresses et d'obtenir en même temps le nom d'une personne (par exemple le premier trouvé) demeurant à cette adresse.

Actuellement, voici ma requête :

Code :

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

1
2
3
4
5
 
SELECT t1.nom, t1.prenom, t2.rue, t2.numero, t2.code, t2.localite
FROM `personne` AS t1 
LEFT JOIN adresse AS t2 ON t2.id_adresse = t3.fk_id_adresse
LEFT JOIN personne_has_adresse AS t3 ON t3.fk_id_personne = t1.id_personne

Déjà merci de vous attarder sur mon problème.

[Antoun]

Code :

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

1
2
3
4
5
6
SELECT t1.nom, t1.prenom, t2.rue, t2.numero, t2.code, t2.localite
FROM `personne` AS t1 
LEFT JOIN adresse AS t2 ON t2.id_adresse = t3.fk_id_adresse
LEFT JOIN personne_has_adresse AS t3 ON t3.fk_id_personne = t1.id_personne
GROUP BY t2.id_adresse

attention, j'utilise ici l' "optimisation du GROUP BY" propre à MySQL. Cette requête serait considérée comme erronée par les autres SGBDR.

[defcon_suny]

Je ne vois pas en quoi la clause GROUP BY peut solutionner mon problème...
Le résultat renvoyé par la requête est identique avec ou sans la clause GROUP BY. Autre chose?

[Orgied]

Ce que tu veux, c'est renvoyer par adresse le nom du premier habitant de chaque maison, c'est bien ca ?

[defcon_suny]

Oui! C'est ça

[Orgied]

Mais la réponse de Antoun me semble correcte alors ou il y a une subtilité que je visualise pas

Code :

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

1
2
3
4
5
6
7
 
SELECT p.nom, p.prenom, a.rue, a.numero, a.code, a.localite
FROM `personne` AS p 
LEFT JOIN `adresse` AS a ON (a.id_adresse = (SELECT pa.fk_id_adresse
                                             FROM `personne_has_adresse` AS pa    
                                             WHERE (pa.fk_id_personne = p.id_personne)))
GROUP BY a.id_adresse

et ca me donne le listing désiré.

[defcon_suny]

Et bien autant pour moi!

Effectivment, la clause GROUP BY de Antoun fonctionne bien. Je l'avais pourtant déjà testé sans avoir le résultat escompté.

Bref, Je peux encore travailler mon SQL!!! Merci à vous deux.

Une question toutefois... existe t'il une parade pouvant "standardisé" la requête ?

[Orgied]

Tu veux dire quoi par "standardisé" ?

[defcon_suny]

attention, j'utilise ici l' "optimisation du GROUP BY" propre à MySQL. Cette requête serait considérée comme erronée par les autres SGBDR.

Eviter ce genre de chose...

[Antoun]

oui, je te donne ça d'ici demain.

[Orgied]

bah mon code en exemple logiquement.

si je me rappelle bien, mysql a un système d'optimisation des JOIN mais toutes les sgbd n'en tolèrent pas deux consécutives.

après, il faut retirer les ` qui encadrent les noms des tables.

Code :

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

1
2
3
4
5
6
7
 
SELECT p.nom, p.prenom, a.rue, a.numero, a.code, a.localite
FROM personne AS p 
LEFT JOIN adresse AS a ON (a.id_adresse = (SELECT pa.fk_id_adresse
                                             FROM personne_has_adresse AS pa    
                                             WHERE (pa.fk_id_personne = p.id_personne)))
GROUP BY a.id_adresse

logiquement, ça doit passer pour la plupart des db. Sous MSSQL, c'est le cas.

[defcon_suny]

Très bien Orgied! Je sais que je peux te faire confiance

Je considère donc le post comme "résolu"

Merci à vous 2.

PS : Antoun, si t'as un complément d'information, n'hésite pas!

[Antoun]

Hello, et désolé de ne pas avoir été plus dispo aujourd'hui.

Il y a deux points qui me chiffonnent dans la requête d'Orgied :

• la jointure est personnes LEFT JOIN adresses ; le point n'a pas été précisé par defcon_suny, mais il me semble que soit on veut toutes les adresses, même sans habitant, auquel cas il faut utiliser adresses LEFT JOIN personnes (ou évidemment personnes RIGHT JOIN adresses) ; soit on ne veut que les adresses habitées, et il faut donc utiliser INNER JOIN
  
• il me semble que la jointure avec sous-requête est l'équivalent exact d'une jointure personnes LEFT JOIN personnne_has_adresse LEFT JOIN adresse, ce qui implique que la requête devrait faire une erreur dès qu'il y a deux personnes à la même adresse. Orgied, si je me trompe, merci de me dire ce que je rate.

Avant de vous proposer ma solution normalisée, je voudrais revenir sur la différence entre DISTINCT et GROUP BY. Cela nous amènera à la particularité de MySQL, et de là à la solution normalisée.

DISTINCT veut dire "afficher les combinaisons uniques des colonnes du SELECT". Donc à partir du moment où tu veux afficher à la fois les adresses et les personnes, le DISTINCT te donnera forcément tous les habitants de chaque adresse.

GROUP BY veut dire "ramener à une seule ligne les combinaisons uniques des colonnes précisées" ; la différence est donc que, contrairement au DISTINCT, tu peux ici choisir tes colonnes de regroupement. GROUP BY a.id_adresse permet donc de dire "une seule ligne pour chaque adresse".

Reste à voir que faire des autres colonnes affichées. En standard, chaque colonne du SELECT doit soit être dans le GROUP BY, soit être traitée par une fonction d'agrégation (COUNT, SUM, MAX, etc.) qui indique comment ramener les données de la colonne à une seule ligne.

MySQL est moins rigoureux. Si une colonne du SELECT n'est ni agrégée ni dans le GROUP BY, il se contente de prendre la première valeur trouvée, ce qui correspond bien à ta demande. Au passage, cela évite de faire un GROUP BY sur chacune des colonnes de l'adresse, donc c'est une bonne optimisation. D'un point de vue normatif par contre, c'est parfaitement hérétique car le résultat sur la table personne va dépendre de l'ordre des lignes dans la table, donc être plus ou moins aléatoire.

Pour normaliser, on est donc obligé de préciser la consigne "toutes les adresses, avec pour chacune un habitant quelconque". On peut par exemple dire "toutes les adresses, avec pour chacune l'habitant ayant l'ID la plus petite".

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT p.nom, p.prenom, a.rue, a.numero, a.code, a.localite
FROM adresse AS a 
  LEFT JOIN personne_has_adresse AS pha ON a.id_adresse = pha.fk_id_adresse
  LEFT JOIN personne AS p ON pha.fk_id_personne = p.id_personne
     AND p.id_personne = (
       SELECT MIN(fk_id_personne)  -- personne avec la plus petite ID...
       FROM personne_has_adresse  pha2
       WHERE pha2.fk_id_adresse = a.id_adresse  -- ... pour l'adresse en cours
     )
GROUP BY a.rue, a.numero, a.code, a.localite

Evidemment, on peut utiliser MAX au lieu de MIN, raisonner sur le nom de famille plutôt que sur l'ID, etc.

[defcon_suny]

Hello!

Grand merci Antoun pour tes explications. Elles me permettent d'y voir plus claire.

La table personne contient 101 000 enregistrements
La table adresse 79 000.

La requête d'Orgied me renvoie 77501 résultats en 4.6833 sec
La requête d'Antoun me renvoie 78467 résultats en 17.2265 sec (LEFT JOIN) et 0 résultat pour le INNER JOIN.

Bien évidemment, je ne devrais normalement jamais sortir toutes mes adresses sans définir une clause WHERE plus sélective. (nombre maximum d'adresse à sélectionner : +-6000)

Bref, à mon sens, la solution d'orgied est la plus performante. Toutefois je ne m'explique pas la différence dans le nombre de résultat...

Qu'en pensez-vous?

[Antoun]

Qu'en pensez-vous?

Que tu devrais nous donner le code de création de tes tables et d'insertion d'un jeu de données-test comprenant les différents cas de figure (adresse sans habitant, avec un habitant, avec deux habitants, habitant avec une adresse, deux adresses).

[defcon_suny]

Je peux vous fournir la structure des tables mais malheureusement pas les enregistrements (Soumis à la protection de la vie privée)

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
50
 
CREATE TABLE `aff_adresse` (
  `id_adresse` int(10) unsigned NOT NULL auto_increment,
  `fk_id_iso` int(10) unsigned NOT NULL,
  `rue` varchar(100) collate utf8_unicode_ci NOT NULL,
  `num` varchar(5) collate utf8_unicode_ci NOT NULL,
  `bte` varchar(4) collate utf8_unicode_ci default NULL,
  `cp` varchar(8) collate utf8_unicode_ci NOT NULL,
  `localite` varchar(50) collate utf8_unicode_ci NOT NULL,
  `commune` varchar(50) collate utf8_unicode_ci NOT NULL,
  `province` varchar(50) collate utf8_unicode_ci NOT NULL,
  `pays` varchar(3) collate utf8_unicode_ci NOT NULL,
  `date_creation` datetime NOT NULL,
  `date_update` datetime NOT NULL,
  `date_fin` datetime NOT NULL,
  PRIMARY KEY  (`id_adresse`),
  KEY `fk_id_iso` (`fk_id_iso`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=102490 ;
 
CREATE TABLE `aff_individu` (
  `id_individu` int(6) unsigned zerofill NOT NULL auto_increment,
  `fk_id_etude` int(10) unsigned NOT NULL,
  `fk_id_profession` int(10) unsigned NOT NULL,
  `fk_id_statut` int(10) unsigned NOT NULL,
  `nom` varchar(50) collate utf8_unicode_ci NOT NULL,
  `prenom` varchar(50) collate utf8_unicode_ci NOT NULL,
  `telephone` varchar(20) collate utf8_unicode_ci default NULL,
  `fax` varchar(20) collate utf8_unicode_ci default NULL,
  `gsm` varchar(20) collate utf8_unicode_ci default NULL,
  `mail` varchar(50) collate utf8_unicode_ci default NULL,
  `sexe` enum('M','F') collate utf8_unicode_ci NOT NULL,
  `date_naissance` date NOT NULL,
  `date_creation` datetime NOT NULL,
  `date_update` datetime NOT NULL,
  `historique` int(10) unsigned zerofill default NULL,
  PRIMARY KEY  (`id_individu`),
  KEY `fk_id_statut` (`fk_id_statut`),
  KEY `fk_id_profession` (`fk_id_profession`),
  KEY `fk_id_etude` (`fk_id_etude`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=101036 ;
 
 
CREATE TABLE `aff_individu_has_aff_adresse` (
  `fk_id_individu` int(6) unsigned zerofill NOT NULL,
  `fk_id_adresse` int(10) unsigned NOT NULL,
  `date_creation` datetime NOT NULL,
  `date_fin` datetime NOT NULL,
  PRIMARY KEY  (`fk_id_individu`,`fk_id_adresse`),
  KEY `fk_id_adresse` (`fk_id_adresse`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Je ne sais pas si ceci pourra vraiment t'aider...

[Orgied]

Oui

Ce qui m'ennuie le plus, ce sont les 1000 résultats de différence entre les deux requêtes proposées... La requête d'Antoun est plutot sympa pourtant et les explications sont claires. Je vais tenter de faire des tests aussi pour voir car ce problème attise ma curiosité.

[Antoun]

Je peux vous fournir la structure des tables mais malheureusement pas les enregistrements (Soumis à la protection de la vie privée)

Je ne sais pas si ceci pourra vraiment t'aider...

Si, ça permet de re-créer l'environnement pour tester ce qui se passe vraiment.

Pour les données, peux-tu nous préparer 5-6 requêtes d'insertion de personnes imaginaires ?

[defcon_suny]

Voilà, je vous ai préparé des insertions... j'ai récupéré les 500 premiers enregistrements de la table aff_adresse et j'ai crypté les caractères alphabétiques!

Le tout est téléchargeable ici

[Orgied]

Bon, je me suis fait un petit script d'insertion aléatoire et j'ai ajouté 18000 champs dans chaque table.

Ma requête peut effectivement générer l'erreur Subquery return more than one row.

En testant la requête d'Antoun, cela fait maintenant 6 minutes que j'attend un retour. Logiquement, entre 5000 et 7000 champs devraient avoir été retournés.

EDIT :

même en limitant les résultats, c'est la panade complète. Je passe en timeout quoi qu'il arrive.

Je continue mes tests