MySQL : une table pivot dynamique

Comment transformer les lignes d'une table en colonnes ?
  • website - logo
  • linkedin - logo
  • twitter - logo
  • github - logo

Ce tutoriel a pour but de réaliser une table pivot dynamique pour MySQL.

Qu'est ce qu'une table pivot ?

La table pivot (en anglais pivot table ou crosstab) est une technique pour faire pivoter une table verticale en table horizontale. En agrégeant les bonnes données, les lignes de la table verticale deviennent les colonnes de la table horizontale. Le pivot est une technique très utilisée pour générer des rapports humainement lisibles ou décomplexifier les requêtes sur les tables verticales.

Un cas concret

Pour des besoins marketing, je souhaite enregistrer les informations dont je dispose sur un visiteur de mon site web : date de la visite, navigateur, système d'exploitation, provenance du visiteur etc...

Voici la table associée : 

Une simple table verticale

CREATE TABLE IF NOT EXISTS `tracking` (
  `user` VARCHAR(127) NOT NULL,
  `date_visit` datetime NOT NULL,
  `key` VARCHAR(127) NOT NULL,
  `value` VARCHAR(127) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `tracking` (`user`, `date_visit`, `key`, `value`) VALUES
('Nico', '2014-10-26 18:10:00', 'browser', 'chromium'),
('Nico', '2014-10-26 18:10:00', 'os', 'ubuntu'),
('Nico', '2014-10-26 18:10:00', 'source', 'google'),
('Obi-Wan', '2014-10-26 19:20:00', 'browser', 'firefox'),
('Obi-Wan', '2014-10-26 19:20:00', 'os', 'mac os x'),
('Obi-Wan', '2014-10-26 19:20:00', 'source', 'emailing_campaign'),
('Nico', '2014-10-27 15:45:00', 'browser', 'chrome'),
('Nico', '2014-10-27 15:45:00', 'os', 'windows 8'),
('Nico', '2014-10-27 15:45:00', 'source', 'direct_access');

Dans cette table il y a deux utilisateurs :

  • Nico a visité deux fois le site : la première avec chromium sous ubuntu, la seconde avec chrome sous windows 8.
  • Obi-Wan n'est venu qu'une fois avec firefox sous mac os x.

Pour avoir une vision plus claire de la situation, nous allons faire pivoter cette table verticale vers une table : user | date_visit | browser | os | source.

Pour obtenir ce résultat, il faudra grouper par "user" et "date_visit" et utiliser une fonction d'agrégation sur la colonne "value". Pour être sûr de ne pas perdre de données dans le cas où le même visiteur vient deux fois sur la page dans la même seconde, nous utiliserons la fonction d'agrégation GROUP_CONCAT. Sachez tout de même que selon le résultat que vous souhaitez obtenir, notamment sur les nombres, les autres fonctions d'agrégation (COUNT, MIN, MAX, SUM etc..) font tout aussi bien le job. 

Un simple group_concat

SELECT
        t.user,
        t.date_visit,
        GROUP_CONCAT(t.value, NULL) AS agreg_value,
FROM
        tracking t
GROUP BY
        t.user,
        t.date_visit;

/* Résultat :
Nico | 2014-10-26 18:10:00 | chromium,ubuntu,google
*/

Nous avons maintenant une table horizontale avec une colonne fourre-tout. Un truc à savoir sur GROUP_CONCAT : la fonction ignore totalement les valeurs NULL. Ainsi, si dans cette colonne nous ne souhaitons que les navigateurs, il suffit de mettre une condition pour ne concaténer "value" que si la colonne "key" vaut "browser" et renvoyer NULL sinon. Si nous répétons cette opération pour les deux autres colonnes "os" et "source" nous obtenons cette requête : 

Table de pivot avec group_concat

SELECT
        t.user,
        t.date_visit,
        GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS 'browser',
        GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS 'os',
        GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS 'source'
FROM
        tracking t
GROUP BY
        t.user,
        t.date_visit;

/* Résultat :
Nico | 2014-10-26 18:10:00 | chromium | ubuntu | google
*/

Bingo ! nous avons totalement fait pivoter notre table. Dans notre cas, la condition sur la colonne "key" est relativement simple. Pour les cas plus complexes vous pourrez sans problème remplacer le IF par un CASE.

Notre table ne contient actuellement que 3 clés possibles, ce qui sera surement loin de la vérité dans un cas réel de production. Pour éviter de devoir écrire une longue et fastidieuse requête, nous allons faire en sorte de la construire dynamiquement. Malheureusement cette étape n'est pas non plus une partie de plaisir et il est impossible avec MySQL de le faire en une seule requête.

Nous souhaitons donc ne plus avoir besoin d'écrire nous même pour chaque clé possible la partie : GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser.

Voici une explication par étape : 

Construire dynamiquement les colonnes d'une table de pivot

SELECT CONCAT(
        'GROUP_CONCAT(IF(t.key = "',
        t.key ,
        '", t.value, NULL)) AS ',
        t.key
)
FROM tracking t;

/* Résultat :
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser
GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS os
GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS source
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser
etc...
*/


SELECT DISTINCT CONCAT(
        'GROUP_CONCAT(IF(t.key = "',
        t.key ,
        '", t.value, NULL)) AS ',
        t.key
)
FROM tracking t;

/* Résultat :
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser
GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS os
GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS source
*/

SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
                'GROUP_CONCAT(IF(t.key = "',
                t.key ,
                '", t.value, NULL)) AS ',
                t.key
        )
)
FROM tracking t;

/* Résultat :
GROUP_CONCAT(IF(t.key = "browser", t.value, NULL)) AS browser,
GROUP_CONCAT(IF(t.key = "os", t.value, NULL)) AS os,
GROUP_CONCAT(IF(t.key = "source", t.value, NULL)) AS source
*/

Il ne reste qu'à construire le reste de la requête.

La procédure complète

SET @COLUMNS = NULL;

/* Construit les colonnes à horizontaliser */
SELECT GROUP_CONCAT(
        DISTINCT CONCAT(
                'GROUP_CONCAT(IF(t.key = "',
                t.key ,
                '", t.value, NULL)) AS ',
                t.key
        )
) INTO @COLUMNS
FROM tracking t;

/* Construit la requête complète */
SET @SQL = CONCAT(
        'SELECT
                t.user,
                t.date_visit,
                ',@COLUMNS,'
        FROM
                tracking t
        GROUP BY
                t.user,
                t.date_visit'
);

/* Prépare et exécute la requête */
PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Si vous avez des problèmes pour tester cette requête avec un client type phpMyAdmin, essayez en ligne de commande ou en l'intégrant dans une procédure stockée.