Window functions - Hologres - Alibaba Cloud Documentation Center

Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL syntax for data development. This topic describes the window functions supported by Hologres and provides examples on how to use the functions.

Note

For more information about the window functions that are compatible with PostgreSQL, see Window Functions in the PostgreSQL documentation.

Function	Description
DENSE_RANK	Returns the rank of the current row in the window. This function assigns consecutive ranks.
FIRST_VALUE	Returns the value of a field in the first row of the window.
LAG	Returns the value in the previous row of the current row of a specific value in the window.
LAST_VALUE	Returns the value of a field in the last row of the window.
LEAD	Returns the value in the next row of the current row of a specific value in the window.
PERCENT_RANK	Returns the rank of the current row in the window in the percentage format. The percentage-format rank is calculated by using the following formula: `(Rank of the current row - 1)/(Total number of rows in the window - 1)`.
RANK	Returns the rank of the current row in the window. This function adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.
ROW_NUMBER	Returns the number of the current row in the window. The number counts from 1.

Sample data

The examples of window functions that are provided in this topic use the data in the test_wf table. You can execute the following sample statements to create the test_wf table and insert data into the table:

CREATE TABLE test_wf(
    ID INT,
    c1 TEXT
);

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

Functions

ROW_NUMBER

Description: Returns the number of the current row in the window. The number counts from 1.
```
ROW_NUMBER()
```
Usage note: This function is supported by the Hologres Query Engine (HQE) in Hologres V1.1 and later.
Return value type:
BIGINT

Example:

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

Returned result:

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

RANK

Description: Returns the rank of the current row in the window. This function adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers.
```
RANK()
```
Usage note: This function is supported by the HQE in Hologres V1.1 and later.
Return value type:
BIGINT

Example:

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

Returned result:

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

DENSE_RANK

Description: Returns the rank of the current row in the window. This function assigns consecutive ranks.
```
DENSE_RANK()
```
Usage note: This function is supported by the HQE in Hologres V1.1 and later.
Return value type:
BIGINT

Example:

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

Returned result:

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

PERCENT_RANK

Description: Returns the rank of the current row in the window in the percentage format. The percentage-format rank is calculated by using the following formula: (Rank of the current row - 1)/(Total number of rows in the window - 1).
```
PERCENT_RANK()
```
Usage note:
This function is supported by the Postgres Query Engine (PQE).
Return value type:
DOUBLE PRECISION

Example:

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

Returned result:

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

LAG

Description: Returns the value in the previous row of the current row of a specific value in the window.
```
LAG(<value> ANYELEMENT [, <offset> INTEGER [, <default> ANYELEMENT]])
```
Usage note:
This function is supported by the PQE in Hologres V1.1.71 and later.
Parameter description:
- value: the column or expression whose value in the previous row you want to obtain. This parameter is required.
- offset: the row offset. Default value: 1. This parameter is optional.
- default: the default value that is returned if the current row is the first row in the group and no previous row exists. By default, the value of this parameter is left empty. This parameter is optional.

Example:

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

Returned result:

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

LEAD

Description: Returns the value in the next row of the current row of a specific value in the window.
```
LEAD(<value> ANYELEMENT [, <offset> INTEGER [, <default> ANYELEMENT]])
```
Usage note:
The HQE in Hologres V1.1.71 and later allows a single parameter as the input of this function.
Parameter description:
- value: the column or expression whose value in the next row you want to obtain. This parameter is required.
- offset: the row offset. Default value: 1. This parameter is optional.
- default: the default value that is returned if the current row is the last row in the group and no next row exists. By default, the value of this parameter is left empty. This parameter is optional.

Example:

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

Returned result:

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

FIRST_VALUE

Description: Returns the value of a field in the first row of the window.
```
FIRST_VALUE(<value> ANYELEMENT)
```
Usage note:
This function is supported by the HQE in Hologres V1.1.71 and later.

Example:

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

Returned result:

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

LAST_VALUE

Description: Returns the value of a field in the last row of the window.
```
LAST_VALUE(<value> ANYELEMENT)
```
Usage note:
This function is supported by the HQE in Hologres V1.1.71 and later.

Example:

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

Returned result:

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

Characteristics of window functions

When you call a window function, you must use an OVER clause after the window function and the function parameters that you configure. An OVER clause is used to specify the rows in which field values are to be processed by the window function.

If you use the PARTITION BY keyword in the OVER clause, rows that have the same partition key value belong to the same window. The window function performs calculations on each row based on the window to which the row belongs.
If you use the ORDER BY keyword in the OVER clause, the window function performs calculations in the sequence that is defined by the ORDER BY keyword.

A set of rows that belong to the same window is called a window frame. Some window functions perform calculations only on the window frame rather than the window to which the current row belongs. For example, if you use the ORDER BY keyword in the OVER clause, the window frame includes rows from the first row in the window to which the current row belongs to the current row. If you do not use the ORDER BY keyword in the OVER clause, the window frame includes all rows that belong to the same window as the current row.

Whether the ORDER BY keyword is used determines the calculation results of window functions. The following two examples are provided. In the examples, the SUM function is used.

Example 1:

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

Returned result:

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

Example 2:

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

Returned result:

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

When the ORDER BY clause is used, the SUM function returns the sum of values from the first row to the current row, including rows that have the same value as the current row. If the ORDER BY clause is not used, the SUM function returns the sum of values in all rows in the table.