Ordre naturel des mois (2)

Je travaille dans un terminal psql connecté à ma base de données PostgreSQL bdtest.
La table utilisée s'appelle bcard, mais tout ce qui suit est valable quelle que soit la table pourvu que celle-ci contienne une colonne date et une colonne numérique.
Si date_exec est une date, to_char(date_exec, 'TMMonth') affichera une chaîne de caractère contenant le nom du mois de cette date. Une clause 'Order by date_exec' dans un query SQL provoquera l'apparition du nom des mois dans leur ordre naturel.
Si on effectue un regroupement par mois, une telle clause n'a aucun sens et l'exécution du query donnera une erreur.
Pour avoir les mois dans le bon ordre, on peut utiliser la fonction to_char(date_exec, 'MM TMMonth'): chaque mois sera alors précédé de son numéro d'ordre.
Je veux faire disparaître ce numéro. Je peux alors utiliser awk où la fonction SQL substr (voir l'article précédent)
Un query ressemblant à celui-ci

select substr(to_char(date_exec, 'MM TMMonth'), 4) AS mois, 
sum (montant) AS total
from bcard
where extract(year from date_exec) = 2013
group by to_char(date_exec, 'MM TMMonth')
order by to_char(date_exec, 'MM TMMonth')

donnera donc le résultat escompté.

Cependant si je désire avoir aussi les différents montants qui constituent le total de chaque mois avec un query tel que celui-ci:

select substr(to_char(date_exec, 'MM TMMonth'), 4) AS mois, 
sum (montant) AS total
from bcard
where extract(year from date_exec) = 2013
UNION
select substr(to_char(date_exec, 'MM TMMonth'), 4), 
sum (montant)
from bcard
where extract(year from date_exec) = 2013
group by to_char(date_exec, 'MM TMMonth')
order by to_char(date_exec, 'MM TMMonth')

je rencontre de nouveau une erreur:

ERROR:  column "bcard.date_exec" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select substr(to_char(date_exec, 'MM TMMonth'), 4) AS mois, 
                              ^

A moi awk!

Après avoir enlevé les 'substr', je complète mon query en ajoutant une colonne pour montrer les dates des opérations ainsi qu'une colonne qui contiendra les mentions 'SOUS-TOTAL' et 'TOTAL' car une troisième instruction 'SELECT' me fournira le total général.

Au final le query est le suivant:

select to_char(date_exec, 'MM TMMonth') AS mois,
'' as " ", date_exec::text,
montant
from bcard
where extract(year from date_exec) = 2013
UNION
select to_char(date_exec, 'MM TMMonth'),
'SOUS-TOTAL', '',
sum (montant) AS total
from bcard
where extract(year from date_exec) = 2013
group by 1, 2, 3
UNION
select '99', 'TOTAL', '', sum(montant)
from bcard
order by 1, 2, 3

Les colonnes correspondantes des différents 'select' doivent avoir le même type de données. Si la colonne 3 du premier 'select' contient des dates, il doit en être de même pour les autres 'select'. Ce n'est évidemment pas le cas et c'est pourquoi dans la colonne 3 du premier 'select' les dates sont transformées en texte.
L'exécution pure et dure de ce query  donne (dans le cas d'un cadre de type ascii, ce qui s'obtient avec \pset linestyle a et \pset border 2):

Pour faire disparaître les numéros d'ordre il suffit de faire précéder l'exécution de

\o | awk '{print t=substr($0, 1, 2)substr($0, 6)}'

et ensuite pour rediriger l'output vers la sortie standard:

\o

(voir l'article précédant)

Mais je veux plus: je veux que les noms des mois soient visibles seulement dans les rangées 'SOUS-TOTAL'.

Cette fois je dois faire précéder l'exécution des instructions SQL de

\o | awk -f fichier.awk

où fichier.awk contient:

{

 if ($0 ~ /[A-Z]/ && $0 !~/SOUS/)

    {
     if ($3 ~ /[éû]/) 

        $0=substr($0, 1, 5)"         "substr($0, 16)

     else

        $0=substr($0, 1, 5)"         "substr($0, 15)
    }

 t=substr($0, 1, 2)substr($0, 6)    

 print t

}

Et voilà ce que ça donne:

Quelques explications sur le code awk:
t contient la ligne lue ($0) dont on a retiré les caractères 3, 4, 5.
Pour faire disparaître le nom des mois,  les caractères 6 à 14 de $0 sont, le cas échéant, remplacés par des espaces.
Certains noms de mois comprennent des caractères (é et û) qui sont codés sur 2 octets. Dans ce cas la reprise s'effectue à l'octet 16 au lieu de 15.
Prenons un exemple:

| 09 Septembre | 
12345678901234567890
| 02 Février   |
1234567901234567890

Dans le premier cas, l'espace devant la deuxième séparation se trouve à l'octet 15, mais à l'octet 16 dans l'autre cas car le 'é' de Février occupe les octets 7 et 8.

Je veux aussi que les rangées avec le mot  'SOUS-TOTAL' soient séparées des autres, ce qui peut être obtenu avec un fichier.awk contenant:

NR==3 {y=substr($0,1, 2)substr($0, 6)}

{

 if ($0 ~ /[A-Z]/ && $0 !~/SOUS/)

    {
     if ($3 ~ /[éû]/) 

        $0=substr($0, 1, 5)"         "substr($0, 16)

     else

        $0=substr($0, 1, 5)"         "substr($0, 15)
    }

 t=substr($0, 1, 2)substr($0, 6)

 if ($0 ~ /SOUS/) 

    {print y;print t;print y}

 else    

    print t

}

En ligne 3, on sauvegarde la ligne de séparation qui sera imprimée avant et après les lignes "sous-total".

Et voilà:

.....

Si on utilise un style de ligne du type unicode (après exécution de \pset linestyle u), le fichier.awk précédent doit être adapté:

NR==3 {y=substr($0,1, 3)substr($0, 13)}
{
 if ($0 ~ /[A-Z]/ && $0 !~/SOUS/)
    {
     if ($3 ~ /[éû]/) 
        $0=substr($0, 1, 7)"         "substr($0, 18)
     else
        $0=substr($0, 1, 7)"         "substr($0, 17)
    }
 if ($0 !~/[A-Za-z0-9]/)
    t=substr($0,1, 3)substr($0, 13)
 else
    t=substr($0, 1, 4)substr($0, 8)
 if ($0 ~ /SOUS/) 
    {print y;print t;print y}
 else    
    print t
}

Les caractères du cadre sont cette fois codés sur 3 octets: enlever 3 morceaux de ligne du cadre = enlever 9 octets.
Dans le traitement il faut distinguer les lignes 100% cadre ($0 !~/[A-Za-z0-9]/) des autres.
(Un exemple beaucoup plus simple est développé dans l'article précédent )

Ce qui donne:

Rappelons que le résultat peut-être envoyé dans un fichier avec:

\o | awk -fichier.awk > fichier.txt

Ordre naturel des mois

J'affiche à l'aide d'une instruction SQL exécutée dans un terminal psql, le contenu de deux colonnes de ma table bcard appartenant à la base de données PostgreSQL bdtest:

A la place de la date, je veux voir en clair le nom du mois. De plus je m'intéresse seulement à l'année 2013.
J'adapte mon query en conséquence:

Évidemment les mois ne sont pas dans l'ordre. J'ajoute une clause 'order' pour résoudre le problème:

En fait, ça ne résout rien du tout: les mois sont maintenant classés de manière alphabétique.
La clause 'order' doit porter sur la date:

Maintenant ça fonctionne. Sauf que si on veut un groupement par mois afin d'avoir des totaux mensuels on obtient une erreur:

ERROR:  column "bcard.date_exec" must appear in the GROUP BY clause or be used in an aggregate function
LINE 5: order by date_exec
                 ^
Une solution serait de faire précéder chaque nom de mois par son numéro d'ordre:

Il nous est loisible de faire disparaître ces fichus numéros en traitant l'output par awk:

(on a précédemment exécuté la méta commande \pset footer off)

La méta commande \o envoie les résultats du query vers awk via le tube |.
$0 désigne la ligne reçue par awk, qui imprime donc toutes les lignes à partir de la position 4 .
La méta commande \o sans argument spécifié envoie le flux sortant produit par awk vers la sortie standard.
Le ';' doit être placé avant cette méta commande afin que le query soit exécuté alors que l'output est encore envoyé vers awk.

Si la résultats sont encadrés (suite à la méta commande \pset border 2), il faut adapter le traitement par awk:

Le script awk est cette fois:

'{print substr($0, 1, 2)substr($0, 6)}'

On imprime le caractère du cadre puis un espace, ensuite 3 caractères sont sautés avant l'impression de la fin de chaque ligne.
Mais je veux un plus bel output: je lance la méta commande \pset linestyle u. Les caractères du cadres sont cette fois codés sur 3 octets!
Ce qui implique plus de complication. Il faut maintenant lors du traitement par awk, distinguer les lignes qui contiennent des données des lignes 100% cadre.
Je mets le script awk dans un fichier dont voici le contenu:
{
if ($0 ~ /[a-z]/) 
   t=substr($0, 1, 4)substr($0, 8)
else 
   t=substr($0, 1, 3)substr($0, 13)
}
{print t}

Les lignes avec des données contiennent forcément un caractère alphabétique.
J'imprime d'abord le caractère du cadre (3 octets) + 1 espace, ensuite je saute 3 caractères (3 octets).
Pour les lignes 100% cadre, j'imprime d'abord un caractère (3 octets), ensuite je saute 3 caractères, soit 9 octets avant d'imprimer la suite:

(Et oui le fichier awk dont il est question s'appelle qmois6u2.awk)

Que de complications, alors que dans le cas simple ici traité il suffit d'exécuter le query

select substr(to_char(date_exec, 'MM TMMonth'), 4) AS mois, 
sum (montant) AS total
from bcard
where extract(year from date_exec) = 2013
group by to_char(date_exec, 'MM TMMonth')
order by to_char(date_exec, 'MM TMMonth')

qui fait appel à la fonction substr de SQL:

Cependant le traitement par awk présenté ici est utile dans d'autre cas plus complexes.

Travailler avec LibreOffice évite aussi pas mal de soucis.
Il faut bien évidemment que le fichier bdtest.odb qui permet la connexion à la base de données bdtest existe. Celui-ci est à créer dans LibreOffice Base.
Ensuite j'ouvre LibreOffice Calc et clique sur l'icône adéquate :

Avant de sélectionner la source:

Ensuite il reste à glisser ce qui convient là où ça convient :

Je ne veux pas créer de tableau croisé donc je ne mets rien dans 'Champs de colonne'.

Je sélectionne l'ensemble des dates dans le tableau qui surgit suite à un appui sur OK:

CTRL-1 me permet alors de choisir le format dans lequel ces dates seront affichées.
Procédant de même je peux également choisir le format d’affichage des montants.

Une date étant sélectionnée, un appui sur F12 donne la possibilité d'effectuer différents groupements :

Je choisis un groupement par Années et par Mois, ce qui donne:

Je veux éliminer l'année 2014:

Je clique droit sur le tableau et dans le menu contextuel qui surgit, je sélectionne : Éditer la mise en page :

Puis je double clique sur Années

Et via le bouton 'Options', je peux alors éliminer l'année 2014.
Et voilà le travail:

Notons que je pourrais facilement avoir des sous-totaux par année si telle était mon intention.

Expressions régulières dans SQL

J’ai déjà traité plusieurs fois de l’utilisation d’expressions régulières dans des instructions SQL, et notamment dans ce billet.
Je vais cette fois étudier le problème de manière systématique.
Pour quelques explications concernant les expressions régulières, voir ce billet.
Dans la table PostgreSQL dépenses (déjà rencontrée ici mais le contenu a changé depuis lors), je recherche les rangées dont la colonne détails contient le mot 'jardin'.
Travaillant dans un terminal psql, je lance l’instruction qui convient, utilisant la fonction regexp_matches :

bdtest=> select référence, montant, détails,
bdtest-> regexp_matches(détails, 'jardins')
bdtest-> from dépenses
bdtest-> order by référence
bdtest-> ;
 référence | montant |       détails       | regexp_matches 
-----------+---------+---------------------+----------------
 2013-0014 |  103,00 | Docteur Desjardins  | {jardins}
 2013-0023 |   40,00 | Station des jardins | {jardins}
 2017-0045 |  173,24 | Les jardins arborés | {jardins}
(3 rows)

Très bien mais une clause
where détails like '%jardins%'
aurait aussi bien fait l’affaire sauf que regexp_matches donne la possibilité d'utiliser des expressions régulières offrant donc plus de souplesse.
Ainsi si je remplace 'jardins' par '\yjardins\y', la rangée 2013-0014 va disparaître du résultat car dans une expression régulière \y indique une bordure de mot.
(Les bordures de mot sont habituellement indiquées par \b, mais pas selon le standard suivi par PostgreSQL)
La fonction substring permet elle aussi l'utilisation d'expressions régulières, mais elle nécessite l'utilisation d'une clause where si on veut exclure du résultat les rangées qui ne satisfont pas au critère.
Exemple :

select référence, montant, détails,
substring(détails, '\yjardins\y')
from dépenses
where substring(détails, '\yjardins\y') is not null
order by référence
;
 référence | montant |       détails       | substring 
-----------+---------+---------------------+-----------
 2013-0023 |   40,00 | Station des jardins | jardins
 2017-0045 |  173,24 | Les jardins arborés | jardins
(2 rows)

A l'inverse avec regexp_matches, il faut utiliser un subselect pour afficher toutes les rangées :

bdtest=> select référence, montant, détails,
bdtest-> (select regexp_matches(détails, '\yjardins\y'))
bdtest-> from dépenses
bdtest-> order by référence
bdtest-> ;
 référence | montant |       détails        | regexp_matches 
-----------+---------+----------------------+----------------
 2013-0014 |  103,00 | Docteur Desjardins   | 
 2013-0015 |   56,47 | Courrefar            | 
 2013-0016 |   49,56 | Station des camélias | 
 2013-0017 |  117,23 | Caro                 | 
 2013-0018 |   43,00 | Docteur Lebon        | 
 2013-0019 |  245,74 | La marmite           | 
 2013-0020 |   24,53 | Phie Arcodor         | 
 2013-0021 |  104,25 | Aupré                | 
 2013-0022 |   68,17 | Courrefar            | 
 2013-0023 |   40,00 | Station des jardins  | {jardins}
 2014-0016 |   87,23 | Caro                 | 
 2014-0017 |   30,00 | Docteur Ledoux       | 
 2014-0018 |  149,57 | La bonne auberge     | 
 2014-0019 |   50,00 | Station Les cafés    | 
 2017-0045 |  173,24 | Les jardins arborés  | {jardins}
(15 rows)

Recherchons les mots de 7 lettres minuscules :

bdtest=> select référence, montant, détails,
bdtest-> regexp_matches(détails, '\y[[:lower:]]{7}\y', 'g')
bdtest-> from dépenses
bdtest-> order by référence
bdtest-> ;
 référence | montant |       détails       | regexp_matches 
-----------+---------+---------------------+----------------
 2013-0019 |  245,74 | La marmite          | {marmite}
 2013-0023 |   40,00 | Station des jardins | {jardins}
 2014-0018 |  149,57 | La bonne auberge    | {auberge}
 2017-0045 |  173,24 | Les jardins arborés | {jardins}
 2017-0045 |  173,24 | Les jardins arborés | {arborés}
(5 rows)

Je rappelle que \y indique une bordure de mots. Celui-ci doit comporter 7 caractères de la classe [[:lower:]]. Nous utilisons cette classe plutôt que la classe [a-z] qui ne comprend pas les caractères accentués.
L’option facultative ‘g’ indique à la fonction regexp_matches d’afficher toutes les occurrences du motif recherché et non pas seulement la première. C’est pourquoi la rangée '2017-0045' apparaît deux fois.
La donnée renvoyée par la fonction regexp_matches est une donnée de type 'array' contenant une ou plusieurs données de type texte.
Mettons cela en évidence :

bdtest=> select référence, montant, détails,
bdtest-> regexp_matches(détails, '\y([[:lower:]]{3})([[:lower:]]{4})\y')
bdtest-> from dépenses
bdtest-> order by référence
bdtest-> ;
 référence | montant |       détails       | regexp_matches 
-----------+---------+---------------------+----------------
 2013-0019 |  245,74 | La marmite          | {mar,mite}
 2013-0023 |   40,00 | Station des jardins | {jar,dins}
 2014-0018 |  149,57 | La bonne auberge    | {aub,erge}
 2017-0045 |  173,24 | Les jardins arborés | {jar,dins}
(4 rows)

Le motif est découpé en deux à l'aide de parenthèses ce qui conduit à des tableaux à deux éléments.
De telles parenthèses (dites mémorisantes) sont utiles quand on envisage de procéder au remplacement de la chaîne trouvée. Dans la chaîne de remplacement, \1, \2, …, correspondent aux différents morceaux de la chaîne d'origine qui sont entre parenthèses.
Illustrons le en utilisant la fonction regexp_replace :

bdtest=> select référence, montant,
bdtest-> regexp_replace(détails, '\y([[:lower:]]{3})([[:lower:]]{4})\y', '\2\1', 'g')
bdtest-> from dépenses
bdtest-> order by référence
bdtest-> ;
 référence | montant |    regexp_replace    
-----------+---------+----------------------
 2013-0014 |  103,00 | Docteur Desjardins
 2013-0015 |   56,47 | Courrefar
 2013-0016 |   49,56 | Station des camélias
 2013-0017 |  117,23 | Caro
 2013-0018 |   43,00 | Docteur Lebon
 2013-0019 |  245,74 | La mitemar
 2013-0020 |   24,53 | Phie Arcodor
 2013-0021 |  104,25 | Aupré
 2013-0022 |   68,17 | Courrefar
 2013-0023 |   40,00 | Station des dinsjar
 2014-0016 |   87,23 | Caro
 2014-0017 |   30,00 | Docteur Ledoux
 2014-0018 |  149,57 | La bonne ergeaub
 2014-0019 |   50,00 | Station Les cafés
 2017-0045 |  173,24 | Les dinsjar orésarb
(15 rows)

regexp_replace renvoie une chaîne inchangée si le motif n'est pas trouvé.
L'option facultative 'g' a la même signification qu'auparavant. En son absence, on aurait pour la rangée '2017-0045' le texte : 'Les dinsjars arborés'.

Je vais maintenant donner un exemple pratique d'utilisation de ces fonctions.
Considérons une table (par exemple mvts) reprenant les mouvements effectués sur un compte en banque. La banque fournit quantité de données (date, montant etc), mais pas directement le numéro de compte de la contrepartie. Aussi quand de nouvelles rangées sont créées lors de l'importation des données fournies, les éléments de la colonne 'compte' prévue pour contenir le numéro de compte sont null ou vides. Cependant ce numéro de compte se trouve parfois perdu au milieu du texte informatif se trouvant dans un champ souvent appelé 'détails', jamais à la même place et parfois sous des formats différents.
L' instruction SQL

select référence, détails, regexp_matches(détails, 'FR[0-9]{2} [0-9]{4} [0-9]{4} [0-9]{4}')
from mvts
where compte is null
or compte = ''

permet de repérer certains de ces comptes.
En image :

Il reste à mettre le champ compte à jour, mais il ne faut pas oublier que le retour de regexp_matches n'est pas du texte. Il faut d'abord le transformer. L'instruction update est donc :

update mvts
set compte = substr(regexp_matches(détails, 'FR[0-9]{2} [0-9]{4} [0-9]{4} [0-9]{4}')::text, 3, 19)
where compte is null
or compte = ''

Il n'y a pas de mise à jour si le motif n'est pas rencontré.

On pourrait utiliser la fonction substring à condition d'adapter la clause where afin d'éviter les mises à jour indésirables:

update mvts
set compte = substring(détails,'FR[0-9]{2} [0-9]{4} [0-9]{4} [0-9]{4}')
where (compte is null
or compte = '')
and substring(détails,'FR[0-9]{2} [0-9]{4} [0-9]{4} [0-9]{4}')
is not null

Les comptes figurant dans 'détails' peuvent se trouver sous un autre format.
L'instruction pour les retrouver doit être adaptée :

select référence, détails, regexp_matches(détails, 'FR[0-9]{14}')
from mvts
where compte is null
or compte = ''

En images le résultat:

L'instruction 'update' est elle aussi modifiée :

update mvts
set compte = substr(regexp_matches(détails, 'FR[0-9]{14}')::text, 2, 16)
where compte is null
or compte = ''

Le problème est que maintenant les comptes se trouvent dans la table mvts sous deux formats différents.
Qu'à cela ne tienne : la fonction regexp_replace nous permet de remettre tout ça en place :

update mvts
set compte = regexp_replace(compte, '(FR[0-9]{2})([0-9]{4})([0-9]{4})([0-9]{4})', '\1 \2 \3 \4')
where compte <> regexp_replace(compte, '(FR[0-9]{2})([0-9]{4})([0-9]{4})([0-9]{4})', '\1 \2 \3 \4')

Le motif est scindé en 4 morceaux à l'aide de parenthèses mémorisantes. Dans la chaîne de remplacement ces morceaux (\1, \2, ...) sont séparées par des espaces. La clause where empêche les mises à jour inutiles (compte inchangé) : en effet regexp_replace retourne le compte d'origine si le motif n'est pas trouvé.

Les numéros de compte peuvent se trouver sous d'autres formats que ceux indiqués dans ces exemples.
Il faut évidemment adapter les expressions régulières en conséquence.

Si on utilise une table 'comptes' qui comprend la clef primaire 'compte', il reste à insérer des rangées pour les comptes de mvts qui n'existent pas dans cette table. Ceci peut se faire avec l'instruction :

insert into comptes
(compte)
select distinct a.compte
from mvts a natural left outer join comptes b
where b.compte is null
and a.compte > ''

Lorsque la colonne a.compte contient une valeur qui n'existe pas dans la table comptes, le système joint à la rangée de la table mvts une rangée fictive de la table comptes dont tous les champs sont à la valeur NULL (pour des informations sur les différents type de join voir ici).
La clause where permet donc d'afficher la liste des comptes présents dans mvts et qui n'existent pas dans la table comptes.

Pour être complet, signalons l'existence des deux fonctions regex_split_to_array et regexp_split_to_table. L'expression régulière sert alors de délimitateur.

Illustrons :

Dans ces instructions la fonction regexp_replace de la clause where est inopérante (la chaîne de remplacement pourrait être n'importe quoi) : elle sert simplement à éliminer les rangées sans le mot 'des' dans détails car les fonctions regexp_split retournent toujours quelque chose même si l'expression régulière n'est pas trouvée.

Montrons le:

Ces fonctions n'admettent pas les options du genre 'g'.

Note: on pourrait dans les 2 premières des 4 instructions précédentes utiliser

where substring(détails, '\ydes\y') is not null

au lieu de

where détails <> regexp_replace(détails, '\ydes\y', '')

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)