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

一、PostgreSQL的SQL高級特性

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

1、WITH查詢

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

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

 

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

執(zhí)行結(jié)果如下：

 

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

這個簡單的CTE示例中，一開始定義了一條輔助語句t取數(shù)，之后在主查詢語句中查詢t，定義的輔助語句就像是定義了一張臨時表，對于復(fù)雜查詢?nèi)绻皇褂肅TE，可以通過創(chuàng)建視圖方式簡化SQL。

WITH查詢的一個重要屬性是RECURSIVE，使用RECURSIVE屬性可以引用自己的輸出，從而實現(xiàn)遞歸，一般用于層次結(jié)構(gòu)或樹狀結(jié)構(gòu)的應(yīng)用場景。

例如，存在一張包含如下數(shù)據(jù)的表：

 

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

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

 

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; 

查詢結(jié)果如下：

 

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

2、批量插入

批量插入是指一次性插入多條數(shù)據(jù)，主要用于提升數(shù)據(jù)插入效率，PostgreSQL有多種方法實現(xiàn)批量插入：

方式一：INSERT INTO..SELECT.

過表數(shù)據(jù)或函數(shù)批量插入，這種方式大部分關(guān)系數(shù)據(jù)庫都支持，語法如下：

INSERT INTO table_name SELECT …FROM source_table

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

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

 

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 

數(shù)據(jù)如下：

 

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

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

方式三：COPY或\COPY元命令

COPY或\COPY元命令能夠?qū)⒁欢ǜ袷降奈募?shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，相比INSERT命令插入效率更高，通常大數(shù)據(jù)量的文件導(dǎo)入一般在數(shù)據(jù)庫服務(wù)端主機通過PostgreSQL超級用戶使用COPY命令導(dǎo)入。

將文件tbl_batch4.txt的一千萬數(shù)據(jù)導(dǎo)入到表中，如下所示：

 

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

3、RETURNING返回修改的數(shù)據(jù)

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

RETURNING返回插入的數(shù)據(jù)

INSERT語句后接RETURNING屬性返回插入的值，以下創(chuàng)建測試表，并返回已插入的整行數(shù)據(jù)。

 

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返回更新后數(shù)據(jù)

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返回刪除的數(shù)據(jù)

DELETE后接RETURNING屬性返回刪除的數(shù)據(jù)，如下所示：

 

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，用來解決在數(shù)據(jù)插入過程中數(shù)據(jù)沖突的情況，比如違反用戶自定義約束，日志數(shù)據(jù)應(yīng)用場景通常在事務(wù)中批量插入日志數(shù)據(jù)，如果其中有一條數(shù)據(jù)違反表上的約束，則整個插入事務(wù)將會回滾，PostgreSQL的UPSERT特性可解決這一問題。

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

 

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字段上定義主鍵，批量插入數(shù)據(jù)中如有重復(fù)會報錯，如下所示：

 

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試圖插入兩條數(shù)據(jù)，其中matiler這條數(shù)據(jù)不違反主鍵沖突，而francs這條數(shù)據(jù)違反主鍵沖突，結(jié)果兩條數(shù)據(jù)都不能插入。PostgreSQL的UPSERT可以處理沖突的數(shù)據(jù)，比如當插入的數(shù)據(jù)沖突時不報錯，同時更新沖突的數(shù)據(jù)，如下所示：

 

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語句插入兩條數(shù)據(jù)，并設(shè)置規(guī)則：

當數(shù)據(jù)沖突時更新登錄次數(shù)字段login_cnt值加1，同時更新最近登錄時間last_login_time，ON CONFLICT(user_name)定義沖突類型為user_name字段，DO UPDATE SET是指沖突動作，后面定義了一個UPDATE語句，注意上述SET命令中引用了user_loins表和內(nèi)置表EXCLUDED，引用原表user_loins訪問表中已存在的沖突記錄，內(nèi)置表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這條數(shù)據(jù)被更新了login_cnt和last_login_time字段，另一方面新的數(shù)據(jù)matiler記錄已正常插入。

5、數(shù)據(jù)抽樣

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

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

9.5版本以后PostgreSQL支持TABLESAMPLE數(shù)據(jù)抽樣，語法如下：

SELECT …

FROM table_name

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

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

SYSTEM抽樣方式

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

創(chuàng)建test_sample測試表，并插入150萬數(shù)據(jù)，抽樣因子設(shè)置成0.01，意味著返回1500000*0.01%=150條記錄，執(zhí)行如下SQL：

以上執(zhí)行計劃主要有兩點：

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

BERNOULLI抽樣方式

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

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

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

多次執(zhí)行以下查詢，查看返回記錄數(shù)的變化，如下所示：

 

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抽樣方式返回的數(shù)據(jù)量非常接近抽樣數(shù)據(jù)的百分比，而SYSTEM抽樣方式數(shù)據(jù)返回以數(shù)據(jù)塊為單位，被抽樣的塊上的所有數(shù)據(jù)都被返回，因此SYSTEM抽樣方式的數(shù)據(jù)量返回的偏差較大。

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

6、聚合函數(shù)

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

在介紹兩個聚合函數(shù)之前，先來看一個應(yīng)用場景，假如一張表有以下數(shù)據(jù)，如下：

 

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

要求得到如下結(jié)果集：

 

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

這個SQL大家想想如何寫？

string_agg函數(shù)

首先介紹string_agg函數(shù)，此函數(shù)語法如下：

string_agg(expression, delimiter)

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

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

 

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

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

 

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

array_agg函數(shù)

array_agg函數(shù)和string_agg函數(shù)類似，最主要的區(qū)別為返回的類型為數(shù)組，數(shù)組數(shù)據(jù)類型同輸入?yún)?shù)數(shù)據(jù)類型一致，array_agg函數(shù)支持兩種語法，***種如下：

array_agg(expression)  --輸入?yún)?shù)為任何非數(shù)組類型 

輸入?yún)?shù)可以是任何非數(shù)組類型，返回的結(jié)果是一維數(shù)組，array_agg函數(shù)將結(jié)果集某個字段的所有行連接成數(shù)組，執(zhí)行以下查詢：

 

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

array_agg函數(shù)輸出的結(jié)果為字符類型數(shù)組，其他無明顯區(qū)別，使用array_agg函數(shù)主要優(yōu)點在于可以使用數(shù)組相關(guān)函數(shù)和操作符。

7、窗口函數(shù)

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

窗口函數(shù)的調(diào)用語法稍復(fù)雜，如下所示：

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表示窗口函數(shù)的關(guān)鍵字。
• PARTITON BY屬性對查詢返回的結(jié)果集進行分組，之后窗口函數(shù)處理分組的數(shù)據(jù)。
• ORDER BY屬性設(shè)定結(jié)果集的分組數(shù)據(jù)的排序。

row_number() 窗口函數(shù)

創(chuàng)建一張成績表并插入測試數(shù)據(jù)，如下所示：

 

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()窗口函數(shù)

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

查詢每名學(xué)生學(xué)習(xí)成績并且顯示課程的平均分，通常是先計算出課程的平均分，之后score表再與平均分表關(guān)聯(lián)查詢，如下所示：

使用窗口函數(shù)很容易實現(xiàn)以上需求，如下所示：

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

rank()窗口函數(shù)

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

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

dense_rank ()窗口函數(shù)

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

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

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

二、總結(jié)

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

 

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