前面?zhèn)z個章節(jié)我們介紹了窗口函數(shù)、滑動窗口函數(shù)的概念，接下來我們介紹一下PG支持的原生通用窗口函數(shù)，總共11個(9.6版本, 中國社區(qū)官網(wǎng)文檔地址 ）
成都創(chuàng)新互聯(lián)成立于2013年，我們提供高端成都網(wǎng)站建設(shè)、成都網(wǎng)站制作、成都網(wǎng)站設(shè)計、網(wǎng)站定制、全網(wǎng)營銷推廣、微信小程序、微信公眾號開發(fā)、成都網(wǎng)站推廣服務(wù)，提供專業(yè)營銷思路、內(nèi)容策劃、視覺設(shè)計、程序開發(fā)來完成項目落地，為成都門簾企業(yè)提供源源不斷的流量和訂單咨詢。
1、row_number 函數(shù)
row_number函數(shù)可以給每隔數(shù)據(jù)行返回一個虛擬的自增ID,也就是相當(dāng)于給行分配一個編號，這些編號不會出現(xiàn)重復(fù)，即使over()里面沒有按照字段排序字段也能正常工作，
2、rank函數(shù)
rank的官方解釋是：帶間隙的當(dāng)前行排名； 與該行的第一個同等行的row_number相同
從上面的的結(jié)果我們可以看出 rank函數(shù)和row_number一樣可以將行編號，但是號碼可能重復(fù)，比如我們按照年份排序，年份相同的話rank值相同，2017年的數(shù)據(jù)rank直接跳到了7，這就相當(dāng)于上學(xué)的時候考試，用rank計算排名的話，如果同年級出現(xiàn)三個并列的第一名的話，那么計算的結(jié)果將是三個人的rank值都是第一，但是實際上的第二高的分數(shù)的同學(xué)會被rank排名為第四名，如果我們想第二高的分數(shù)的排名為2,我們可以使用dense_rank函數(shù)；
3、dense_rank函數(shù)
從上面的結(jié)果我們可以看出dense_rank函數(shù)會把編號弄得更加緊密，中間不會出現(xiàn)像rank那樣的斷層編碼。
4、percent_rank函數(shù)
官方文檔解釋： 當(dāng)前行的相對排名=(rank- 1) / (總行數(shù) - 1) ,
??? 排名和rank值成正相關(guān) ，rank值相同的行號?percent_rank獲取的結(jié)果也一樣，返回的 結(jié)果是個小數(shù)范圍在[0,1]之間 ，可以等于0或者1
5、cume_dist函數(shù)
官方文檔解釋： 當(dāng)前行的相對排名=(rank- 1) / (總行數(shù) - 1) ，
??? 排名和rank值成正相關(guān) ，rank值相同的行號?percent_rank獲取的結(jié)果也一樣，返回的 結(jié)果是個小數(shù)范圍在[0,1]之間 ，可以等于0或者1
6、ntile函數(shù)
官方文檔解釋：從1到參數(shù)值的整數(shù)范圍，盡可能等分分區(qū)，
ntile(num_buckets)，num_buckets的值表示將結(jié)果集分成num_buckets組，有限填滿前面的組，最后一組可能出現(xiàn)個數(shù)不足(非等分)情況，實際上就是把每隔行分個組號。
7、lag函數(shù)
官方文檔解釋：lag( value ?anyelement?[,? offset ?integer?[,? default ?anyelement?]])，返回 value ， 它在分區(qū)內(nèi)當(dāng)前行的之前 offset 個位置的行上計算；如果沒有這樣的行，返回 default 替代。 (作為 value 必須是相同類型)。? offset 和 default 都是根據(jù)當(dāng)前行計算的結(jié)果。如果忽略它們，則 offset 默認是1， default 默認是空值
? ? 官方文檔的解釋很晦澀難懂，我們直接使用用例執(zhí)行一下看一下數(shù)據(jù)分布就好了
從上圖可以知道當(dāng)前行的lag值是當(dāng)前行的前offset行的值，沒有的話就返回default,default不想存在的話就返回null,從數(shù)據(jù)姐過再去看官方文檔的解釋的話可能清晰很多，lag函數(shù)可以在結(jié)果集的行內(nèi)移動，經(jīng)常使用到的場景是計算今年和全年的年產(chǎn)量的差值，
8、lead函數(shù)
官方文檔解釋：lead( value ?anyelement?[,? offset ?integer?[,? default ?anyelement?]])?返回 value ，它在分區(qū)內(nèi)當(dāng)前行的之后 offset 個位置的行上計算；如果沒有這樣的行，返回 default 替代。(作為 value 必須是相同類型)。 offset 和 default 都是根據(jù)當(dāng)前行計算的結(jié)果。如果忽略它們，則 offset 默認是1， default 默認是空值
其實lead函數(shù)和lag函數(shù)的作用是相同的，如果lead的offset參數(shù)值為-N,那么lag的offset的參數(shù)為N的話計算結(jié)果是相同的，lag(gdp,-1)是lead(gdp,1)的替代
9、first_value、last_value、nth_value函數(shù)較為簡單不做介紹
至此我們講完了幾乎所有的窗口函數(shù)，希望這五篇關(guān)于PostgreSQL的文章能對大家在平時的開發(fā)中有所幫助
最近在寫postgres的函數(shù)，整理下常用語法備忘
regexp_split_to_table 字符串分割為表格
regexp_split_to_array 字符串分割為數(shù)組
定義內(nèi)部變量
執(zhí)行動態(tài)sql，并傳入?yún)?shù)
執(zhí)行動態(tài)sql，取出結(jié)果
打印變量
字符串拼接
兩種方法：
方法一：查詢pg_proc:
osdba=# select prosrc from pg_proc where proname='get_username';
prosrc
--------------------------------------------------------
declare
ret text;
begin
SELECT name into ret from tang01 where id=userid;
return ret;
end;
(1 row)
方法二：調(diào)用pg_catalog.pg_get_functiondef函數(shù)：
osdba=# select pg_get_functiondef('get_username'::regproc);
pg_get_functiondef
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION public.get_username(userid integer)
RETURNS text
LANGUAGE plpgsql
AS $function$
declare
ret text;
begin
SELECT name into ret from tang01 where id=userid;
return ret;
end;
$function$
postgresql支持數(shù)組類型，可以是基本類型，也可以是用戶自定義的類型。日常中使用數(shù)組類型的機會不多，但還是可以了解一下。不像C或JAVA高級語言的數(shù)組下標從0開始，postgresql數(shù)組下標從1開始，既可以指定長度，也可以不指定長度。且postgresql既支持一維數(shù)組，也支持多維數(shù)組，但是平時二維數(shù)組也就夠用了。
示例1.使用ARRAY構(gòu)建數(shù)組類型
---1*4的一維數(shù)組test=#selectarray[1,2,3,4];
array? -----------{1,2,3,4}
(1 row)--2*2的二維數(shù)組test=#selectarray[[1,2],[3,4]];
array? ? ---------------{{1,2},{3,4}}
(1 row)--1*2的二維數(shù)組，基本類型為box矩形類型，且和上面相比box類型元素之間是以分號分隔的，其他所有類型的數(shù)據(jù)都是以逗號分隔的test=#selectarray[box'(1,1),(0,0)',box'(0,0),(-1,-1)'];
? ? ? ? array? ? ? ? ? ? -----------------------------{(1,1),(0,0);(0,0),(-1,-1)}
(1row)
示例2.創(chuàng)建一張表，字段包含數(shù)組類型
其中int[]表示數(shù)組長度無限制，int[4]表示數(shù)組長度為4.
test=#createtabletbl_array(aint[],bvarchar(32)[][],cint);CREATETABLEtest=#insertintotbl_array (a,b,c)values(array[1,2],array[[1,2,3],[4,5,6]],1);INSERT01test=#insertintotbl_array (a,b,c)values(array[1,2,3],array[[1,2],[4,5]],2);INSERT01test=#select*from tbl_array ;
a? ? |b| c ---------+-------------------+---{1,2}|{{1,2,3},{4,5,6}}|1 {1,2,3}|{{1,2},{4,5}}|2(2 rows)
test=#selecta[1],b[2]fromtbl_arraywherec=1;
a | b ---+---1|
(1 row)
test=#selecta[1],b[2][1]fromtbl_arraywherec=1;
a | b ---+---1|4(1 row)
test=#selecta[1],b[2][4]fromtbl_arraywherec=1;
a | b ---+---1|
(1row)
test=#updatetbl_arrayseta[1]=200wherea[1]=1;UPDATE1test=#selecta[1],b[2][4]from tbl_array ;
a? | b -----+---100|200|
(2rows)
也可以使用[下標:上標]方式來查詢數(shù)組連續(xù)的某些元素。
test=#selecta[2:3]from tbl_array ;
a? -------{2}
{2,3}
(2 rows)
test=#selecta[1:3]from tbl_array ;
a? ? -----------{100,2}
{200,2,3}
(2rows)
數(shù)組操作符與函數(shù)
操作符
操作符描述示例結(jié)果
=相等?SELECT ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3];?t
不等于?select ARRAY[1,2,3] ARRAY[1,2,4];?t
小于?select ARRAY[1,2,3] ARRAY[1,2,4];?t
大于?select ARRAY[1,4,3] ARRAY[1,2,4];?t
=小于或等于?select ARRAY[1,2,3] = ARRAY[1,2,3];?t
=大于或等于?select ARRAY[1,4,3] = ARRAY[1,4,3];?t
@包含?select ARRAY[1,4,3] @ ARRAY[3,1];?t
@包含于?select ARRAY[2,7] @ ARRAY[1,7,4,2,6];?t
重疊（是否有相同元素）?select ARRAY[1,4,3] ARRAY[2,1];?t
||數(shù)組與數(shù)組連接?select ARRAY[1,2,3] || ARRAY[4,5,6];?{1,2,3,4,5,6}
||數(shù)組與數(shù)組連接?select ARRAY[1,2,3] || ARRAY[[4,5,6],[7,8,9]];?{{1,2,3},{4,5,6},{7,8,9}}
||元素與數(shù)組連接?select 3 || ARRAY[4,5,6];?{3,4,5,6}
||數(shù)組與元素連接?select ARRAY[4,5,6] || 7;?{4,5,6,7}
函數(shù)
函數(shù)返回類型描述示例結(jié)果
array_append(anyarray,anyelement)anyarray?在數(shù)組末尾追加元素?
SELECT array_append(ARRAY[1,2], 3);
{1,2,3}
array_cat(anyarray,anyarray)anyarray?連接兩個數(shù)組?SELECT array_cat(ARRAY[1,2,3], ARRAY[4,5]);?{1,2,3,4,5}
array_ndims(anyarray)int?返回數(shù)組維數(shù)?SELECT array_ndims(ARRAY[[1,2,3], [4,5,6]]);?2
array_dims(anyarray)text?返回數(shù)組維數(shù)的文本表示?SELECT array_dims(ARRAY[[1,2,3], [4,5,6]]);?[1:2][1:3]
array_fill(anyelement,int[], [,int[]])anyarray使用提供的值和維度初始化一個數(shù)組，其中anyelement是值，第一個int[]是數(shù)組的長度，第二個int[]是數(shù)組下界，下界默認是1?SELECT array_fill(7, ARRAY[3], ARRAY[2]);?[2:4]={7,7,7}
array_length(anyarray,int)int?返回數(shù)組指定維度的長度?SELECT array_length(array[1,2,3], 1);?3
array_lower(anyarray,int)int?返回數(shù)組指定維度的下界?SELECT array_lower('[0:2]={1,2,3}'::int[], 1);?0
array_position(anyarray,anyelement[,int])int?返回數(shù)組元素anyelement從數(shù)組的[,int]位置（默認為1）開始第一次出現(xiàn)在數(shù)組中的位置，數(shù)組必須是一維的?SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');?2
array_positions(anyarray,anyelement)int[]?返回元素在數(shù)組中的所有位置?SELECT array_positions(ARRAY['A','A','B','A'], 'A');?{1,2,4}
array_prepend(anyelement,anyarray)anyarray?在數(shù)組開頭添加新的元素?SELECT array_prepend(1, ARRAY[2,3]);?{1,2,3}
array_remove(anyarray,anyelement)anyarray?從數(shù)組中刪除所有的指定元素，必須是一維數(shù)組?SELECT array_remove(ARRAY[1,2,3,2], 2);?{1,3}
array_replace(anyarray,anyelement,anyelement)anyarray?替換指定數(shù)組元素為新的元素?SELECT array_replace(ARRAY[1,2,5,4], 5, 3);?{1,2,3,4}
array_to_string(anyarray,text[,text])text?將數(shù)組元素使用分隔符連接為文本，NULL可以使用指定元素替換?SELECT array_to_string(ARRAY[1, 2, 3, NULL, 5], ',', '*');?1,2,3,*,5
array_upper(anyarray,int)int?數(shù)組指定維度的上屆?SELECT array_upper(ARRAY[1,8,3,7], 1);?4
cardinality(anyarray)int?返回數(shù)組所有維度的長度總和，如果是空數(shù)組則返回0?SELECT cardinality(ARRAY[[1,2],[3,4]]);?4
string_to_array(text,text[,text])text[]?將文本使用分隔符分隔后轉(zhuǎn)換為數(shù)組，如果指定第三個參數(shù)，則第三個參數(shù)在數(shù)組中被轉(zhuǎn)換為NULL?SELECT string_to_array('xx~^~yy~^~zz', '~^~', 'yy');?{xx,NULL,zz}
unnest(anyarray)setof anyelement?將數(shù)組元素轉(zhuǎn)換為行?SELECT unnest(ARRAY[1,2]);?
1
2
unnest(anyarray,anyarray[, ...])setof anyelement, anyelement [, ...]?將多維數(shù)組轉(zhuǎn)換為行集合，其中第一個數(shù)組顯示為第一列，第二個數(shù)組顯示為第二列，以此類推。但是這個函數(shù)只在from子句中使用?SELECT * from unnest(ARRAY[1,2],ARRAY['foo','bar','baz']);?
unnest | unnest
--------+----
1 | foo
2 | bar
| baz
有的。PostgreSQL內(nèi)建有分析函數(shù)，PostgreSQL稱之為Window Function，有如下這些：
row_number()
rank()
dense_rank()
percent_rank()
cume_dist()
ntile(num_buckets integer)
lag(value any [, offset integer [, default any ]])
lead(value any [, offset integer [, default any ]])
first_value(value any)
last_value(value any)
nth_value(value any, nth integer)
具體說明參看PostgreSQL說明文件中Funcstions Operates下的Winow Functions。
用drop語句,
drop
function
function_name
(parameters_list);
要注意：在寫drop語句時，需要將函數(shù)參數(shù)寫完整，因為postgresql中允許函數(shù)的重載的，即同一個函數(shù)名可以擁有不同的參數(shù)，雖然函數(shù)名一樣，但是代表不同的函數(shù)體；故參數(shù)需要寫完整。
重慶分公司
重慶分公司
