Linux, Base de données Postgresql, développement, Internet, emailing et déliverabilité.

Aller au contenu | Aller au menu | Aller à la recherche

PostgreSQL : Affichez vos données en ligne avec Crosstab

Quel client ne vous a pas demandé un jour de lui présenter des statistiques mensuelles de ses données ? Souvent il fallait faire les calculs, obtenir des agrégations et coder une interface de présentation qui prenait des résultats en colonne pour les remettre en ligne. Laborieux, souvent source d'erreur et de perte de temps cette méthode n'avait pas tellement d'équivalent directement en base de données. La solution est apparue depuis quelques release de PostgreSQL, il suffit de savoir manipuler l'extension Crosstab.

Prenons un exemple simple. Vous avez un table de données qui contient des contrats, et pour chacun d'eux vous avez des volumes d'affaires répartis par date de transaction. Comme par exemple :

contract_code | revenue | transaction_date
--------------+---------+-----------------
123456        | 123.00  | 2011-04-01
123456        | 456.00  | 2011-05-25
etc.

Normalement, pour faire votre statistique vous procédiez de la façon suivante :

SELECT contract_code AS row_name,
            SUM(revenue) OVER (PARTITION BY contract_code) AS total,
            TO_CHAR('2010-12-01'::date + (EXTRACT(month FROM transaction_date) || ' month')::interval, 'mon') AS cat
  FROM contract
  ORDER BY contract_code;

Ce qui donne le résultat suivant :

 row_name |   total   | cat
 ---------+-----------+-----
  123456  |     74.00 | jan
  123456  |    123.00 | jan
  123456  |    147.00 | feb
  123456  |    587.00 | feb
  123456  |    100.00 | mar
  123456  |     57.00 | mar
etc.

C'est donc maintenant que l'extension Crosstab de PostgreSQL va entrer en jeu.

Pour cela il faut au préalable se préparer les colonnes d'affichage nécessaire pour la mise en ligne des données, par exemple dans notre cas en générant la liste des mois de l'année :

SELECT to_char('2010-12-01'::date + (n || ' month')::interval, 'mon') As short_mname
  FROM generate_series(1,12);

Qui donne simplement le résultat suivant :

 short_mname 
-------------
 jan
 feb
 mar
 apr
 may
 jun
 jul
 aug
 sep
 oct
 nov
 dec

Puis monter votre requête en appelant l'extension Crosstab qui va faire la fonction de pivot entre vos données brutes de travail et votre table des mois afin de les afficher horizontalement :

SELECT *
   FROM crosstab ('
     SELECT contract_code AS row_name,
            SUM(revenue) OVER (PARTITION BY contract_code) AS total,
            TO_CHAR('2010-12-01'::date + (EXTRACT(month FROM transaction_date) || ' month')::interval, 'mon') AS cat
  ORDER BY contract_code',
     'SELECT
         to_char(''2010-12-01''::date + (n || '' month'')::interval, ''mon'') As short_mname
     FROM generate_series(1,12) n') as (code text, total numeric, jan numeric, feb numeric, mar numeric,
                                        apr numeric, may numeric, jun numeric, jul numeric, aug numeric,
                                        sep numeric, oct numeric, nov numeric, dec numeric)
ORDER BY total DESC
   LIMIT 1;

Vous obtenez donc directement le résultat suivant :

   code  |   total   | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec
 --------+-----------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
  123456 |   2041.00 | 197 | 734 | 157 | 354 | 247 | 172 |     |     |     |     |     |

Il ne vous reste plus qu'à prendre les données pour les afficher simplement dans votre tableau statistique, et le tour est joué !
Un bon gain de temps pour tout le monde !

Commentaires

1. Le vendredi, 26 mars 2021, 06:57 par www.pcb.its.dot.gov

I blog quite often and I truly thank you for your information. The article has truly peaked my interest.
I am going to take a note of your website and keep checking for new details
about once per week. I subscribed to your Feed too.

Ajouter un commentaire

Le code HTML est affiché comme du texte et les adresses web sont automatiquement transformées.

La discussion continue ailleurs

URL de rétrolien : https://www.footcow.com/index.php/trackback/103

Fil des commentaires de ce billet