Tableau croisé dans terminal psql

Pour le fun nous avons essayé de reproduire, dans un terminal postgresql, le tableau:

(voir ce billet)

Commençons par afficher par exemple la cellule (cela, avr.) avec

select distinct contrepartie, (select  sum(solde) 
from opérationsv 
where contrepartie = 'cela'
and   an = '2013'
and   mois_n = '04') AS "Avril"
from opérationsv
where contrepartie = 'cela'

Modifions l'instruction SQL de manière telle que la condition contrepartie =  'cela' n'apparaisse qu'une seule fois (dans la requête principale). Dans le même ordre d'idée, transportons la condition an = '2013' de la sous-requête à la requête principale':

select distinct contrepartie, (select  sum(solde) 
from opérationsv B
where B.contrepartie = A.contrepartie
and   B.an = A.an
and   mois_n = '04') AS "Avril"
from opérationsv A
where an = '2013'
and contrepartie = 'cela'

Ces modifications ne changent en rien le résultat obtenu par exécution de l'instruction, c'est-à-dire l'affichage de la cellule (cela, avr.).

Pour compléter la colonne "Avril", il suffit d'enlever la dernière condition:

Il manque le total de la colonne avril. Ajoutons le:

select contrepartie, (select  sum(solde) 
from opérationsv B
where B.contrepartie = A.contrepartie
and   B.an = A.an
and   mois_n = '04') AS "Avril"
from opérationsv A
where an = '2013'
UNION
select 'ZZ-Total', (select  sum(solde) 
from opérationsv B
where  B.an = A.an
and   mois_n = '04')
from opérationsv A
where an = '2013'
order by 1

(la clause UNION rend le distinct superflu)

Pourquoi 'ZZ-Total'? Simplement pour que l'order by le place en dernière position.
Il reste à ajouter les colonnes pour les autres mois, la colonne 'Total' (avec une sous-requête du même genre que celles qui donnent les colonnes mois) et à améliorer le look de l'output.

Bref, voici le fichier bilancroisé.sql qui sera exécuté dans un terminal psql connecté à la base de données bdtest:

\set QUIET
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title 'Bilan Croisé 2013'
\pset border 2
\o | awk 'NR==4 {y=$0};/ZZ-TOTAL/ {print y};1'
select contrepartie AS "Usage"
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '01') as "Janvier "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '02') as "Février "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '03') as "  Mars  "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '04') as "  Avril "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '05') as "   Mai  "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an) as "Total"
from opérationsv A
where an = '2013'
UNION
select 'ZZ-TOTAL'
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '01') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '02') 
, (select sum(solde)
     from opérationsv B                     
                      where   B.an = A.an
                      and   mois_n = '03') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '04') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '05') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an) 
from opérationsv A
where an = '2013'
order by 1
;
\pset title
\pset border 1
\unset QUIET
\o

L'output est amélioré grâce aux instructions awk que voici:

1. NR==4 {y=$0}
2. /ZZ-TOTAL/ {print y}
3. 1

Instruction 1: la ligne de séparation (la 4ième ligne) est sauvegardée en y
Instruction 2: si la ligne lue par awk comprend 'ZZ-TOTAL': impression de la ligne de séparation
Instruction 3: la condition 1 est toujours vraie, donc toutes les lignes lues sont imprimées (action par défaut).

Et voici le résultat:

Le tableau est déjà prêt pour recevoir les données du mois de Mai.
Pour les autres mois il faudra modifier le SQL en conséquence.
On peut également construire le tableau à l'envers avec les instructions (contenues dans bilancroisé2.sql):

\set QUIET
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title 'Bilan Croisé 2013'
\pset border 2
\o | awk 'NR==4 {y=$0};/TOTAL/ {print y};1' | tee bilancroisé2.txt
select mois_n || ' ' || mois AS "Mois"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Autre') AS "Autre"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'ceci') AS "ceci"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'cela') AS "cela"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Div') AS "Div"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'GS') AS "GS"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Loyer') AS "Loyer"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'RN1') AS "RN1"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'RN2') AS "RN2"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Tél') AS "Tél"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'TV') AS "TV"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'VOIT') AS "VOIT"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an) AS "Total"
from opérationsv a
where an = '2013'
UNION
select 'TOTAL'
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Autre')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'ceci')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'cela')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Div')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'GS')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Loyer')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'RN1')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'RN2')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Tél')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'TV')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'VOIT')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an)
from opérationsv a
where an = '2013'
;
\pset title
\pset border 1
\pset footer on
\unset QUIET
\o

Ce qui donne:

Pour autant que les postes de dépenses et de recettes ne changent pas, le SQL ne doit pas être adapté en cas de nouvelles opérations en 2013: les mois s'ajouteront automatiquement.

(Pour la définition de la table et les données voir la fin de l'article précédent)