pg內建函數__函數

一、            視窗函數

PG是在8.4以後版本中添加了一些視窗函數的功能，所謂視窗函數就是對分組中的每個行都返回分組結果，而彙總函式則是對分組中的每個組只返回一個分組結果，對於這句話大家可能還不理解，不過沒關係，下面通過執行個體來講解。

 

1、  PG視窗函數有(黃底的為常用視窗函數)：

sum(): 組內求和

avg(): 組內平均

row_number(): 組內排序，不間斷,如1，2，3，4，5，6

rank() : 組內排序，會間斷，如1，2，2，4，5，6

dense_rank(): 組內排序，但會重複，如1，2，2，3，4，5

first_value(value any)：返回視窗架構中的第一個值

last_value(value any)：返回視窗架構中的最後一個值

count(*) over(partition by col_name)

max(*) over(partition by col_name)

min(*) over(partition by col_name)

nth_value(value any, nth integer)：返回視窗架構中的指定值，如nth_value(salary,2),則表示返回欄位salary的第二個視窗函數值

lag(value any [, offset integer [, defaultany ]])：位移量函數，取滯後值,如lag(column_name,2,0)表示欄位位移量為2，沒有則用default值代替，這裡是0，不寫預設是null

lead(value any [, offset integer [, defaultany ]])：位移量函數，取提前值,類上

percent_rank()：和CUME_DIST（累積分配）函數類似，對於一個組中給定的行來說，在計算那行的序號時（如果存在排序一樣的情況，則取排序小的序號），先減1，然後除以n-1（n為組中所有的行數）。該函數總是返回0～1（包括1）之間的數

cume_dist()：累積分配，對於一個組中給定的行來說，在計算那行的序號時（如果存在排序一樣的情況，則取排序大的序號）然後除以n（n為組中所有的行數）。該函數總是返回0～1（包括1）之間的數

ntile(num_buckets integer)：將一個組分為"運算式"的散列表示，例如，如果運算式=4，則給組中的每一行分配一個數（從1到4），如果組中有20行，則給前5行分配1，給下5行分配2等等。如果組的基數不能由運算式值平均分開，則對這些行進行分配時，組中就沒有任何percentile的行數比其它percentile的行數超過一行。例如，若運算式=4，行數=21，則percentile=1的有6行，percentile=2的有5行等等

 

2、  構建執行個體所需要的資料，如下：

DROPTABLEIFEXISTS empsalary;

CREATETABLE empsalary(

  depname varchar,

  empno bigint,

  salary int,

  enroll_date date

);

INSERTINTO empsalaryVALUES('develop',10,5200,'2007/08/01');

INSERTINTO empsalaryVALUES('sales',1,5000,'2006/10/01');

INSERTINTO empsalaryVALUES('personnel',5,3500,'2007/12/10');

INSERTINTO empsalaryVALUES('sales',4,4800,'2007/08/08');

INSERTINTO empsalaryVALUES('sales',6,5500,'2007/01/02');

INSERTINTO empsalaryVALUES('personnel',2,3900,'2006/12/23');

INSERTINTO empsalaryVALUES('develop',7,4200,'2008/01/01');

INSERTINTO empsalaryVALUES('develop',9,4500,'2008/01/01');

INSERTINTO empsalaryVALUES('sales',3,4800,'2007/08/01');

INSERTINTO empsalaryVALUES('develop',8,6000,'2006/10/01');

INSERTINTO empsalaryVALUES('develop',11,5200,'2007/08/15');

 

3、  sum()：統計各部門的總薪水，視窗函數的結果如下：

SELECTSUM(salary)OVER(PARTITIONBY depname),* FROM empsalary;

彙總函式的結果如下：

SELECTSUM(salary),depnameFROM empsalaryGROUPBY depname;

從上面兩個語句返回的結果可以看出，彙總函式只返回各個分組的匯總結果，而視窗函數則是在原表展現的基礎上返回所在部門的匯總結果，有了此函數我們就可以很簡單的解決一個業務情境，求每個人薪水在整個部分中的比例，如下

SELECT *,

salary/(SUM(salary)OVER(PARTITION BYdepname))::numeric(10,2)

FROM empsalary;

當然不用此函數也能解決這個業務問題，但是寫法稍複雜，效能稍差。

 

4、  avg() 統計部門的平均薪水

SELECTAVG(salary)OVER(PARTITIONBY depname),* FROM empsalary;

 

5、  row_number()\rank()\dense_rank()，組內排序函數，按部門統計薪水從低到高排名：

--如果部門有相同的薪水排名也不重複

SELECTROW_NUMBER()OVER(PARTITIONBY depname ORDERBY salary),*

FROMempsalary;

--如果部門有相同的薪水排名重複，但是接下來的一個排名會間斷

SELECTRANK()OVER(PARTITIONBY depname ORDERBY salary),*

     FROM empsalary;

--如果部門有相同的薪水排名重複，但是接下來的一個排名不會間斷

SELECTDENSE_RANK()OVER(PARTITIONBY depname ORDERBY salary),*

     FROM empsalary;

 

6、  first_value\last_value\ nth_value 取組內指定值

--取各個部門最高薪水

SELECT first_value(salary)OVER(PARTITIONBY depname ORDERBY salaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING),

        *

FROM empsalary;

--取各個部門最低薪水

SELECT last_value(salary)OVER(PARTITIONBY depname ORDERBY salaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING),

        *

FROM empsalary;

--取各個部門第二高薪

SELECT nth_value(salary,2)OVER(PARTITIONBY depname ORDERBY salaryDESCROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING),

        *

FROM empsalary;

7、  lag\lead 取組內位移行值

--部門薪水向後位移2行

SELECT LAG(salary,2,NULL)OVER(PARTITIONBY depname ORDERBY salaryDESC),

*

FROM empsalary;

--部門薪水向前位移2行

SELECT LEAD(salary,2,NULL)OVER(PARTITIONBY depname ORDERBY salaryDESC),

*

FROM empsalary;

8、  percent_rank()\cume_dist()\ntile() 分布函數

SELECT percent_rank()over(PARTITIONBY depname ORDERBY