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.

Figure 1: Schéma des tables contenant les données requises afin de rattacher une protéine à son organisme, s’il existe.
Figure 1: Schéma des tables contenant les données requises afin de rattacher une protéine à son organisme, s’il existe.

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:

Figure 2: Résultat de la jointure sans condition.
Figure 2: Résultat de la jointure sans condition.

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):

Figure 3: Résultat d’une jointure conditionnée. Seules les lignes surlignées seront sélectionnées
Figure 3: Résultat d’une jointure conditionnée. Seules les lignes surlignées seront sélectionnées

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.

Figure 4: Relation cyclique dans la table organismes.
Figure 4: Relation cyclique dans la table organismes.

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.