文档首页/ 云数据库 GaussDB/ M-Compatibility开发指南（集中式）/ SQL参考/ 函数和操作符/ 窗口函数
更新时间：2025-09-12 GMT+08:00
查看PDF
分享

窗口函数

窗口函数必须通过over_clause指定OVER子句，以确定如何将查询行划分为多个组。

窗口函数在未开启精度传递参数时，其结果的精度可能会存在问题，请在开启精度GUC参数（即m_format_behavior_compat_options值包含'enable_precision_decimal'）后使用。

over_clause语法形式：

{ OVER (window_spec) | OVER existing_window_name }
其中window_spec的具体语法形式： 
[ existing_window_name ] [ partition_clause ] [ order_clause ] [ frame_clause ]
  • existing_window_name：引用的已存在的窗口名称。
  • partition_clause：指定如何将查询行进行分组。具体语法形式： 
    PARTITION BY expr [, ...]
  • order_clause：指定如何对每个分组中的行进行排序。具体语法形式： 
    ORDER BY expr [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...]
    • NULLS FIRST：指定空值在排序中位于非空值之前，指定DESC排序时的默认行为。
    • NULLS LAST：指定空值在排序中位于非空值之后，未指定DESC排序时的默认行为。
  • frame_clause：为窗口函数定义一个窗口框架window frame，window frame为当前查询行的一组相关行。frame_clause可以是以下的形式： 
    [ RANGE | ROWS ] frame_start
[ RANGE | ROWS ] BETWEEN frame_start AND frame_end
    其中frame_start和frame_end形式为： 
    UNBOUNDED PRECEDING
VALUE PRECEDING
CURRENT ROW
VALUE FOLLOWING
UNBOUNDED FOLLOWING

    针对使用到VALUE场景存在如下约束：

    • VALUE的值仅支持为非负数。
    • VALUE PRECEDING以及VALUE FOLLOWING仅支持在指定为ROWS场景时使用，不支持在指定为RANGE场景使用。

DENSE_RANK

DENSE_RANK() over_clause

描述：在一次查询中，按照over_clause指定的行顺序，为每一行分配一个编号，编号从1开始。排序列数值相同时，保留重复编号，遇到下一个不同值时，依然按照连续数字排列。

返回值类型：BIGINT UNSIGNED

此函数在参数m_format_dev_version值为's2'或以上版本且参数m_format_behavior_compat_options值包含'enable_conflict_funcs'的情况下为M-Compatibility模式数据库实现行为，即本章节描述的行为；其他行为和《开发指南》中的“SQL参考 > 函数和操作符 > 窗口函数”章节中的dense_rank函数保持一致。

示例：

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('研发部','1001','研发1','2021-11-01',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1002','研发2','2021-11-02',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1003','研发3','2021-11-03',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1004','研发4','2021-11-04',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1005','研发5','2021-11-05',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1006','研发6','2021-11-06',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1007','销售1','2021-11-01',2000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1008','销售2','2021-11-02',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1009','销售3','2021-11-03',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1010','销售4','2021-11-04',6000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1011','销售5','2021-11-05',9000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1012','销售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 
--------+-------+--------+----
 研发部 | 研发1 |        | 1
 研发部 | 研发3 |   7000 | 2
 研发部 | 研发4 |   7000 | 2
 研发部 | 研发2 |   5000 | 3
 研发部 | 研发5 |   4000 | 4
 研发部 | 研发6 |   4000 | 4
 销售部 | 销售2 |        | 1
 销售部 | 销售5 |   9000 | 2
 销售部 | 销售6 |   6000 | 3
 销售部 | 销售4 |   6000 | 3
 销售部 | 销售3 |   5000 | 4
 销售部 | 销售1 |   2000 | 5
(12 rows)

m_db=# DROP TABLE employee;
DROP TABLE

FIRST_VALUE

FIRST_VALUE(expr) [null_treatment] over_clause

描述：在一次查询中，按照over_clause指定的行顺序，返回当前窗口第一行数据中expr的值。

返回值类型：任意类型（与expr的类型有关）

示例：

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

描述：在一次查询中取出当前行的前N行中expr对应的值。如果没有这样的行，则返回值为default。

参数：如表1所示。

表1 LAG函数参数说明

名称

类型

描述

取值范围

expr

任意类型

表示要进行对比的字段。

任意类型值。

N

BIGINT

表示expr的偏移量，缺省值为1。可以是以下取值形式：

  • 常量字面量的无符号整数。
  • 用户自定义的变量。

只允许是在范围[0, 231-1]的整数值。

default

任意类型

表示默认值，缺省值为NULL。

任意类型值。

[null_treatment]

-

是否忽略NULL值。

RESPECT NULLS或IGNORE NULLS（可选，但不支持）。

返回值类型：任意类型（与default和expr有关）。

示例：

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

描述：在一次查询中，按照over_clause指定的行顺序，返回当前窗口最后一行数据中expr的值。

返回值类型：任意类型（与expr的类型有关）

示例：

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

描述：在一次查询中获取当前行之后N行数据中expr对应的值。如果不存在相应的行，则返回值为default。

参数：如表1所示。

返回值类型：任意类型（与default和expr有关）。

示例：

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

描述：将排序分区中的行尽可能平均分配给n个桶，并将桶号分配给每一行，其中n为正整数。

返回值类型：BIGINT UNSIGNED

示例：

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

描述：为各组内对应值生成相对序号，即根据公式 (rank - 1) / (totalrows - 1)计算所得的值。其中rank为该值依据RANK函数所生成的对应序号，totalrows为该分组内的总元素个数。

返回值类型：DOUBLE PRECISION

示例：

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

描述：在一次查询中，按照over_clause指定的行顺序，为每一行分配一个编号，编号从1开始。排序列数值相同时，保留重复编号，遇到下一个不同值时，跳跃到总体的排名。

返回值类型：BIGINT UNSIGNED

此函数在参数m_format_dev_version值为's2'或以上版本且参数m_format_behavior_compat_options值包含'enable_conflict_funcs'的情况下，为M-Compatibility模式数据库实现行为，即当前函数描述的行为；其他场景下，此函数的行为和《开发指南》中的“SQL参考 > 函数和操作符 > 窗口函数”章节中的rank函数保持一致。

示例：

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('研发部','1001','研发1','2021-11-01',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1002','研发2','2021-11-02',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1003','研发3','2021-11-03',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1004','研发4','2021-11-04',7000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1005','研发5','2021-11-05',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('研发部','1006','研发6','2021-11-06',4000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1007','销售1','2021-11-01',2000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1008','销售2','2021-11-02',NULL);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1009','销售3','2021-11-03',5000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1010','销售4','2021-11-04',6000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1011','销售5','2021-11-05',9000);
INSERT 0 1
m_db=# INSERT INTO employee VALUES('销售部','1012','销售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 
--------+-------+--------+----
 研发部 | 研发1 |        | 1
 研发部 | 研发3 |   7000 | 2
 研发部 | 研发4 |   7000 | 2
 研发部 | 研发2 |   5000 | 4
 研发部 | 研发5 |   4000 | 5
 研发部 | 研发6 |   4000 | 5
 销售部 | 销售2 |        | 1
 销售部 | 销售5 |   9000 | 2
 销售部 | 销售6 |   6000 | 3
 销售部 | 销售4 |   6000 | 3
 销售部 | 销售3 |   5000 | 5
 销售部 | 销售1 |   2000 | 6
(12 rows)

m_db=# DROP TABLE employee;
DROP TABLE

ROW_NUMBER

ROW_NUMBER() over_clause

描述：按照over_clause指定的行顺序，为每一行分配一个编号。

返回值类型：BIGINT UNSIGNED

此函数在参数m_format_dev_version值为's2'或以上版本且参数m_format_behavior_compat_options值包含'enable_conflict_funcs'的情况下为M-Compatibility模式数据库实现行为，即本章节描述的行为；其他行为和《开发指南》中的“SQL参考 > 函数和操作符 > 窗口函数”章节中的row_number函数保持一致。

示例：

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
父主题： 函数和操作符

相关文档