PostgreSQL數據庫在SQL和NoSQL方面具有很多豐富的特性，本文將先從SQL高級特性入手來進行介紹。

一、PostgreSQL的SQL高級特性

這一部分主要介紹PostgreSQL在SQL方面的高級特性，例如WITH查詢、批量插入、RETURNING返回修改的數據、UPSERT、數據抽樣、聚合函數、窗口函數等。

1、WITH查詢

WITH查詢是PostgreSQL支持的高級SQL特性之一，這一特性常稱為CTE(Common Table Expressions)，WITH查詢在復雜查詢中定義一個輔助語句（可理解成在一個查詢中定義的臨時表），這一特性常用于復雜查詢或遞歸查詢應用場景。

先通過一個簡單的CTE示例了解WITH查詢，如下所示：

 

WITH t as (  
  SELECT generate_series(1,3)  
)  
SELECT * FROM t; 

執行結果如下：

 

generate_series  
-----------------  
                      1  
                      2  
                      3  
(3 rows) 

這個簡單的CTE示例中，一開始定義了一條輔助語句t取數，之后在主查詢語句中查詢t，定義的輔助語句就像是定義了一張臨時表，對于復雜查詢如果不使用CTE，可以通過創建視圖方式簡化SQL。

WITH查詢的一個重要屬性是RECURSIVE，使用RECURSIVE屬性可以引用自己的輸出，從而實現遞歸，一般用于層次結構或樹狀結構的應用場景。

例如，存在一張包含如下數據的表：

 

id name fatherid  
1 中國 0  
2 遼寧 1  
3 山東 1  
4 沈陽 2  
5 大連 2  
6 濟南 3  
7 和平區 4  
8 沈河區 4 

使用PostgreSQL的WITH查詢檢索ID為7以及以上的所有父節點，如下：

 

WITH RECURSIVE r AS (  
       SELECT * FROM test_area WHERE id = 7  
     UNION   ALL  
       SELECT test_area.* FROM test_area, r WHERE test_area.id = r.fatherid  
     )  
 SELECT * FROM r ORDER BY id; 

查詢結果如下：

 

id   |  name   | fatherid  
----+--------+----------  
  1  |   中國    |        0  
  2  |    遼寧   |        1  
  4  |    沈陽   |        2  
  7  |   和平區  |        4  
(4 rows) 

2、批量插入

批量插入是指一次性插入多條數據，主要用于提升數據插入效率，PostgreSQL有多種方法實現批量插入：

方式一：INSERT INTO..SELECT.

過表數據或函數批量插入，這種方式大部分關系數據庫都支持，語法如下：

INSERT INTO table_name SELECT …FROM source_table

方式二：INSERT INTO VALUES (),(),…()

這種批量插入方式為一條INSERT語句中通過VALUES關鍵字插入多條記錄，通過一個例子就很容易理解，如下所示：

 

mydb=> CREATE TABLE tbl_batch3(id int4,info text);  
CREATE TABLE  
 
mydb=> INSERT INTO tbl_batch3(id,info) VALUES (1,'a'),(2,'b'),(3,'c');  
INSERT 0 3 

數據如下：

 

mydb=> SELECT * FROM tbl_batch3;  
    id  | info  
  ----+------  
      1 | a  
      2 | b  
      3 | c  
(3 rows) 

這種批量插入方式非常獨特，一條SQL插入多行數據，相比一條SQL插入一條數據的方式能減少和數據庫的交互，減少數據庫WAL（Write-Ahead Logging）日志的生成，提升插入效率，通常很少有開發人員了解PostgreSQL的這種批量插入方式。

方式三：COPY或\COPY元命令

COPY或\COPY元命令能夠將一定格式的文件數據導入到數據庫中，相比INSERT命令插入效率更高，通常大數據量的文件導入一般在數據庫服務端主機通過PostgreSQL超級用戶使用COPY命令導入。

將文件tbl_batch4.txt的一千萬數據導入到表中，如下所示：

 

mydb=# TRUNCATE TABLE pguser.tbl_batch4;  
TRUNCATE TABLE 
 
mydb=# COPY pguser.tbl_batch4 FROM '/home/pg10/tbl_batch4.txt';  
COPY 10000000 

3、RETURNING返回修改的數據

PostgreSQL的RETURNING特性可以返回DML修改的數據，具體為以下三個場景 ，INSERT語句后接RETURNING屬性返回插入的數據，UPDATE語句后接RETURNING屬性返回更新后的新值，DELETE語句后接RETURNING屬性返回刪除的數據，這個特性的優點在于不需要額外的SQL獲取這些值，能夠方便應用開發，接著通過示例演示。

RETURNING返回插入的數據

INSERT語句后接RETURNING屬性返回插入的值，以下創建測試表，并返回已插入的整行數據。

 

mydb=> CREATE TABLE test_r1(id serial,flag char(1));  
CREATE TABLE  
mydb=> INSERT INTO test_r1(flag) VALUES ('a') RETURNING *;  
   id | flag  
----+------  
   1 | a  
(1 row)  
INSERT 0 1 

RETURNING返回更新后數據

UPDATE后接RETURNING屬性返回UPDATE語句更新后的值，如下所示：

 

mydb=> SELECT * FROM test_r1 WHERE id=1;  
   id | flag  
----+------  
    1 | a  
(1 row)  
 
mydb=> UPDATE test_r1 SET flag='p' WHERE id=1 RETURNING *;  
   id | flag  
----+------  
    1 | p  
(1 row)  
UPDATE 1 

RETURNING返回刪除的數據

DELETE后接RETURNING屬性返回刪除的數據，如下所示：

 

mydb=> DELETE FROM test_r1 WHERE id=2 RETURNING *;  
   id | flag  
----+------  
    2 | b  
(1 row)  
DELETE 1 

4、UPSERT

PostgreSQL的UPSERT特性是指INSERT ... ON CONFLICT UPDATE，用來解決在數據插入過程中數據沖突的情況，比如違反用戶自定義約束，日志數據應用場景通常在事務中批量插入日志數據，如果其中有一條數據違反表上的約束，則整個插入事務將會回滾，PostgreSQL的UPSERT特性可解決這一問題。

接下來通過例子來理解UPSERT的功能，定義一張用戶登錄日志表并插入一條數據，如下：

 

mydb=> CREATE TABLE user_logins(user_name text primary key,  
login_cnt int4,  
last_login_time timestamp(0) without time zone);  
CREATE TABLE  
 
mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('francs',1);  
INSERT 0 1 

在user_logins表user_name字段上定義主鍵，批量插入數據中如有重復會報錯，如下所示：

 

mydb=> INSERT INTO user_logins(user_name,login_cnt) VALUES ('matiler',1),('francs',1);  
ERROR:  duplicate key value violates unique constraint "user_logins_pkey"  
DETAIL:  Key (user_name)=(francs) already exists. 

上述SQL試圖插入兩條數據，其中matiler這條數據不違反主鍵沖突，而francs這條數據違反主鍵沖突，結果兩條數據都不能插入。PostgreSQL的UPSERT可以處理沖突的數據，比如當插入的數據沖突時不報錯，同時更新沖突的數據，如下所示：

 

mydb=> INSERT INTO user_logins(user_name,login_cnt)VALUES ('matiler',1),('francs',1)  
ON CONFLICT(user_name)      
DO UPDATE SET  
login_cnt=user_logins.login_cnt+EXCLUDED.login_cnt,last_login_time=now();  
INSERT 0 2 

上述INSERT語句插入兩條數據，并設置規則：

當數據沖突時更新登錄次數字段login_cnt值加1，同時更新最近登錄時間last_login_time，ON CONFLICT(user_name)定義沖突類型為user_name字段，DO UPDATE SET是指沖突動作，后面定義了一個UPDATE語句，注意上述SET命令中引用了user_loins表和內置表EXCLUDED，引用原表user_loins訪問表中已存在的沖突記錄，內置表EXCLUDED引用試圖插入的值，再次查詢表user_login，如下所示：

 

mydb=> SELECT * FROM user_logins ;  
 user_name | login_cnt |   last_login_time    
  -----------+-----------+---------------------  
     matiler   |              1 |  
      francs    |              2 | 2017-08-08 15:23:13  
    (2 rows) 

一方面沖突的francs這條數據被更新了login_cnt和last_login_time字段，另一方面新的數據matiler記錄已正常插入。

5、數據抽樣

數據抽樣（TABLESAMPLE）在數據處理方面經常用到，特別是當表數據量比較大時，隨機查詢表一定數量記錄很常見，PostgreSQL早在9.5版時就已經提供了TABLESAMPLE數據抽樣功能，9.5版前通常通過ORDER BY random()方式實現數據抽樣，這種方式雖然在功能上滿足隨機返回指定行數據，但性能很低，如下：

表user_ini數據量為100萬，從100萬隨機取一條上述SQL執行時間為367ms，這種方法走了全表掃描和排序，效率非常低，當表數據量大時，性能幾乎無法接受。

9.5版本以后PostgreSQL支持TABLESAMPLE數據抽樣，語法如下：

SELECT …

FROM table_name

TABLESAMPLE sampling_method ( argument [, ...] ) [ REPEATABLE ( seed ) ]

sampling_method指抽樣方法，主要有兩種：SYSTEM和BERNOULLI。接下來詳細介紹這兩種抽樣方式，argument指抽樣百分比。

SYSTEM抽樣方式

SYSTEM抽樣方式為隨機抽取表上數據塊上的數據，理論上被抽樣表的每個數據塊被檢索的概率是一樣的，SYSTEM抽樣方式基于數據塊級別，后接抽樣參數，被選中的塊上的所有數據將被檢索。

創建test_sample測試表，并插入150萬數據，抽樣因子設置成0.01，意味著返回1500000*0.01%=150條記錄，執行如下SQL：

以上執行計劃主要有兩點：

• 一方面走了Sample Scan掃描(抽樣方式為SYSTEM)，執行時間為0.166毫秒，性能較好；
• 另一方面優化器預計訪問150條記錄，實際返回107條。

BERNOULLI抽樣方式

BERNOULLI抽樣方式隨機抽取表的數據行，并返回指定百分比數據，BERNOULLI抽樣方式基于數據行級別，理論上被抽樣表的每行記錄被檢索的概率是一樣的，因此BERNOULLI抽樣方式抽取的數據相比SYSTEM抽樣方式具有更好的隨機性，但性能上相比SYSTEM抽樣方式低很多，下面演示下BERNOULLI抽樣方式，同樣基于test_sample測試表。

設置抽樣方式為BERNOULLI，抽樣因子為0.01，如下所示：

從以上執行計劃看出走了Sample Scan掃描（抽樣方式為BERNOULLI），執行計劃預計返回150條記錄，實際返回152條，從返回的記錄數來看，非常接近150條（1000000*0.01%），但執行時間卻要22.569毫秒，性能相比SYSTEM抽樣方式0.166毫秒差了136倍。

多次執行以下查詢，查看返回記錄數的變化，如下所示：

 

mydb=>  SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01);  
 count  
-------  
   151  
(1 row) 
 
 
mydb=>  SELECT count(*) FROM test_sample TABLESAMPLE BERNOULLI(0.01);  
 count  
-------  
   147  
(1 row) 

從以上看出，BERNOULLI抽樣方式返回的數據量非常接近抽樣數據的百分比，而SYSTEM抽樣方式數據返回以數據塊為單位，被抽樣的塊上的所有數據都被返回，因此SYSTEM抽樣方式的數據量返回的偏差較大。

這里演示了SYSTEM和BERNOULLI抽樣方式，SYSTEM抽樣方式基于數據塊級別，隨機抽取表數據塊上的記錄，因此這種方式抽取的記錄的隨機性不是很好，但返回的數據以數據塊為單位，抽樣性能很高，適用于抽樣效率優先的場景，例如抽樣大小為GB的日志表；而BERNOULLI抽樣方式基于數據行，相比SYSTEM抽樣方式所抽樣的數據隨機性更好，但性能相比SYSTEM差很多，適用于抽樣隨機性優先的場景，讀者可根據實際應用場景選擇抽樣方式。

6、聚合函數

聚合函數可以對結果集進行計算，常用的聚合函數有avg()、sum()、min()、max()、count()等，本節將介紹PostgreSQL兩個特殊功能的聚合函數并給出測試示例。

在介紹兩個聚合函數之前，先來看一個應用場景，假如一張表有以下數據，如下：

 

country  | city  
---------+------  
 中國      | 臺北  
 中國      | 香港  
 中國      | 上海  
 日本      | 東京  
 日本      | 大阪  
(5 rows) 

要求得到如下結果集：

 

中國       臺北，香港，上海  
日本       東京，大阪 

這個SQL大家想想如何寫？

string_agg函數

首先介紹string_agg函數，此函數語法如下：

string_agg(expression, delimiter)

簡單的說string_agg函數能將結果集某個字段的所有行連接成字符串，并用指定delimiter分隔符分隔，expression表示要處理的字符類型數據；參數的類型為(text, text) 或 (bytea, bytea)，函數返回的類型同輸入參數類型一致，bytea屬于二進制類型，使用情況不多，我們主要介紹text類型輸入參數，本節開頭的場景正好可以用string_agg函數處理。

將city字段連接成字符串如下：

 

mydb=> SELECT string_agg(city,',') FROM city;  
        string_agg          
--------------------------  
 臺北，香港，上海，東京，大阪  
(1 row) 

可見string_agg函數將輸出的結果集連接成了字符串，并用指定的逗號分隔符分隔，回到本文開頭的問題，通過以下SQL實現，如下所示：

 

mydb=> SELECT country,string_agg(city,',') FROM city GROUP BY country;  
 country |   string_agg    
---------+----------------  
   日本    | 東京，大阪  
   中國    | 臺北，香港，上海 

array_agg函數

array_agg函數和string_agg函數類似，最主要的區別為返回的類型為數組，數組數據類型同輸入參數數據類型一致，array_agg函數支持兩種語法，***種如下：

array_agg(expression)  --輸入參數為任何非數組類型 

輸入參數可以是任何非數組類型，返回的結果是一維數組，array_agg函數將結果集某個字段的所有行連接成數組，執行以下查詢：

 

mydb=> SELECT country,array_agg(city) FROM city GROUP BY country;  
 country |    array_agg       
---------+------------------  
   日本    | {東京,大阪}  
   中國    | {臺北,香港,上海} 

array_agg函數輸出的結果為字符類型數組，其他無明顯區別，使用array_agg函數主要優點在于可以使用數組相關函數和操作符。

7、窗口函數

PostgreSQL提供內置的窗口函數，例如row_num()、rank()、lag()等，除了內置的窗口函數外，聚合函數、自定義函數后接OVER屬性也可作為窗口函數。

窗口函數的調用語法稍復雜，如下所示：

function_name ([expression [, expression ... ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

其中window_definition語法如下：

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

[ frame_clause ]

• OVER表示窗口函數的關鍵字。
• PARTITON BY屬性對查詢返回的結果集進行分組，之后窗口函數處理分組的數據。
• ORDER BY屬性設定結果集的分組數據的排序。

row_number() 窗口函數

創建一張成績表并插入測試數據，如下所示：

 

CREATE TABLE score ( id serial primary key,  
                     subject character varying(32),  
                     stu_name character varying(32),  
                     score numeric(3,0) );  
 
INSERT INTO score ( subject,stu_name,score ) VALUES ('Chinese','francs',70);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('Chinese','matiler',70);  
INSERT INTO score ( subject,stu_name,score) VALUES ('Chinese','tutu',80);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('English','matiler',75);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('English','francs',90);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('English','tutu',60);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('Math','francs',80);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('Math','matiler',99);  
INSERT INTO score ( subject,stu_name,score ) VALUES ('Math','tutu',65); 

avg() OVER()窗口函數

聚合函數后接OVER屬性的窗口函數表示在一個查詢結果集上應用聚合函數，本小節將演示avg()聚合函數后接OVER屬性的窗口函數，此窗口函數用來計算分組后數據的平均值。

查詢每名學生學習成績并且顯示課程的平均分，通常是先計算出課程的平均分，之后score表再與平均分表關聯查詢，如下所示：

使用窗口函數很容易實現以上需求，如下所示：

以上查詢前三列來源于表score，第四列表示取課程的平均分，PARTITION BY subject表示根據字段subject進行分組。

rank()窗口函數

rank()窗口函數和row_number()窗口函數相似，主要區別為當組內某行字段值相同時，行號重復并且行號產生間隙（手冊上解釋為gaps），如下：

以上示例中，Chinese課程前兩條記錄的score字段值都為70，因此前兩行的rank字段值1，而第三行的rank字段值為3，產生了間隙。

dense_rank ()窗口函數

dense_rank ()窗口函數和rank ()窗口函數相似，主要區別為當組內某行字段值相同時，雖然行號重復，但行號不產生間隙（手冊上解釋為gaps），如下：

以上示例中，Chinese課程前兩行的rank字段值1，而第三行的rank字段值為2，沒有產生間隙。

PostgreSQL還支持很多其它內置窗口函數，例如、lag()、first_values()、last_values()等，篇幅關系不再介紹。

二、總結

本篇文章主要介紹了PostgreSQL支持的一些高級SQL特性，例如WITH查詢、批量插入、RETURNING返回DML修改的數據、UPSERT、數據抽樣、聚合函數、窗口函數等，了解這些功能能夠簡化SQL代碼，提升開發效率，并且實現普通查詢不容易實現的功能，希望通過閱讀本章，大家能夠在實際工作中應用SQL高級特性，同時挖掘PostgreSQL的其它高級SQL特性。

 

PostgreSQL不僅是關系型數據庫，同時支持NoSQL特性，關于PostgreSQL的NoSQL特性我們將在下一篇文章中介紹。