Help Center/ GaussDB/ M-Compatibility Developer Guide(Centralized)/ SQL Reference/ Functions and Operators/ Window Functions
Updated on 2025-10-23 GMT+08:00
View PDF
Share

Window Functions

The window function must specify the OVER clause through over_clause to determine how to divide the query rows into multiple groups.

If the precision transfer parameter is not enabled for the window function, the result precision may be incorrect. Use the window function after the precision GUC parameter is enabled (that is, m_format_behavior_compat_options is set to 'enable_precision_decimal').

The syntax of over_clause is as follows:

{ OVER (window_spec) | OVER existing_window_name }
The syntax of window_spec is as follows: 
[ existing_window_name ] [ partition_clause ] [ order_clause ] [ frame_clause ]
  • existing_window_name: name of an existing window.
  • partition_clause: specifies how to group query rows. Syntax format: 
    PARTITION BY expr [, ...]
  • order_clause: specifies how to sort rows in each group. Syntax format: 
    ORDER BY expr [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]
    • NULLS FIRST: specifies that null values are placed before non-null values. This is the default behavior when DESC sorting is specified.
    • NULLS LAST: specifies that null values are placed after non-null values. This is the default behavior when DESC sorting is not specified.
  • frame_clause: defines a window frame for a window function. The window frame is a group of related rows for the current query row. The formats of frame_clause are as follows: 
    [ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end
    The formats of frame_start and frame_end are as follows: 
    UNBOUNDED PRECEDING
VALUE PRECEDING
CURRENT ROW
VALUE FOLLOWING
UNBOUNDED FOLLOWING

    Restrictions on scenarios where VALUE is used:

    • The value of VALUE can only be a non-negative number.
    • VALUE PRECEDING and VALUE FOLLOWING can be used only when ROWS is specified and cannot be used when RANGE is specified.

DENSE_RANK

DENSE_RANK() over_clause

Description: Assigns a rank to each row according to the order specified by over_clause in a query, starting from 1. When the values in the sorting column are the same, the duplicate ranks are retained. When the next different value is encountered, the ranks continue with consecutive numbers.

Return type: BIGINT UNSIGNED

When the value of the m_format_dev_version parameter is s2 or later and that of the m_format_behavior_compat_options parameter contains enable_conflict_funcs, this function is implemented in M-compatible databases, that is, the behavior described in this section. Other operations are the same as those of the dense_rank function described in "SQL Reference > Functions and Operators > Window Functions" in Developer Guide.

Examples:

m_db=# CREATE TABLE employee(dname VARCHAR(20),eid VARCHAR(20),ename VARCHAR(20),hiredate DATE,salary INT);
CREATE TABLE
m_db=# INSERT INTO employee VALUES('R&D Dept','1001','R&D 1','2021-11-01',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1002','R&D 2','2021-11-02',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1003','R&D 3','2021-11-03',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1004','R&D 4','2021-11-04',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1005','R&D 5','2021-11-05',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1006','R&D 6','2021-11-06',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1007','Sales 1','2021-11-01',2000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1008','Sales 2','2021-11-02',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1009','Sales 3','2021-11-03',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1010','Sales 4','2021-11-04',6000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1011','Sales 5','2021-11-05',9000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1012','Sales 6','2021-11-06',6000);
INSERT 0 1
m_db=# SELECT dname,ename,salary,DENSE_RANK() OVER(PARTITION BY dname ORDER BY salary DESC) as rk FROM employee;
 dname  | ename | salary | rk 
--------+-------+--------+----
 R&D Dept | R&D 1 |        | 1
 R&D Dept | R&D 3 |   7000 | 2
 R&D Dept | R&D 4 |   7000 | 2
 R&D Dept | R&D 2 |   5000 | 3
 R&D Dept | R&D 5 |   4000 | 4
 R&D Dept | R&D 6 |   4000 | 4
 Sales Dept | Sales 2 |        | 1
 Sales Dept | Sales 5 |   9000 | 2
 Sales Dept | Sales 6 |   6000 | 3
 Sales Dept | Sales 4 |   6000 | 3
 Sales Dept | Sales 3 |   5000 | 4
 Sales Dept | Sales 1 |   2000 | 5
(12 rows)

m_db=# DROP TABLE employee;
DROP TABLE

FIRST_VALUE

FIRST_VALUE(expr) [null_treatment] over_clause

Description: Returns the value of expr in the first row of data in the current window based on the row order specified by over_clause in a query.

Return type: any type (related to the type of expr)

Examples:

m_db=# CREATE TABLE t_stu(id INT, name VARCHAR(30), gender VARCHAR(5), class INT, math INT, eng INT, his INT);
CREATE TABLE
m_db=# INSERT INTO t_stu VALUES (1, 'Mike', 'm', 1, 99, 86, 67), (2, 'Judy', 'f', 1, 85, 86, 85), (3, 'Jonh', 'm', 2, 79, 59, 95), (4, 'Lily', 'f', 2, 88, 81, 80), (5, 'Ethan', 'm', 2, 77, 70, 69), (6, 'Chris', 'f', 3, 65, 96, 94), (7, 'Vio', 'f', 3, 66, 69, 93);
INSERT 0 7
m_db=# SELECT id, name, math, class, FIRST_VALUE(name) OVER (PARTITION BY class ORDER BY math DESC) FROM t_stu;
 id | name  | math | class | first_value
----+-------+------+-------+-------------
  1 | Mike  |   99 |     1 | Mike
  2 | Judy  |   85 |     1 | Mike
  4 | Lily  |   88 |     2 | Lily
  3 | Jonh  |   79 |     2 | Lily
  5 | Ethan |   77 |     2 | Lily
  7 | Vio   |   66 |     3 | Vio
  6 | Chris |   65 |     3 | Vio
(7 rows)

m_db=# DROP TABLE t_stu;
DROP TABLE

LAG

LAG(expr[, N[, default]]) [null_treatment] over_clause

Description: Obtains the values of expr in the first N rows of the current row in a query. If there is no such row, the return value is default.

Parameters: See Table 1.

Table 1 LAG parameters

Name

Type

Description

Value Range

expr

Any type

Specifies the field to be compared.

Any type value.

N

BIGINT

Specifies the offset of expr. The default value is 1. The value can be in the following format:

  • Unsigned integer of a constant literal.
  • User-defined variable.

The value must be an integer ranging from 0 to 231 – 1.

default

Any type

Specifies the default value. The default value is NULL.

Any type value.

[null_treatment]

-

Specifies whether to ignore NULL values.

RESPECT NULLS or IGNORE NULLS (optional, but not supported).

Return type: any type (related to default and expr)

Examples:

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# SELECT id, name, LAG(name) OVER() FROM employee;
 id |  name   |   lag   
----+---------+---------
  2 | Bob     | 
  3 | John    | Bob
  5 | Mary    | John
  4 | Michael | Mary
  1 | Tom     | Michael
(5 rows)

m_db=# SELECT id, name, LAG(name, 2) OVER() FROM employee;
 id |  name   | lag  
----+---------+------
  2 | Bob     | 
  3 | John    | 
  5 | Mary    | Bob
  4 | Michael | John
  1 | Tom     | Mary
(5 rows)

m_db=# SELECT id, name, LAG(name, 2, 'N/A') OVER() FROM employee;
 id |  name   | lag  
----+---------+------
  2 | Bob     | N/A
  3 | John    | N/A
  5 | Mary    | Bob
  4 | Michael | John
  1 | Tom     | Mary
(5 rows)

m_db=# SELECT id, name, LAG(name, 2, 'N/A') OVER(ORDER BY id) FROM employee;
 id |  name   | lag  
----+---------+------
  1 | Tom     | N/A
  2 | Bob     | N/A
  3 | John    | Tom
  4 | Michael | Bob
  5 | Mary    | John
(5 rows)

m_db=# SELECT id, age,name, LAG(name, 1, 'NA') OVER(PARTITION BY age) FROM employee;
 id | age |  name   | lag  
----+-----+---------+------
  3 |  25 | John    | NA
  5 |  25 | Mary    | John
  1 |  25 | Tom     | Mary
  2 |  36 | Bob     | NA
  4 |  36 | Michael | Bob
(5 rows)

m_db=# DROP TABLE employee;
DROP TABLE

LAST_VALUE

LAST_VALUE(expr) [null_treatment] over_clause

Description: Returns the value of expr in the last row of data in the current window based on the row order specified by over_clause in a query.

Return type: any type (related to the type of expr)

Examples:

m_db=# CREATE TABLE t_stu(id INT, name VARCHAR(30), gender VARCHAR(5), class INT, math INT, eng INT, his INT);
CREATE TABLE
m_db=# INSERT INTO t_stu VALUES (1, 'Mike', 'm', 1, 99, 86, 67), (2, 'Judy', 'f', 1, 85, 86, 85), (3, 'Jonh', 'm', 2, 79, 59, 95), (4, 'Lily', 'f', 2, 88, 81, 80), (5, 'Ethan', 'm', 2, 77, 70, 69), (6, 'Chris', 'f', 3, 65, 96, 94), (7, 'Vio', 'f', 3, 66, 69, 93);
INSERT 0 7
m_db=# SELECT id, name, math, class, LAST_VALUE(name) OVER (PARTITION BY class ORDER BY math DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM t_stu;
 id | name  | math | class | last_value
----+-------+------+-------+------------
  1 | Mike  |   99 |     1 | Judy
  2 | Judy  |   85 |     1 | Judy
  4 | Lily  |   88 |     2 | Ethan
  3 | Jonh  |   79 |     2 | Ethan
  5 | Ethan |   77 |     2 | Ethan
  7 | Vio   |   66 |     3 | Chris
  6 | Chris |   65 |     3 | Chris
(7 rows)

m_db=# DROP TABLE t_stu;
DROP TABLE

LEAD

LEAD(expr[, N[, default]]) [null_treatment] over_clause

Description: Obtains the values of expr in N rows after the current row in a query. If the corresponding row does not exist, default is returned.

Parameters: See Table 1.

Return type: any type (related to default and expr)

Examples:

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# SELECT id, name, LEAD(name) OVER() FROM employee;
 id |  name   |  lead   
----+---------+---------
  2 | Bob     | John
  3 | John    | Mary
  5 | Mary    | Michael
  4 | Michael | Tom
  1 | Tom     | 
(5 rows)

m_db=# SELECT id, name, LEAD(name, 2) OVER() FROM employee;
 id |  name   |  lead   
----+---------+---------
  2 | Bob     | Mary
  3 | John    | Michael
  5 | Mary    | Tom
  4 | Michael | 
  1 | Tom     | 
(5 rows)

m_db=# SELECT id, name, LEAD(name, 2, 'N/A') OVER() FROM employee;
 id |  name   |  lead   
----+---------+---------
  2 | Bob     | Mary
  3 | John    | Michael
  5 | Mary    | Tom
  4 | Michael | N/A
  1 | Tom     | N/A
(5 rows)

m_db=# SELECT id, name, LEAD(name, 2, 'N/A') OVER(ORDER BY id) FROM employee;
 id |  name   |  lead   
----+---------+---------
  1 | Tom     | John
  2 | Bob     | Michael
  3 | John    | Mary
  4 | Michael | N/A
  5 | Mary    | N/A
(5 rows)

m_db=# SELECT id, age,name, LEAD(name, 1, 'NA') OVER(PARTITION BY age) FROM employee;
 id | age |  name   |  lead   
----+-----+---------+---------
  3 |  25 | John    | Mary
  5 |  25 | Mary    | Tom
  1 |  25 | Tom     | NA
  2 |  36 | Bob     | Michael
  4 |  36 | Michael | NA
(5 rows)

m_db=# DROP TABLE employee;
DROP TABLE

NTILE

NTILE(n) over_clause

Description: Distributes rows in a sorting partition as evenly as possible into n buckets, and assigns a bucket number to each row, where n is a positive integer.

Return type: BIGINT UNSIGNED

Examples:

m_db=# CREATE TABLE ntile_t1(val INT);
CREATE TABLE
m_db=# INSERT INTO ntile_t1 VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
INSERT 0 10
m_db=# SELECT val, NTILE(4) OVER() as groupid FROM ntile_t1;
 val | groupid 
-----+---------
   1 | 1
   2 | 1
   3 | 1
   4 | 2
   5 | 2
   6 | 2
   7 | 3
   8 | 3
   9 | 4
  10 | 4
(10 rows)

m_db=# DROP TABLE ntile_t1;
DROP TABLE

PERCENT_RANK

PERCENT_RANK() over_clause

Description: Generates corresponding sequence numbers for the values in each group. That is, the function calculates the value according to the formula: Sequence number = (rank – 1) / (totalrows – 1). rank is the corresponding sequence number generated based on the RANK function for the value and totalrows is the total number of elements in a group.

Return type: DOUBLE PRECISION

Examples:

m_db=# CREATE TABLE percent_rank_t1(group_id INT, data INT);
CREATE TABLE
m_db=# INSERT INTO percent_rank_t1 VALUES(1,1),(1,2),(1, 4),(1, 5),(2, 6),(2, 7),(2,8),(2,9),(2,10);
INSERT 0 9
m_db=# SELECT group_id,data,PERCENT_RANK() OVER(PARTITION BY group_id ORDER BY data) FROM percent_rank_t1;
 group_id | data |    percent_rank    
----------+------+--------------------
        1 |    1 |                  0
        1 |    2 | 0.3333333333333333
        1 |    4 | 0.6666666666666666
        1 |    5 |                  1
        2 |    6 |                  0
        2 |    7 |               0.25
        2 |    8 |                0.5
        2 |    9 |               0.75
        2 |   10 |                  1
(9 rows)

m_db=# DROP TABLE percent_rank_t1;
DROP TABLE

RANK

RANK() over_clause

Description: Assigns a rank to each row according to the order specified by over_clause in a query, starting from 1. When the values in the sorting column are the same, the duplicate ranks are retained. When the next different value is encountered, the rank jumps to the overall ranking.

Return type: BIGINT UNSIGNED

When the value of the m_format_dev_version parameter is s2 or later and that of the m_format_behavior_compat_options parameter contains enable_conflict_funcs, this function is implemented in M-compatible databases, that is, the behavior described in this function. In other scenarios, the behavior of this function is the same as that of the rank function described in "SQL Reference > Functions and Operators > Window Functions" in Developer Guide.

Examples:

m_db=# CREATE TABLE employee(dname VARCHAR(20),eid VARCHAR(20),ename VARCHAR(20),hiredate DATE,salary INT);
CREATE TABLE
m_db=# INSERT INTO employee VALUES('R&D Dept','1001','R&D 1','2021-11-01',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1002','R&D 2','2021-11-02',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1003','R&D 3','2021-11-03',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1004','R&D 4','2021-11-04',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1005','R&D 5','2021-11-05',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('R&D Dept','1006','R&D 6','2021-11-06',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1007','Sales 1','2021-11-01',2000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1008','Sales 2','2021-11-02',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1009','Sales 3','2021-11-03',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1010','Sales 4','2021-11-04',6000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1011','Sales 5','2021-11-05',9000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('Sales Dept','1012','Sales 6','2021-11-06',6000);
INSERT 0 1
m_db=# SELECT dname,ename,salary,RANK() over (PARTITION BY dname ORDER BY salary DESC) AS rk FROM employee;
 dname  | ename | salary | rk 
--------+-------+--------+----
 R&D Dept | R&D 1 |        | 1
 R&D Dept | R&D 3 |   7000 | 2
 R&D Dept | R&D 4 |   7000 | 2
 R&D Dept | R&D 2 |   5000 | 4
 R&D Dept | R&D 5 |   4000 | 5
 R&D Dept | R&D 6 |   4000 | 5
 Sales Dept | Sales 2 |        | 1
 Sales Dept | Sales 5 |   9000 | 2
 Sales Dept | Sales 6 |   6000 | 3
 Sales Dept | Sales 4 |   6000 | 3
 Sales Dept | Sales 3 |   5000 | 5
 Sales Dept | Sales 1 |   2000 | 6
(12 rows)

m_db=# DROP TABLE employee;
DROP TABLE

ROW_NUMBER

ROW_NUMBER() over_clause

Description: Assigns a number to each row in the row order specified by over_clause.

Return type: BIGINT UNSIGNED

When the value of the m_format_dev_version parameter is s2 or later and that of the m_format_behavior_compat_options parameter contains enable_conflict_funcs, this function is implemented in M-compatible databases, that is, the behavior described in this section. Other operations are the same as those of the row_number function described in "SQL Reference > Functions and Operators > Window Functions" in Developer Guide.

Examples:

m_db=# CREATE TABLE employee (id INT PRIMARY KEY, name VARCHAR(20) NOT NULL, age INT);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "employee_pkey" for table "employee"
CREATE TABLE
m_db=# INSERT INTO employee VALUES(2, 'Bob', 36),(3, 'John', 25),(5, 'Mary', 25),(4, 'Michael', 36),(1, 'Tom', 25);
INSERT 0 5
m_db=# SELECT id, name, ROW_NUMBER() OVER() FROM employee;
 id |  name   | row_number 
----+---------+------------
  2 | Bob     |          1
  3 | John    |          2
  5 | Mary    |          3
  4 | Michael |          4
  1 | Tom     |          5
(5 rows)

m_db=# SELECT id, name, ROW_NUMBER() OVER(ORDER BY id) FROM employee;
 id |  name   | row_number 
----+---------+------------
  1 | Tom     |          1
  2 | Bob     |          2
  3 | John    |          3
  4 | Michael |          4
  5 | Mary    |          5
(5 rows)

m_db=# SELECT id, age,name, ROW_NUMBER() OVER(PARTITION BY age) FROM employee;
 id | age |  name   | row_number 
----+-----+---------+------------
  3 |  25 | John    |          1
  5 |  25 | Mary    |          2
  1 |  25 | Tom     |          3
  2 |  36 | Bob     |          1
  4 |  36 | Michael |          2
(5 rows)

m_db=# DROP TABLE employee;
DROP TABLE
Parent topic: Functions and Operators