引言
PostgreSQL資料庫在SQL和NoSQL方面具有很多豐富的特性，本文将從
《PostgreSQL實戰》一書的“ 第4章 SQL進階特性”中摘選部分内容介紹。
這一部分主要介紹PostgreSQL在SQL方面的進階特性，例如WITH查詢、批量插入、RETURNING傳回修改的資料、UPSERT、資料抽樣、聚合函數、視窗函數。
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) 批量插入
批量插入是指一次性插入多條資料，主要用于提升資料插入效率，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 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 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記錄已正常插入。
資料抽樣
資料抽樣（TABLESAMPLE）在資料處理方面經常用到，特别是當表資料量比較大時，随機查詢表一定數量記錄很常見，PostgreSQL早在9.5版時就已經提供了TABLESAMPLE資料抽樣功能，9.5版前通常通過ORDER BY random()方式實作資料抽樣，這種方式雖然在功能上滿足随機傳回指定行資料，但性能很低，如下：
mydb=> EXPLAIN ANALYZE SELECT * FROM user_ini ORDER BY random() LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------
Limit (cost=25599.98..25599.98 rows=1 width=35) (actual time=367.867..367.868 rows=1 loops=1)
-> Sort (cost=25599.98..28175.12 rows=1030056 width=35) (actual time=367.866..367.866 rows=1 loops=1)
Sort Key: (random())
Sort Method: top-N heapsort Memory: 25kB
-> Seq Scan on user_ini (cost=0.00..20449.70 rows=1030056 width=35) (actual time=0.012..159.569 rows=1000000 loops=1)
Planning time: 0.083 ms
Execution time: 367.909 ms
(7 rows) 表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。
mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE SYSTEM(0.01);
QUERY PLAN
----------------------------------------------------------------------------------
Sample Scan on test_sample (cost=0.00..3.50 rows=150 width=45) (actual time=0.099..0.146 rows=107 loops=1)
Sampling: system ('0.01'::real)
Planning time: 0.053 ms
Execution time: 0.166 ms
(4 rows) 以上執行計劃主要有兩點，一方面走了Sample Scan掃描(抽樣方式為SYSTEM)，執行時間為0.166毫秒，性能較好，另一方面優化器預計通路150條記錄，實際傳回107條。
BERNOULLI抽樣方式
BERNOULLI抽樣方式随機抽取表的資料行，并傳回指定百分比資料，BERNOULLI抽樣方式基于資料行級别，理論上被抽樣表的每行記錄被檢索的機率是一樣的，是以BERNOULLI抽樣方式抽取的資料相比SYSTEM抽樣方式具有更好的随機性，但性能上相比SYSTEM抽樣方式低很多，下面示範下BERNOULLI抽樣方式，同樣基于test_sample測試表。
設定抽樣方式為BERNOULLI，抽樣因子為0.01，如下所示。
mydb=> EXPLAIN ANALYZE SELECT * FROM test_sample TABLESAMPLE BERNOULLI (0.01);
QUERY PLAN
----------------------------------------------------------------------------------
Sample Scan on test_sample (cost=0.00..14020.50 rows=150 width=45) (actual time=0.025..22.541 rows=152 loops=1)
Sampling: bernoulli ('0.01'::real)
Planning time: 0.063 ms
Execution time: 22.569 ms
(4 rows) 從以上執行計劃看出走了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差很多，适用于抽樣随機性優先的場景，讀者可根據實際應用場景選擇抽樣方式。
聚合函數
聚合函數可以對結果集進行計算，常用的聚合函數有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函數主要優點在于可以使用數組相關函數和操作符。
視窗函數
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); row_number()視窗函數對結果集分組後的資料标注行号，從1開始，如下。
mydb=> SELECT row_number() OVER (partition by subject ORDER BY score desc),* FROM score;
row_number | id | subject | stu_name | score
------------+----+---------+----------+-------
1 | 3 | Chinese | tutu | 80
2 | 1 | Chinese | francs | 70
3 | 2 | Chinese | matiler | 70
1 | 5 | English | francs | 90
2 | 4 | English | matiler | 75
3 | 6 | English | tutu | 60
1 | 8 | Math | matiler | 99
2 | 7 | Math | francs | 80
3 | 9 | Math | tutu | 65
(9 rows) 以上row_number()視窗函數顯示的是分組後記錄的行号，如果不指定partition屬性，row_number()視窗函數顯示表所有記錄的行号，類似oracle裡的ROWNUM，如下。
mydb=> SELECT row_number() OVER (ORDER BY id) AS rownum ,* FROM score;
rownum | id | subject | stu_name | score
--------+----+---------+----------+-------
1 | 1 | Chinese | francs | 70
2 | 2 | Chinese | matiler | 70
3 | 3 | Chinese | tutu | 80
4 | 4 | English | matiler | 75
5 | 5 | English | francs | 90
6 | 6 | English | tutu | 60
7 | 7 | Math | francs | 80
8 | 8 | Math | matiler | 99
9 | 9 | Math | tutu | 65
(9 rows) avg() OVER()視窗函數
聚合函數後接OVER屬性的視窗函數表示在一個查詢結果集上應用聚合函數，本小節将示範avg()聚合函數後接OVER屬性的視窗函數，此視窗函數用來計算分組後資料的平均值。
查詢每名學生學習成績并且顯示課程的平均分，通常是先計算出課程的平均分，之後score表再與平均分表關聯查詢，如下所示：
mydb=> SELECT s.subject, s.stu_name,s.score, tmp.avgscore
FROM score s
LEFT JOIN (SELECT subject, avg(score) avgscore FROM score GROUP BY subject) tmp
ON s.subject = tmp.subject;
subject | stu_name | score | avgscore
---------+----------+-------+---------------------
Chinese | francs | 70 | 73.3333333333333333
Chinese | matiler | 70 | 73.3333333333333333
Chinese | tutu | 80 | 73.3333333333333333
English | matiler | 75 | 75.0000000000000000
English | francs | 90 | 75.0000000000000000
English | tutu | 60 | 75.0000000000000000
Math | francs | 80 | 81.3333333333333333
Math | matiler | 99 | 81.3333333333333333
Math | tutu | 65 | 81.3333333333333333
(9 rows) 使用視窗函數很容易實作以上需求，如下所示：
mydb=> SELECT subject,stu_name, score, avg(score) OVER(PARTITION BY subject) FROM score;
subject | stu_name | score | avg
---------+----------+-------+---------------------
Chinese | francs | 70 | 73.3333333333333333
Chinese | matiler | 70 | 73.3333333333333333
Chinese | tutu | 80 | 73.3333333333333333
English | matiler | 75 | 75.0000000000000000
English | francs | 90 | 75.0000000000000000
English | tutu | 60 | 75.0000000000000000
Math | francs | 80 | 81.3333333333333333
Math | matiler | 99 | 81.3333333333333333
Math | tutu | 65 | 81.3333333333333333
(9 rows) 以上查詢前三列來源于表score，第四清單示取課程的平均分，PARTITION BY subject表示根據字段subject進行分組。
rank()視窗函數
rank()視窗函數和row_number()視窗函數相似，主要差別為當組内某行字段值相同時，行号重複并且行号産生間隙（手冊上解釋為gaps），如下：
mydb=> SELECT rank() OVER(PARTITION BY subject ORDER BY score),* FROM score;
rank | id | subject | stu_name | score
------+----+---------+----------+-------
1 | 2 | Chinese | matiler | 70
1 | 1 | Chinese | francs | 70
3 | 3 | Chinese | tutu | 80
1 | 6 | English | tutu | 60
2 | 4 | English | matiler | 75
3 | 5 | English | francs | 90
1 | 9 | Math | tutu | 65
2 | 7 | Math | francs | 80
3 | 8 | Math | matiler | 99
(9 rows) 以上示例中，Chinese課程前兩條記錄的score字段值都為70，是以前兩行的rank字段值1，而第三行的rank字段值為3，産生了間隙。
dense_rank ()視窗函數
dense_rank ()視窗函數和rank ()視窗函數相似，主要差別為當組内某行字段值相同時，雖然行号重複，但行号不産生間隙（手冊上解釋為gaps），如下：
mydb=> SELECT dense_rank() OVER(PARTITION BY subject ORDER BY score),* FROM score;
dense_rank | id | subject | stu_name | score
------------+----+---------+----------+-------
1 | 2 | Chinese | matiler | 70
1 | 1 | Chinese | francs | 70
2 | 3 | Chinese | tutu | 80
1 | 6 | English | tutu | 60
2 | 4 | English | matiler | 75
3 | 5 | English | francs | 90
1 | 9 | Math | tutu | 65
2 | 7 | Math | francs | 80
3 | 8 | Math | matiler | 99
(9 rows) 以上示例中，Chinese課程前兩行的rank字段值1，而第三行的rank字段值為2，沒有産生間隙。
PostgreSQL還支援很多其它内置視窗函數，例如、lag()、first_values()、last_values()等，篇幅關系不再介紹。
總結
本篇文章主要介紹了PostgreSQL支援的一些進階SQL特性，例如WITH查詢、批量插入、RETURNING傳回DML修改的資料、UPSERT、資料抽樣、聚合函數、視窗函數，了解這些功能能夠簡化SQL代碼，提升開發效率，并且實作普通查詢不容易實作的功能，希望通過閱讀本章讀者能夠在實際工作中應用SQL進階特性，同時挖掘PostgreSQL的其他進階SQL特性。
PosgreSQL不僅是關系型資料庫，同時支援NoSQL特性，關于PostgreSQL的NoSQL特性将在下篇文章中介紹。
本書基于PostgreSQL 10 編寫，共18章，重點介紹SQL進階特性、并行查詢、分區表、實體複制、邏輯複制、備份恢複、高可用、性能優化、PostGIS等，涵蓋大量實戰用例!
連結：
https://item.jd.com/12405774.html
參考
源文:
https://postgres.fun/20181214145600.html![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)