PostgreSQL8.4新功能：Window函数

简单介绍一下PostgreSQL 8.4中新增功能Window函数。

Window函数不同于一般的集约 函数。不能像集 约 函数一 样 把复数行 输 出一行，而是复数行都能 够 保持自己的属性，不能 访问 其他行的信息。

语 法如下：

function_name ([ expression [, expression ... ]]) OVER ( window_definition )

function_name ([ expression [, expression ... ]]) OVER window_name

function_name ( * ) OVER ( window_definition )

function_name ( * ) OVER window_name

其中window_definition部分的定义如下：

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]

[ frame_clause ]

简单描述就是以下 形式

函数(...) OVER (PARTITION BY ...) : 区间 分割

函数(...) OVER (ORDER BY ...) : 每个区间 排序

通常的集约函数也能够利用。如count，sum，avg等

主要的Window函数如下：

row_number()：行号

rank()：排名 (顺 序一 样 的 时 候，跳 过 番号)

dense_rank()：排名 (顺 序一 样 的 时 候，不跳 过 番号)

percent_rank()：排名 (%百分比表示) : (rank - 1) / (全行数 - 1)

cume_dist()：和percent_rank类 似 : (現在的行的位置) / (全行数)

ntile(N)：排名(1..N 分割)

lag(value, offset, default)：排序状态 的前面一行的 值

lead(value, offset, default)：排序状态 的后面一行的 值

first_value(value)：最初的值

last_value(value)：最后的值

nth_value(value, N)：第N行的值 (行号从1开始数)

例子1：每个部门 的 员 工的平均工 资 和 员 工的工 资 相比 较

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname  | empno | salary | avg部门平均工资

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

develop   |    11 |   5200 | 5020.0000000000000000

develop   |     7 |   4200 | 5020.0000000000000000

develop   |     9 |   4500 | 5020.0000000000000000

develop   |     8 |   6000 | 5020.0000000000000000

develop   |    10 |   5200 | 5020.0000000000000000

personnel |     5 |   3500 | 3700.0000000000000000

personnel |     2 |   3900 | 3700.0000000000000000

sales     |     3 |   4800 | 4866.6666666666666667

sales     |     1 |   5000 | 4866.6666666666666667

sales     |     4 |   4800 | 4866.6666666666666667

(10 rows)

例子2：部门员 工的工 资 排名

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

depname  | empno | salary | rank排名

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

develop   |     8 |   6000 |    1

develop   |    10 |   5200 |   2

develop   |    11 |   5200 |   2

develop   |     9 |   4500 |    4

develop   |     7 |   4200 |    5

personnel |     2 |   3900 |   1

personnel |     5 |   3500 |   2

sales      |     1 |   5000 |    1

sales      |     4 |   4800 |    2

sales      |     3 |   4800 |    2

(10 rows)