SQL 資料庫 - PostgreSQL

PostgreSQL Crosstab -- (V)

交叉表二
－－－－－－－－－－－－－－－－－－－

SELECT *
FROM crosstab(
  'select currency, currType, purpose, rate From exgRate',
  'Select distinct purpose from exgRate' )
  AS t(
   currency char(3),
   currType varchar(10),
   "買入" numeric(18,6),
   "賣出" numeric(18,6)
   ) order by currType, currency

看起來，資料表還需要進一步的資料整理，不過功能是OK了

PostgreSQL Crosstab -- (IV)

接下來，就是以輸入的基礎資料表，測試crosstab函式的功能。
crosstab有兩道SQL，第一道稱為Source SQL，指定資料的來源；第二道稱為Category SQL，指定crosstab要由那個欄位延伸展開。

在這個例子中，我們將資料依不同幣別，進行展開。

交叉表一
－－－－－－－－－－－－－－－－－－－
SELECT *
FROM crosstab(
  'select currType, purpose, currency, rate From exgRate',
  'Select distinct currency from exgRate Order by currency' )
  AS t(
   currType varchar(10),
   purpose varchar(10),
   AUD numeric(18,6),
   CAD numeric(18,6),
   GBP numeric(18,6),
   HKD numeric(18,6),
   USD numeric(18,6)
   )
order by currType;

PostgreSQL Crosstab -- (III)

接下來要建個測試資料，我就以之前提到的匯率應用做例子，建個匯率表exgRate

建資料表的SQL如下

Create Table exgRate (
   exgRateId serial primary key, -- 主鍵
   factTime timestamp, 　 -- 匯入時間
   currency char(3), 　　 -- 貨幣別
   purpose varchar(2), 　 -- 匯率種類
   currType varchar(10), 　 -- 貨幣種類
   rate numeric(18,6) 　 -- 匯率
);

插入資料的SQL如下：
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','買入','現金',31.08);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','買入','現金',3.911);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','買入','現金',46.51);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','買入','現金',28.24);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','買入','現金',30.27);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','買入','即期',31.37);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','買入','即期',4.014);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','買入','即期',47.36);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','買入','即期',28.45);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','買入','即期',30.53);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','賣出','現金',31.62);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','賣出','現金',4.087);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','賣出','現金',48.44);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','賣出','現金',28.95);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','賣出','現金',31.08);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'USD','賣出','即期',31.49);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'HKD','賣出','即期',4.084);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'GBP','賣出','即期',47.86);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'AUD','賣出','即期',28.71);
insert into exgRate (factTime, currency, purpose, currType, rate) values(to_timestamp('2010-05-01 09:00:00','YYYY-MM-DD HH24:MI:SS'),'CAD','賣出','即期',30.81);

比較有趣的是這道SQL指令中，用到to_timestamp的函數。因為匯率是不斷更新變動的，所以我們必需記錄匯率輸入的時間（精確到秒），這樣在換匯時，才能知道要用什麼時候的匯率。

PostgreSQL Crosstab -- (II)

PostgreSQL的預設安裝，是沒有啟用crosstab功能的。如果你需要crosstab，你需要

1. 檢查函式庫
檢查一下C:\Program Files\PostgreSQL\8.4\lib目錄中，有沒有名為tablefunc.dll的函式庫檔。

2. 裝載tablefunc功能
開啟　C:\Program Files\PostgreSQL\8.4\share\contrib目錄，找到名為tablefunc.sql的檔案，執行它。

3. 檢查資料庫的函式
如下圖，如果安裝成功，你應該多了11個資料庫的函式

其它平台的安裝，我就不細講了，相信能用其它平台的人，也應該有能力照貓畫虎才是。

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套件，支援交叉表的實作。

資料庫移植性　移植什麼？

有個同行跟我說，他們家的系統移植性很棒，DB2, Oracle, MS SQL每個資料庫都可以搭

我心裡在說，那是你沒碰上要一秒內要處理上百萬上千萬筆資料的狀況，沒碰上那種連IBM大暴龍都搞不定的運算，等再過五年，你再來說說這話。

實際上，你就是會碰上這種操資料庫，就是會碰上需要那種需要某牌資料庫特異功能的應用，碰上這種東西，資料庫移植性，就變成一句笑話。

讓我們面對現實吧，鳥托邦並不存在，我們不能保證完美的資料庫移植性。我們應該要考慮的是，我的系統要支援幾個資料庫?　為什麼? 通常，考慮的因素是

一、商業的需求
客戶已經講的很清楚，他們家就是用DB2 Oracle，維護人員受的訓也都是DB2, Oracle。如果你不用，那最好你們家的系統強悍無比，Domain knowledge比客戶還精，客戶不找你不行，要不然，接不到案子不要怪別人。

二、功能的需求
如果客戶熟悉的資料庫，功能不符合你系統的需求。像是你就是需要某牌資料庫的特異功能，才能把他要的功能或報表跑出來，那你可以堅持，要不然，還是別太鐵齒。

三、預算的需求
這就更簡單了，付錢的是老大，老大要什麼，我們就給什麼。如果老大沒錢，那更簡單，我們就是老大，就用PostgreSQL吧，畢竟系統原生就是在PostgreSQL環境發展的。

資料庫移植性，這其實是一個角力的問題，其實終端客戶才不管你用什麼資料庫，只是有時候你需要去符合客戶IT部門的某些政策，某些規定。碰上這種東西，我都是叫業務先去談上線，再談移植計劃。先把系統裝進去，符合客戶的需求，終端客戶用上手了，我們再來談資料庫移植。

而在大多的的狀況下，移植資料庫要花時間改程式，要花時間測試，更重要的，是終端客戶也要花時間確認沒有問題，客戶會願意花時間去做這些沒用的功嗎？有這些時間，不如幫他多寫一隻新功能，滿足他一個新需求，資料庫移植這件事，自然就無疾而終了。

但是，這種作法，有時候也會碰上鐵板，特別是在應用很成熟的領域裡。在月度工作會議裡，終端客戶的部門沒有新需求提出，IT部門就會把這件事拉出來講。我的作法是，就配合嘛，反正這領域沒有新功能好寫了，那就排進度做移植，擴充支援的資料庫，也是增強這個產品線的競爭力，要不然，客戶簽維護是讓我們大眼看小眼嗎? 總要有個交待吧，是吧 !!

PostgreSQL 9.0出來了, So What ??

PostgreSQL 9.0出來了, 那又怎麼樣 ??

最近PostgreSQL 9.0出來了，大家都十分的興奮，看了看，零零總總共２００個修改，其中有許多是許多人想很久的，像是64位元視窗平台的支援，支援Python 3等。

不過，就我個人而言，我想說的是「那又怎樣？」

我開發的平台是Java，配合Hibernate建立的ORM映射層，比較關心的是後端PL/pgSQL的功能有沒有新發展。9.0 的出現，並沒有什麼殺手級的新功能，讓我一定要馬上昇級到9.0，也就是說9.0並沒有大幅改變我的工作。

相反的，9.0 為我帶來不必要的昇級負擔。年輕的時候，一有新版本出來，總會想要馬上昇級到最新版本，但是，年紀漸長後，卻出現不同的思考。

對我來說，發展的是ERP進銷存之類的商用軟體，如果不是在開發功能時需要新版的新功能，我為什麼要昇級？客戶端已佈署好、運作正常的系統，為什麼要昇級? 為了爽嗎？不必吧!! 就算客戶的IT部門提到，我總會問，為什麼要昇級，是現在的功能不符需求，需要新版本的新功能嗎？客戶買的是一個系統，不是買一個資料庫引擎。如果是系統的需求，客戶不提，我也會主動去幫客戶昇級，如果不是，昇級過程發生的風險，是我負責，還是客戶自己扛？

狀況很明顯，昇級是要走的，但是什麼時候走，必需由我決定。

根據我的經驗，新版本剛出現的時候，絕對不是該昇級的時候。我會等到9.2, 9.4, 9.6，甚至是10.0出現的時候，再針對9.X系列，挑一個穩定的平台出來做移植，並進行所有功能的測試。至於現在，並不是我會考慮昇級的時間，再等一兩年，這一兩年，好好測測新功能，才是該做的事情。

絕對..絕對..不要趕流行，可以玩玩新功能，可以利用新功能，想想系統可以加什麼賣點，但是絕對不要為了昇級而昇級。這是一個老工程師的肺腑之言。