概述
窗口函数(Window Function) 本身是聚集类函数,具有对特定结果行聚合处理的功能,但是窗口函数并不会使多行被聚集成一个单独的输出行,而是在函数计算的每一行都输出相应的结果,这与通常的非窗口聚集函数不同;此外,窗口函数作用在由关键字 OVER 划定的所谓 窗口 行上,这些行就是窗口函数的作用域。
个人的理解就是将数据表中的数据按照指定的功能进行分区(窗口),然后再各自区域内进行操作的函数就是窗口函数。窗口函数不能聚合数据行,只能进行结果的输出计算。
支持窗口函数的查询元素
并不是所有的查询子句都支持窗口函数,相反,仅仅SELECT和ORDER BY 子句支持窗口函数。为帮助理解,我们先看看SQL不同子句的执行顺序:
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
5.1. Evalute Expressions(判断表达式)
5.2. 删除重复数据 - ORDER BY
- OFFSET-FETCH/TOP
只有SELECT和ORDER BY 子句直接支持窗口函数。做这个限制的原因是为了避免二义性,因此把(几乎是)查询的最终结果当作窗口的起点。如果窗口函数可以早于SELECT阶段出现,那么通过一些查询表单会无法得到正确的结果。
使用
关于窗口函数如何进行定义这里不做过多的说明,可以取网络查找资料,这里只是简单介绍。
- 直接定义使用
窗口函数名(函数参数) OVER (窗口定义)
function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
或
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
- 先定义后使用
WINDOW 窗口名 AS (窗口定义) [,…]
SELECT 窗口函数名(函数参数) OVER 窗口名 FROM 表名 WINDOW 窗口名 AS (窗口定义) [,…]
SELECT function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause ) ] OVER window_name FROM from_item WINDOW window_name AS ( window_definition ) [, …]
或
SELECT function_name ( * ) [ FILTER ( WHERE filter_clause ) ] FROM from_item OVER window_name
- over:窗口函数关键字
- partition by:对结果集进行分组
- order by:设定结果集的分组数据排序
- 窗口函数既可以显示聚集前的数据,又可以显示聚集后的数据
- 窗口函数都是最后一步执行,而且仅位于order by字句之前
- partition by子句可以称为查询分区子句,非常类似于group by都是将数据按照边界值分组,而over之前的函数在每一个分组之内进行,如果超出了分组,则函数会重新计算
- order by子句会让输入的数据强制排序。order by子句对于诸如的row_number(),lead(),lag()等函数是必须,否则结果无意义
- 如果只使用partition by子句,未指定order by此时聚合是分组内的聚合
- 当同一个select查询中存在多个窗口函数时,他们相互之间是没有影响的
postgresql内置窗口函数
函数名称 | 返回类型 | 描述 |
---|---|---|
row_number() | bigint | 在其分区中的当前行号,从1开始计算 |
rank() | bigint | 有间隔的当前行排名;与它的第一个相同行的row_number相同 |
dense_rank() | bigint | 没有间隔的当前行排名;这个函数计数对等组 |
percent_rank | double precision | 当前行的相对排名:(rank-1)/(总行数-1) |
cume_dist() | double precision | 当前行的相对排名:(前面的行数或与当前行相同的行数)/(总行数) |
ntile(num_buckets integer) | integer | 从1到参数的整数范围,尽可能相等的划分分区 |
lag(value any [, offset integer [, default any ]]) | 类型同value | 计算分区当前行的前offset行,返回value。如果没有这样的行,返回default替代。offset和default都是当前计算的结果。如果忽略了则offset默认是1,default默认是null |
lead(value any [, offset integer [, default any ]]) | 类型同value | 计算分区当前行的后offset行,返回value。如果没有这样的行,返回default替代。offset和default都是当前计算的结果。如果忽略了则offset默认是1,default默认是null |
first_value(value any) | 类型同value | 返回窗口第一行的计算value值 |
last_value(value any) | 类型同value | 返回窗口最后一行的计算value值 |
nth_value(value any,nth integer) | 类型同value | 返回窗口的第nth行的计算value,从1开始计数,没有符合的行则返回null |
postgresql聚合函数+over
函数 | 描述 |
---|---|
sum | 求和 |
avg | 求平均数 |
count | 统计个数 |
max | 最大值 |
min | 最小值 |
window子句使用:分组后的再次划分
关键字 | 描述 |
---|---|
preceding | 往前 |
following | 往后 |
current row | 当前行 |
unbounded | 起点 |
unbounded preceding | 表示从前面的起点 |
unbounded following | 表示到后面的终点 |