转自：[https://blog.csdn.net/hyazz_/article/details/95052222](https://blog.csdn.net/hyazz_/article/details/95052222)

# 一．窗口函数简介

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。

开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

# 二．窗口函数基本语句形式

```
Function(arg1 , ……) over(partition by query_patition_clause order by order_by_clause Window_clause )

Window_clause : rows | range between start_expr and end_expr
Start_expr : unbounded preceding | current row | n preceding | n following
End_expr : unbounded following | current row | n preceding | n following
```

窗口函数带有一个开窗函数`over()`，包含三个分析子句：

```
分组(partition by)，即query_patition_clause
排序(order by)，即order_by_clause
窗口(rows)，即Window_clause
```

# 三．可使用窗口的函数

不是所有的函数都支持开窗函数。目前支持的窗口函数可结合的函数有：

```
Row_number；
和排名函数(rank、dense_rank)连用；
和错行函数(lead、lag)联合使用；
First_value和last_value；
NTILE；
Ratio_to_report；
和统计函数(max、min、avg、sum)等连用；
```

先构建测试表：

```
create table test(id int,name varchar(10),sale int);
insert into test values(1,'aaa',100);
insert into test values(1,'bbb',200);
insert into test values(1,'ccc',200);
insert into test values(1,'ddd',300);
insert into test values(2,'eee',400);
insert into test values(2,'fff',200);
select * from test;
```

| Ln   | ID   | NAME | SALE |
| ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  |
| 2    | 1    | bbb  | 200  |
| 3    | 1    | ccc  | 200  |
| 4    | 1    | ddd  | 300  |
| 5    | 2    | eee  | 400  |
| 6    | 2    | fff  | 200  |

下面依次介绍支持的窗口函数。

## 3.1 ROW_NUMBER

```
ROW_NUMBER() OVER(partition by col1 order by col2)
```

表示根据`col1`分组，在分组内部根据`col2`排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内是连续且唯一的）。

```
select t.*,row_number() over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 1    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | 3    |
| 4    | 1    | ddd  | 300  | 4    |
| 5    | 2    | fff  | 200  | 1    |
| 6    | 2    | eee  | 400  | 2    |

当`query_patition_clause`没有时，将视全部记录为一个分组。使用`row_number()`，`order_by_clause`必须有，否则报错。

```
select t.*,row_number() over(order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 1    |
| 2    | 2    | fff  | 200  | 2    |
| 3    | 1    | bbb  | 200  | 3    |
| 4    | 1    | ccc  | 200  | 4    |
| 5    | 1    | ddd  | 300  | 5    |
| 6    | 2    | eee  | 400  | 6    |


另外，如果在`over`中已经添加了`order by`…，就不建议在from后面再添加相同的`order by`。二者一致的话还好（效果一样，没有差别），但不一致时结果有时就可能令人费解了。

## 3.2 RANK与DENSE_RANK

`rank `和 `dense_rank`主要的功能是计算一组数值中的排序值。其语法格式如下:

```
RANK() OVER ([query_partition_clause] order_by_clause)
```

```
select t.*,rank() over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 1    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | 2    |
| 4    | 1    | ddd  | 300  | 4    |
| 5    | 2    | fff  | 200  | 1    |
| 6    | 2    | eee  | 400  | 2    |

```
select t.*,dense_rank() over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 1    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | 2    |
| 4    | 1    | ddd  | 300  | 3    |
| 5    | 2    | fff  | 200  | 1    |
| 6    | 2    | eee  | 400  | 2    |

`rank()`是跳跃排序，有两个第二名时接下来就是第四名，不会出现第三名；`dense_rank()`是连续排序，有两个第二名时仍然跟着第三名。

## 3.3 LEAD与LAG

这两个函数是偏移量函数，可以查出一个字段的上一个值或者下一个值。`lead`函数是向下取值，即当前行是当前分区内最后一条则显示`null`；`lag`函数是向上取值，即当前行是当前分区内第一条则显示`null`。其语法如下：

```
lead(EXPR,<OFFSET>,<DEFAULT>)
lag(EXPR,<OFFSET>,<DEFAULT>)
```

* `EXPR`通常是直接是列名，也可以是从其他行返回的表达式；
* `OFFSET`是默认为1，表示在当前分区内基于当前行的偏移行数；
* `DEFAULT`是在`OFFSET`指定的偏移行数超出了分组的范围时（此时会返回`null`），可以通过设置这个字段来返回一个默认值来替代`null`。

```
select t.*,lead(sale) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 200  |
| 2    | 1    | bbb  | 200  | 200  |
| 3    | 1    | ccc  | 200  | 300  |
| 4    | 1    | ddd  | 300  | NULL |
| 5    | 2    | fff  | 200  | 400  |
| 6    | 2    | eee  | 400  | NULL |

```
select t.*,lag(sale) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | NULL |
| 2    | 1    | bbb  | 200  | 100  |
| 3    | 1    | ccc  | 200  | 200  |
| 4    | 1    | ddd  | 300  | 200  |
| 5    | 2    | fff  | 200  | NULL |
| 6    | 2    | eee  | 400  | 200  |

```
select t.*,lead(sale,2) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 200  |
| 2    | 1    | bbb  | 200  | 300  |
| 3    | 1    | ccc  | 200  | NULL |
| 4    | 1    | ddd  | 300  | NULL |
| 5    | 2    | fff  | 200  | NULL |
| 6    | 2    | eee  | 400  | NULL |

```
select t.*,lead(sale,2,500) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 200  |
| 2    | 1    | bbb  | 200  | 300  |
| 3    | 1    | ccc  | 200  | 500  |
| 4    | 1    | ddd  | 300  | 500  |
| 5    | 2    | fff  | 200  | 500  |
| 6    | 2    | eee  | 400  | 500  |

```
select t.*,lead(2) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 2    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | 2    |
| 4    | 1    | ddd  | 300  | NULL |
| 5    | 2    | fff  | 200  | 2    |
| 6    | 2    | eee  | 400  | NULL |

如果只指定一个常量，则将该值直接作为结果返回（偏移行数默认为1），但分区内最后一行仍然为`null`（同理，`lag`时分区内第一行也是`null`）。

```
select t.*,lead(2,2) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 2    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | NULL |
| 4    | 1    | ddd  | 300  | NULL |
| 5    | 2    | fff  | 200  | NULL |
| 6    | 2    | eee  | 400  | NULL |

如果指定2个常量，也是将该值直接作为结果返回（第一个参数为偏移行数），这样分区内最后两行仍然为null（同理，`lag`时分区内前两行也是`null`）。

```
select t.*,lead(2,2,2) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 2    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | 2    |
| 4    | 1    | ddd  | 300  | 2    |
| 5    | 2    | fff  | 200  | 2    |
| 6    | 2    | eee  | 400  | 2    |

如果是3个参数都是常量，则会把最后一个常量当作默认值替代`null`。（这两个函数的确挺有趣的！）

## 3.4 FIRST_VALUE与LAST_VALUE

`FIRST_VALUE`返回一组排序值后的第一个值，`LAST_VALUE`返回一组排序值后的最后一个值。其语法如下：

```
FIRST_VALUE( expr ) OVER ( analytic_clause )
LAST_VALUE( expr ) OVER ( analytic_clause )
```

```
select t.*,first_value(sale) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 100  |
| 2    | 1    | bbb  | 200  | 100  |
| 3    | 1    | ccc  | 200  | 100  |
| 4    | 1    | ddd  | 300  | 100  |
| 5    | 2    | fff  | 200  | 200  |
| 6    | 2    | eee  | 400  | 200  |

```
select t.*,first_value(2) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 2    |
| 2    | 1    | bbb  | 200  | 2    |
| 3    | 1    | ccc  | 200  | 2    |
| 4    | 1    | ddd  | 300  | 2    |
| 5    | 2    | fff  | 200  | 2    |
| 6    | 2    | eee  | 400  | 2    |

可以接收一个常量参数，效果等同于直接将该值作为结果返回。

```
select t.*,last_value(sale) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 100  |
| 2    | 1    | bbb  | 200  | 200  |
| 3    | 1    | ccc  | 200  | 200  |
| 4    | 1    | ddd  | 300  | 300  |
| 5    | 2    | fff  | 200  | 200  |
| 6    | 2    | eee  | 400  | 400  |

这两个函数可以不跟`order by`子句，也可以指定窗口上下界。

## 3.5 NTILE

`NTILE`函数对一个数据分区中的有序结果集进行划分，将其分组到各个桶，并为每个小组分配一个唯一的组编号。这个函数在统计分析中是很有用的。例如，如果想移除异常值,我们可以将它们分组到顶部或底部的桶中，然后在统计分析的时候将这些值排除。在统计信息收集可以使用NTILE函数来计算直方图信息边界。在统计学术语中，`NTILE`函数创建等宽直方图信息。其语法如下：

```
NTILE(ntile_num) OVER ( analytic_clause )
```

其中`ntile_num`不能小于等于0。`analytic_clause`中必须有`order by`子句。

```
select t.*,ntile(3) over(order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 1    |
| 2    | 2    | fff  | 200  | 1    |
| 3    | 1    | bbb  | 200  | 2    |
| 4    | 1    | ccc  | 200  | 2    |
| 5    | 1    | ddd  | 300  | 3    |
| 6    | 2    | eee  | 400  | 3    |

## 3.6 RATIO_TO_REPORT

`ratio_to_report`函数用来计算总数百分比。通常计算百分比的方法是在总计报告的子查询中使用`SUM`函数总计报告，然后把那个结果放到细节表中相除来计算百分比。还可以用一个子查询作为`SELECT`语句表达式。`RATIO_TO_REPORT`函数使得这种类型的查询更容易编写。其语法如下：

```
RATIO_TO_REPORT (expr) OVER (query_partition_clause)
```

```
select t.*,ratio_to_report(sale) over(partition by id) rn from tes t;
```

| Ln   | ID   | NAME | SALE | RN    |
| ---- | ---- | ---- | ---- | ----- |
| 1    | 1    | aaa  | 100  | 0.125 |
| 2    | 1    | bbb  | 200  | 0.25  |
| 3    | 1    | ccc  | 200  | 0.25  |
| 4    | 1    | ddd  | 300  | 0.375 |
| 5    | 2    | fff  | 200  | 0.333 |
| 6    | 2    | eee  | 400  | 0.667 |

此函数不能跟`order by`子句。

## 3.7 统计函数(MAX、MIN、AVG、SUM、COUNT)

统计函数最为常见，这里只列举例子，语法形式如下：

```
SUM[MAX][MIN][AVG] OVER (query_partition_clause)
```

```
select t.*,sum(sale) over(partition by id order by sale) rn from test t;
select t.*,sum(sale) over(partition by id order by sale range between unbounded preceding and current row) rn from test t;
```

（注：以上二者是等效的）

（注：以上二者是等效的）

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 100  |
| 2    | 1    | bbb  | 200  | 500  |
| 3    | 1    | ccc  | 200  | 500  |
| 4    | 1    | ddd  | 300  | 800  |
| 5    | 2    | fff  | 200  | 200  |
| 6    | 2    | eee  | 400  | 600  |

```
select t.*,sum(sale) over(partition by id order by sale rows between unbounded preceding and current row) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 100  |
| 2    | 1    | bbb  | 200  | 300  |
| 3    | 1    | ccc  | 200  | 500  |
| 4    | 1    | ddd  | 300  | 800  |
| 5    | 2    | fff  | 200  | 200  |
| 6    | 2    | eee  | 400  | 600  |

```
select t.*,max(sale) over(partition by id order by sale) rn from test t;*
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 100  |
| 2    | 1    | bbb  | 200  | 200  |
| 3    | 1    | ccc  | 200  | 200  |
| 4    | 1    | ddd  | 300  | 300  |
| 5    | 2    | fff  | 200  | 200  |
| 6    | 2    | eee  | 400  | 400  |

```
select t.*,min(sale) over(partition by id order by sale) rn from test t;
```

| Ln   | ID   | NAME | SALE | RN   |
| ---- | ---- | ---- | ---- | ---- |
| 1    | 1    | aaa  | 100  | 100  |
| 2    | 1    | bbb  | 200  | 100  |
| 3    | 1    | ccc  | 200  | 100  |
| 4    | 1    | ddd  | 300  | 100  |
| 5    | 2    | fff  | 200  | 200  |
| 6    | 2    | eee  | 400  | 200  |

# 四．Window_clause窗口子句

本章详细介绍下`Window_clause`。即窗口子句，它标识了在分组上的一个范围。其中，

* `between…and`用来指定窗口的起始点和终结点；
* `Unbounded preceding`指明窗口开始于分组的第一行；
* `Current row`作为起始点，指明窗口开始于当前行或当前值；作为终结点，指明窗口结束于当前行或当前值；
* `Unbounded following`指明窗口结束于分组的最后一行；

`range`是逻辑窗口，是指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内。`rows`是物理窗口，即根据`order by`子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）。

Oracle对窗口子句的逻辑限定：

> *Cause: If the window specification is specified
> using RANGE option and there are multiple ORDER BY expressions, then
> the aggregation group cannot contain any expression (It can only
> have CURRENT ROW, UNBOUNDED PRECEDING, or UNBOUNDED FOLLOWING).
> First end point (bound) cannot be UNBOUNDED FOLLOWING and second end
> point cannot be UNBOUNDED PRECEDING. If the first end point is
> CURRENT ROW, then second end point can only be CURRENT ROW or
> /UNBOUNDED FOLLOWING. If the first end point is FOLLOWING,
> then second end point can only be /UNBOUNDED FOLLOWING.

即，如果使用窗口说明中使用了`range/rows`以及`order by`子句时，聚合组里只能含有`CURRENT ROW, UNBOUNDED PRECEDING`或`UNBOUNDED FOLLOWING`。

下界点（the first end point）不能是`UNBOUNDED FOLLOWING`；上界点（second end point）不能是`UNBOUNDED PRECEDING`。

如果下界点是`CURRENT ROW`，那么上界点只能是`CURRENT ROW`或者`UNBOUNDED FOLLOWING`或者`FOLLOWING`；如果下界点是`FOLLOWING`，那么上界点只能是` UNBOUNDED FOLLOWING`。

编者按：窗口说明中起始点与终结点之间排列组合顺序(由下限到上限)依次为：

`UNBOUNDED PRECEDING, PRECEDING，CURRENT ROW, FOLLOWING, UNBOUNDED FOLLOWING`。

因此只允许`BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`，而不允许`BETWEEN CURRENT ROW AND UNBOUNDED PRECEDING`。其他情况相同。

目前除了聚合函数可以指定窗口上下界之外，非聚合函数只有`first_value`和`last_value`函数可以指定。其他的函数都不能指定。

# 五．补充说明

当sql语句中的`order by`子句里的内容和开窗函数`over()`中的`order by`子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；`order by`字段两者不一致时，那么sql语句中的排序将最后在分析函数分析结束后执行排序。所以如果在over中已经添加了`order by…`，就不建议在`from`后面再添加相同的`order by`。二者一致的话还好（效果一样，没有差别），但不一致时结果有时就可能令人费解了。

窗口子句必须和`order by`子句同时使用，且如果指定了`order by`子句未指定窗口子句，则默认为`RANGE BETWEEN unbounded preceding AND CURRENT ROW`。

如果分析函数没有指定`ORDER BY`子句，也就不存在`ROWS/RANGE`窗口的计算。

# 参考

* [窗口函数的基本使用方法](https://blog.csdn.net/hyazz_/article/details/95052222)
* [SQL中分析函数first_value(),last_value，sum() over(partition by...)详解](https://blog.csdn.net/yilulvxing/article/details/86627969)