PostgreSQL : Affichez vos données en ligne avec Crosstab
Par footcow le dimanche 11 septembre 2011, 23:18 - PostgreSQL - Lien permanent
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
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.