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

一、PostgreSQL的SQL高級(jí)特性

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

1、WITH查詢

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

先通過(guò)一個(gè)簡(jiǎn)單的CTE示例了解WITH查詢，如下所示：

 

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

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

 

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

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

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

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

 

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

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

 

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  |   中國(guó)    |        0  
  2  |    遼寧   |        1  
  4  |    沈陽(yáng)   |        2  
  7  |   和平區(qū)  |        4  
(4 rows) 

2、批量插入

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

方式一：INSERT INTO..SELECT.

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

INSERT INTO table_name SELECT …FROM source_table

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

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

 

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) 

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

方式三：COPY或\COPY元命令

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

將文件tbl_batch4.txt的一千萬(wàn)數(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ù)，具體為以下三個(gè)場(chǎng)景 ，INSERT語(yǔ)句后接RETURNING屬性返回插入的數(shù)據(jù)，UPDATE語(yǔ)句后接RETURNING屬性返回更新后的新值，DELETE語(yǔ)句后接RETURNING屬性返回刪除的數(shù)據(jù)，這個(gè)特性的優(yōu)點(diǎn)在于不需要額外的SQL獲取這些值，能夠方便應(yīng)用開發(fā)，接著通過(guò)示例演示。

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

INSERT語(yǔ)句后接RETURNING屬性返回插入的值，以下創(chuàng)建測(cè)試表，并返回已插入的整行數(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語(yǔ)句更新后的值，如下所示：

 

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

接下來(lái)通過(guò)例子來(lái)理解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ù)會(huì)報(bào)錯(cuò)，如下所示：

 

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ù)，比如當(dāng)插入的數(shù)據(jù)沖突時(shí)不報(bào)錯(cuò)，同時(shí)更新沖突的數(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語(yǔ)句插入兩條數(shù)據(jù)，并設(shè)置規(guī)則：

當(dāng)數(shù)據(jù)沖突時(shí)更新登錄次數(shù)字段login_cnt值加1，同時(shí)更新最近登錄時(shí)間last_login_time，ON CONFLICT(user_name)定義沖突類型為user_name字段，DO UPDATE SET是指沖突動(dòng)作，后面定義了一個(gè)UPDATE語(yǔ)句，注意上述SET命令中引用了user_loins表和內(nèi)置表EXCLUDED，引用原表user_loins訪問(wèn)表中已存在的沖突記錄，內(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)常用到，特別是當(dāng)表數(shù)據(jù)量比較大時(shí)，隨機(jī)查詢表一定數(shù)量記錄很常見，PostgreSQL早在9.5版時(shí)就已經(jīng)提供了TABLESAMPLE數(shù)據(jù)抽樣功能，9.5版前通常通過(guò)ORDER BY random()方式實(shí)現(xiàn)數(shù)據(jù)抽樣，這種方式雖然在功能上滿足隨機(jī)返回指定行數(shù)據(jù)，但性能很低，如下：

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

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

SELECT …

FROM table_name

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

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

SYSTEM抽樣方式

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

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

以上執(zhí)行計(jì)劃主要有兩點(diǎn)：

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

BERNOULLI抽樣方式

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

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

從以上執(zhí)行計(jì)劃看出走了Sample Scan掃描（抽樣方式為BERNOULLI），執(zhí)行計(jì)劃預(yù)計(jì)返回150條記錄，實(shí)際返回152條，從返回的記錄數(shù)來(lái)看，非常接近150條（1000000*0.01%），但執(zhí)行時(shí)間卻要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ù)塊級(jí)別，隨機(jī)抽取表數(shù)據(jù)塊上的記錄，因此這種方式抽取的記錄的隨機(jī)性不是很好，但返回的數(shù)據(jù)以數(shù)據(jù)塊為單位，抽樣性能很高，適用于抽樣效率優(yōu)先的場(chǎng)景，例如抽樣大小為GB的日志表；而BERNOULLI抽樣方式基于數(shù)據(jù)行，相比SYSTEM抽樣方式所抽樣的數(shù)據(jù)隨機(jī)性更好，但性能相比SYSTEM差很多，適用于抽樣隨機(jī)性優(yōu)先的場(chǎng)景，讀者可根據(jù)實(shí)際應(yīng)用場(chǎng)景選擇抽樣方式。

6、聚合函數(shù)

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

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

 

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

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

 

中國(guó)       臺(tái)北，香港，上海  
日本       東京，大阪 

這個(gè)SQL大家想想如何寫？

string_agg函數(shù)

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

string_agg(expression, delimiter)

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

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

 

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

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

 

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

array_agg函數(shù)

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

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

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

 

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

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

7、窗口函數(shù)

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

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

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

其中window_definition語(yǔ)法如下：

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

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

[ frame_clause ]

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

row_number() 窗口函數(shù)

創(chuàng)建一張成績(jī)表并插入測(cè)試數(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ù)表示在一個(gè)查詢結(jié)果集上應(yīng)用聚合函數(shù)，本小節(jié)將演示avg()聚合函數(shù)后接OVER屬性的窗口函數(shù)，此窗口函數(shù)用來(lái)計(jì)算分組后數(shù)據(jù)的平均值。

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

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

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

rank()窗口函數(shù)

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

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

dense_rank ()窗口函數(shù)

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

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

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

二、總結(jié)

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

 

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