• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 7/11/19

Agrégez vos données grâce au GROUP BY

Log in or subscribe for free to enjoy all this course has to offer!

Voyons maintenant l'agrégation !

Vous vous souvenez qu'une agrégation est composée de deux éléments :

  1. un groupe d'attributs de partitionnement

  2. une ou plusieurs fonction(s) d'agrégation

Nous allons voir ici comment implémenter ces deux éléments en SQL.

L'agrégation

Bon, ce chapitre est un peu long, afin de bien appréhender les concepts.

Mais l'essentiel se résume en une ligne de code :

SELECT status, count(*) FROM entity GROUP BY status;

Nous avons placé l'attribut de partitionnement (ici  status) derrière le mot clé  GROUP BY, et la fonction d'agrégationCOUNT()  (en français "compter") dans le  SELECT  . Optionnellement, on peut aussi ajouter dans le  SELECT  les attributs de partitionnement.

Voilà, vous savez tout (ou presque) !

Réfléchissons...

Testons tout d’abord les fonctions d’agrégation, sans nous soucier des attributs de partitionnement.

Les fonctions d'agrégation

Petit rappel : une fonction d’agrégation prend en entrée plusieurs lignes, et retourne une unique ligne.

Par exemple, si nous écrivons :

SELECT max(incorporation_date) AS maxi FROM entity ;

alors la fonction max prend l'attribut incorporation_date de plusieurs lignes (toutes les lignes de la table entity), et renvoie un attribut (que nous appelons ici maxi) sur une seule ligne. Exécutez cette requête pour bien comprendre. ;)

Entrées et sorties…

La majorité des fonctions d’agrégation basiques en SQL (celles qui sont déjà prédéfinies par le langage) prennent en entrée un seul attribut et renvoient également un seul attribut. C’est le cas de la fonction max que nous venons de voir.

Il y a cependant une fonction qui échappe à cette règle : la fonction count. La fonction count renvoie un nombre entier : le nombre de lignes qu’elle a reçu en entrée. Pour compter le nombre de lignes, pas besoin de connaître la valeur d’un quelconque argument de ces lignes ! Alors on lui donne simplement le caractère * :

SELECT count(*) FROM entity ;

Une petite énigme...

Si entity contient 1000 lignes, combien de ligne renverrons chacune de ces 2 requêtes ?

SELECT incorporation_date FROM entity ;
SELECT min(id) FROM entity ;
SELECT incorporation_date, min(id) FROM entity ;

La première requête renverra 1000 lignes, et la seconde n'en renverra qu'une seule. Cependant, avec la 3e requête, votre SGBDR devrait vous retourner une erreur, car celui-ci ne saura pas s’il faut retourner 1 ou 1000 lignes.

Par contre, la requête suivante fonctionnera, et renverra 1 ligne :

SELECT max(incorporation_date), min(id) FROM entity ;

En effet, les 2 colonnes que nous demandons résultent toutes deux d’une fonction d’agrégation.

Peut-on en conclure que dans le SELECT, on ne peut faire appel à une fonction d’agrégation uniquement si toutes les autres colonnes résultent elles aussi d’une fonction d’agrégation ?

Oui ! Sauf si votre requête contient un GROUP BY. Dans ce cas, cette phrase de conclusion devra être complétée, c’est ce que nous allons voir dans la partie suivante.

Les attributs de partitionnement

Dans une agrégation, les attributs de partitionnement sont à spécifier dans le GROUP BY :

SELECT count(*) FROM entity GROUP BY status ;

 Ici, on compte le nombre de sociétés pour chacun des statuts présents dans entity.

Oui, mais cette requête ne renvoie qu'une colonne avec des nombres ! On ne sait pas à quoi correspond chaque nombre.

Effectivement ! Pour résoudre ce problème, on peut afficher la valeur de status de cette manière :

SELECT status, count(*) FROM entity GROUP BY status ;

... ce qui nous amène à compléter la règle énoncée un peu plus haut, qui est une règle d'or :

La règle d'or de l'agrégation

Continuons notre investigation

Prêts à reprendre la grosse requête du chapitre précédent ? Courage, vous en êtes capables !

SELECT
    i.id as intermediary_id,
    i.name as intermediary_name,
    e.id as entity_id,
    e.name as entity_name,
    e.status as entity_status
FROM 
    intermediary i,
    assoc_inter_entity a,
    entity e
WHERE
    a.entity = e.id
    AND a.inter = i.id
    AND e.name = 'Big Data Crunchers Ltd.' ;

Vous vous souvenez qu'elle lie des intermédiaires avec des entités. Elle nous a permis de trouver 2 intermédiaires qui ont les identifiants 5000 et 5001.

Nous souhaitons ici connaître le nombre d'entités (le nombre de sociétés) qu'ont créé ces 2 intermédiaires dans chacune des juridictions (juridiction = pays, à peu de choses près ;) ). Il va donc falloir utiliser le GROUP BY !

D'après cette phrase, nous pouvons déduire que les attributs de partitionnement seront :

  • la juridiction

  • l'intermédiaire (prenons par exemple son identifiant et son nom)

Commençons par modifier la requête précédente. Tout d'abord, cette dernière est limitée à l'entité  Big Data Crunchers Ltd.  . Ici, nous souhaitons étudier toutes les entités créées par les 2 intermédiaires. Remplaçons donc la ligne 14 par AND (i.id = 5000 OR i.id = 5001)  .

Ajoutons nos 3 attributs de partitionnement dans le GROUP BY, puis supprimons du SELECT toutes les colonnes qui ne sont pas des attributs de partitionnement (c'est-à-dire les lignes 4,5 et 6).

Il ne reste plus que notre fonction d'agrégation :  COUNT()  , car nous souhaitons compter le nombre d'entités. Voici le résultat :

SELECT
    i.id as intermediary_id, 
    i.name as intermediary_name,
    e.jurisdiction, 
    count(*) 
FROM 
    intermediary i,
    assoc_inter_entity a, 
    entity e 
WHERE 
    a.entity = e.id 
    AND a.inter = i.id 
    AND (i.id = 5000 OR i.id = 5001) 
GROUP BY 
    i.id, i.name, e.jurisdiction;

Aller plus loin : fonctions d'agrégation et fonctions scalaires

Nous avons vu dans le chapitre sur la clause SELECT qu'il est possible utiliser des fonctions dans le SELECT. S’agit-il de fonctions d'agrégation ?

SELECT abs(id) AS valeur_absolue FROM entity ;

Non. :( Dans cette requête (qui renvoie la valeur absolue de l’identifiant de la société), abs renvoie un résultat sur autant de lignes qu’elle en a reçu en entrée. S’il y a 1000 sociétés dans la table entity, alors il y aura 1000 valeurs absolues à calculer. Au contraire, une fonction d’agrégation ne renvoie qu’une seule ligne.

La fonction abs est donc appelée fonction scalaire.

En résumé

  • Dans une agrégation, les fonctions d'agrégation se placent dans le SELECT ...

  • ... et les attributs de partitionnement dans le GROUP BY.

  • Dans le SELECT, on ne peut faire appel à une fonction d’agrégation uniquement si toutes les autres colonnes résultent elles aussi d’une fonction d’agrégation, OU sont présentes dans la clause GROUP BY.

Un peu d'entraînement !

La base de données est en ligne, vous pouvez vous entraîner en réécrivant les requêtes de ce chapitre. N'hésitez pas à les modifier, à jouer avec, pour "voir ce que ça fait". ;)

Console interactive

Example of certificate of achievement
Example of certificate of achievement