PostgreSQL window function

window function，简单来说就是窗口函数。postgresql 在8.4版本开始有了窗口函数的特性。

看多很多中文解释，找不到合适的解释什么是窗口函数，有句英文很好的诠释了什么是窗口函数。 “The whole idea behind window functions is to allow you to process several values of the result set at a time: you see through the window some peer rows and are able to compute a single output value from them,much like when using an aggregate function. ”
先来介绍一下postgresql里面关于窗口函数的语法吧。
function name over ( partition by column order by column [ RANGE | ROWS ] BETWEEN frame_start AND frame_end);
这是简化的版本，官网上对于语句写的更详细，有兴趣的可以自己去看看。对于第一次看见的人来说，这么长的语法可能一下子无法接受，那我们就慢慢一步一步来的说。

首先创建下面这张表

点击(此处)折叠或打开

 
   createtableempsalary
 
 (
 
 depnamevarchar(20),
 
 empno 
 salaryinteger
 
 )

然后插入数据

@H_301_81@

点击(此处)折叠或打开

postgres=#select*fromempsalary;

depname|empno|salary

----------+-------+--------

develop|11|5200

develop|7|4200

develop|9|4500

personel|5|3500

personel|6|6500

personel|12|15|8900

(7 行记录)

Function	Return Type	Description
row_number()	bigint	number of the current row within its partition,counting from 1
rank()	rank of the current row with gaps; same asrow_numberof its first peer
dense_rank()	rank of the current row without gaps; this function counts peer groups
percent_rank()	double precision	relative rank of the current row: (rank- 1) / (total rows - 1)
cume_dist()	relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)
ntile(num_bucketsinteger)	integer	integer ranging from 1 to the argument value,dividing the partition as equally as possible
lag(valueany[,offsetinteger[,defaultany]])	same type asvalue	returnsvalueevaluated at the row that isoffsetrows before the current row within the partition; if there is no such row,instead returndefault. Bothoffsetanddefaultare evaluated with respect to the current row. If omitted,offsetdefaults to 1 anddefaultto null
lead(valueany[,255)"> returnsvalueevaluated at the row that isoffsetrows after the current row within the partition; if there is no such row,255)"> first_value(valueany)	returnsvalueevaluated at the row that is the first row of the window frame
last_value(valueany)	returnsvalueevaluated at the row that is the last row of the window frame
nth_value(valueany,nthinteger)	returnsvalueevaluated at the row that is thenthrow of the window frame (counting from 1); null if no such row

Function

Return Type

Description

row_number()

bigint

number of the current row within its partition,counting from 1

rank()

rank of the current row with gaps; same asrow_numberof its first peer

dense_rank()

rank of the current row without gaps; this function counts peer groups

percent_rank()

double precision

relative rank of the current row: (rank- 1) / (total rows - 1)

cume_dist()

relative rank of the current row: (number of rows preceding or peer with current row) / (total rows)

ntile(num_bucketsinteger)

integer

integer ranging from 1 to the argument value,dividing the partition as equally as possible

lag(valueany[,offsetinteger[,defaultany]])

same type asvalue

returnsvalueevaluated at the row that isoffsetrows before the current row within the partition; if there is no such row,instead returndefault. Bothoffsetanddefaultare evaluated with respect to the current row. If omitted,offsetdefaults to 1 anddefaultto null

lead(valueany[,255)"> returnsvalueevaluated at the row that isoffsetrows after the current row within the partition; if there is no such row,255)"> first_value(valueany)

returnsvalueevaluated at the row that is the first row of the window frame

last_value(valueany)

returnsvalueevaluated at the row that is the last row of the window frame

nth_value(valueany,nthinteger)

returnsvalueevaluated at the row that is thenthrow of the window frame (counting from 1); null if no such row

PostgreSQL window function

猜你在找的Postgre SQL相关文章