Hologres is compatible with PostgreSQL and allows you to use the standard PostgreSQL syntax for data development.

Functions

The following table describes the window functions supported by Hologres. The functions supported by Hologres are only a subset of the PostgreSQL functions. For more information about how to use these functions, see Window Functions in PostgreSQL documentation.
FunctionDescriptionExampleResultRequired engine
row_number()Returns the number of the current row in the window. The return value is of the BIGINT type.
Note The number counts from 1.
row_number() over (order by c1)
c1 | row_number 
---+------------ 
a  |   1 
a |   2 
b |   3 
c |   4
(4 rows)
This function is supported by the Hologres Query Engine (HQE) in Hologres V1.1 and later.
rank()Returns the rank of the current row in the window. The return value is of the BIGINT type.
Note 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() over (order by c1)
c1 | rank 
---+------ 
a  | 1 
a  | 1 
b  | 3 
c  | 4
(4 rows)
This function is supported by the HQE in Hologres V1.1 and later.
dense_rank()Returns the rank of the current row in the window. The return value is of the BIGINT type.
Note This function assigns consecutive ranks.
dense_rank() over (order by c1)
c1 | dense_rank 
---+------------ 
a  | 1 
a  | 1 
b  | 2 
c  | 3
(4 rows)
This function is supported by the HQE in Hologres V1.1 and later.
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). The return value is of the DOUBLE PRECISION type. 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]])Returns the first values of the value parameter in the current row of the window. The number of returned rows is specified by the offset parameter. If the returned rows specified by the offset parameter do not exist, the value of the default parameter is returned. The return values are of the same type as the value parameter. By default, the value of the offset parameter is 1, and the value of the default parameter is left empty. c1, lag(c1) over (order by c1)
c1 | lag 
---+----- 
a  | 
a  | a 
b  | a 
c  | b
(4 rows)
The HQE in Hologres V1.1.71 and later allows a single parameter as the input of this function.
lead(value anyelement [, offset integer [, default anyelement]])Returns the last values of the value parameter in the current row of the window. The number of returned rows is specified by the offset parameter. If the returned rows specified by the offset parameter do not exist, the value of the default parameter is returned. The return values are of the same type as the value parameter. By default, the value of the offset parameter is 1, and the value of the default parameter is left empty. c1, lead(c1) over (order by c1)
c1 | lead 
---+------ 
a  | a 
a  | b 
b  | c 
c  | 
(4 rows)
The HQE in Hologres V1.1.71 and later allows a single parameter as the input of this function.
first_value(value anyelement)Returns the value of a field in the first row of the window. The return value is of the same type as the value parameter. c1, first_value(c1) over (order by c1)
c1 | first_value 
---+------------- 
a  | a 
a  | a 
b  | a 
c  | a
(4 rows)
This function is supported by the HQE in Hologres V.1.1.71 and later.
last_value(value anyelement)Returns the value of a field in the last row of the window. The return value is of the same type as the value parameter. c1, last_value(c1) over (order by c1)
c1 | last_value 
---+------------ 
a  | a 
a  | a 
b  | b 
c  | c
(4 rows)
This function is supported by the HQE in Hologres V.1.1.71 and later.

Characteristics of window functions

When you call a window function, you must use an OVER clause after the window function and the function parameter that you set. 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 on rows 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 table provides two examples. In the examples, the SUM function is used.
ExampleResult
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)
When the ORDER BY keyword is used, the SUM function processes field values from the first row to the current row, including rows that have the same partition key value as the current row. If the ORDER BY keyword is not used, the SUM function processes field values of all rows in the table.