Window functions

Updated at:

Hologres is compatible with PostgreSQL and supports standard PostgreSQL window function syntax for data analysis.

Note

For the full PostgreSQL window function specification, see Window Functions in the PostgreSQL documentation.

Supported functions

FunctionSyntaxDescription
ROW_NUMBERROW_NUMBER()Returns the sequential number of the current row within the window, starting at 1. Returns BIGINT.
RANKRANK()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_RANKDENSE_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_RANKPERCENT_RANK()Returns the relative rank as a fraction: (rank - 1) / (total rows - 1). Returns DOUBLE PRECISION.
LAGLAG(value ANYELEMENT [, offset INTEGER [, default ANYELEMENT]])Returns the value from a preceding row. offset defaults to 1; default defaults to NULL.
LEADLEAD(value ANYELEMENT [, offset INTEGER [, default ANYELEMENT]])Returns the value from a following row. offset defaults to 1; default defaults to NULL.
FIRST_VALUEFIRST_VALUE(value ANYELEMENT)Returns the value from the first row of the window frame.
LAST_VALUELAST_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.

FunctionHQEPQEMinimum version
ROW_NUMBERSupported—V1.1
RANKSupported—V1.1
DENSE_RANKSupported—V1.1
PERCENT_RANKNot supportedSupported—
LAGNot supportedSupportedV1.1.71 (PQE)
LEADSupported (single-parameter form only)—V1.1.71 (HQE)
FIRST_VALUESupported—V1.1.71
LAST_VALUESupported—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  |          4

RANK

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  |    4

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. 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  |          4

PERCENT_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  |                 1

LAG

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.
ParameterTypeRequiredDefaultDescription
valueANYELEMENTYes—Column or expression to read from the preceding row
offsetINTEGERNo1Number of rows to look back
defaultANYELEMENTNoNULLValue 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  | c

LEAD

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.
ParameterTypeRequiredDefaultDescription
valueANYELEMENTYes—Column or expression to read from the following row
offsetINTEGERNo1Number of rows to look ahead
defaultANYELEMENTNoNULLValue 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  | a

LAST_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  | d

How 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  |  10

Without 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