SQL学习之开窗函数--内置函数盘点

开窗函数

翻译自https://www.postgresql.org/docs/8.4/functions-window.html

开窗函数提供了跨越行集执行计算的能力，行集中的行通过某种方式与当前查询行联系在一起。

内置开窗函数列表见表-1。注意调用这些函数必须使用开窗函数语法；也就是需要加上OVER子句。

除了这些函数，任何内置和自定义的聚合函数也可以作为开窗函数使用。聚合函数只有当在调用语句后面加上OVER子句后才会当作开窗函数执行，否则他们就是正常的聚合函数。

表1-常见的开窗函数

函数	返回值类型	描述
row_number()	bigint	组内当前行的序号，从1开始
rank()	bigint	有间隔排名，多个相同排名的行，各行排名与相同排名行中第一行的行号（即row_number）一致
dense_rank()	bigint	无间隔排名， 这个函数讲相同排名行作为一个整体组进行计数
percent_rank()	double precision	当前行的相对排名：（rank -1 )/(total rows -1)
cume_dist()	double precision	当前行的相对排名：（当前行前面的行数或者当前行的同序行的行数)/(总行数)
ntile(num_buckets integer)	integer	从1开始到给定参数的整数，将分区内的行尽量均匀分割
lag(value any [, offset integer [, default any ]])	same type as value	在分区内从当前行向上移动offset行，函数返回的是这行给定的值；如果没有那样的行，则返回默认值； offset和default都是针对当前行的。如果缺省，offset默认为1，default默认为null。
lead(value any [, offset integer [, default any ]])	same type as value	在分区内从当前行向下移动offset行，函数返回的是这行给定的值；如果没有那样的行，则返回默认值； offset和default都是针对当前行的。如果缺省，offset默认为1，default默认为null。
first_value(value any)	same type as value	返回窗口的第一行给定的值
last_value(value any)	same type as value	返回窗口的最后一行给定的值
nth_value(value any, nth integer)	same type as value	返回窗口第nth行给定的值（从1开始数），如果没有第nth行则为null

 

 开窗函数定义中的order by语句确定了排序规则，表1中列的所有函数都参照排序进行赋值。在order by排序时有相同值的行称为同序行（peers）；表中四个排名函数规定任何两个同序行函数的返回值相同。

注意first_value,last_value,和nth_value只针对“窗口”中的行，这些行包括从分区的开始行开始到当前行的最后一个同序行（译者注：这样想象：可以将每一行看作是百叶窗的一个叶，开始百叶窗的叶子全部叠积在上面，每往下拉展开一叶（行），窗口就增加一行，刚下拉的这一行也即当前行）。nth_value，尤其是last_value可能会返回没用的结果。你可以在over语句的最后添加ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING以将“窗口”重新定义为整个分区。查看Section 4.2.8获得更多信息。

当一个聚合函数当作开窗函数使用时，它作用在当前行所在“窗口“中的行。为了获得整个分区的聚合结果，可以省略order by或者使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。当使用order by和默认的“窗口”定义时，聚合函数会出现“动态求和”式的行为，这种结果或许不是你想要的。

 

注：标准SQL为lead,lag,first_value,last_value和nth_value定义了一个RESPECT NULLS或IGNORE NULLS的参数选项。但是在PostgreSQL中没有应用：函数只有标准的默认值，即RESPECT NULLS。同样的，nth_value函数标准的FROM FIRST 或 FROM LAST选项也在PostgreSQL中没有应用：只支持默认的FROM FIRST。（你可以对order by倒序排序以实现FROM LAST的效果。）