窗口表达式

窗口表达式允许应用开发者更容易地使用标准SQL命令构造复杂的在线分析处理（OLAP）查询。例如，通过窗口查询，用户可以在多个区间上计算移动平均或者总和、在选择的列值改变时重置聚集和排名以及用简单的术语表达合比。

窗口表达式表示将一个窗口函数应用到一个窗口帧，后者在一个特殊的OVER()子句中定义。窗口分区是一个行的集合，这些行被集合起来应用一个窗口函数。这与使用聚合函数和GROUP BY子句可以完成的计算类型相当。和聚集函数（为每个行分组返回一个结果值）不同，窗口函数为每一行返回一个结果值，但是该结果值是针对一个特定窗口分区中的行计算得来。如果没有指定分区，窗口函数会在完整的中间结果集上计算。

Greenplum数据库不支持将窗口函数制定为另一个窗口函数的参数。

窗口表达式的语法是：

window_function ( [expression [, ...]] ) OVER ( window_specification )
其中window_functionwindow_function是列在 Table 4中的函数之一，expression是任何不含窗口表达式的值表达式，而window_specification是：

[window_name]
[PARTITION BY expression [, ...]]
[[ORDER BY expression [ASC | DESC | USING operator] [NULLS {FIRST | LAST}] [, ...]
    [{RANGE | ROWS} 
       { UNBOUNDED PRECEDING
       | expression PRECEDING
       | CURRENT ROW
       | BETWEEN window_frame_bound AND window_frame_bound }]]

其中window_frame_bound可以是下列之一：

    UNBOUNDED PRECEDING
    expression PRECEDING
    CURRENT ROW
    expression FOLLOWING
    UNBOUNDED FOLLOWING

窗口只能出现在SELECT 命令的选择列表中。例如：

SELECT count(*) OVER(PARTITION BY customer_id), * FROM sales;

在窗口表达式中，一个窗口函数调用必须紧跟OVER 子句。OVER子句使得窗口函数与其他聚集或者报表函数相区别。OVER子句定义窗口函数要应用于其上的window_specification。

窗口说明具有下列特点：

PARTITION BY 子句定义窗口函数要应用于其上的窗口分区。如果省略，整个结果集会被当做一个分区。
ORDER BY 子句定义在窗口分区内用来排序行的表达式。窗口说明的ORDER BY子句是独立的并且与一个常规查询表达式的ORDER BY 子句不同。计算排名的窗口函数会要求ORDER BY 子句，因为它要确定排名值的排名程度。对于OLAP聚集，使用窗口帧（ROWS和RANGE 子句）也要求ORDER BY子句。
Note: 没有一致排序的数据类型列（如 time）并不适合在窗口说明的ORDER BY子句中使用。 time（不管有没有指定的时区）缺少一种一致的排序，因为加法和减法无法得到预期的效果。例如，下面的式子并不总是为真：x::time < x::time + '2 hour'::interval
ROWS 或者 RANGE 子句为聚集（非排名）窗口函数定义窗口帧。一个窗口帧在一个窗口分区内定义一个行集合。当一个窗口帧被定义时，窗口函数会在这个移动帧的内容上计算，而不是在整个窗口分区的固定内容上计算。窗口帧可以是基于行的（ROWS）或者基于值的（RANGE）。
窗口例子

以下示例演示如何使用带有分区和窗口框架的窗口函数

例1 –分区上的聚合窗口函数

在OVER子句用PARTITION BY将行进行分组或分区，在相同值的分组内使用指定表达式。

此例子比较员工工资和部门平均工资：

SELECT depname, empno, salary, avg(salary) OVER(PARTITION BY depname)
FROM empsalary;
  depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

前三个输出列来自表empsalary，表中每一行都有一个输出行。第四列计算具有相同的 depname值的平均数。相同的 depname行组成一个分区，在这个例子中有三个分区。 avg函数与常规的聚合函数avg相同, 但是 OVER子句使它被用作一个窗口函数。

你可以把窗口的命名放在WINDOW 子句中，在select中引用. 这个例子相当于之前的查询:

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

当select列表中有多个使用相同功能的窗口函数时，定义一个命名的窗口比较有用。

例2 – 使用ORDER BY子句对窗口函数进行排序

在OVER子句中的ORDER BY ，控制窗口函数处理行的顺序。窗口函数的ORDER BY 列表不必匹配查询的输出顺序。这个例子使用 rank() 窗口函数来排序部门内的员工工资：

SELECT depname, empno, salary,
    rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;
  depname  | empno | salary | rank 
-----------+-------+--------+------
 develop   |     8 |   6000 |    1
 develop   |    11 |   5200 |    2
 develop   |    10 |   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)

例3 – 行窗口帧的具体函数

A RANGE 或 ROWS子句定义窗口函数计算的窗口帧(一个分区内的多个行)。 ROWS指定要处理的物理行，例如从分区开始到当前行之间的所有行。

此例按部门计算员工工资的连续总额。使用 sum() 函数计算分区开始到当前行的总行数：

SELECT depname, empno, salary,
    sum(salary) OVER (PARTITION BY depname ORDER BY salary
        ROWS between UNBOUNDED PRECEDING AND CURRENT ROW)
FROM empsalary ORDER BY depname, sum;
  depname  | empno | salary |  sum  
-----------+-------+--------+-------
 develop   |     7 |   4200 |  4200
 develop   |     9 |   4500 |  8700
 develop   |    11 |   5200 | 13900
 develop   |    10 |   5200 | 19100
 develop   |     8 |   6000 | 25100
 personnel |     5 |   3500 |  3500
 personnel |     2 |   3900 |  7400
 sales     |     4 |   4800 |  4800
 sales     |     3 |   4800 |  9600
 sales     |     1 |   5000 | 14600
(10 rows)

例4 – Range窗口帧的聚合函数

RANGE 指定OVER子句中基于ORDER BY 表达式的逻辑值。这个例子展示了 ROWS和RANGE的不同。窗口帧包含小于或等于当前行的所有行的工资值。与之前的例子不同，相同工资的员工，总和是相同的，包括所有这些员工的工资。

SELECT depname, empno, salary,
    sum(salary) OVER (PARTITION BY depname ORDER BY salary
        RANGE between UNBOUNDED PRECEDING AND CURRENT ROW)
FROM empsalary ORDER BY depname, sum;
  depname  | empno | salary |  sum  
-----------+-------+--------+-------
 develop   |     7 |   4200 |  4200
 develop   |     9 |   4500 |  8700
 develop   |    11 |   5200 | 19100
 develop   |    10 |   5200 | 19100
 develop   |     8 |   6000 | 25100
 personnel |     5 |   3500 |  3500
 personnel |     2 |   3900 |  7400
 sales     |     4 |   4800 |  9600
 sales     |     3 |   4800 |  9600
 sales     |     1 |   5000 | 14600
(10 rows)

下列内建窗口函数是Greenplum对于PostgreSQL数据库的扩展。所有的窗口函数都是immutable。

函数返回类型完整语法描述

cume_dist() double precision

CUME_DIST() OVER ( [PARTITION BY expr ] ORDER BY expr ) 计算一组值中一个值的累积分布。具有相等值的行总是具有相同的累积分布值。

dense_rank() bigint

DENSE_RANK () OVER ( [PARTITION BY expr ] ORDER BY expr ) 计算一个有序行组中一行的无跳跃排名值的排名。具有相等值的行会得到相同的排名值。

first_value(expr) same as input expr type

FIRST_VALUE( expr ) OVER ( [PARTITION BY expr ] ORDER BY expr [ROWS|RANGE frame_expr ] ) 返回一个有续值集合中的第一个值。

lag(expr [,offset] [,default]) same as input expr type

LAG( expr [, offset ] [, default ]) OVER ( [PARTITION BY expr ] ORDER BY expr ) 在不做自连接的情况下，提供对于同一个表中多于一行的访问。给定一个查询返回的一系列行以及该游标的一个位置，LAG提供对位于该位置之前一个给定物理偏移量的行的访问。默认的offset为1。 default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。

last_value(expr) same as input expr type

LAST_VALUE(expr) OVER ( [PARTITION BY expr] ORDER BY expr [ROWS|RANGE frame_expr] ) 返回一个有序值集合中的最后一个值。

lead(expr [,offset] [,default]) same as input expr type

LEAD(expr [,offset] [,exprdefault]) OVER ( [PARTITION BY expr] ORDER BY expr ) 在不做自连接的情况下，提供对于同一个表中多于一行的访问。给定一个查询返回的一系列行以及该游标的一个位置，lead提供对位于该位置之后一个给定物理偏移量的行的访问。如果没有指定offset，默认偏移量是1。default设置当偏移量超出窗口范围之外时要返回的值。如果没有指定default，默认值是空值。

ntile(expr) bigint

NTILE(expr) OVER ( [PARTITION BY expr] ORDER BY expr ) 把一个有序数据集划分成一些桶（由expr)定义）并且为每一行分配一个桶号。

percent_rank() double precision

PERCENT_RANK () OVER ( [PARTITION BY expr] ORDER BY expr ) 计算一个假设行R的排名减1，然后除以被计算的行数（在一个窗口分区内）减1。

rank() bigint

RANK () OVER ( [PARTITION BY expr] ORDER BY expr ) 计算一行在一个有序值组中的排名。根据排名标准有相等值的行得到相同的排名。被占用的行数被加到排名数上来计算下一个排名值。在这种情况下，排名可能不是连续的数字。

row_number() bigint

ROW_NUMBER () OVER ( [PARTITION BY expr] ORDER BY expr ) 为窗口分区中的每一行或者查询中的每一行分配一个唯一的编号。

greenplum中的窗口函数，转自GP中文网