PostgreSQL Crosstab -- (I)

我準備用長一點的篇幅來講講Crosstab。Crosstab的中文，在postgresql中一般翻做「交叉表」，而微軟系列一般翻做「樞紐分析表」「樞軸表」，我個人喜歡交叉表。

那什麼是Crosstab呢? 簡單講，如果我現在有一個匯率表如下，
－－－－－－－－－－－－－－－－－－－－－
貨幣種類  匯率種類      AUD  CAD  GBP  HKD  USD
即期      　買入    　      28.45 30.53 47.36 4.014 31.37
即期      　賣出        　  28.71 30.81 47.86 4.084 31.49
現金      　買入          　28.24 30.27 46.51 3.911 31.08
現金      　賣出          　28.95 31.08 48.44 4.087 31.622

我在資料庫中的資料設計是不是就照著看到的做就好？很明顯的答案是NO，舉例來說，如果明天要支援的貨幣多一個日元JPY呢? 那是不是要去alter table 變更資料表的設計??
換一步說，明天，可能人家要的資料會變這樣
－－－－－－－－－－－－－－－－－－－－－
貨幣種類  幣別  買入 賣出
即期      AUD  28.45 28.71
即期      CAD  30.53 30.81
即期      GBP  47.36 47.86
即期      HKD  4.014 4.084
即期      USD  31.37 31.49
現金      AUD  28.24 28.95
現金      CAD  30.27 31.08
現金      GBP  46.51 48.44
現金      HKD  3.911 4.087
現金      USD  31.08 31.622

甚至可能是這樣，同時還要多支援「遠期10天」「遠期30天」，兩個貨幣種類

－－－－－－－－－－－－－－－－－－－－－

匯率種類  幣別  即期 現金    

買入      AUD  28.45 28.24   

買入      CAD  30.53 30.27   

買入      GBP  47.36 46.51   

買入      HKD  4.014 3.911   

買入      USD  31.37 31.08   

賣出      AUD  28.71 28.95   

賣出      CAD  30.81 31.08   

賣出      GBP  47.86 48.44   

賣出      HKD  4.084 4.087   

賣出      USD  31.49 31.622  

哇，又要人仰馬翻去重新整理一次嗎？我們有更聰明的解法，資料庫的設計，應該如下，把所有可能變顯示欄位的資料項，變成一個個欄位，之後再採用Crosstab的方式，去動態產生所有的資料表。

------------------------------------------------------------
幣別     匯率種類 貨幣種類 匯率
 USD     買入     現金    31.08
 HKD     買入     現金    3.911
 GBP     買入    現金    46.51
 AUD     買入    現金    28.24
 CAD     買入    現金    30.27
 USD     買入    即期    31.37
 HKD     買入    即期    4.014
 GBP     買入    即期    47.36
 AUD     買入    即期    28.45
 CAD     買入    即期    30.53
 USD     賣出    現金    31.62
 HKD     賣出    現金    4.087
 GBP     賣出    現金    48.44
 AUD     賣出    現金    28.95
 CAD     賣出    現金    31.08
 USD     賣出    即期    31.49
 HKD     賣出    即期    4.084
 GBP     賣出    即期    47.86
 AUD     賣出    即期    28.71
 CAD     賣出    即期    30.81

這樣子，前面三個表，就是最後這個「資料表」的「交叉表」，PostgreSQL以tablefunc套件，支援交叉表的實作。