視窗函數 - 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版本開始支援該函數，執行引擎為PQE。
參數說明
- 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

如上所示，當使用排序子句時，求和值為第一行（最小值）到當前行的和，並且包括與當前行具有相同值的行；當不使用排序子句時，每次求和都針對整個表。