Hologres支持的窗口函数有哪些及其特性_实时数仓 Hologres(Hologres)-阿里云帮助中心

Hologres兼容PostgreSQL，支持使用标准的PostgreSQL语法进行开发。本文为您介绍Hologres已支持的窗口函数列表及使用用例。

说明

关于兼容PostgreSQL的窗口函数的详情，请参见窗口函数。

函数	功能
DENSE_RANK	获取当前行在窗口中的排名，生成的序号是连续的。
FIRST_VALUE	获取窗口第一行计算值。
LAG	获取指定值在窗口中当前行的前一行位置的值。
LAST_VALUE	获取窗口最后一行计算值。
LEAD	获取指定值在窗口中当前行的后一行位置的值。
PERCENT_RANK	获取当前行在窗口中的百分比排名`（rank-1）/（总分区行-1）`。
RANK	获取当前行在窗口中的排名，采用跳跃排序，生成的序号可能是不连续的。
ROW_NUMBER	获取当前行在窗口中的编号，从1开始计数。

示例数据

下文中函数的相关示例均基于test_wf示例数据。以下是创建表test_wf并添加数据的命令示例。

CREATE TABLE test_wf(
    ID INT,
    c1 TEXT
);

INSERT INTO test_wf ("id","c1") VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d');

函数列表

ROW_NUMBER

描述：获取当前行在窗口中的编号，从1开始计数。
```
ROW_NUMBER()
```
使用说明：Hologres从V1.1版本开始，HQE（Hologres Query Engine）提供支持。
返回值说明
返回BIGINT类型。

示例

SELECT c1, ROW_NUMBER() OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | row_number
----+------------
 a  |          1
 b  |          2
 c  |          3
 d  |          4

RANK

描述：获取当前行在窗口中的排名，采用跳跃排序，生成的序号可能是不连续的。
```
RANK()
```
使用说明：Hologres从V1.1版本开始，HQE（Hologres Query Engine）提供支持。
返回值说明
返回BIGINT类型。

示例

SELECT c1, RANK() OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | rank
----+------
 a  |    1
 b  |    2
 c  |    3
 d  |    4

DENSE_RANK

描述：获取当前行在窗口中的排名，生成的序号是连续的。
```
DENSE_RANK()
```
使用说明：Hologres从V1.1版本开始，HQE（Hologres Query Engine）提供支持。
返回值说明
返回BIGINT类型。

示例

SELECT c1, DENSE_RANK() OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | dense_rank
----+------------
 a  |          1
 b  |          2
 c  |          3
 d  |          4

PERCENT_RANK

描述：获取当前行在窗口中的百分比排名（rank-1）/（总分区行-1）。
```
PERCENT_RANK()
```
使用说明
PQE（Postgres Query Engine）提供支持。
返回值说明
返回DOUBLE PRECISION类型。

示例

SELECT c1, PERCENT_RANK() OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 |   percent_rank
----+-------------------
 a  |                 0
 b  | 0.333333333333333
 c  | 0.666666666666667
 d  |                 1

LAG

描述：获取指定值在窗口中当前行的前一行位置的值。
```
LAG(<value> ANYELEMENT [, <offset> INTEGER [, <default> ANYELEMENT]])
```
使用说明
Hologres从V1.1.71版本开始，HQE（Hologres Query Engine）提供支持。
参数说明
- value：必填，需要获取前一行值的列或表达式。
- offset：可选，指定向前偏移的行数，即获取直接前一行的值，默认值为1。
- default：可选，当查询的行是分组的第一行，没有前一行时，可以指定一个默认值返回，默认值为空。

示例

SELECT c1, LAG(c1) OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | lag
----+-----
 a  |
 b  | a
 c  | b
 d  | c

LEAD

描述：获取指定值在窗口中当前行的后一行位置的值。
```
LEAD(<value> ANYELEMENT [, <offset> INTEGER [, <default> ANYELEMENT]])
```
使用说明
Hologres从V1.1.71版本开始，HQE（Hologres Query Engine）提供支持单入参场景。
参数说明
- value：必填，需要获取前一行值的列或表达式。
- offset：可选，指定向后偏移的行数，即获取下一行的值，默认值为1。
- default：可选，当查询的行是分组的最后一行，没有后一行时，可以指定一个默认值返回，默认值为空。

示例

SELECT c1, LEAD(c1) OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | lead
----+------
 a  | b
 b  | c
 c  | d
 d  |

FIRST_VALUE

描述：获取窗口第一行计算值。
```
FIRST_VALUE(<value> ANYELEMENT)
```
使用说明
Hologres从V1.1.71版本开始，HQE（Hologres Query Engine）提供支持。

示例

SELECT c1, FIRST_VALUE(c1) OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | first_value
----+-------------
 a  | a
 b  | a
 c  | a
 d  | a

LAST_VALUE

描述：获取窗口最后一行计算值。
```
LAST_VALUE(<value> ANYELEMENT)
```
使用说明
Hologres从V1.1.71版本开始，HQE（Hologres Query Engine）提供支持。

示例

SELECT c1, LAST_VALUE(c1) OVER (ORDER BY c1) FROM test_wf;

返回结果如下。

 c1 | last_value
----+------------
 a  | a
 b  | b
 c  | c
 d  | d

窗口函数特性

调用窗口函数时，需要在窗口函数及其参数后增加一个OVER子句。OVER子句决定了查询结果中的哪些行需要被分离出来，由窗口函数处理。

当OVER子句使用分区子句PARTITION BY时，分区列值相同的行归属同一窗口。对于每一行，窗口函数都会针对其所在窗口进行计算。
当OVER子句使用排序子句ORDER BY时，窗口函数会按其定义的顺序处理行。

对于每一行，在其窗口中的行集被称为窗口帧。针对部分窗口函数，只会作用于当前行所在的窗口帧，而非整个窗口。在使用排序子句ORDER BY的情况下，窗口帧默认为从窗口开始到当前行的范围；如果没有定义排序，则窗口帧默认为当前窗口的所有行。

以SUM函数为例，是否使用排序子句查询得到的结果如下表。

示例1

SELECT id, c1, SUM(id) OVER (ORDER BY id) FROM test_wf;

返回结果如下。

id | c1 | sum
----+----+-----
  1 | a  |   1
  2 | b  |   3
  3 | c  |   6
  4 | d  |  10

示例2

SELECT id, c1, SUM(id) OVER () FROM test_wf;

返回结果如下。

 id | c1 | sum
----+----+-----
  3 | c  |  10
  1 | a  |  10
  2 | b  |  10
  4 | d  |  10

如上所示，当使用排序子句时，求和值为第一行（最小值）到当前行的和，并且包括与当前行具有相同值的行；当不使用排序子句时，每次求和都针对整个表。