Window functions
Hologres is compatible with PostgreSQL and supports standard PostgreSQL window function syntax for data analysis.
For the full PostgreSQL window function specification, see Window Functions in the PostgreSQL documentation.
Supported functions
| Function | Syntax | Description |
|---|---|---|
| ROW_NUMBER | ROW_NUMBER() | Returns the sequential number of the current row within the window, starting at 1. Returns BIGINT. |
| RANK | RANK() | Returns the rank of the current row. Rows with equal values receive the same rank; the next rank skips the number of tied rows. Returns BIGINT. |
| DENSE_RANK | DENSE_RANK() | Returns the rank of the current row with no gaps. Equal values receive the same rank, and the next rank is always one higher. Returns BIGINT. |
| PERCENT_RANK | PERCENT_RANK() | Returns the relative rank as a fraction: (rank - 1) / (total rows - 1). Returns DOUBLE PRECISION. |
| LAG | LAG(value ANYELEMENT [, offset INTEGER [, default ANYELEMENT]]) | Returns the value from a preceding row. offset defaults to 1; default defaults to NULL. |
| LEAD | LEAD(value ANYELEMENT [, offset INTEGER [, default ANYELEMENT]]) | Returns the value from a following row. offset defaults to 1; default defaults to NULL. |
| FIRST_VALUE | FIRST_VALUE(value ANYELEMENT) | Returns the value from the first row of the window frame. |
| LAST_VALUE | LAST_VALUE(value ANYELEMENT) | Returns the value from the last row of the window frame. |
Engine and version support
Hologres has two query engines: the Hologres Query Engine (HQE) and the Postgres Query Engine (PQE). The following table lists the engine and version requirements for each window function.
| Function | HQE | PQE | Minimum version |
|---|---|---|---|
| ROW_NUMBER | Supported | — | V1.1 |
| RANK | Supported | — | V1.1 |
| DENSE_RANK | Supported | — | V1.1 |
| PERCENT_RANK | Not supported | Supported | — |
| LAG | Not supported | Supported | V1.1.71 (PQE) |
| LEAD | Supported (single-parameter form only) | — | V1.1.71 (HQE) |
| FIRST_VALUE | Supported | — | V1.1.71 |
| LAST_VALUE | Supported | — | V1.1.71 |
Sample data
All examples in this topic use the test_wf table. Run the following statements to create the table and insert sample data:
CREATE TABLE test_wf(
ID INT,
c1 TEXT
);
INSERT INTO test_wf ("id","c1") VALUES (1,'a'),(2,'b'),(3,'c'),(4,'d');Function reference
ROW_NUMBER
Returns the sequential number of the current row within the window, starting at 1. Return type: BIGINT.
SELECT c1, ROW_NUMBER() OVER (ORDER BY c1) FROM test_wf;Result:
c1 | row_number
----+------------
a | 1
b | 2
c | 3
d | 4RANK
Returns the rank of the current row. Equal values receive the same rank; the next rank skips the number of tied rows, so ranks may not be consecutive. Return type: BIGINT.
SELECT c1, RANK() OVER (ORDER BY c1) FROM test_wf;Result:
c1 | rank
----+------
a | 1
b | 2
c | 3
d | 4DENSE_RANK
Returns the rank of the current row with no gaps. Equal values receive the same rank, and the next rank is always one higher. Return type: BIGINT.
SELECT c1, DENSE_RANK() OVER (ORDER BY c1) FROM test_wf;Result:
c1 | dense_rank
----+------------
a | 1
b | 2
c | 3
d | 4PERCENT_RANK
Returns the relative rank of the current row as a value between 0 and 1, calculated as (rank - 1) / (total rows in window - 1). Return type: DOUBLE PRECISION.
Available on the PQE only.
SELECT c1, PERCENT_RANK() OVER (ORDER BY c1) FROM test_wf;Result:
c1 | percent_rank
----+-------------------
a | 0
b | 0.333333333333333
c | 0.666666666666667
d | 1LAG
Returns the value of value from the row that is offset rows before the current row. If no such row exists, returns default.
Available on the PQE in Hologres V1.1.71 and later.
| Parameter | Type | Required | Default | Description |
|---|---|---|---|---|
value | ANYELEMENT | Yes | — | Column or expression to read from the preceding row |
offset | INTEGER | No | 1 | Number of rows to look back |
default | ANYELEMENT | No | NULL | Value returned when no preceding row exists |
SELECT c1, LAG(c1) OVER (ORDER BY c1) FROM test_wf;Result:
c1 | lag
----+-----
a |
b | a
c | b
d | cLEAD
Returns the value of value from the row that is offset rows after the current row. If no such row exists, returns default.
On the HQE (V1.1.71 and later), only the single-parameter form is supported.
| Parameter | Type | Required | Default | Description |
|---|---|---|---|---|
value | ANYELEMENT | Yes | — | Column or expression to read from the following row |
offset | INTEGER | No | 1 | Number of rows to look ahead |
default | ANYELEMENT | No | NULL | Value returned when no following row exists |
SELECT c1, LEAD(c1) OVER (ORDER BY c1) FROM test_wf;Result:
c1 | lead
----+------
a | b
b | c
c | d
d |FIRST_VALUE
Returns the value of value from the first row of the window frame. Available on the HQE in Hologres V1.1.71 and later.
SELECT c1, FIRST_VALUE(c1) OVER (ORDER BY c1) FROM test_wf;Result:
c1 | first_value
----+-------------
a | a
b | a
c | a
d | aLAST_VALUE
Returns the value of value from the last row of the window frame. Available on the HQE in Hologres V1.1.71 and later.
SELECT c1, LAST_VALUE(c1) OVER (ORDER BY c1) FROM test_wf;Result:
c1 | last_value
----+------------
a | a
b | b
c | c
d | dHow window functions work
All window functions require an OVER clause, which defines the set of rows the function operates on.
PARTITION BY: Groups rows into separate windows by the specified column. The function runs independently within each window.
ORDER BY: Defines the row order within each window.
Window frame
A window frame is the subset of rows within a window that a function evaluates for each row.
With ORDER BY: The frame defaults to
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW—from the first row of the partition to the current row, including all rows with the same ORDER BY value as the current row.Without ORDER BY: The frame includes all rows in the partition.
The following examples show how ORDER BY changes the result of SUM:
With ORDER BY — SUM accumulates from the first row to the current row:
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 | 10Without ORDER BY — SUM spans the entire partition:
SELECT id, c1, SUM(id) OVER () FROM test_wf; id | c1 | sum
----+----+-----
3 | c | 10
1 | a | 10
2 | b | 10
4 | d | 10