PostgreSQL window function

发布时间：2020-12-13 17:16:10 所属栏目：百科来源：网络整理

导读：Postgresql window function 第一篇 window function，简单来说就是窗口函数。postgresql 在8.4版本开始有了窗口函数的特性。看多很多中文解释，找不到合适的解释什么是窗口函数，有句英文很好的诠释了什么是窗口函数。 “ The whole idea behind window fu

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
 
 )

然后插入数据

点击(此处)折叠或打开

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 行记录)

EXAMPLE 1
只使用 function name over（），这是窗口函数使用最简单的方式了吧。

点击(此处)折叠或打开

selectdepnamesalarysum(salary)over(|salary|sum

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

develop|5200|39300

develop|4200|4500|39300

personel|3500|6500|8900|39300

)

光看这个结果，可以看出最后相等的一列就是表是所有行的salary的总值。仔细体会上面那句英文： you see through the window some peer rows and are able to compute a single output value from them。这里的single output value 就是sum，这里的rows就是说有的表中所有的行。这就可以很好的理解了窗口函数的意思了吧。由于这里没有对窗口进行什么设定，所以看到的就是整个表当中的数据。

关于窗口函数就先写到这里吧。

Postgresql window function 第二篇

上一篇很好的解释了什么是窗口函数，并且举了一个最简单的例子，帮助理解什么是窗口函数。接下来我们来更深入的理解postgresql的窗口函数。还是借用上一篇新建的表来讲解。

点击(此处)折叠或打开

d empsalary

资料表"public.empsalary"

栏位|型别|修饰词

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

depname|character varying|

empno|

salary|integer|

EXAMPLE 2：
function name over ( partition by column) 这里的partition by 子句用于对行进行分组的。

点击(此处)折叠或打开

(partitionbydepname) fromempsalary;

depname|13900

develop|13900

personel|25400

)

由于这次对窗口进行了限制，每一行只能看见自己的分组，所以develop组的sum都是一样的，而personel组的sum是一样的。

EXAMPLE3：
function name over (order by column)

点击(此处)折叠或打开

orderbysalaryfrom

empsalary;

depname----------+-------+--------+-------

personel|3500

develop|7700

develop|12200

develop|17400

personel|30400

personel 上面的结果是按照salary的正序排列的，但是sum列显得很奇怪，好像是累积的加法，好像不是。这就取决于，到底这里的每一行从窗口看看到了什么，我们可以用以下的语句看一下。

点击(此处)折叠或打开

)

|array_agg ----------+-------+--------+--------------------------------------+-------

personel|5 |3500 {3500} |3500

develop |7 |4200 } |7700

develop |9 |4500 } |12200

develop |5200 } |6 |6500 } |8900 } 每一行能看见的结果都在array_agg列当中可以看出，这就很容易理解为什么sum的值会变成现在像上面一样奇怪的结果吧。其实这里的
EXAMPLE 3:
function name over （order by column [rows | range] betweenframestart and frameend）;
rows between 子句无法独自和over一起使用，这个子句的作用也是决定了那些行可以被每一行看到。

点击(此处)折叠或打开

rowsbetween unbounded precedingandcurrentrow)|23900

personel 现在的结果是安salary从小到大排列的，sum刚好就是累积的加法运算。这里的framestart和frameend只能从固定的三个值当中取： unbounded preceding ， current row， unbounded following。第一个表示第一行，第二个表示当前行，最后一个表示最后一行，而且他们顺序也不能调换。这能是 unbounded preceding and current row 或者是 current row and unbounded following 再或者就是 unbounded preceding and unbounded following。
至此所有的部分都已经讲完了，在复杂的用法就是把他们结合起来使用。相信大家能很好的使用窗口函数。

postgresql windows function 第三篇

前面两篇已经很好的介绍了窗口函数，这篇我们来关注一下function name这个部分。postgresql有一些内置的函数，专门用于窗口函数。如下

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

这里我们一个一个的来。
row_number()

点击(此处)折叠或打开

;

depname|empno|salary|row_number

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

develop |1

develop |2

develop |3

personel|4

personel|5

personel|6

personel|7

其实这个函数就是给窗口内的每一行编号，从一号开始。不过如果没有排序的话，这样的编号感觉也没有意思啊。
fromempsalary orderbysalary|row_number

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

personel|4

develop|2

develop|3

develop|1

personel怪了，使用order by 但是这里放的地方错误，可以从这里推断，row_number(）是比order by 先执行的。
正确的写法是

点击(此处)折叠或打开

#select depname(order by salary)from empsalary|row_number

-+-

personel|1

develop)

rank（）

点击(此处)折叠或打开

)from empsalary|rank

从结果可以看出，rank其实和row_number()差不多，唯一的差别有两个5，没有6.这就是所谓的gap吧。
dense_rank()

点击(此处)折叠或打开

)from empsalary|dense_rank

dense_rank()和rank是一样的，但是区别还是有的，他是没有gap的。
percent_rank() 和 cume_dist() 这两个函数不知道有什么用，不过计算的公式就在上面，看看就知道怎么算了。

ntile()

点击(此处)折叠或打开

(3)from empsalary|ntile

personel|3

这个函数的作用是将结果分组，3 表示分为三组。每一组再编号。
lag( value any [, offsetinteger default ]])

点击(此处)折叠或打开

from empsalary|lag

develop|5200

personel|6500

获取前一行的对应列，如果没有的话，就用0表示。而lead刚好和他相反。自己试一下就知道了。
first_value()

点击(此处)折叠或打开

)from empsa

lary|first_value

develop|5200

获取windows frame的第一行。
last_value() 刚好和first_value()相反，取最后一列。

点击(此处)折叠或打开

)from empsal

ary|last_value

|8900

develop|8900

personel再来一个

点击(此处)折叠或打开

(order by sala

ry)from empsalary|4500

develop为什么这里会不一样的，仔细想想order by为什么会导致这么样的结果，如果你看了第一篇的话，就能明白了。
而nth_value()我想你自己从字面上就能理解了吧。这里就不讲解了。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!