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

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

Why Postgres

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

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

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

在整個技術(shù)棧中，PG也是一個完善開放的生態(tài)環(huán)境中的一個良好的成員，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ù)據(jù)的功能，在需要判斷操作結(jié)果的場景中非常有用，特別是SQL執(zhí)行操作和調(diào)用語言一起進(jì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ù)制一個表，還包括一些擴(kuò)展選項。在數(shù)據(jù)管理工作中會經(jīng)常用到。

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

插入或更新(upsert)

在業(yè)務(wù)應(yīng)用開發(fā)中，經(jīng)常會遇到"如果沒有就插入，如果存在就更新"的數(shù)據(jù)處理需求。傳統(tǒng)方式是先進(jìn)行查詢，如果有記錄，則執(zhí)行update語句，否則執(zhí)行insert語句。在規(guī)則明確的情況下，這樣的操作顯然效率比較低，因為需要多次的客戶端處理邏輯和服務(wù)器交互。這時可以使用insert onconflict的模式來進(jìn)行處理，從而簡化執(zhí)行代碼，并可以支持批量處理數(shù)據(jù)。

示例代碼和要點如下：

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

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

查詢插入(select insert)

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

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

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

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

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

--  從一個表復(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)造一個標(biāo)準(zhǔn)SQL記錄集，然后進(jìn)行后續(xù)的操作，如聯(lián)合查詢，更新操作等等，是比較常用的操作方式。在Postgres中，經(jīng)常使用CTE(Common Table Expression 公共表表達(dá)式，通常也指With字句)來進(jì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;

這個語句可以使用一組數(shù)據(jù)，創(chuàng)建一個虛表?？梢钥吹饺绾味x虛表的結(jié)構(gòu)，并且可以使用多個虛表或者記錄集的情況。

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

子查詢(subquery)

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

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 ;

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

使用字段編號替代字段名稱

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

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

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

bit操作和計算

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

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

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

-- 更新狀態(tài)字段，增加一種狀態(tài)2
update students set status = status | 2 where id = 1;  
-- 更新狀態(tài)字段，增加兩種狀態(tài)(2,4)的同時，去除一種狀態(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 這里的單個狀態(tài)，通常為一個簡單的2^n整數(shù)，復(fù)合狀態(tài)為其算數(shù)和，可表示的狀態(tài)，使用普通正整數(shù)時有32個

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

聚合過濾(filter)

PG提供了filter方法，可以在聚合計算時，使用某些條件，這樣可以使用單個SQL語句，使用一條記錄，來實現(xiàn)多種條件聚會的查詢。如果不這樣操作，可能需要使用Unionall等語句，將多個條件查詢的結(jié)果合并起來。按照PG的文檔，這種操作在執(zhí)行的時候，可能只對記錄集進(jì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 ;

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

重復(fù)記錄處理

這里有兩類重復(fù)的記錄，一類是邏輯的重復(fù)記錄。如由于錯誤的輸入，導(dǎ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ù)的記錄，可以有聚合查詢計數(shù)和窗口函數(shù)過濾兩種方法。理論上前一種性能好點，但如果有排序條件確定重復(fù)信息或者需要直接獲得細(xì)節(jié)，也可以使用后者。

還有一類是完全重復(fù)，比如不小心數(shù)據(jù)庫操作插入了兩條完全一樣的記錄。如果要清除其中的一條，使用常規(guī)邏輯操作是無效的，因為所有條件都一樣，這時可以使用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é)生只保留一個。

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

隨機(jī)查詢(randomQuery)

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

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

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

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

create extension pgcrypto;
-- sha256摘要
SELECT encode(digest('China中國', 'sha256'), 'hex');
-- hmac256
SELECT encode(hmac('China中國', 'key','sha256'), 'hex');
-- 存儲密碼
UPDATE ... SET pswhash = crypt('new password', gen_salt('md5'));
-- 檢查密碼 返回值是一個布爾值
SELECT (pswhash = crypt('entered password', pswhash)) AS pswmatch FROM ... ; -- false
SELECT (pswhash = crypt('new password', pswhash)) AS pswmatch FROM ... ; --true
-- 生成一個鹽
select gen_salt('md5');
-- encrypt and decrypt
with 
V(passwd,otext) as (values ('t0pSecret'::bytea,'china中國')),
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;

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

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

數(shù)組操作(Array)

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

-- 增加一個數(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ī)會另外撰文說明。

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

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

-- 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ù)組

帶行號的記錄集(Row Number)

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

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)，對記錄集排序時非常簡單的，只需要對列值進(jìn)行計算作為排序依據(jù)即可。但這些排序都遵循固定的排序規(guī)則。

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

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

計算列 (Computed Column)

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

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

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

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

時間整數(shù) (TimeInt)

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

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

在實際項目使用中，在時間格式化的時候，可能需要注意系統(tǒng)的時區(qū)，因為默認(rèn)都是UTC。

表繼承(Table Inherits)

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

-- 城市信息表
CREATE TABLE cities (
    name            text,
    population      float,
    altitude        int     -- 英尺
);
-- 省會城市繼承自城市，多了所屬省的屬性
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)識是否省會
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ù)修改和索引使用都有一些限制。如果確實要使用繼承表的業(yè)務(wù)結(jié)構(gòu)，需要確認(rèn)了解這些限制，并仔細(xì)評估和業(yè)務(wù)需求之間的沖突。

Having

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

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

Exists

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

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

合理使用exists，可以通過設(shè)置預(yù)選條件，縮小查詢備選結(jié)果集，提高查詢效率?；蛘呓⒉樵冎g的關(guān)聯(lián)關(guān)系。

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

COALESCE, NULLIF

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

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

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

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

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

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

case when

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

-- 簡單方式
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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

延遲模擬(Sleep)

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

pg_sleep(3); // 休眠3秒

延遲引用(Lateral)

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

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è)計的原因，大概是由于SQL語句查詢執(zhí)行的次序是 from 和 join，一般情況下，就無法處理之后的字段了，所以需要一個關(guān)鍵字特此聲明。在某些場景之下，提高了SQL語句編寫的靈活性。

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

最新評論