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.
Function Description Example Result
row_number() Returns the ordinal number of the current row in a partition. The return value is of the BIGINT type.
Note The ordinal number counts from 1.
row_number() over (order by c1)
c1 | row_number 
---+------------ 
a  |   1 
a |   2 
b |   3 
c |   4
(4 rows)
rank() Returns the rank of the current row in a partition. 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)
dense_rank() Returns the rank of the current row in a partition. 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)
percent_rank() Returns the rank of the current row in a partition 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 partition - 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)
lag(value anyelement [, offset integer [, default anyelement]]) Returns the value of a field in the row that is the given offset number of rows before the current row in a partition. The return value is of the same type as the specified field.
Note The default value is returned if the current row does not have a preceding row that is the given offset number of rows before the current row. By default, the offset parameter is set to 1, and the default parameter is not specified.
c1, lag(c1) over (order by c1)
c1 | lag 
---+----- 
a  | 
a  | a 
b  | a 
c  | b
(4 rows)
lead(value anyelement [, offset integer [, default anyelement]]) Returns the value of a field in the row that is the given offset number of rows after the current row in a partition. The return value is of the same type as the specified field.
Note The default value is returned if the current row does not have a following row that is the given offset number of rows after the current row. By default, the offset parameter is set to 1, and the default parameter is not specified.
c1, lead(c1) over (order by c1)
c1 | lead 
---+------ 
a  | a 
a  | b 
b  | c 
c  | 
(4 rows)
first_value(value anyelement) Returns the value of a field with respect to the first row in the window frame. The return value is of the same type as the specified field. c1, first_value(c1) over (order by c1)
c1 | first_value 
---+------------- 
a  | a 
a  | a 
b  | a 
c  | a
(4 rows)
last_value(value anyelement) Returns the value of a field with respect to the last row in the window frame. The return value is of the same type as the specified field. c1, last_value(c1) over (order by c1)
c1 | last_value 
---+------------ 
a  | a 
a  | a 
b  | b 
c  | c
(4 rows)

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 partition. The window function performs calculations on each row based on the partition 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 partition is called a window frame. Some window functions perform a calculation only on the window frame rather than the partition 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 partition 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 partition 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.
Example Result
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.