---
url: /v6.2/calculation-function-window.md
---
# 窗口函数

窗口函数的语法：

function_name(\[expression \[, expression ... ]]) OVER( \[ PARTITION BY expr1 ] \[ ORDER BY expr2 \[ DESC ] ] )

* \[ PARTITION BY expr1 ]
  可选参数，partition by 会将查询的数据进行分组，窗口函数会独立的处理每一个分组。
* \[ ORDER BY expr2 \[ DESC ] ]
  可选参数，根据expr2进行排序。
* \[ DESC ]
  可选参数，默认会使用正序排序，使用DESC参数后结果将会使用倒序排序。

本文的介绍中，会使用下表的数据做为示例数据进行说明。

| id  | zh_name               | director           | prime_genre | runtime | rate_num | votes  | stars           | pubdate   |
|-----|-----------------------|--------------------|-------------|---------|----------|--------|-----------------|-----------|
| 1   | 星际穿越              | 克里斯托弗·诺兰    | 剧情        | 169     | 7.75     | 416252 | 马修·麦康纳     | 2014-11-12 |
| 2   | 辛德勒的名单          | 史蒂文·斯皮尔伯格  | 剧情        | 195     | 7.97     | 329749 | 连姆·尼森       | 1993-11-30 |
| 3   | 唐伯虎点秋香          | 李力持             | 喜剧        | 102     | 4.25     | 260928 | 周星驰          | 1993-07-01 |
| 4   | 致命ID                | 詹姆斯·曼高德      | 剧情        | 90      | 9.57     | 271970 | 约翰·库萨克     | 2003-04-25 |
| 5   | 指环王3：王者无敌      | 彼得·杰克逊        | 剧情        | 201     | 5.24     | 255167 | 维果·莫腾森     | 2004-03-12 |
| 6   | 城市之光              | 查理·卓别林        | 剧情        | 87      | 0.11     | 34216  | 查理·卓别林     | 1931-01-30 |
| 7   | 这个男人来自地球      | 理查德·沙因克曼    | 剧情        | 87      | 4.15     | 147835 | 大卫·李·史密斯  | 2007-11-13 |
| 8   | 上帝也疯狂            | 加美·尤伊斯        | 喜剧        | 109     | 2.67     | 83463  | Marius Weyers   | 1980-09-10 |
| 9   | 大话西游之大圣娶亲    | 刘镇伟             | 喜剧        | 95      | 3.91     | 395680 | 周星驰          | 2014-10-24 |
| 10  | 放牛班的春天          | 克里斯托夫·巴拉蒂  | 剧情        | 97      | 9.92     | 396189 | 杰拉尔·朱诺     | 2004-10-16 |

## dense_rank

| 语法         | dense_rank() OVER( \[ PARTITION BY expr1 ] \[ ORDER BY expr2 \[ DESC ] ] ) |
|--------------|-------------------------------------------------------------------------|
| 参数说明     | 1. expr1：可选参数，用于分区的表达式，可选类型：任意类型；2. expr2：可选参数，用于排序的表达式，可选类型：任意类型；3. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型   | 数字                                                                 |
| 描述         | 返回当前窗口的排名(不保持间隔)， 计数从 1 开始。dense_rank与rank的区别在于dense_rank在出现重复排名时，下一个排名会仅接其上的序号，而rank则会跳过因为重复而没有出现的序号。 |

* 示例

```text
dense_rank() OVER (PARTITION BY {prime_genre} ORDER BY {runtime} ASC)
```

结果如下：

| runtime  | prime_genre  | value |
|-------|-------|------|
| 169   | 剧情  | 4    |
| 195   | 剧情  | 5    |
| 102   | 喜剧  | 2    |
| 90    | 剧情  | 2    |
| 201   | 剧情  | 6    |
| 87    | 剧情  | 1    |
| 87    | 剧情  | 1    |
| 109   | 喜剧  | 3    |
| 95    | 喜剧  | 1    |
| 97    | 剧情  | 3    |

## lag

| 语法         | lag(ARG, I) OVER( \[ PARTITION BY expr1 ] \[ ORDER BY expr2 \[ DESC ] ] ) |
|--------------|-----------------------------------------------------------------------|
| 参数说明     | 1. ARG：窗口框架，可选类型：任意类型；2. I：指定记录与当前记录的距离，可选类型：数字；3. expr1：可选参数，用于分区的表达式，可选类型：任意类型；4. expr2：可选参数，用于排序的表达式，可选类型：任意类型；5. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型   | 任意类型                                                                   |
| 描述         | 窗口函数，向前获得相对于当前记录指定i距离的那条记录的 arg 数据 |

* 示例

```text
lag({zh_name}, 2) OVER (ORDER BY {runtime} ASC)
```

结果如下：

| zh_name           | runtime  | value           |
|--------------------|-------|--------------------|
| 星际穿越           | 169   | 唐伯虎点秋香       |
| 辛德勒的名单       | 195   | 上帝也疯狂         |
| 唐伯虎点秋香       | 102   | 大话西游之大圣娶亲 |
| 致命ID             | 90    | 城市之光           |
| 指环王3：王者无敌   | 201   | 星际穿越           |
| 城市之光           | 87    | null               |
| 这个男人来自地球   | 87    | null               |
| 上帝也疯狂         | 109   | 放牛班的春天       |
| 大话西游之大圣娶亲 | 95    | 这个男人来自地球   |
| 放牛班的春天       | 97    | 致命ID             |

## lead

| 语法         | lead(ARG, I) |
|--------------|--------------|
| 参数说明     | 1. 参与计算的参数，可选类型：任意类型；2. 整数类型参数，可选类型：数字 |
| 返回值类型   | 任意类型          |
| 描述         | 窗口函数，向后获得相对于当前记录指定 i 距离的那条记录的 arg 数据 |

* 示例

```text
lead({zh_name}, 2) OVER (ORDER BY {runtime} ASC)
```

结果如下：

| zh_name           | runtime  | value           |
|--------------------|-------|--------------------|
| 星际穿越           | 169   | 指环王3：王者无敌       |
| 辛德勒的名单       | 195   | null         |
| 唐伯虎点秋香       | 102   | 星际穿越 |
| 致命ID             | 90    | 放牛班的春天           |
| 指环王3：王者无敌   | 201   | null           |
| 城市之光           | 87    | 致命ID               |
| 这个男人来自地球   | 87    | 大话西游之大圣娶亲               |
| 上帝也疯狂         | 109   | 辛德勒的名单       |
| 大话西游之大圣娶亲 | 95    | 唐伯虎点秋香   |
| 放牛班的春天       | 97    | 上帝也疯狂             |

## nth_value

| 语法         | nth_value(ARG, I) OVER( \[ PARTITION BY expr1 ] \[ ORDER BY expr2 \[ DESC ] ] ) |
|--------------|-----------------------------------------------------------------------------|
| 参数说明     | 1. ARG：窗口框架，可选类型：任意类型；2. I：想要获取的数据的行数，可选类型：数字；3. expr1：可选参数，用于分区的表达式，可选类型：任意类型；4. expr2：可选参数，用于排序的表达式，可选类型：任意类型；5. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型   | 任意类型                                                                         |
| 描述         | 获取窗口中第 i 个 arg 值 |

* 示例

```text
NTH_VALUE({zh_name}, 2) OVER( PARTITION BY {prime_genre} ORDER BY {runtime} DESC )
```

结果如下：

| zh_name           | runtime  | prime_genre  | value         |
|--------------------|-------|-------|------------------|
| 星际穿越           | 169   | 剧情  | 辛德勒的名单     |
| 辛德勒的名单       | 195   | 剧情  | 辛德勒的名单     |
| 唐伯虎点秋香       | 102   | 喜剧  | 唐伯虎点秋香     |
| 致命ID             | 90    | 剧情  | 辛德勒的名单     |
| 指环王3：王者无敌   | 201   | 剧情  | null             |
| 城市之光           | 87    | 剧情  | 辛德勒的名单     |
| 这个男人来自地球   | 87    | 剧情  | 辛德勒的名单     |
| 上帝也疯狂         | 109   | 喜剧  | null             |
| 大话西游之大圣娶亲 | 95    | 喜剧  | 唐伯虎点秋香     |
| 放牛班的春天       | 97    | 剧情  | 辛德勒的名单     |

## rank

| 语法         | rank() OVER( \[ PARTITION BY expr1 ] \[ ORDER BY expr2 \[ DESC ] ] ) |
|--------------|-------------------------------------------------------------------------|
| 参数说明     | 1. expr1：可选参数，用于分区的表达式，可选类型：任意类型；2. expr2：可选参数，用于排序的表达式，可选类型：任意类型；3. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型   | 数字                                                                 |
| 描述         | 返回当前窗口的排名(保持间隔)，计数从 1 开始 |

* 示例

```text
rank() OVER (PARTITION BY {prime_genre} ORDER BY {runtime} ASC)
```

结果如下：

| runtime  | prime_genre  | value |
|-------|-------|------|
| 169   | 剧情  | 5    |
| 195   | 剧情  | 6    |
| 102   | 喜剧  | 2    |
| 90    | 剧情  | 3    |
| 201   | 剧情  | 7    |
| 87    | 剧情  | 1    |
| 87    | 剧情  | 1    |
| 109   | 喜剧  | 3    |
| 95    | 喜剧  | 1    |
| 97    | 剧情  | 4    |

## row_number

| 语法         | row_number() OVER( \[ PARTITION BY expr1 ] \[ ORDER BY expr2 \[ DESC ] ] ) |
|--------------|-------------------------------------------------------------------------|
| 参数说明     | 1. expr1：可选参数，用于分区的表达式，可选类型：任意类型；2. expr2：可选参数，用于排序的表达式，可选类型：任意类型；3. DESC：可选参数，指定按降序排序，默认升序 |
| 返回值类型   | 数字                                                                 |
| 描述         | 返回当前窗口的行数，计数从 1 开始 |

* 示例

```text
ROW_NUMBER() OVER (PARTITION BY {prime_genre} ORDER BY {runtime} ASC)
```

结果如下：

| runtime  | prime_genre  | value |
|-------|-------|------|
| 169   | 剧情  | 5    |
| 195   | 剧情  | 6    |
| 102   | 喜剧  | 2    |
| 90    | 剧情  | 3    |
| 201   | 剧情  | 7    |
| 87    | 剧情  | 2    |
| 87    | 剧情  | 1    |
| 109   | 喜剧  | 3    |
| 95    | 喜剧  | 1    |
| 97    | 剧情  | 4    |

## 支持窗口计算的聚合函数

部分聚合函数也能够作为窗口函数使用。仅当聚集函数调用后面跟着一个OVER子句时，聚合函数会进行窗口计算。函数列表如下：

| 函数名          | 描述                                                                 |
|-----------------|----------------------------------------------------------------------|
| SUM             | 计算数值列的总和                                                     |
| AVG             | 计算数值列的平均值                                                   |
| MAX             | 计算数值列的最大值                                                   |
| MIN             | 计算数值列的最小值                                                   |
| CORR            | 计算两列数值的相关系数                                               |
| COVAR_POP       | 计算两列数值的总体协方差                                             |
| COVAR_SAMP      | 计算两列数值的样本协方差                                             |
| REGR_AVGX       | 计算线性回归中自变量的平均值                                         |
| REGR_AVGY       | 计算线性回归中因变量的平均值                                         |
| REGR_COUNT      | 计算线性回归中非空数据点的数量                                       |
| REGR_INTERCEPT  | 计算线性回归方程的截距                                               |
| REGR_R2         | 计算线性回归的决定系数（R²）                                         |
| REGR_SLOPE      | 计算线性回归方程的斜率                                               |
| REGR_SXX        | 计算线性回归中自变量的平方和                                         |
| REGR_SXY        | 计算线性回归中自变量与因变量的交叉乘积和                             |
| REGR_SYY        | 计算线性回归中因变量的平方和                                         |
| STDDEV_POP      | 计算数值列的总体标准差                                               |
| STDDEV_SAMP     | 计算数值列的样本标准差                                               |
| VAR_POP         | 计算数值列的总体方差（总体标准差的平方）                             |
| VAR_SAMP        | 计算数值列的样本方差（样本标准差的平方）                             |
| STRING_AGG      | 将字符串列按指定方式聚合连接                                         |
| JSON_AGG        | 将数据聚合为JSON格式数组                                             |
| COUNT           | 计算数据的行数（可指定列，忽略NULL值；或用\*包含所有行）              |

## order by 子句

> \[!TIP] 提示
> 这里有一个与窗口函数相关的重要概念：对于每一行，在它的分区中的行集合被称为它的窗口帧。
> **一些窗口函数只作用在窗口帧中的行上，而不是整个分区**。此类函数如果同时使用`ORDER BY`，则帧包括从分区开始到当前行的所有行，以及后续任何与当前行在ORDER BY子句上相等的行。
> **所有的聚合函数在使用OVER子句中的`ORDER BY`表达式时，最终的返回结果均会与不使用`ORDER BY`表达式会有各种各样的差异。**

* 示例1

获取每个班学生的总人数。

```
COUNT(1) OVER (PARTITION BY {class})
```

* 示例2

将各个班级的学生姓名按照成绩顺序拼接成一个JSON数组。

```
JSON_AGG({student_name}) OVER (PARTITION BY {class} ORDER BY {student_score})
```

## 窗口函数注意事项

使用窗口函数做为图表指标时的注意事项:

1. 窗口函数不是聚合函数，不能作为聚合函数所用。
2. 图表中有维度列的时候，如果窗口函数使用了 OVER 子句\[PARTITION BY expr1]/\[ORDER BY expr2]，非聚合运算的 expr1 或者 expr2 都必须是图表的维度列，否则会报错。
3. 假设图表有维度 a，b， 度量为 MAX(COUNT({书籍编号})) OVER (\*\*\*)。计算的逻辑是:
   * 以 a， b 分组，计算 COUNT({书籍编号}) 的值， 得到一个中间结果： （a, b, count_tmp）
   * 度量是 MAX(COUNT({书籍编号})) OVER ()， 计算结果是：在 count_tmp 列中找出最大值
   * 度量是 MAX(COUNT({书籍编号})) OVER (PARTITION BY a)， 计算结果是：从（a， tmp） 这个数组中，以a的值为划分依据，找出每个组内tmp的最大值
   * 度量是 MAX(COUNT({书籍编号})) OVER (PARTITION BY b)， 计算结果是：从（b， tmp） 这个数组中，以b的值为划分依据，找出每个组内tmp的最大值