概述
窗口函数（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 | 表示到后面的终点 |
