這里就不用什么標(biāo)題黨了，本文會(huì)總結(jié)一些Postgres中，從應(yīng)用需求和場(chǎng)景出發(fā)，不太常見，但比較常用并且有用的SQL語句，并進(jìn)行簡(jiǎn)單的說明和分析。這些技巧和操作的主要目的，是通過簡(jiǎn)化操作，更高效的處理數(shù)據(jù)，或者提高開發(fā)效率。

本文并不是什么體系化的材料，可以看成一些使用經(jīng)驗(yàn)和感想，覺得有必要分享出來的內(nèi)容集錦。由于這部分內(nèi)容和組織可能會(huì)比較零散，本文會(huì)長(zhǎng)期持續(xù)更新完善。

Why Postgres

主要是有人總是糾結(jié)這個(gè)問題。筆者的理解簡(jiǎn)單說來就是喜歡和合適(僅針對(duì)筆者和所在的應(yīng)用場(chǎng)景)。當(dāng)然其實(shí)世界上既沒有一種完美無瑕，也沒有一無是處的技術(shù)，如果不能理解這一點(diǎn)，可能就不用往下看了。但任何一種技術(shù)或者產(chǎn)品，除了它本身的功能和特性之外，它會(huì)體現(xiàn)一些構(gòu)建者所秉持的想法和價(jià)值觀，理解和認(rèn)同這些，會(huì)建構(gòu)和使用者之間更深層次的契合。

從歷史說起吧。PG源自伯克利的Postgres軟件包(關(guān)系型數(shù)據(jù)庫模型設(shè)計(jì)和實(shí)現(xiàn)，1986年)項(xiàng)目，具有相對(duì)獨(dú)立和長(zhǎng)期的技術(shù)發(fā)展過程，現(xiàn)已經(jīng)發(fā)展成為世界上最先進(jìn)，特性最完善的開源關(guān)系型數(shù)據(jù)庫系統(tǒng)(你深入了解后就知道這不是吹牛)，并在開源社區(qū)和信息技術(shù)行業(yè)內(nèi)得到了非常廣泛的應(yīng)用。作為長(zhǎng)期穩(wěn)定的開源軟件系統(tǒng)PG擁有眾多的衍生系統(tǒng)，比如GreenPlum等，阿里和華為等很多技術(shù)公司都基于或者參考PG開發(fā)了相關(guān)的衍生產(chǎn)品。

PG誕生之初就定位于企業(yè)級(jí)的數(shù)據(jù)庫系統(tǒng)，注重設(shè)計(jì)嚴(yán)謹(jǐn)，系統(tǒng)健壯，易用性和功能豐富。PG對(duì)于SQL標(biāo)準(zhǔn)的支持是在所有數(shù)據(jù)庫系統(tǒng)中最完備的。PG技術(shù)社區(qū)也非常活躍，開放性也非常好，技術(shù)發(fā)展也比較快，從而能夠盡早提供豐富和先進(jìn)的功能特性，文檔和資料詳實(shí)豐富，能夠很好的幫助程序員快速開發(fā)和改進(jìn)他們的應(yīng)用程序。

在整個(gè)技術(shù)棧中，PG也是一個(gè)完善開放的生態(tài)環(huán)境中的一個(gè)良好的成員，PG可以運(yùn)行在所有主流操作系統(tǒng)之上，并通過開放源碼方式可以方便的移植到其他架構(gòu)和操作系統(tǒng)上。PG完善的生態(tài)環(huán)境提供所有主流語言和開發(fā)環(huán)境的支持。在筆者現(xiàn)有使用的nodejs體系中，可以非常方便的移植和集成。無論是數(shù)據(jù)庫連接，參數(shù)化語句和執(zhí)行，查詢結(jié)果處理，異常處理等，都非常方便和直觀。

修改操作返回記錄(returning)

PG提供了修改記錄時(shí)返回?cái)?shù)據(jù)的功能，在需要判斷操作結(jié)果的場(chǎng)景中非常有用，特別是SQL執(zhí)行操作和調(diào)用語言一起進(jìn)行操作的情況下。這個(gè)操作語句也非常簡(jiǎn)單，使用returning。

insert into students(id,name) values ('id1','name1') returing id,name;
update students set status = 1 where id = 'id1' returning id,name, status;
delete from students where id = 'id1' returning id,name ;

復(fù)制表結(jié)構(gòu)(createLike)

PG提供了在create table中，new字句來幫助復(fù)制一個(gè)表，還包括一些擴(kuò)展選項(xiàng)。在數(shù)據(jù)管理工作中會(huì)經(jīng)常用到。

create table new (
    like old
    including defaults
    including constraints
    including indexes
);

插入或更新(upsert)

在業(yè)務(wù)應(yīng)用開發(fā)中，經(jīng)常會(huì)遇到"如果沒有就插入，如果存在就更新"的數(shù)據(jù)處理需求。傳統(tǒng)方式是先進(jìn)行查詢，如果有記錄，則執(zhí)行update語句，否則執(zhí)行insert語句。在規(guī)則明確的情況下，這樣的操作顯然效率比較低，因?yàn)樾枰啻蔚目蛻舳颂幚磉壿嫼头?wù)器交互。這時(shí)可以使用insert onconflict的模式來進(jìn)行處理，從而簡(jiǎn)化執(zhí)行代碼，并可以支持批量處理數(shù)據(jù)。

示例代碼和要點(diǎn)如下：

insert into students (id,name,status) values ( 1, 's1', 10)
on conflict(id) do update set (name,status) = (excluded.name, excluded.status) 
returning id,name,status;

• 本質(zhì)上，是一個(gè)插入操作，但遇到了某種邏輯沖突，然后進(jìn)行后續(xù)處理的邏輯
• 需要確定一個(gè)沖突標(biāo)準(zhǔn)，通?？梢杂弥麈I，或者唯一索引(或復(fù)合索引)
• 可以選擇進(jìn)行更新操作，也可以選擇不做處理如 do nothing
• 更新記錄時(shí)，可以選擇使用excluded邏輯記錄，來進(jìn)行數(shù)據(jù)操作
• 可以通過returning判斷記錄的存在性

PG15中，已經(jīng)提供了類似于Oracle的Merge into的功能(也叫Merge)，也可以實(shí)現(xiàn)這種業(yè)務(wù)需求了。

查詢插入(select insert)

--  從一個(gè)表復(fù)制記錄
insert into students_bak
select *, 100 as flag from students;

將一個(gè)查詢的結(jié)果集，插入一張表，是一個(gè)非常常見的操作，需要注意的是插入和選擇字段的數(shù)量和類型都必須匹配，可能有時(shí)需要用到類型轉(zhuǎn)換。

聯(lián)合更新和刪除(join update/delete)

聯(lián)合更新的意思是更新數(shù)據(jù)不是簡(jiǎn)單的基于一個(gè)條件，可能是基于一個(gè)查詢結(jié)果，并且需要參考其和這個(gè)結(jié)果之間的關(guān)聯(lián)關(guān)系。 這個(gè)在實(shí)際業(yè)務(wù)場(chǎng)景中非常常見，如果這個(gè)功能支持的不好，可能需要在客戶端進(jìn)行多次查詢，處理和操作。

筆者感覺，雖然在邏輯上是沒有問題的，但類似的操作，在Oracle和PG的實(shí)現(xiàn)和運(yùn)行，是有比較大的差別的。PG對(duì)這種操作模式的支持顯然更好更穩(wěn)定。

--  從一個(gè)表復(fù)制記錄
insert into students_bak
select *, 100 as flag from students;
-- 基于查詢和關(guān)聯(lián)更新和刪除記錄
with I as (select idhash id2 from students where region = 1)
update students set status = 1 from I where id2 = idhash

CTE和虛表(with as)

使用現(xiàn)成的數(shù)據(jù)和信息，構(gòu)造一個(gè)標(biāo)準(zhǔn)SQL記錄集，然后進(jìn)行后續(xù)的操作，如聯(lián)合查詢，更新操作等等，是比較常用的操作方式。在Postgres中，經(jīng)常使用CTE(Common Table Expression 公共表表達(dá)式，通常也指With字句)來進(jìn)行處理。

我們來看一個(gè)簡(jiǎn)單的例子

with 
A(id,name) as (values ('id1','name1'),('id2','name2'),('id3','name3')),
B as (select row_number() over () rn, A.* from A)
select * from B;

這個(gè)語句可以使用一組數(shù)據(jù)，創(chuàng)建一個(gè)虛表。可以看到如何定義虛表的結(jié)構(gòu)，并且可以使用多個(gè)虛表或者記錄集的情況。

實(shí)際上，CTE的使用非常靈活和強(qiáng)大，它可以連接多個(gè)CTE，鏈接使用，結(jié)合記錄修改操作等等，合理使用可以幫助開發(fā)人員組織數(shù)據(jù)處理流程，提前過濾和準(zhǔn)備數(shù)據(jù)，同時(shí)操作不同的數(shù)據(jù)集合等等。當(dāng)然，如果是比較大的數(shù)據(jù)集合或者比較復(fù)雜的操作，需要在編寫SQL的時(shí)候，注意其執(zhí)行的效率。

子查詢(subquery)

在比較簡(jiǎn)單的情況下(比如只嵌套一層)，可以直接使用子查詢。

select iorder, split_part(l, ',', n) city from 
(select 'lz,wh,cd' l , generate_series(1, 3) n) V;
select iorder, vl[vi] city from 
( SELECT generate_series(1,3) iorder, string_to_array('lz,wh,cd', ',') vl) V ;

上面的兩個(gè)例子，都沒有使用row_number,但使用了generate_series和子查詢來從一個(gè)字符串形式的數(shù)組中，構(gòu)造了一個(gè)排序記錄集。

使用字段編號(hào)替代字段名稱

在聚會(huì)函數(shù)和排序方法中，有時(shí)可以使用字段編號(hào)來替換字段名詞，可以用于簡(jiǎn)化SQL語句。

select region, count(1) rcount from students group by 1 order by 2 desc;

這個(gè)語句，可以使用第一個(gè)字段進(jìn)行聚會(huì)計(jì)數(shù)計(jì)算，并且將記錄集使用計(jì)數(shù)的結(jié)果進(jìn)行倒排序。

bit操作和計(jì)算

PG提供了標(biāo)準(zhǔn)的bit運(yùn)算符，可以直接在SQL中使用，不需要使用擴(kuò)展函數(shù)。在一些場(chǎng)景中使用這個(gè)功能，可以大幅度簡(jiǎn)化代碼和執(zhí)行。一般在實(shí)際中的使用，這類操作都是圍繞著標(biāo)簽化的狀態(tài)管理來使用的，無非就是以下幾種操作。

• 設(shè)置某些狀態(tài)或者組合
• 在原來的狀態(tài)上，增加一個(gè)狀態(tài)(無論原來是否具備此狀態(tài))
• 在原來的狀態(tài)上，去除一個(gè)狀態(tài)(無論原來是否具備此狀態(tài))
• 查找具備某些狀態(tài)，或者狀態(tài)組合的記錄(使用狀態(tài)標(biāo)簽作為查詢條件)
• 查找不具備狀態(tài)，或者狀態(tài)組合的記錄
• 查找具備某些狀態(tài)，但不包括另一些狀態(tài)的記錄

下面的SQL語句，可以實(shí)現(xiàn)這些要求:

-- 更新狀態(tài)字段，增加一種狀態(tài)2
update students set status = status | 2 where id = 1;  
-- 更新狀態(tài)字段，增加兩種狀態(tài)(2,4)的同時(shí)，去除一種狀態(tài)1
update students set status = status | (2+4) - (status & 1) where id = 1; 
-- 查找狀態(tài)中有2的記錄
select id from students where status & 2 = 2; 
-- 查找狀態(tài)中只為2的記錄(如果有8種狀態(tài))
select id from students where status & (2**8-1) = 2; 
-- 查找狀態(tài)中沒有2的記錄
select id from students where status & 2 = 0; 
-- 查找狀態(tài)中有2和1的記錄
select id from students where status & 3 = 3; 
-- 查找狀態(tài)中有2，或者1的記錄
select id from students where status & 3 > 0; 
-- 查找狀態(tài)中既沒有2，也沒有1的記錄
select id from students where status & 3 = 0; 

注：

1 這里的單個(gè)狀態(tài)，通常為一個(gè)簡(jiǎn)單的2^n整數(shù)，復(fù)合狀態(tài)為其算數(shù)和，可表示的狀態(tài)，使用普通正整數(shù)時(shí)有32個(gè)

2 要去除一個(gè)狀態(tài)，不能直接使用-，或者xor，其實(shí)是使用-和&的復(fù)合操作，即先使用&判斷這個(gè)狀態(tài)是否存在，然后再減去這個(gè)結(jié)果。

聚合過濾(filter)

PG提供了filter方法，可以在聚合計(jì)算時(shí)，使用某些條件，這樣可以使用單個(gè)SQL語句，使用一條記錄，來實(shí)現(xiàn)多種條件聚會(huì)的查詢。如果不這樣操作，可能需要使用Unionall等語句，將多個(gè)條件查詢的結(jié)果合并起來。按照PG的文檔，這種操作在執(zhí)行的時(shí)候，可能只對(duì)記錄集進(jìn)行一次，是比較高效的。

下面的例子，可以幫助我們來理解這一點(diǎn):

select region,
count(1) iall, 
count(1) filter (where status & 1 = 1) i1, 
count(1) filter (where status & 2 = 2) i2,
count(1) filter (where status & 3 = 3) i3,
count(1) filter (where status & 3 > 0) i31,
count(1) filter (where status & 3 = 0) i32
from students group by 1 ;

這個(gè)filter過濾的功能設(shè)計(jì)和使用的是非常巧妙的，但個(gè)人覺得過濾條件語句中的where完全可以省略。

重復(fù)記錄處理

這里有兩類重復(fù)的記錄，一類是邏輯的重復(fù)記錄。如由于錯(cuò)誤的輸入，導(dǎo)致的身份證號(hào)相同，需要在數(shù)據(jù)維護(hù)的工作中，進(jìn)行查找和處理：

-- CTE count
with I as (
select name n2,icount from (select name, count(1) icount from students group by 1 )C 
where icount > 1)
select name, idnumber from students join I on name = n2 order by 1;
-- having filter
select name, count(1) from students group by 1 having count(1) > 1;
-- row_number
select * from (
select idnumber,name, row_number() over(partition by idnumber order by name ) rn from students) S where rn > 1;

以上語句可以查詢這些重復(fù)的記錄，可以有聚合查詢計(jì)數(shù)和窗口函數(shù)過濾兩種方法。理論上前一種性能好點(diǎn)，但如果有排序條件確定重復(fù)信息或者需要直接獲得細(xì)節(jié)，也可以使用后者。

還有一類是完全重復(fù)，比如不小心數(shù)據(jù)庫操作插入了兩條完全一樣的記錄。如果要清除其中的一條，使用常規(guī)邏輯操作是無效的，因?yàn)樗袟l件都一樣，這時(shí)可以使用postgres中的ctid來進(jìn)行處理。

delete from students where ctid not in (
select ctid from students where idnumber = 'xxx' limit 1
) and idnumber = 'xxx' ;

上面的語句應(yīng)該可以將重復(fù)的學(xué)生只保留一個(gè)。

ctid是PG的"系統(tǒng)字段"，筆者覺得可以將其理解為這條記錄在磁盤上的位置，可以作為一條記錄在系統(tǒng)中的絕對(duì)唯一標(biāo)識(shí)。當(dāng)然可能在不同的系統(tǒng)狀態(tài)下，這個(gè)信息可能會(huì)變動(dòng)，所以要作為一種特殊的處理方案，謹(jǐn)慎使用。

隨機(jī)查詢(randomQuery)

使用order by random()，可以在查詢時(shí)隨機(jī)返回查詢結(jié)果:

select idnumber, name from students order by random() limit 10;

密碼學(xué)擴(kuò)展和簡(jiǎn)單應(yīng)用(pgcrypto)

PG通過擴(kuò)展提供了密碼學(xué)相關(guān)的功能和操作方式。這里只是簡(jiǎn)單列舉一下常用的功能和方式。

create extension pgcrypto;
-- sha256摘要
SELECT encode(digest('China中國(guó)', 'sha256'), 'hex');
-- hmac256
SELECT encode(hmac('China中國(guó)', 'key','sha256'), 'hex');
-- 存儲(chǔ)密碼
UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
-- 檢查密碼 返回值是一個(gè)布爾值
SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ; -- false
SELECT (pswhash = crypt('new password', pswhash)) AS pswmatch FROM ... ; --true
-- 生成一個(gè)鹽
select gen_salt('md5');
-- encrypt and decrypt
with 
V(passwd,otext) as (values ('t0pSecret'::bytea,'china中國(guó)')),
E as (select encrypt(otext::bytea, passwd,'aes') econtent from V),
D as (select encode(econtent,'base64') b64, 
convert_from(decrypt(econtent, passwd,'aes'),'utf8') dtext from V,E)
select V.otext,V.passwd, D.* from V,D;

要使用這個(gè)擴(kuò)展，必須先進(jìn)行安裝。安裝完成后，pgcrypto提供了一系列自定義函數(shù)可以進(jìn)行密碼學(xué)操作。和普通的語言一樣，pgcrypot操作數(shù)據(jù)的標(biāo)準(zhǔn)格式是bytea(字節(jié)數(shù)組)，所以在實(shí)際使用時(shí)，需要注意進(jìn)行編碼和格式的轉(zhuǎn)換。

• encode: 編碼，將bytea轉(zhuǎn)換為指定格式如hex
• digest: 摘要函數(shù)，支持sha1和sha256
• hmac: 摘要消息驗(yàn)證碼
• crypto: pg實(shí)現(xiàn)的一種密碼存儲(chǔ)和驗(yàn)證方式，沒有解密過程，只有匹配驗(yàn)證
• gen_salt: 隨機(jī)鹽生成，但好像只是pgcryto設(shè)置的格式
• encrypt/decrypt: 標(biāo)準(zhǔn)AES加解密，但好像可選設(shè)置不多，簡(jiǎn)單使用
• pgp密碼學(xué)相關(guān)函數(shù)，這部分比較復(fù)雜，內(nèi)容比較多，使用也不廣泛，不在這里討論

數(shù)組操作(Array)

PG可以很方便的進(jìn)行數(shù)組的相關(guān)操作。使用時(shí)，需要注意的是PG的數(shù)組索引是從1開始的，另外定義數(shù)組時(shí)需要指定類型。合理的使用數(shù)組，可以簡(jiǎn)化程序開發(fā)和數(shù)據(jù)庫維護(hù)。比如可以使用一個(gè)數(shù)組，來記錄相關(guān)的操作時(shí)間線，如創(chuàng)建、修改、完成時(shí)間等等。也可以記錄如標(biāo)簽等數(shù)組類型的數(shù)據(jù)。

-- 增加一個(gè)數(shù)組字段
alter table students add column otimes integer[]; 
-- 插入數(shù)據(jù)
insert into students (id,otimes[1]) values (1, 200);
-- 查詢數(shù)據(jù)
select id,name from students where otimes[1] = 100;
-- 使用any和all函數(shù)
select id,name from students where any(otimes) > 100;
select id,name from students where all(otimes) > 100;
-- 擴(kuò)展數(shù)組 ||操作符, array_cat方法
select array[1, 2, 3] || 4 as element_append;
select array_cat('{1, 2}', ARRAY[3, 4]) as concatenated_arrays;
-- 刪除元素 數(shù)組，位置
select array_remove(ARRAY[1,2,3,2,5], 2) as removed_2s;
-- 替換元素 數(shù)組，查找值，新值
select array_replace(ARRAY[1,2,3,2,5], 2, 10) as two_becomes_ten;
-- 填充數(shù)組 填充值，填充數(shù)量，開始位置
select array_fill(90,array[5],array[3])

JSON操作(JSON)

PG內(nèi)置JSON支持，為Web應(yīng)用開發(fā)，提供了很大的方便。這部分內(nèi)容比較多，有機(jī)會(huì)另外撰文說明。

字符串、數(shù)組和記錄互轉(zhuǎn)(Agg)

可以使用的方法包括 string_to_array, unnest, string_agg, array_agg等。也可以在聚合場(chǎng)景下使用。

-- string 2 array to row
select  unnest(string_to_array('a,b,c',','));
-- array agg, string agg
with A as (select unnest(string_to_array('a,b,c',',')) c)
select array_agg(A.c), string_agg(A.c,';') from A;

• string_to_array: 使用分隔符，將字符串轉(zhuǎn)成數(shù)組
• unnest: 將數(shù)組轉(zhuǎn)成記錄
• string_agg: 使用分隔符鏈接記錄，并轉(zhuǎn)成字符串
• array_agg: 將記錄轉(zhuǎn)成數(shù)組

帶行號(hào)的記錄集(Row Number)

有時(shí)候需要強(qiáng)行將兩個(gè)記錄集在橫向合并起來，就可能需要使用附加的關(guān)聯(lián)字段，如可以使用一個(gè)行編號(hào)來實(shí)現(xiàn)這個(gè)。下面的語句可以給一個(gè)記錄集增加一個(gè)行號(hào)的字段，從而構(gòu)造一個(gè)新的記錄集。

with 
S as (select name from students limit 100),
N as (select row_number() over() rn,name from S)
select * from N;

自定義排序規(guī)則(Custom Order)

一般情況下，在關(guān)系數(shù)據(jù)庫系統(tǒng)，對(duì)記錄集排序時(shí)非常簡(jiǎn)單的，只需要對(duì)列值進(jìn)行計(jì)算作為排序依據(jù)即可。但這些排序都遵循固定的排序規(guī)則。

有時(shí)候會(huì)遇到需要自己定義排序的次序，而這個(gè)規(guī)則和默認(rèn)文本或者數(shù)組排序規(guī)則又有點(diǎn)沖突，就需要使用一些特別的處理方式，有下面幾個(gè)思路。

• UnionALL強(qiáng)行將記錄集按順序輸出
• 增加一個(gè)排序字段，增加一個(gè)排序用的字段，并且手段設(shè)置和維護(hù)排序用的數(shù)值，這種方式可以處理任意的排序要求
• 前面的思路，使用計(jì)算列
• case when設(shè)置排序順序，將排序規(guī)則使用case when 返回排序序號(hào)來進(jìn)行表達(dá)，然后以此進(jìn)行排序
• 虛表關(guān)聯(lián)，將排序用的規(guī)則，寫到一個(gè)虛擬記錄集中，里面有排序數(shù)值，查詢時(shí)關(guān)聯(lián)此記錄集，并使用里面的排序序號(hào)進(jìn)行排序

計(jì)算列 (Computed Column)

PG12以上的版本支持計(jì)算列，也稱為生成字段(generated column)，定義方式如下：

CREATE TABLE Students (
  Id INTEGER PRIMARY KEY,
  FirstName VARCHAR(50),
  LastName VARCHAR(50),
  FullName VARCHAR(101) GENERATED ALWAYS AS (FirstName || ' ' || LastName) STORED
);

和視圖一樣，計(jì)算列的合理規(guī)劃和使用，可能可以優(yōu)化業(yè)務(wù)邏輯，簡(jiǎn)化編程，并減少數(shù)據(jù)操作和維護(hù)等工作。

PG的計(jì)算列定義語法是固定的，即只支持ALWAYS和STORED，實(shí)體字段。理論上計(jì)算列的使用，和普通列是完全一樣的。現(xiàn)在PG的計(jì)算列的使用還有一些限制，如不能嵌套參照其他計(jì)算列，不能使用參照當(dāng)前行的子查詢，不能作為分區(qū)字段，不能作為主鍵等等。

時(shí)間整數(shù) (TimeInt)

PG提供了一系列時(shí)間函數(shù)和保留字用于處理時(shí)間。

-- 時(shí)間戳秒
select extract(epoch from current_timestamp)::integer; 
-- 時(shí)間類型
select current_timestamp,now(),current_time, current_date;
-- 時(shí)間戳轉(zhuǎn)成格式化字符串
SELECT TO_CHAR(to_timestamp((28070418 + 480) * 60), 'YYYY-MM-DD HH:MI');

在實(shí)際項(xiàng)目使用中，在時(shí)間格式化的時(shí)候，可能需要注意系統(tǒng)的時(shí)區(qū)，因?yàn)槟J(rèn)都是UTC。

表繼承(Table Inherits)

PG支持表之間有繼承關(guān)系，類似于面向?qū)ο缶幊痰睦^承。合理規(guī)劃和使用的話，可以簡(jiǎn)化編程和數(shù)據(jù)管理。下面的例子可以幫助我們理解表繼承的實(shí)現(xiàn)和使用:

-- 城市信息表
CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- 英尺
);
-- 省會(huì)城市繼承自城市，多了所屬省的屬性
CREATE TABLE capitals (
    state char(2)
) INHERITS (cities); 
insert into cities (name,population, altitude) 
values ('蘇州', 100,50),('綿陽', 100,50),('深圳', 100,50),('洛陽', 100,50);
insert into capitals (name,population, altitude, state) 
values ('廣州', 2100,50,'gd'),('成都', 1600,500,'sc');
select * from cities;
select * from capitals;
-- 所有城市，標(biāo)識(shí)是否省會(huì)
select C.*, nullif(P.state,null) capOfState 
from cities C left join capitals P on P.name = C.name;

默認(rèn)情況下，查詢是包括繼承表的。如果要指定查詢范圍，可以使用only關(guān)鍵字。另外，繼承表的數(shù)據(jù)修改和索引使用都有一些限制。如果確實(shí)要使用繼承表的業(yè)務(wù)結(jié)構(gòu)，需要確認(rèn)了解這些限制，并仔細(xì)評(píng)估和業(yè)務(wù)需求之間的沖突。

Having

HAVING字句可以用于在對(duì)分組后的結(jié)果進(jìn)行過濾時(shí)使用。它通常與GROUP BY子句一起使用，用于篩選符合特定條件的分組結(jié)果。筆者理解，Having就是group by配套的where字句，可能考慮到和普通查詢的where有邏輯方面的沖突，才使用單獨(dú)的關(guān)鍵字和處理方式。

SELECT country, COUNT(*) AS total_customers
FROM customers GROUP BY country
HAVING COUNT(*) > 5;

Exists

exists，是使用一個(gè)子查詢，將其查詢結(jié)果作為主查詢的查詢條件之一，如下：

SELECT * FROM orders
WHERE EXISTS (
  SELECT 1 FROM customers
  WHERE orders.customer_id = customers.customer_id
);

合理使用exists，可以通過設(shè)置預(yù)選條件，縮小查詢備選結(jié)果集，提高查詢效率。或者建立查詢之間的關(guān)聯(lián)關(guān)系。

需要注意，exists子查詢，和主查詢可以邏輯上沒有任何關(guān)系。對(duì)于主查詢結(jié)果的每一條結(jié)果，都會(huì)以子查詢的結(jié)果進(jìn)行檢查，所以如果是關(guān)聯(lián)查詢，就需要特別小心其使用條件，否則可能會(huì)有比較嚴(yán)重的性能問題。

COALESCE, NULLIF

PG提供了函數(shù)colaesce，它接受多個(gè)參數(shù)，并返回第一個(gè)不為空的值。經(jīng)常用于，“如果字段內(nèi)容為空時(shí)，則使用默認(rèn)值”的應(yīng)用場(chǎng)景。

SELECT COALESCE(null, 5, null, 10); -- 返回 5

"COALESCE"這個(gè)單詞由詞根co-(表示共同或一起)，和拉丁語 "alescere"(意為變長(zhǎng)、增長(zhǎng))構(gòu)成。因此，其原義可以理解為將多個(gè)元素或選項(xiàng)合并為一個(gè)，或者將多個(gè)部分融合、組合成一個(gè)整體。在計(jì)算機(jī)科學(xué)領(lǐng)域，"COALESCE" 是一個(gè)通用的術(shù)語，用于描述將多個(gè)值合并為一個(gè)值的操作。在PostgreSQL中的COALESCE函數(shù)也是以此意義使用，作用類似于將多個(gè)值"合并"成一個(gè)值的概念，它可以合并多個(gè)參數(shù)并返回第一個(gè)非NULL值。

PG中還有一個(gè)比較常用的函數(shù)nullif，它可以比較兩個(gè)輸入的參數(shù)，如果相等，則會(huì)返回null；否則返回第一個(gè)值。

SELECT NULLIF(10, 10); -- 返回 NULL
SELECT NULLIF('abc', 'def'); -- 返回 'abc'

nullif比較常見的場(chǎng)景包括檢查0，檢查默認(rèn)值，錯(cuò)誤處理和數(shù)據(jù)替換等等。

case when

其實(shí)，PG提供的caseWhen功能是相當(dāng)強(qiáng)大的。即它可以以簡(jiǎn)單方式和搜索方式工作。開發(fā)者可以根據(jù)需求靈活選用。

-- 簡(jiǎn)單方式
CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result
END
-- 搜索方式
CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  ...
  ELSE result
END

默認(rèn)查詢結(jié)果(Default Row)

下面的語句，可以在結(jié)果集為空的情況下，返回一條默認(rèn)記錄?？赡茉诳蛻舳司筒恍枰貏e的處理邏輯了。

SELECT column1, column2, ... FROM table WHERE condition
UNION ALL
SELECT default_value1, default_value2, ...
LIMIT 1

但這里的問題是，無論這個(gè)查詢是否有結(jié)果，都會(huì)輸出一條默認(rèn)的記錄？ 也許可以在后面那個(gè)結(jié)果集加上exists字句來進(jìn)行檢查，那樣顯然比較麻煩，如果確有這個(gè)需求，可以考慮。

判斷查詢結(jié)果是否為空(Query Empty)

如果只關(guān)心滿足查詢條件的記錄是否存在，最簡(jiǎn)單和高效的方式應(yīng)該是：

SELECT true from students where ...
union all 
select false limit 1;

窗口函數(shù)(Window Function)

PG提供了窗口函數(shù)系列功能，極大的方便了各種統(tǒng)計(jì)查詢的功能開發(fā)。這部分內(nèi)容非常多，筆者會(huì)另行撰文討論。這里只簡(jiǎn)單的說明一下其中的重點(diǎn)。

首先需要理解窗口函數(shù)和聚合函數(shù)的區(qū)別。 雖然它們都是常用的統(tǒng)計(jì)函數(shù)，但聚合函數(shù)只能處理簡(jiǎn)單的數(shù)據(jù)分類和相關(guān)計(jì)算，比如，記錄在分區(qū)中的排名，聚合函數(shù)就無法處理，因?yàn)樗魂P(guān)心個(gè)體和群體之間的關(guān)系。

而窗口函數(shù)不僅能將數(shù)據(jù)進(jìn)行分區(qū)(窗口函數(shù)的名字由來)，并且可以基于分區(qū)進(jìn)行統(tǒng)計(jì)方面的處理，更重要的是，它可以更細(xì)致的分析個(gè)體和群體之間的關(guān)系，將統(tǒng)計(jì)屬性，直接附加到原有個(gè)體記錄上(聚合函數(shù)會(huì)丟失個(gè)體信息)，從而不丟失細(xì)節(jié)信息，提供了更強(qiáng)大的數(shù)據(jù)分析和呈現(xiàn)的可能性。當(dāng)然，我們也可以想見，這樣的操作勢(shì)必會(huì)造成一些性能方面的問題，因?yàn)樾枰幚淼臄?shù)據(jù)和細(xì)節(jié)更多了，所以在實(shí)際使用需要進(jìn)行考慮和平衡。

窗口函數(shù)和統(tǒng)計(jì)相關(guān)函數(shù)也不同，那些函數(shù)主要處理一組可以進(jìn)行統(tǒng)計(jì)計(jì)算的數(shù)據(jù)，窗口函數(shù)用于處理一組記錄，當(dāng)然可以結(jié)合起來使用，達(dá)到分組統(tǒng)計(jì)分析的目的。

窗口函數(shù)可以直接寫在普通查詢語句中(不使用group by 字句)，這個(gè)函數(shù)系列的標(biāo)準(zhǔn)形式是：

f() over (partition by ... order by ... )

分區(qū)和排序都可以使用多個(gè)字段，也可以同時(shí)返回記錄的其他字段。

常用的窗口函數(shù)包括:

• row_number: 分區(qū)內(nèi)按照排序規(guī)則的序號(hào)，絕對(duì)不會(huì)有重復(fù)值
• rank, dense_rank, percent_rank: 分區(qū)內(nèi)按照排序規(guī)則的排名，要注意這個(gè)排名可以是并列的，中間可以有中斷或者不中斷，常用作成績(jī)排名;dense_rank是緊密排名(并列不跳過位次); percent_rank是百分比排名。
• firstvalue,lastvalue: 分區(qū)中的第一個(gè)和最后一個(gè)記錄，指定字段的值
• cume_dist: 當(dāng)前行在分區(qū)中的累計(jì)分布值
• lag, lead: 當(dāng)前分區(qū)中，當(dāng)前記錄之前或者之后，指定偏移量的值
• ntile: 返回在對(duì)分區(qū)平均分片后，當(dāng)前行所屬分片的編號(hào)；
• nth_value: 獲取分區(qū)中，指定字段，指定位置的值

延遲模擬(Sleep)

不知道什么地方會(huì)用到，也許是在存儲(chǔ)過程中的定時(shí)或延遲執(zhí)行吧：

pg_sleep(3); // 休眠3秒

延遲引用(Lateral)

對(duì)這個(gè)語句的理解，筆者也比較模糊，還沒有找到其特別有用的應(yīng)用場(chǎng)合。 先來看一個(gè)例子

select pledged_usd, avg_pledge_usd, amt_from_goal, duration, (usd_from_goal / duration) 
as usd_needed_daily from kickstarter_data, 
lateral (select pledged / fx_rate as pledged_usd) pu 
lateral (select pledged_usd / backers_count as avg_pledge_usd) apu 
lateral (select goal / fx_rate as goal_usd) gu 
lateral (select goal_usd - pledged_usd as usd_from_goal) ufg 
lateral (select (deadline - launched_at)/86400.00 as duration) dr;

有人這樣解釋: "lateral關(guān)鍵字，允許訪問在from后面定義的字段，同時(shí)引用在此之前定義的字段"。 這樣設(shè)計(jì)的原因，大概是由于SQL語句查詢執(zhí)行的次序是 from 和 join，一般情況下，就無法處理之后的字段了，所以需要一個(gè)關(guān)鍵字特此聲明。在某些場(chǎng)景之下，提高了SQL語句編寫的靈活性。

以上就是PostgreSQL的應(yīng)用技巧和示例分享的詳細(xì)內(nèi)容，更多關(guān)于PostgreSQL應(yīng)用的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！