pgsql的窗口函数简述

概述

  窗口函数（Window Function） 本身是聚集类函数，具有对特定结果行聚合处理的功能，但是窗口函数并不会使多行被聚集成一个单独的输出行，而是在函数计算的每一行都输出相应的结果，这与通常的非窗口聚集函数不同；此外，窗口函数作用在由关键字 OVER 划定的所谓 窗口 行上，这些行就是窗口函数的作用域。

  个人的理解就是将数据表中的数据按照指定的功能进行分区（窗口），然后再各自区域内进行操作的函数就是窗口函数。窗口函数不能聚合数据行，只能进行结果的输出计算。

支持窗口函数的查询元素

并不是所有的查询子句都支持窗口函数，相反，仅仅SELECT和ORDER BY 子句支持窗口函数。为帮助理解，我们先看看SQL不同子句的执行顺序：

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
   5.1. Evalute Expressions（判断表达式）
   5.2. 删除重复数据
6. ORDER BY
7. OFFSET-FETCH/TOP

只有SELECT和ORDER BY 子句直接支持窗口函数。做这个限制的原因是为了避免二义性，因此把（几乎是）查询的最终结果当作窗口的起点。如果窗口函数可以早于SELECT阶段出现，那么通过一些查询表单会无法得到正确的结果。

使用

  关于窗口函数如何进行定义这里不做过多的说明，可以取网络查找资料，这里只是简单介绍。

• 定义

1. 直接定义使用

窗口函数名(函数参数) OVER (窗口定义)

function_name ([expression [, expression … ]]) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )
或
function_name ( * ) [ FILTER ( WHERE filter_clause ) ] OVER ( window_definition )

2. 先定义后使用

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

• 语法

1. over:窗口函数关键字
2. partition by:对结果集进行分组
3. order by:设定结果集的分组数据排序

• 说明

1. 窗口函数既可以显示聚集前的数据，又可以显示聚集后的数据
2. 窗口函数都是最后一步执行，而且仅位于order by字句之前
3. partition by子句可以称为查询分区子句，非常类似于group by都是将数据按照边界值分组，而over之前的函数在每一个分组之内进行，如果超出了分组，则函数会重新计算
4. order by子句会让输入的数据强制排序。order by子句对于诸如的row_number(),lead(),lag()等函数是必须，否则结果无意义
5. 如果只使用partition by子句，未指定order by此时聚合是分组内的聚合
6. 当同一个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	表示到后面的终点