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?

La forme canonique

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;

La forme utilisant crosstab()

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:

  1. une première requête construit le résultat avec toutes ses colonnes, et renvoie une référence indirecte à ce résultat.

  2. 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.

Comment dé-pivoter un 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.