El Blog de Trespams

Suposem que tenim una base de dades de factures i algú ens demana a veure si li podem dir quina és la facturació total de cada any i la seva distribució en els diferents mesos. Això és el que s'anomena fer un crosstab report, és a dir, fer la transformació de dades que estan en files a columnes i normalment fer una operació damunt alguna quantitat numèrica d'interès.

Al blog d'Stephen Forte he vist un mètode que ens permet fer precisament això d'una manera elegant anomenat el mètode Rozenshtein. Stephen diu que n'està enamorat d'aquest mètode i és ben comprensible, el mètode és elegant i resol d'una manera senzilla el tema dels crosstabs reports a bases de dades sql. L'Stephen ho explica molt bé, però us resumiré la idea, es tracta de trobar una expressió numèrica que s'avalui com a zero o un que ens farà de discriminador de la columna, de manera que multiplicada per la quantitat que volem operar fa que aquesta es compti (quan l'expressió val un) o que no.

Per no repetir l'exemple de Stephen en posaré un de més senzill, suposem que tenim una taula on hem anat guardant els e-mails que hem rebut on tenim la data en que l'hem rebut. Volem saber quants e-mails hem rebut cada mes. A la taula mantenim un camp anomenat created_at que conté la data en que hem rebut l'e-mail. Podríem fer

select extract(year
from created_at) as any, count(id) as total, sum(1 - abs(sign(extract(month
from created_at)-1))) as "GEN", sum(1 - abs(sign(extract(month
from created_at)-2))) as "FEB", sum(1 - abs(sign(extract(month
from created_at)-3))) as "MAR", sum(1 - abs(sign(extract(month
from created_at)-4))) as "ABR", sum(1 - abs(sign(extract(month
from created_at)-5))) as "MAY", sum(1 - abs(sign(extract(month
from created_at)-6))) as "JUN", sum(1 - abs(sign(extract(month
from created_at)-7))) as "JUL", sum(1 - abs(sign(extract(month
from created_at)-8))) as "AGO", sum(1 - abs(sign(extract(month
from created_at)-9))) as "SEP", sum(1 - abs(sign(extract(month
from created_at)-10))) as "OCT", sum(1 - abs(sign(extract(month
from created_at)-11))) as "NOV", sum(1 - abs(sign(extract(month
from created_at)-12))) as "DIC"
from taula_emails group by 1

Això és un cas particular del mètode Rozenshtein, ja que com es pot veure el que feim realment és comptar registres. Sense fer les simplificacions cada més hauria quedat com

sum(1*(1 - abs(sign(extract(month from created_at)-MES)))) as "EL_MES"

El primer un correspon a la dada que volem operar de la fila, en el nostre cas és tan simple com dir que és 1 per a que la suma vagi comptant-les, però pot ser qualsevol operació que hi puguem fer. El mètode ho podem classificar d'idea feliç, però una vegada captat és prou senzill de fer anar. En aquest cas el que s'ha fet és extreure l'ordinal del mes i restar-li l'ordinal que correspon a la columna del mes que volem tractar. Aquesta operació ens donarà zero si la data de la fila que tractam és igual a la columna del mes o diferent de zero si no ho és. Per exemple,

extract(month from now()) = 11

ja que aquest post està escrit al novembre del 2007, si repassam l'sql veurem que els resultats qute tendríem per gener seria (11-1 = 10), per febrer (11-2 = 9), per octubre (11-10 = 1) per novembre (11-11 = 0 aja!) i per desembre 11-12 = -1.

Recordem que el que necessitam és un valor que sigui zero o un, i per això el que es fa és utilitzar la funció sign, que retorna -1 per valors negatius, 1 per valors positius i 0 pel zero.

Si després li aplicam el valor absolut (abs) resulta que hem aconseguit una funció que ens dona zero quan té el valor que nosaltres volem i un quan no el té, la restam d'un tenim just el contrari, és a dir, que valdrà un quan tengui el valor desitjat i zero quan no el tengui.

Aquesta funció és la que multiplicarem pel valor a operar, en el nostre cas 1 però podria ser perfectament el valor d'una altra columna. I ja ho tenim!

any total GEN FEB MAR ABR MAY JUN JUL AGO SEP OCT NOV DEC 2007 100 10 10 19 20 5 15 8 5 2 5 1 0