Qu'est-ce qu'un pivot?
Le pivot est une opération par laquelle des données sous une colonne
deviennent des noms de colonnes, de sorte que visuellement une sorte
de rotation à 90° est opérée: ce qui se lisait verticalement de haut
en bas se retrouve ordonné horizontalement de gauche à droite. Les
termes "transposition", "requête analyse croisée" ou "crosstab" sont
aussi utilisés pour désigner ce concept, qui vient instiller un peu de
la vision "tableur" des données dans l'approche relationnelle.
Dans le cas le plus simple, on part d'une colonne qui est fonction
d'une autre. Considérons un exemple météorologique à 2 colonnes:
l'année, et la pluviométrie correspondante, exprimée en nombre de
jours où les précipitations ont dépassé 1mm.
Avant pivot:
Année | Pluie |
----------+----------+
2012 | 112 |
2013 | 116 |
2014 | 111 |
2015 | 80 |
2016 | 110 |
2017 | 102 |
Après pivot:
2012 | 2013 | 2014 | 2015 | 2016 | 2017
------+------+------+------+------+------
112 | 116 | 111 | 80 | 110 | 102
Souvent, il y a une deuxième dimension, c'est-à-dire qu'on part de 3 colonnes,
dont une dépend fonctionnellement des deux autres:
(dimension X, dimension Y)=>Valeur.
Dans l'exemple de la pluviométrie, la seconde dimension pourrait être
un nom de ville, comme ci-dessous:
Avant pivot:
Année | Ville | Jours
-------+-----------+-------
2012 | Lyon | 112
2013 | Lyon | 116
2014 | Lyon | 111
... | ... | ...
2014 | Toulouse | 111
2015 | Toulouse | 83
Considérons un jeu de données réduit de 13 villes x 6 ans.
La série ci-dessus ferait donc 78 lignes.
(un dump SQL pour cet exemple est disponible en téléchargement
ici: exemple-pluviometrie.sql;
les données brutes sont à l'échelle du mois et proviennent originellement
de https://www.infoclimat.fr/climatologie/).
Et voici une présentation typique après pivot:
Ville | 2012 | 2013 | 2014 | 2015 | 2016 | 2017
-----------+------+------+------+------+------+------
Ajaccio | 69 | 91 | 78 | 48 | 81 | 51
Bordeaux | 116 | 138 | 137 | 101 | 117 | 110
Brest | 178 | 161 | 180 | 160 | 165 | 144
Dijon | 114 | 124 | 116 | 93 | 116 | 103
Lille | 153 | 120 | 136 | 128 | 138 | 113
Lyon | 112 | 116 | 111 | 80 | 110 | 102
Marseille | 47 | 63 | 68 | 53 | 54 | 43
Metz | 98 | 120 | 110 | 93 | 122 | 115
Nantes | 124 | 132 | 142 | 111 | 106 | 110
Nice | 53 | 77 | 78 | 50 | 52 | 43
Paris | 114 | 111 | 113 | 85 | 120 | 110
Perpignan | 48 | 56 | 54 | 48 | 69 | 48
Toulouse | 86 | 116 | 111 | 83 | 102 | 89
(13 lignes)
Pour exprimer ça de manière générale, le résultat pivoté d'une série
de tuples (X,Y,V) est une grille de N+1 colonnes fois M lignes, où:
-
N est le nombre de valeurs distinctes de X
-
M est le nombre de valeurs distinctes de Y.
-
la 1ere colonne (la plus à gauche) porte les valeurs distinctes
de Y, généralement dans un ordre défini, par exemple ici les noms
de ville dans l'ordre alphabétique.
-
les noms des autres colonnes sont constituées des valeurs distinctes
de X, également dans un ordre défini. Dans l'exemple ci-dessus ce sont
les années, par ordre croissant ou décroissant.
-
pour chaque couple (X,Y), si un tuple (X,Y,V) est présent dans le jeu de données
avant pivot, la valeur V est placée dans la grille
au croisement de la colonne de nom X et de la ligne commençant par Y.
C'est pourquoi on parle de tableau croisé (crosstab).
Si à (X,Y) ne correspond pas de V dans le jeu de données,
la case correspondante reste vide (NULL), ou à une valeur spécifique
si on préfère.
Quand le nombre de colonnes reste raisonnable, cette
représentation a quelques avantages visuels par rapport à l'affichage
en ligne des tuples (X,Y,V):
- elle occupe mieux l'espace 2D.
- elle est plus intuitive, parce qu'il n'y a pas de répétition des dimensions.
- l'absence éventuelle de valeur dans une case saute aux yeux.
- il y a deux axes de tri indépendants.
Quelles requêtes pour pivoter un jeu de données?
Contrairement à Oracle ou MS-SQL Server, PostgreSQL n'a pas de clause PIVOT
dans son dialecte SQL, mais cette clause n'est pas essentielle.
Une requête pour pivoter ces données à 3 colonnes (x,y,v)
peut s'écrire sous cette forme:
SELECT
y,
(CASE WHEN x='valeur 1' THEN v END) "valeur 1",
(CASE WHEN x='valeur 2' THEN v END) "valeur 2",
...à répéter pour chaque valeur de x devenant une colonne
FROM table ou sous-requête
[ORDER BY 1]
Assez fréquemment une requête pivot agrège en même temps qu'elle pivote:
La forme sera alors plutôt de ce genre-là:
SELECT
y,
AGG(v) FILTER (WHERE x='valeur 1') AS "valeur 1",
AGG(v) FILTER (WHERE x='valeur 2') AS "valeur 2",
...à répéter pour chaque valeur de x devenant une colonne
FROM table ou sous-requête
GROUP BY y [ORDER BY 1];
La clause FILTER est une nouveauté de PostgreSQL 9.4, sinon il est toujours
possible d'utiliser une expression CASE WHEN.
AGG(v) symbolise une fonction d'agrégation, qui pourrait être
typiquement SUM(v) pour cumuler des valeurs, ou COUNT(v) pour
compter des occurrences, ou encore MIN() ou MAX().
Pour la table d'exemple de pluviométrie sur 6 ans, si on a une mesure par mois
et qu'on veut afficher un pivot Ville/Année, la requête serait la suivante:
SELECT
ville,
SUM(pluvio) FILTER (WHERE annee=2012) AS "2012",
SUM(pluvio) FILTER (WHERE annee=2013) AS "2013",
SUM(pluvio) FILTER (WHERE annee=2014) AS "2014",
SUM(pluvio) FILTER (WHERE annee=2015) AS "2015",
SUM(pluvio) FILTER (WHERE annee=2016) AS "2016",
SUM(pluvio) FILTER (WHERE annee=2017) AS "2017"
FROM pluviometrie
GROUP BY ville
ORDER BY ville;
L'extension tablefunc
de contrib fournit entre autres une fonction:
crosstab(text source_sql, text category_sql)
qui est souvent citée en premier dans les questions sur les pivots avec Postgres.
Le premier argument de crosstab est une requête renvoyant les
données avant pivot. Le deuxième argument est une autre requête
renvoyant les noms des colonnes après pivot, dans l'ordre désiré. La
fonction renvoyant un type SETOF RECORD, en pratique il faut
re-spécifier ces colonnes via une clause AS (col1 type, col2 type,
etc...) pour qu'elles soient interprétables comme telles par le moteur
SQL.
Exemple:
SELECT * FROM crosstab(
-- requête pour le contenu de la grille
'SELECT ville,annee,SUM(pluvio)
FROM pluviometrie GROUP BY ville,annee ORDER BY ville',
-- requête pour l'entête horizontal
'SELECT DISTINCT annee FROM pluviometrie ORDER BY annee')
AS ("Ville" text,
"2012" int,
"2013" int,
"2014" int,
"2015" int,
"2016" int,
"2017" int);
Les limites des pivots statiques
L'ennui avec ces deux formes de requêtes, aussi bien celle qui a recours
à la fonction crosstab() que celle construite sur autant d'expressions
que de colonnes, c'est qu'il faut lister les colonnes et que dès qu'il
y a une donnée en plus à pivoter, l'ajouter manuellement à la liste.
Sinon avec la première forme la nouvelle donnée sera ignorée, et avec
crosstab() elle provoquera une erreur.
D'autre part, ces requêtes ne sont pas malléables: si on veut présenter
les colonnes dans l'ordre inverse, ou bien pivoter sur une autre
colonne (ici en l'occurrence mettre des villes horizontalement au lieu
des années), elles doivent être modifiées presque intégralement.
Enfin, certains pivots génèrent des centaines de colonnes, et la perspective
de gérer ça à la main en SQL paraît absurde.
Souvent, en tant qu'utilisateur de SQL, on voudrait faire ce qu'on pourrait
appeler un pivot dynamique, c'est-à-dire une requête polymorphe qui, sans
modification du SQL, se trouverait automatiquement avoir toutes les
colonnes du résultat à partir des lignes correspondantes. Ce serait
utile d'une part si ces données sont susceptibles de changer, et
d'autre part quand il y a beaucoup de colonnes et qu'il est trop
fastidieux de les spécifier.
Mais il se trouve qu'une requête SQL ne peut pas avoir des colonnes dynamiques
au sens où il le faudrait pour un pivot dynamique.
On pourra objecter que dans un SELECT * FROM table, le * est bien
remplacé dynamiquement par liste des colonnes, mais la différence est
que cette opération se fait dans l'étape d'analyse de la requête, pas
dans la phase d'exécution. Avant l'exécution, le moteur SQL doit impérativement
savoir quels sont le nombre, les types et les noms des colonnes de la requête
et des sous-requêtes qui la composent. C'est d'ailleurs pour ça
que la sortie de crosstab(), au même titre que n'importe quelle fonction qui renvoie
un SETOF RECORD, doit être qualifiée statiquement par une liste de noms
de colonnes avec leurs types sous la forme AS (col1 type1, col2 type2...)
Méthodes pour des pivots dynamiques
La difficulté du pivot dynamique peut être résumée ainsi: pour toute
requête SQL, il faut que le type du résultat (noms et types de toutes
les colonnes) soit connu avant la phase d'exécution. Or pour savoir
quelles sont les colonnes composant le résultat pivoté, il faudrait
exécuter la requête: c'est un cercle vicieux, et pour s'en sortir il
faut forcément changer un peu les termes du problème.
Résultat encapsulé dans une colonne
Une première solution est que la requête SQL renvoie le résultat pivoté non
pas en colonnes séparées, mais encapsulé dans une seule colonne avec un type
multi-dimensionnel: array[text], JSON, ou XML. Cette solution est par
exemple intégrée dans Oracle avec sa clause PIVOT XML. C'est en une seule
étape, mais le résultat a une structure non-tabulaire qui ne correspond
pas forcément à ce que les utilisateurs espèrent.
Voici un exemple en PostgreSQL moderne avec JSON:
SELECT ville,
json_object_agg(annee,total ORDER BY annee)
FROM (
SELECT ville, annee, SUM(pluvio) AS total
FROM pluviometrie
GROUP BY ville,annee
) s
GROUP BY ville
ORDER BY ville;
Sans avoir à lister les années dans cette requête, on retrouve notre résultat précédent
complet, mais sous forme de deux colonnes, une pour l'"axe vertical", l'autre pour tout
le reste au format JSON:
ville | json_object_agg
-----------+----------------------------------------------------------------------------------------
Ajaccio | { "2012" : 69, "2013" : 91, "2014" : 78, "2015" : 48, "2016" : 81, "2017" : 51 }
Bordeaux | { "2012" : 116, "2013" : 138, "2014" : 137, "2015" : 101, "2016" : 117, "2017" : 110 }
Brest | { "2012" : 178, "2013" : 161, "2014" : 180, "2015" : 160, "2016" : 165, "2017" : 144 }
Dijon | { "2012" : 114, "2013" : 124, "2014" : 116, "2015" : 93, "2016" : 116, "2017" : 103 }
Lille | { "2012" : 153, "2013" : 120, "2014" : 136, "2015" : 128, "2016" : 138, "2017" : 113 }
Lyon | { "2012" : 112, "2013" : 116, "2014" : 111, "2015" : 80, "2016" : 110, "2017" : 102 }
Marseille | { "2012" : 47, "2013" : 63, "2014" : 68, "2015" : 53, "2016" : 54, "2017" : 43 }
Metz | { "2012" : 98, "2013" : 120, "2014" : 110, "2015" : 93, "2016" : 122, "2017" : 115 }
Nantes | { "2012" : 124, "2013" : 132, "2014" : 142, "2015" : 111, "2016" : 106, "2017" : 110 }
Nice | { "2012" : 53, "2013" : 77, "2014" : 78, "2015" : 50, "2016" : 52, "2017" : 43 }
Paris | { "2012" : 114, "2013" : 111, "2014" : 113, "2015" : 85, "2016" : 120, "2017" : 110 }
Perpignan | { "2012" : 48, "2013" : 56, "2014" : 54, "2015" : 48, "2016" : 69, "2017" : 48 }
Toulouse | { "2012" : 86, "2013" : 116, "2014" : 111, "2015" : 83, "2016" : 102, "2017" : 89 }
(13 lignes)
C'est déjà pas mal, mais visuellement ça manque d'alignement, et
surtout si le but est de copier-coller dans un tableur, on voit bien que ça ne va pas
vraiment le faire.
Résultat tabulaire obtenu en deux temps
Les autres solutions via requête SQL tournent autour
de l'idée de procéder en deux temps:
-
une première requête construit le résultat avec toutes ses
colonnes, et renvoie une référence indirecte à ce résultat.
-
une deuxième requête va ramener réellement le résultat, sa structure
étant maintenant connue par le moteur SQL du fait de l'étape précédente.
A ce niveau, il faut rappeler qu'encapsuler ces deux étapes en une seule
fonction annulerait l'intérêt de la solution:
car pour appeler cette fonction en SQL, il faudrait obligatoirement
spécifier avec une clause AS(...) toutes les colonnes du résultat,
et dans ce cas autant utiliser crosstab().
La référence créée par la première étape peut être un curseur: dans ce cas la
requête SQL est un appel de fonction prenant le même genre d'arguments
que crosstab() mais renvoyant un REFCURSOR. La fonction créé
dynamiquement une requête pivot, et instancie un curseur sur son
résultat. Le code client peut alors parcourir ce résultat avec
FETCH. C'est la solution mise en oeuvre dans la fonction
dynamic_pivot() dont le code est un peu plus bas.
Autre variante: la requête SQL est un appel de fonction prenant le
même genre d'arguments que crosstab() mais créant une vue dynamique
ou une table, temporaire ou permanente, avec les données
pivotées. Dans un second temps, le code client exécute un SELECT sur
cette table ou vue, puis la supprime. Une implémentation en plpgsql
pourrait être assez similaire à celle renvoyant un curseur, sauf
qu'une fois établie la requête dynamique, on exécuterait
CREATE [TEMPORARY] TABLE (ou VIEW) nom AS ... suivi de la requête.
Dans le code ci-dessous, je vous propose une fonction renvoyant
un REFCURSOR qui peut être utilisée telle quelle, mais qui pourrait aussi
servir de base pour une variante.
Ses arguments sont les mêmes que crosstab():
- une requête principale sortant 3 colonnes avant pivot dans l'ordre
(catégorie, valeur à transposer, valeur centrale).
- une requête sortant la liste des colonnes dans l'ordre attendu.
La fonction instancie et renvoie un curseur contenant le résultat,
lequel doit être consommé dans la même transaction (quoiqu'on pourrait
le déclarer WITH HOLD si on voulait garder le résultat toute la session).
Malheureusement la requête principale doit être exécutée en interne deux fois
par cette implémentation, car elle est incorporée en sous-requête
dans deux requêtes totalement distinctes.
De plus, le type des colonnes en sortie est forcé à text, faute de
pouvoir accéder à l'information du type des données source en
plpgsql. Une version en langage C lèverait potentiellement ces
inconvénients, qui sont liés aux limitations du plpgsql (et encore,
sans l'existence de row_to_json, ajoutée en version 9.2, je ne crois
pas qu'il aurait été possible du tout de trouver les noms des colonnes comme
le fait la première étape de la fonction). Quoiqu'il en soit, une
version plpgsql a un avantage considérable ici: elle n'exige qu'une
quarantaine de lignes de code pour faire ce travail, que voici:
CREATE FUNCTION dynamic_pivot(central_query text, headers_query text)
RETURNS refcursor AS
$$
DECLARE
left_column text;
header_column text;
value_column text;
h_value text;
headers_clause text;
query text;
j json;
r record;
curs refcursor;
i int:=1;
BEGIN
-- détermine les noms des colonnes de la source
EXECUTE 'select row_to_json(_r.*) from (' || central_query || ') AS _r' into j;
FOR r in SELECT * FROM json_each_text(j)
LOOP
IF (i=1) THEN left_column := r.key;
ELSEIF (i=2) THEN header_column := r.key;
ELSEIF (i=3) THEN value_column := r.key;
END IF;
i := i+1;
END LOOP;
-- génère dynamiquement la requête de transposition (sur le modèle canonique)
FOR h_value in EXECUTE headers_query
LOOP
headers_clause := concat(headers_clause,
format(chr(10)||',min(case when %I=%L then %I::text end) as %I',
header_column,
h_value,
value_column,
h_value ));
END LOOP;
query := format('SELECT %I %s FROM (select *,row_number() over() as rn from (%s) AS _c) as _d GROUP BY %I order by min(rn)',
left_column,
headers_clause,
central_query,
left_column);
-- ouvre le curseur pour que l'appelant n'ait plus qu'à exécuter un FETCH
OPEN curs FOR execute query;
RETURN curs;
END
$$ LANGUAGE plpgsql;
Exemple d'utilisation:
=> BEGIN;
-- étape 1: obtenir le curseur (le nom du curseur est généré par Postgres)
=> SELECT dynamic_pivot(
'SELECT ville,annee,SUM(pluvio)
FROM pluviometrie GROUP BY ville,annee
ORDER BY ville',
'SELECT DISTINCT annee FROM pluviometrie ORDER BY 1'
) AS curseur
\gset
-- étape 2: extraire les résultats du curseur
=> FETCH ALL FROM :"curseur";
ville | 2012 | 2013 | 2014 | 2015 | 2016 | 2017
-----------+------+------+------+------+------+------
Ajaccio | 69 | 91 | 78 | 48 | 81 | 51
Bordeaux | 116 | 138 | 137 | 101 | 117 | 110
Brest | 178 | 161 | 180 | 160 | 165 | 144
Dijon | 114 | 124 | 116 | 93 | 116 | 103
Lille | 153 | 120 | 136 | 128 | 138 | 113
Lyon | 112 | 116 | 111 | 80 | 110 | 102
Marseille | 47 | 63 | 68 | 53 | 54 | 43
Metz | 98 | 120 | 110 | 93 | 122 | 115
Nantes | 124 | 132 | 142 | 111 | 106 | 110
Nice | 53 | 77 | 78 | 50 | 52 | 43
Paris | 114 | 111 | 113 | 85 | 120 | 110
Perpignan | 48 | 56 | 54 | 48 | 69 | 48
Toulouse | 86 | 116 | 111 | 83 | 102 | 89
(13 lignes)
=> CLOSE :"curseur";
=> COMMIT; -- libérera automatiquement le curseur si pas déjà fait par CLOSE.
Pivot par le code client
La couche de présentation côté client peut aussi se charger de
transposer les lignes en colonnes, sur la base d'un jeu de résultat
non pivoté. En effet certains voient la transposition comme une pure
question de présentation, et pour l'essentiel c'est un point de vue
qui se tient.
L'application psql propose une solution basée sur cette approche
depuis la version 9.6, via la commande
\crosstabview.
En usage interactif, cette méthode est la plus rapide pour obtenir des résultats
immédiatement visibles.
Par exemple, admettons qu'on veuille examiner dans le cadre de notre exemple,
les couples (ville,année) dépassant 120 jours de pluie:
=# SELECT ville, annee, SUM(pluvio)
FROM pluviometrie
GROUP BY ville,annee
HAVING SUM(pluvio)>120
ORDER BY annee
\crosstabview
ville | 2012 | 2013 | 2014 | 2015 | 2016 | 2017
----------+------+------+------+------+------+------
Brest | 178 | 161 | 180 | 160 | 165 | 144
Nantes | 124 | 132 | 142 | | |
Lille | 153 | | 136 | 128 | 138 |
Dijon | | 124 | | | |
Bordeaux | | 138 | 137 | | |
Metz | | | | | 122 |
L'axe horizontal est alimenté par la 2eme colonne de la source mais
pour avoir la transposition inverse, il suffit de mettre les colonnes
annee ville en argument de crosstabview dans cet ordre, sans rien changer
à la requête:
=# \crosstabview annee ville
annee | Brest | Nantes | Lille | Dijon | Bordeaux | Metz
-------+-------+--------+-------+-------+----------+------
2012 | 178 | 124 | 153 | | |
2013 | 161 | 132 | | 124 | 138 |
2014 | 180 | 142 | 136 | | 137 |
2015 | 160 | | 128 | | |
2016 | 165 | | 138 | | | 122
2017 | 144 | | | | |
Ci-dessus on n'a pas de tri particulier des villes. Mais on peut
trier ces colonnes, y compris sur un critère complexe, à travers le
4ème argument de la commande. Le code suivant trie les
colonnes-villes par rang de pluviosité, en l'ajoutant comme 4ème colonne
à la requête et à crosstabview:
=# SELECT ville, annee, SUM(pluvio),
rank() OVER (ORDER BY SUM(pluvio))
FROM pluviometrie
GROUP BY ville,annee
HAVING SUM(pluvio)>120
ORDER BY annee
\crosstabview annee ville sum rank
Résultat:
annee | Metz | Dijon | Nantes | Bordeaux | Lille | Brest
-------+------+-------+--------+----------+-------+-------
2012 | | | 124 | | 153 | 178
2013 | | 124 | 132 | 138 | | 161
2014 | | | 142 | 137 | 136 | 180
2015 | | | | | 128 | 160
2016 | 122 | | | | 138 | 165
2017 | | | | | | 144
On voit que les nombres se répartissent maintenant de telle
sorte qu'en lisant de gauche à droite on a des villes globalement
de plus en plus pluvieuses, et notamment Brest le gagnant
incontestable de ce jeu de données.
L'opération UNPIVOT existe dans certains dialectes SQL, mais pas dans PostgreSQL.
On peut toutefois dé-pivoter facilement avec Postgres et de manière
générique, c'est-à-dire sans liste explicite des colonnes,
en passant par une représentation intermédiaire du jeu de données en JSON.
Imaginons par exemple que les données de pluviométrie se présentent comme ci-dessous,
avec une colonne distincte par mois de l'année, type "tableur"
=> \d pluvmois
Column | Type |
--------+---------+
ville | text |
annee | integer |
m1 | integer |
m2 | integer |
m3 | integer |
m4 | integer |
m5 | integer |
m6 | integer |
m7 | integer |
m8 | integer |
m9 | integer |
m10 | integer |
m11 | integer |
m12 | integer |
En appliquant la fonction json_each_text à chaque ligne de la table
mise au format JSON avec row_to_json, on va obtenir toutes les colonnes
sous forme de tuples (key,value):
SELECT key, value FROM
(SELECT row_to_json(t.*) AS line FROM pluvmois t) AS r
JOIN LATERAL json_each_text(r.line) ON (true);
Pour avoir notre résultat final dé-pivoté, il ne reste plus qu'à enrichir
cette requête pour garder l'année et la ville associée à chaque
mesure, et filtrer et re-typer les colonnes de mois, comme ci-dessous:
SELECT
r.ville,
r.annee,
substr(key,2)::int AS mois, -- transforme 'm4' en 4
value::int AS pluvio
FROM (SELECT ville, annee, row_to_json(t.*) AS line FROM pluvmois t) AS r
JOIN LATERAL json_each_text(r.line) ON (key ~ '^m[0-9]+');
Résultat:
ville | annee | mois | pluvio
-----------+-------+------+--------
Lille | 2017 | 1 | 9
Lille | 2017 | 2 | 10
Lille | 2017 | 3 | 9
etc...
(936 lignes)
On retrouve bien nos 13 villes x 6 ans x 12 mois du jeu de données initial.
mais merci Christophe quand même 
