文档

窗口函数

更新时间:

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

函数列表

Hologres已支持的窗口函数列表如下。当前Hologres版本支持的函数是PostgreSQL的一个子集,函数的使用方法请参见窗口函数

函数名

描述

用例

结果

支持的引擎

row_number()

返回当前行在窗口中的编号,返回类型为BIGINT。

说明

从1开始计数。

row_number() over (order by c1)

c1 | row_number
---+------------
a  |  1
a|  2
b|  3
c|  4
(4 rows)

Hologres从V1.1版本开始HQE支持。

rank()

返回当前行在窗口中的排名,返回类型为BIGINT。

说明

Rank函数是跳跃排序,生成的序号有可能不连续。

rank() over (order by c1)

c1 | rank 
---+------ 
a  | 1 
a  | 1 
b  | 3 
c  | 4
(4 rows)

Hologres从V1.1版本开始HQE支持。

dense_rank()

返回当前行在窗口中的排名,返回类型为BIGINT。

说明

Dense_Rank函数生成的序号是连续的。

dense_rank() over (order by c1)

c1 | dense_rank 
---+------------ 
a  | 1 
a  | 1 
b  | 2 
c  | 3
(4 rows)

Hologres从V1.1版本开始HQE支持。

percent_rank()

求当前行在窗口中的百分比排名(rank-1)/(总分区行-1),返回类型为DOUBLE PRECISION。

percent_rank() over (order by c1)

c1 | percent_rank 
---+------------------- 
a  | 0 
a  | 0 
b  | 0.666666666666667 
c  | 1
(4 rows)

PQE

lag(value anyelement [, offset integer [, default anyelement]])

返回value在窗口中当前行的前offset个位置的值(如果不存在该位置,则返回default值),返回类型与value相同。offset默认值为1,default默认值为空。

c1, lag(c1) over (order by c1)

c1 | lag 
---+----- 
a  | 
a  | a 
b  | a 
c  | b
(4 rows)

Hologres从V1.1.71版本开始HQE支持单入参场景。

lead(value anyelement [, offset integer [, default anyelement]])

返回value在窗口中当前行的后offset个位置的值(如果不存在该位置,则返回default值),返回类型与value相同。offset默认值为1,default默认值为空。

c1, lead(c1) over (order by c1)

c1 | lead 
---+------ 
a  | a 
a  | b 
b  | c 
c  | 
(4 rows)

Hologres从V1.1.71版本开始HQE支持单入参场景。

first_value(value anyelement)

返回在窗口的第一行计算出的值,返回类型与value相同。

c1, first_value(c1) over (order by c1)

c1 | first_value 
---+------------- 
a  | a 
a  | a 
b  | a 
c  | a
(4 rows)

Hologres从V1.1.71版本开始HQE支持。

last_value(value anyelement)

返回在窗口的最后一行计算出的值,返回类型与value相同。

c1, last_value(c1) over (order by c1)

c1 | last_value 
---+------------ 
a  | a 
a  | a 
b  | b 
c  | c
(4 rows)

Hologres从V1.1.71版本开始HQE支持。

窗口函数特性

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

  • 当OVER子句使用分区子句PARTITION BY时,分区列值相同的行归属同一窗口。对于每一行,窗口函数都会针对其所在窗口进行计算。

  • 当OVER子句使用排序子句ORDER BY时,窗口函数会按其定义的顺序处理行。

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

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

用例

结果

id, c1, sum(id) over (order by id)

id | c1 | sum 
---+----+-----
1  | a  |   2
1  | a  |   2
3  | b  |   5
4  | c  |   9
(4 rows)

id, c1, sum(id) over ()

id | c1 | sum
---+----+-----
1  | a  |   9
1  | a  |   9
3  | b  |   9
4  | c  |   9
(4 rows)

可以看出,当使用排序子句时,求和值为第一行(最小值)到当前行的和,并且包括与当前行具有相同值的行;当不使用排序子句时,每次求和都针对整个表。