Requêtes complexes
Jointures et requêtes complexes
Extraction de données de plusieurs tables
Jusqu’à présent, nous n’avons envisagé que des requêtes destinées à l’interrogation de tables uniques. Cependant, le processus de modélisation et de création de la structure de notre base de données nous a conduit à fractionner nos données en plusieurs tables connectées les unes aux autres par l’intermédiaire des identifiants et des clés étrangères.
Si nous reprenons l’exemple de notre base de données de gènes et que nous souhaitons connaître le nom des espèces pour lesquelles nous disposons d’une séquence protéique, il est nécessaire de suivre (traduire) le lien qui unit les tables proteines, genes et organismes (fig. 1). Le principe général pour réaliser cette opération est de refusionner les tables concernées par la requête puis d’extraire les lignes qui respectent les contraintes de liaison et les contraintes liées à la sélection que nous voulons opérer.
Jointure
Produit relationnel et jointure conditionnée
La fusion des tables entre elles est appelée jointure. Cette opération ne nécessite pas l’utilisation de commandes autres que celles que nous avons déjà vues précédemment. En effet, il suffit de spécifier dans la partie from de la requête select le nom des tables dont il faut réaliser la jointure:
sqlite> select * from proteines, genes, organismes
Cette opération (jointure sans condition) est appelée produit relationnel.
Elle a pour résultat de réaliser toutes les combinaisons possibles entre les lignes des tables impliquées dans la jointure, tel qu’illustré dans la fig. 2:
Le résultat est en soi peu intéressant puisque qu’il conduit à un mélange des informations et une connexion erronée entre la plupart d’entre elles. Il convient donc d’appliquer la contrainte de liaison existant entre les différentes tables via l’option where:
sqlite> select * from proteines, genes, organismes
...> where gene_accession=accession and taxon=taxon
Problème de requêtes ambigües ?
Le problème que nous rencontrons alors est que des attributs issus de plusieurs tables peuvent porter les mêmes noms, ce qui rend notre requête ambiguë. Il faut donc que nos attributs puissent être associés à leur table respective. Cela est obtenu simplement en fournissant un alias aux noms des tables au moment de la requête via la commande as:
sqlite> select * from proteines as P, genes as G,
...> organismes as O where gene_accession=G.accession
...> and G.taxon=O.taxon
Notez qu’il est possible d’omettre le as:
sqlite> select * from proteines P, genes G, organismes O
...> where gene_accession=G.accession and G.taxon=O.taxon
Le résultat est illustré à la figure suivante (fig. 3):
Le résultat de la jointure conditionnée peut être obtenu en utilisant la déclaration INNER JOIN ... ON
:
sqlite> select count(*) from genes G inner join reference R on R.accession=G.accession;
count(*)
--------
943
Lignes célibataires
Le résultat d’une jointure conditionnée conduit fréquemment à exclure des lignes d’une ou de plusieurs tables de la requête. C’est en particulier le cas lorsque les clés étrangères font référence à des attributs facultatifs. Dans notre exemple, c’est le cas de la liaison qui existe entre la table genes et la table reference (ou publication). En effet, il n’existe pas de référence bibliographique pour chacune des accessions de la table genes, en conséquence la requête :
sqlite> select * from genes G, reference R where
...> G.accession=R.accession;
conduira à éliminer de l’affichage les lignes correspondant aux accessions qui ne sont pas référencées par des publications. Pour le vérifier, calculez le nombre de lignes de la table genes qui sont exclues par la requête précédente.
sqlite> select count(*) from genes G;
count(*)
--------
1978
sqlite> select count(*) from reference R;
count(*)
--------
943
sqlite> select count(*) from genes G, reference R where G.accession = R.accession;
count(*)
--------
943
1035 genes ne figurent donc pas dans le résultat, ce qui est néanmoins le comportement attendu. En effet, la dernière requête impose la sélection des numéros d’accession de gènes qui ont une correspondance dans la table reference. Cependant, dans certains cas, il peut être utile d’afficher l’ensemble des lignes, y compris les lignes célibataires. Pour ce faire, nous utiliserons une variante de la jointure qui s’appelle jointure externe et qui se traduit par l’option left outer join … on:
Solution: Jointure externe via left outer join … on
sqlite> select count(*) from genes G left join reference R on R.accession=G.accession;
count(*)
--------
1978
Le résultat comprend bien 1978 lignes, soit tous les gènes, alors que seuls 943 d’entre eux sont référencés dans une publication, ce qui est plus clairement révélé par la requête suivante:
sqlite> select G.accession, R.pubmedid from genes G left join reference R on R.accession=G.accession limit 10;
accession pubmedid
----------------- --------
AB022220 BA000014 10819329
AB032531
AB037371
AB060809 11731449
AB242141 17028206
AB277198
AB303361 19179351
AC003000
AC007178
AC007293
Le left précise que ce sont les lignes de la table de gauche qui ne sont pas reliées à la table de droite qui sont ajoutées au résultat. Notez que le langage SQL inclut les 2 autres commandes right outer join et full outer join mais seules left outer join et inner join sont supportées par SQLite.
Il est bien entendu possible d’ajouter d’autres jointures afin d’étendre la requête à d’autres tables de la base de données. Cependant, il faudra veillez à ne pas ajouter de conditions qui viendraient à nouveau exclure les lignes célibataires qui étaient récupérées grâce au left outer join. Ainsi, par exemple, la requête suivante restreint forcément le résultat aux lignes pour lesquelles R.pubmedid
n’est pas NULL
ce qui a pour effet de supprimer les lignes célibataires que le left outer join avait récupéré:
sqlite> select count(*) from organismes O, genes G left join reference R on R.accession=G.accession, publications P where P.pubmedid = R.pubmedid and G.taxon = O.taxon;
count(*)
--------
943
Pour éviter cela, il faut donc conserver toutes les lignes produites par le premier left outer join en le plaçant à gauche de la relation d’un nouveau left outer join qui s’étendra à la table publications:
sqlite> select count(*) from organismes O, genes G left join reference R on R.accession=G.accession left join publications P on P.pubmedid = R.pubmedid where G.taxon = O.taxon;
count(*)
--------
1978
La requête est plus lisible lorsqu’elle est construite entraversant le schéma de gauche à droite en utilisant lorsque c’est nécessaire left outer join et dans les autres cas inner join:
sqlite> select count(*) from genes G left join reference R on R.accession=G.accession left join publications P on P.pubmedid = R.pubmedid inner join organismes O on G.taxon = O.taxon;
count(*)
--------
1978
sqlite> select G.accession, P.pubmedid, O.taxon from genes G left join reference R on R.accession=G.accession left join publications P on P.pubmedid = R.pubmedid inner join organismes O on G.taxon = O.taxon limit 10;
accession pubmedid taxon
----------------- -------- -----
AB022220 BA000014 10819329 3702
AB032531 4097
AB037371 39947
AB060809 11731449 3702
AB242141 17028206 3067
AB277198 35883
AB303361 19179351 3055
AC003000 3702
AC007178 3702
AC007293 3702
Requêtes sur des données cycliques
Nous avons vu précédemment qu’il est possible de définir des associations cycliques (d’une entité (table) vers elle-même). Il en existe un exemple dans notre base de données modèle, sous la forme de la table organismes et de la relation qui unit un taxon à son parent.
Cette table permet donc, par exemple, de répondre à la question suivante: donner les informations concernant les taxons pour lesquels des parents taxonomiques sont présents dans la base de données (fig. 4). Afin d’établir la requête qui permet de répondre à cette question, il suffit de réaliser une jointure comme si la table contenant les données parent et la table contenant les données enfant étaient 2 tables distinctes:
sqlite> select * from organismes E, organismes P;
Voici un extrait du résultat de cette requête:
E P
_________________________ ________________________________
...
39947|4530|Oryza ... rice|4529|4527|Oryza rufipogon|common
39947|4530|Oryza ... rice|4530|4527|Oryza sativa|red rice
39947|4530|Oryza ... rice|4533|4527|Oryza brachyantha|
39947|4530|Oryza ... rice|4535|4527|Oryza officinalis|
39947|4530|Oryza ... rice|4536|4527|Oryza nivara|
39947|4530|Oryza ... rice|4537|4527|Oryza punctata|
39947|4530|Oryza ... rice|4538|4527|Oryza glaberrima|
...
Il faut ensuite sélectionner les lignes pour lesquelles l’attribut taxon de la table parent est égal à l’attribut parent de la table enfant. Ce qui se traduit par:
sqlite> select * from organismes E, organismes P
...> where P.taxon=E.parent;
3068|3067|Volvox ... agariensis| |3067|3066|Volvox carteri|
112509|4513|Hordeum ... vulgare|barley|4513|4512|Hordeum vulgare|barley
39946|4530|Oryza ... roup|Indian rice|4530|4527|Oryza sativa|red rice
39947|4530|Oryza ... up|Japonica rice|4530|4527|Oryza sativa|red rice
Si on veut simplement connaître le nom des taxons parents qui comptent des taxons enfants dans la base de données, on écrira:
sqlite> select distinct P.taxon, P.nom from organismes E,
...> organismes P where P.taxon=E.parent;
Extraction de données groupées
Rappel sur les fonctions statistiques
Nous avons abordé lors de la séance précédente l’existence de fonctions agrégatives telles que count, avg, … . Nous avons vu qu’elles étaient particulièrement utiles pour extraire des données statistiques mais qu’elles devaient être manipulées avec précaution afin de ne pas mal interpréter leur résultat.
sqlite> select count(*), P.taxon, P.nom from organismes P,
...> organismes E where P.taxon=E.parent;
4|4530|Oryza sativa
Ainsi, le résultat obtenu par la requête précédente contient à la fois une donnée calculée (résultant d’une agrégation), 4 et les données correspondant à la dernière ligne de la sélection imposée par la requête select. Le lien entre ces 2 parties de la réponse est évidemment un non-sens.
Agrégation via group by
Les fonctions agrégatives sont donc très souvent associées à une pré-agrégation des résultats qui peut être obtenue par la clause group by. Par exemple, la requête suivante permettra de compter le nombre de taxons enfants pour chaque taxon parent:
sqlite> select count(*), P.taxon, P.nom from organismes P,
...> organismes E where P.taxon=E.parent group by P.taxon;
1|3067|Volvox carteri
1|4513|Hordeum vulgare
2|4530|Oryza sativa
Appliquer des conditions sur le résultat des fonctions statistiques via having
De même qu’il est possible de sélectionner des lignes particulières du résultat d’une jointure via la clause where, il est également possible de filtrer les lignes obtenues par agrégation sur base du résultat de la fonction statistique utilisée. On utilise pour cela la clause having en complément de group by.
sqlite> select count(*), P.taxon, P.nom from organismes P,
...> organismes E where P.taxon=E.parent group by P.taxon
...> having count(*) > 1;
ou, si on fait usage d’un alias :
sqlite> select count(*) as N, P.taxon, P.nom from organismes P,
...> organismes E where P.taxon=E.parent group by P.taxon
...> having N > 1;
2|4530|Oryza sativa
Critères de groupement complexes
Tout comme dans le cas de la clause where, il est évidemment possible d’inclure une expression de calcul quelconque.
sqlite> select count(*) as N, P.taxon, P.nom from organismes P,
...> organismes E where P.taxon=E.parent
...> group by substr(P.taxon, 1, 2);
1|3067|Volvox carteri
3|4530|Oryza sativa
Pour bien en comprendre le résultat, il est utile d’afficher le résultat de la fonction de calcul:
sqlite> select substr(P.taxon, 1, 2) as CAT, count(*) as N,
...> P.taxon, P.nom from organismes P, organismes E
...> where P.taxon=E.parent group by CAT;
Exercices récapitulatifs
- Extraire des informations concernant les publications relatives à des numéros d’accession de protéines
- Compter, pour chaque publication, le nombre de protéines qu’elle référence.
- N’afficher que les publications qui référencent au moins 50 protéines
- Extraire des informations concernant les publications relatives à des taxons
- Compter, pour chaque taxon, le nombre de publications qui le concerne
- Evaluer le nombre de publications relatives à un même genre
- Calculer la longueur moyenne des séquences présentes dans les tables proteines et genes
- Regroupe ces résultats par genre.
- Filtrer ces groupes pour n’afficher que ceux dont la moyenne de la longueur des séquences est comprise entre 1000 et 2000 bp.