Tableaux croisés dans PostgreSQL en utilisant la fonction Crosstab

Il y a quelques années, lors de la sortie de la version 8.3 de PostgreSQL, une nouvelle extension appelée tablefunc a été introduite. Cette extension fournit un ensemble de fonctions vraiment intéressantes. L'une d'entre elles est la fonction crosstab, qui est utilisée pour la création de tableaux croisés dynamiques. C'est ce que nous allons aborder dans cet article.

La façon la plus simple d'expliquer le fonctionnement de cette fonction est d'utiliser un exemple avec un tableau croisé dynamique. Nous allons d'abord expliquer notre point initial d'un point de vue pratique, puis nous définirons le tableau croisé dynamique que nous souhaitons.

Notre point initial : Les données brutes

Pendant que vous lisez cet article, imaginez que vous êtes un enseignant dans une école primaire (élémentaire). Nous supposerons que vous enseignez toutes les matières (langue, musique, etc.). L'école met à votre disposition un système vous permettant d'enregistrer tous les résultats des évaluations ou des tests. L'instruction SQL suivante afficherait les résultats d'évaluation que vous avez précédemment chargés dans le système :

SELECT *
FROM evaluations

Notre objectif : un tableau croisé dynamique

La grille suivante pourrait facilement suivre les progrès de vos élèves. En informatique, on appelle ce type de grille un tableau croisé dynamique. Si vous analysez la manière dont le tableau croisé dynamique est construit, vous constaterez que nous utilisons les valeurs des données brutes comme en-têtes de colonne ou noms de champ (dans ce cas, géographie, histoire, mathématiques, etc.).

Activation de la fonction Tableau croisé

Comme nous l'avons mentionné précédemment, la fonction Tableau croisé fait partie d'une extension PostgreSQL appelée tablefunc. Pour appeler la fonction crosstab, vous devez d'abord activer l'extension tablefunc en exécutant la commande SQL suivante :

CREATE extension tablefunc;

Fonctionnement de la fonction crosstab

La fonction crosstab reçoit en paramètre une commande SQL SELECT, qui doit respecter les restrictions suivantes :

• La commande SELECT doit renvoyer 3 colonnes.
• La première colonne de la commande SELECT sera l'identifiant de chaque ligne du tableau croisé dynamique ou du résultat final. Dans notre exemple, il s'agit du nom de l'étudiant. Remarquez que les noms des étudiants (John Smith et Peter Gabriel) apparaissent dans la première colonne.
• La deuxième colonne dans le SELECT représente les catégories dans le tableau croisé dynamique. Dans notre exemple, ces catégories sont les matières scolaires. Il est important de noter que les valeurs de cette colonne s'étendront à de nombreuses colonnes du tableau croisé dynamique. Si la deuxième colonne renvoie cinq valeurs différentes (géographie, histoire, etc.), le tableau croisé dynamique comportera cinq colonnes.
• La troisième colonne du SELECT représente la valeur à attribuer à chaque cellule du tableau croisé dynamique. Ce sont les résultats de l'évaluation dans notre exemple.

Si nous considérons notre tableau croisé dynamique comme un tableau à deux dimensions, la première colonne SELECT est la première dimension du tableau, la deuxième colonne SELECT est la deuxième dimension et la troisième est la valeur de l'élément du tableau , comme le tableau [première_valeur_colonne, deuxième_valeur_colonne] = troisième_valeur_colonne.

Dans notre exemple, le paramètre SELECT sera :

SELECT student, subject, evaluation_result FROM evaluations ORDER BY 1,2

La fonction crosstab est invoquée dans la clause FROM de l'instruction SELECT. Nous devons définir les noms des colonnes et des types de données qui entreront dans le résultat final. Pour nos besoins, le résultat final est défini comme suit :

AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC)

En joignant tous ces éléments, notre requête finale sera :

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Et nous pouvons voir le résultat ici :

Un ensemble de données brutes, plusieurs tableaux croisés dynamiques

À partir d'un seul ensemble de données, nous pouvons produire de nombreux tableaux croisés dynamiques différents. Poursuivons avec l'exemple de l'enseignant et de la classe en examinant quelques-unes de nos options.

Exemple 1 : Moyennes mensuelles des évaluations

En tant qu'enseignants, nous pouvons également avoir besoin d'un rapport sur les résultats d'évaluation d'un élève pour l'année en cours. Par exemple, supposons que nous voulions obtenir les évaluations moyennes de Jean Dupont de mars à juillet. Dans une grille comme celle qui suit, le tableau ressemblerait à ceci :

Le SQL pour ce tableau croisé dynamique est le suivant :

SELECT * 
FROM crosstab( 'select extract(month from period)::text, subject.name,
		     trunc(avg(evaluation_result),2) 
     from evaluation, subject  
     where evaluation.subject_id = subject.subject_id and student_id = 1 
     group by 1,2 order by 1,2')  AS final_result(Month TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Veuillez comparer ce résultat avec le tableau précédent :

Exemple 2 : Trouver des dossiers d'étudiants incomplets

Nous pourrions également intituler cette section "Une limite du tableau croisé et comment la corriger". Avant d'entrer dans le vif du sujet, plantons le décor :

Supposons que vous vouliez voir si certains étudiants n'ont pas de note d'évaluation pour certaines matières. Vous allez peut-être essayer notre requête précédente, en ajoutant une clause WHERE pour juillet. Le code ressemblerait à ceci :

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations where extract (month from evaluation_day) = 7 order by 1,2') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Le tableau croisé dynamique suivant est le résultat de cette requête. Nous pouvons rapidement constater que nous n'avons pas de notes pour les langues, les mathématiques et la musique pour Pierre.

Cependant, si nous essayons une requête régulière pour obtenir les notes de Pierre en juillet ...

SELECT * from evaluations 
where extract ( month from evaluation_day)=7 and student like 'Gabriel, Peter'

... nous obtenons des résultats différents. Ici, nous avons des notes pour la géographie et les langues :

Bien sûr, la deuxième requête est la bonne car elle montre les données brutes. Le problème se situe dans le processus de construction du tableau croisé dynamique - certaines catégories manquent d'informations. Pour résoudre ce problème, nous pouvons utiliser la fonction crosstab avec un deuxième paramètre, qui représente la liste complète des catégories. S'il y a des valeurs manquantes, le tableau croisé dynamique sera quand même construit correctement. (J'ai mis en évidence la requête du second paramètre en rouge).

SELECT * 
FROM crosstab( 'select student, subject, evaluation_result from evaluations 
                where extract (month from evaluation_day) = 7 order by 1,2',
                'select name from subject order by 1') 
     AS final_result(Student TEXT, Geography NUMERIC,History NUMERIC,Language NUMERIC,Maths NUMERIC,Music NUMERIC);

Nous avons maintenant un tableau croisé dynamique correct avec des valeurs vides aux bons endroits.

Exercice

Les tableaux croisés dynamiques nous offrent une façon différente de voir nos données. De plus, nous pouvons créer différents tableaux croisés dynamiques à partir des mêmes données brutes en utilisant la fonction de tableau croisé. Essayez de créer un tableau croisé dynamique qui indique la température maximale pour chaque ville et chaque mois à partir des données brutes du tableau ci-dessous.

CREATE TABLE weather (city text, when timestamp, temperature float);

Le tableau croisé dynamique doit comporter une ligne pour chaque ville et une colonne pour chaque mois. Si vous le souhaitez, vous pouvez imaginer d'autres tableaux croisés dynamiques qui pourraient être créés à partir des mêmes données.