2015第15周日PostgreSQL學習

標籤：

英文版官網地址：http://www.postgresql.org/

上面顯示的最新版本資訊是PostgreSQL 9.4.1, 9.3.6, 9.2.10, 9.1.15 & 9.0.19 Released!中文版社區地址：http://www.postgres.cn/home 顯示2014/12/18: 全球開發組宣布業界領先的開源關聯式資料庫PostgreSQL9.4版發布，這個版本新增了許多的功能，來適應不同的資料庫 使用者靈活性、延展性和效能提升的要求，包括對JSON 資料的支援改進，增強了複製和索引的效能。

• 功能
  • PostgreSQL 擁有大型商用資料庫最多的功能，例如：事務，子查詢，觸發器，視圖，外鍵參考完整性和複雜的鎖定等。 我們還有一些它們沒有的特性，如使用者定義型別，繼承，規則和多版本並行控制以減少鎖的爭用等。

• 效能
  • PostgreSQL和其他商用或開源的資料庫具有類似的效能。對某些資料處理快一些，對其他一些處理慢一些。 與其他資料庫相比，我們的效能優劣通常在 +/- 10%之間。

• 可靠性
  • 我們都知道資料庫必須是可靠的，否則它就一點用都沒有。我們努力做到發布經過認真測試的，缺陷最少的穩定代碼。每個版本至少有一個月的 beta 測試時間，並且我們的發布曆史顯示我們可以提供穩定的，牢固的，可用於生產使用的版本。我們相信在這方面我們與其他的資料庫軟體是相當的。

• 支援
  • 我們的郵件清單提供一個非常大的開發人員和使用者的組以協助解決所碰到的任何問題。我們不能保證總是能解決問題，相比之下，商用資料庫軟體也並不是總能夠提供解決方案。 直接與開發人員、使用者群、手冊和來源程式接觸使PostgreSQL的支援比其他資料庫還要好。還有一些商業性的全面支援人員，可以給提供給那些需要的人。（參閱1.7 小節）

• 價格
  • 我們對任何用途都免費，包括商用和非商用目的。 你可以不加限制地向你的產品裡加入我們的代碼，除了那些我們在上面的著作權聲明裡聲明的 BSD著作權之外的內容。 

有三個主要方面可以提升PostgreSQL的潛能。

• 查詢方式的變化，這主要涉及修改查詢方式以擷取更好的效能:

• • 建立索引，包括運算式和部分索引；
  • 使用COPY語句代替多個Insert語句；
  • 將多個SQL語句組成一個事務以減少提交事務的開銷；
  • 從一個索引中提取多條記錄時使用CLUSTER；
  • 從一個查詢結果中取出部分記錄時使用LIMIT；
  • 使用先行編譯式查詢（Prepared Query)；
  • 使用ANALYZE以保持精確的最佳化統計；
  • 定期使用 VACUUM 或 pg_autovacuum
  • 進行大量資料更改時先刪除索引（然後重建索引）

• 伺服器的配置
  • 設定檔postgres.conf中的很多設定都會影響效能，所有參數的列表可見：管理員指南/資料庫伺服器運行環境/資料庫伺服器回合組態， 有關參數的解釋可見：http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html 和 http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html。

• 硬體的選擇
  • 電腦硬體對效能的影響可瀏覽 http://candle.pha.pa.us/main/writings/pgsql/hw_performance/index.html 和 http://www.powerpostgresql.com/PerfList/。

相比MySQL，PostgreSQL並沒有什麼剛性的缺點，但是它的純免費特徵確實是MySQL無法比擬的。它完全可以成為軟體產品解決方案中非常重要的一環，特別是針對很多中低端使用者，不僅在使用中不會感到任何差異，而且還能節省一部分額外的費用，這對於產品的市場推廣是非常有協助的。
學習手冊：http://www.cnblogs.com/stephen-liu74/archive/2012/06/08/2315679.html 將多列轉成1列

在實際應用中常常遇見這樣的情況，見下表，我們現在需要統計出來每年每個人的工資總和以及發放月份。

user_name	year	month	money
張三	2011	1	900
張三	2011	2	1200
張三	2011	5	1100
張三	2011	6	1300
李四	2011	1	1100
李四	2011	3	1200

即我們要得到下面表的結果

user_name	year	monthes	total money
張三	2011	1,2,5,6	4500
李四	2011	1,3	2300

首先我們想到的是有這樣的SQL語句：
SELECT user_name,year,myfunction(month) AS monthes, SUM(money) AS total_money FROM table GROUP BY user_name,year;

像count或者sum一樣吧資料表中的某個欄位按照需要拼接起來，但是在PostgreSQL好像沒有直接的方法來實現（或許有筆者沒有發現，正因為沒有發現所以筆者才研究了半天得到下面的心得）。

方法一：在PostgreSQL中如果不自訂函數直接用SQL來實現

SELECT t1.user_name, t1.year, ARRAY_TO_STRING(array(SELECT t2.month FROM table t2 WHERE t2.user_name=t1.user_name AND  t2.year=t1.year), ‘,’)  AS monthes, SUM(t1.money) AS total_money FROM table t1 WHERE 1 GROUP BY t1.user_name, t1.year;

這裡主要是用了PostgreSQL的內建數組函數array_to_string把數組拼接成字串。

PostgreSQL的ARRAY_AGG函數是用來串連到一個數組中的輸入值，包括空

SELECT ARRAY_AGG(SALARY) FROM COMPANY;結果{20000,65000,85000,45000,10000}  

SELECT UNNSET(ARRAY_AGG(SALARY)) FROM COMPANY函數可將結果還原為多行情況

 

2015第15周日PostgreSQL學習