文档首页/ 云数据库 GaussDB/ 兼容性参考（集中式）/ MySQL兼容性说明/ MySQL兼容性M-Compatibility模式/ 系统函数/ 窗口函数
更新时间：2025-09-09 GMT+08:00
查看PDF
分享

窗口函数

表1 窗口函数列表

函数名

与MySQL的差异

LAG()
  • 偏移量N的取值范围不同：

    MySQL中，N只允许是在范围[0, 263-1]整数值。

    GaussDB中，N只允许是在范围[0, 231-1]整数值。

  • 偏移量N的取值形式不同：
    • MySQL中，取值形式如下：
      • 常量字面量的无符号整数。
      • PREPARE语句中使用?声明的标记参数。
      • 用户自定义的变量。
      • 存储过程中的局部变量。
    • GaussDB中，取值形式如下：
      • 常量字面量的无符号整数。
      • 不支持在PREPARE语句中使用?声明的标记参数（prepare语句当前有差异）。
      • 用户自定义的变量。
      • 不支持使用存储过程中的局部变量（PLSQL当前不支持）。
  • 该函数作为子查询结合CREATE TABLE AS使用时，单独执行该函数的子查询语句没有报错或告警时：
    • GaussDB在严格模式和宽松模式下，CREATE TABLE AS语句执行成功，建表成功。
    • MySQL在严格模式下，CREATE TABLE AS语句执行可能报错，建表失败。

LEAD()

ROW_NUMBER()

-

RANK()

-

DENSE_RANK()

-

FIRST_VALUE()

-

LAST_VALUE()

-

PERCENT_RANK()

-

NTILE()

-

MIN()

GaussDB中MIN()函数不支持DISTINCT与OVER子句同时使用，MySQL支持，如下示例：

DROP TABLE IF EXISTS m_test;
CREATE TABLE m_test(age INT, salary INT);
INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL);

-- GaussDB行为：
m_db=# SELECT MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
ERROR:
GAUSS-10875: DISTINCT is not implemented for window functions
SQLSTATE: 0A000
LINE 1: SELECT MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by s...
^
CONTEXT:  referenced column: min

-- MySQL 8.0行为：
mysql> SELECT MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
+------------------------------------------------------------------------------+
| MIN(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) |
+------------------------------------------------------------------------------+
|                                                                         NULL |
|                                                                            1 |
|                                                                            2 |
|                                                                            2 |
|                                                                         NULL |
|                                                                         NULL |
+------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

由于是否使用DISTINCT在MIN()函数中不影响结果，建议直接使用：

SELECT MIN(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;

MAX()

GaussDB中MAX()函数不支持DISTINCT与OVER子句同时使用，MySQL支持，如下示例：

DROP TABLE IF EXISTS m_test;
CREATE TABLE m_test(age INT, salary INT);
INSERT INTO m_test VALUES(1, NULL), (1, 1), (1, 2), (1, 2), (2, NULL), (2, NULL);

-- GaussDB行为：
m_db=# SELECT MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
ERROR:
GAUSS-10875: DISTINCT is not implemented for window functions
SQLSTATE: 0A000
LINE 1: SELECT MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by s...
^
CONTEXT:  referenced column: max

-- MySQL 8.0行为：
mysql> SELECT MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;
+------------------------------------------------------------------------------+
| MAX(DISTINCT salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) |
+------------------------------------------------------------------------------+
|                                                                         NULL |
|                                                                            1 |
|                                                                            2 |
|                                                                            2 |
|                                                                         NULL |
|                                                                         NULL |
+------------------------------------------------------------------------------+
6 rows in set (0.00 sec)

由于是否使用DISTINCT在MAX()函数中不影响结果，建议直接使用：

SELECT MAX(salary) OVER(PARTITION BY age ORDER by salary ROWS CURRENT ROW) FROM m_test;

窗口函数
  • 窗口函数兼容MySQL 8.0，与部分兼容MySQL 5.7特性组合使用时，可能产生复合行为：
    • 例如组合JSON使用时：
      • GaussDB行为： 
        -- 预置表数据
m_db=# CREATE TABLE t1(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
m_db=# CREATE TABLE t2(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
m_db=# INSERT INTO t1 VALUES('1','false','false','false'),('1','false','false','false');
m_db=# INSERT INTO t1 VALUES('2','true','true','true'),('2','true','true','true');
m_db=# INSERT INTO t2 VALUES('3','true','true','true'),('3','true','true','true');
m_db=# INSERT INTO t2 VALUES('4','false','false','false'),('4','false','false','false');
-- JSON自身行为
m_db=# SELECT name,bit_or(col_json1) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
 name | rs 
------+----
 1    | 0
 2    | 0
 3    | 0
 4    | 0
(4 rows)

m_db=# SELECT name,cast(col_json1 AS unsigned) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp ORDER BY name;
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'true' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
WARNING:  The INTEGER value 'false' is incorrect.
CONTEXT:  referenced column: rs
 name | rs 
------+----
 1    | 0
 1    | 0
 2    | 0
 2    | 0
 3    | 0
 3    | 0
 4    | 0
 4    | 0
(8 rows)

-- 窗口函数组合JSON
m_db=# SELECT name,bit_or(col_json1) OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
 name | rs 
------+----
 1    | 0
 2    | 0
 3    | 0
 4    | 0
(4 rows)

m_db=# SELECT name, ROW_NUMBER() OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmmp GROUP BY name ORDER BY name;
 name | rs 
------+----
 1    | 1
 2    | 2
 3    | 2
 4    | 1
(4 rows)
      • MySQL行为： 
        -- 预置表数据
mysql> CREATE TABLE t1(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
mysql> CREATE TABLE t2(name varchar(20), col_json1 json, col_json2 json, col_json3 json);
mysql> INSERT INTO t1 VALUES('1','false','false','false'),('1','false','false','false');
mysql> INSERT INTO t1 VALUES('2','true','true','true'),('2','true','true','true');
mysql> INSERT INTO t2 VALUES('3','true','true','true'),('3','true','true','true');
mysql> INSERT INTO t2 VALUES('4','false','false','false'),('4','false','false','false');

-- JSON自身行为
-- MySQL 5.7
mysql> SELECT name,bit_or(col_json1) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  0 |
| 2    |  0 |
| 3    |  0 |
| 4    |  0 |
+------+----+
4 rows in set (0.00 sec)

mysql> SELECT name,cast(col_json1 AS unsigned) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp ORDER BY name;
+------+------+
| name | rs   |
+------+------+
| 1    |    0 |
| 1    |    0 |
| 2    |    0 |
| 2    |    0 |
| 3    |    0 |
| 3    |    0 |
| 4    |    0 |
| 4    |    0 |
+------+------+
8 rows in set, 8 warnings (0.00 sec)

mysql> SHOW warnings;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'true'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
| Warning | 1292 | Truncated incorrect INTEGER value: 'false' |
+---------+------+--------------------------------------------+
8 rows in set (0.00 sec)

-- MySQL 8.0
mysql> SELECT name,bit_or(col_json1) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  1 |
| 2    |  0 |
| 3    |  1 |
| 4    |  0 |
+------+----+
4 rows in set (0.01 sec)

mysql> SELECT name,cast(col_json1 AS unsigned) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp ORDER BY name;
+------+------+
| name | rs   |
+------+------+
| 1    |    1 |
| 1    |    1 |
| 2    |    0 |
| 2    |    0 |
| 3    |    1 |
| 3    |    1 |
| 4    |    0 |
| 4    |    0 |
+------+------+
8 rows in set (0.00 sec)

-- 窗口函数组合JSON 仅支持MySQL 8.0
mysql> SELECT name,bit_or(col_json1) OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  1 |
| 2    |  0 |
| 3    |  1 |
| 4    |  0 |
+------+----+
4 rows in set (0.00 sec)

mysql> SELECT name, ROW_NUMBER() OVER(PARTITION BY col_json2 ORDER BY col_json3) AS rs FROM (SELECT * FROM t1 UNION ALL SELECT * FROM t2)tmpmp GROUP BY name ORDER BY name;
+------+----+
| name | rs |
+------+----+
| 1    |  1 |
| 2    |  1 |
| 3    |  2 |
| 4    |  2 |
+------+----+
4 rows in set (0.01 sec)
    • AVG、MAX、MIN、BIT_AND、BIT_OR、BIT_XOR等窗口函数，结合CREATE TABLE AS语法操作创建表，创建出的表的字段类型与MySQL 8.0不一致。 
      -- 窗口MIN函数仅支持MySQL 8.0，对INT类型类型数据进行CREATE TABLE AS语法操作的结果差异。
-- GaussDB
m_db=# CREATE TABLE t_int(col_int int(100));
m_db=# CREATE TABLE test AS SELECT min(col_int) OVER(ORDER BY col_int) FROM t_int;
m_db=# DESC test;
 Field |    Type     | Null | Key | Default | Extra 
-------+-------------+------+-----+---------+-------
 min   | bigint(100) | YES  |     |         | 
(1 row)

-- MySQL 8.0
mysql> CREATE TABLE t_int(col_int int(100));
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> CREATE TABLE test AS SELECT min(col_int) OVER(ORDER BY col_int) FROM t_int;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC test;
+-------------------------------------+--------+------+-----+---------+-------+
| Field                               | Type   | Null | Key | Default | Extra |
+-------------------------------------+--------+------+-----+---------+-------+
| min(col_int) over(order by col_int) | bigint | YES  |     | NULL    |       |
+-------------------------------------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • ORDER BY子句排序时，对于NULL值的排序不同：
    • MySQL中，NULL值默认升序排在前面。
    • GaussDB中，NULL值默认升序排在后面。
  • OVER子句中的ORDER BY子句与PARTITION BY子句中使用列别名：
    • MySQL不支持使用列别名。
    • GaussDB支持使用列别名。
  • 当入参为表达式（如1 / col1）形式时，结果的精度存在差异：
    • MySQL会先计算表达式的结果并对表达式结果进行四舍五入，导致最终结果精度下降。
    • GaussDB不会对表达式的结果进行四舍五入。
  • 二进制字符串显示存在差异：
    • MySQL中，将二进制字符串编码为16进制后的编码值进行显示（例如'-4'会显示成编码后的0x2D34）。
    • GaussDB中，显示原字符串的值（例如'-4'会保持显示'-4'）。
  • 当MAX函数、MIN函数入参为FLOAT类型表字段，被用于CREATE TABLE AS语法创建表时，创建出的表的字段类型以及精度结果与MySQL 8.0不一致。 
    m_db=# CREATE TABLE t_float (id int, col_float1 FLOAT4(6,4), col_float2 FLOAT(7), col_float3 DOUBLE(8,5), col_ufloat1 FLOAT4(6,4) unsigned);

m_db=# CREATE TABLE t_tmp AS SELECT min(col_float1) OVER(ORDER BY id) AS col_float1, min(col_float2) OVER(PARTITION BY id) AS col_float2, min(col_float3) OVER(PARTITION BY id) AS col_float3 , min(col_ufloat1) OVER(PARTITION BY id) AS col_ufloat1 FROM t_float;

-- GaussDB
m_db=# DESC t_tmp;
    Field    |    Type     | Null | Key | Default | Extra 
-------------+-------------+------+-----+---------+-------
 col_float1  | double(21,4) | YES  |     |         | 
 col_float2  | double      | YES  |     |         | 
 col_float3  | double(22,5) | YES  |     |         | 
 col_ufloat1 | double(21,4) | YES  |     |         | 
(4 rows)

--MySQL
mysql> DESC t_tmp;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| col_float1  | float(6,4)  | YES  |     | NULL    |       |
| col_float2  | float       | YES  |     | NULL    |       |
| col_float3  | double(8,5) | YES  |     | NULL    |       |
| col_ufloat1 | float(6,4)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
  • GaussDB中，当包含LAG、LEAD、FIRST_VALUE或LAST_VALUE函数的表达式，在进行UNION操作后，再结合CREATE TABLE AS语法进行创建表，那么创建的表的字段类型以及精度结果与MySQL 8.0存在不一致。 
    -- GaussDB
m_db=# CREATE TABLE t_test(id int, col_int1 json, col_text1 tinyint);
CREATE TABLE
m_db=# CREATE TABLE t_tmp AS SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test;
INSERT 0 0
m_db=# DESC t_tmp;
  Field   | Type | Null | Key | Default | Extra 
----------+------+------+-----+---------+-------
 col_int1 | json | YES  |     |         | 
(1 row)

m_db=# DROP TABLE t_tmp;
DROP TABLE

m_db=# CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
INSERT 0 0

m_db=# DESC t_tmp;
   Field   |    Type    | Null | Key | Default | Extra 
-----------+------------+------+-----+---------+-------
 col_text1 | integer(4) | YES  |     |         | 
(1 row)
m_db=# DROP TABLE t_tmp;
DROP TABLE
m_db=# CREATE TABLE t_tmp AS SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test UNION ALL SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test;
INSERT 0 0
m_db=# DESC t_tmp;
  Field   |   Type   | Null | Key | Default | Extra 
----------+----------+------+-----+---------+-------
 col_int1 | longtext | YES  |     |         | 
(1 row)

m_db=# DROP TABLE t_tmp;
DROP TABLE
m_db=# CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test UNION ALL SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
INSERT 0 0
m_db=# DESC t_tmp;
   Field   |    Type     | Null | Key | Default | Extra 
-----------+-------------+------+-----+---------+-------
 col_text1 | integer(11) | YES  |     |         | 
(1 row)


--MySQL 8.0
mysql> DESC t_tmp;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| col_int1 | json | YES  |     | NULL    |       |
+----------+------+------+-----+---------+-------+
1 row in set (0.01 sec)

mysql> DROP TABLE t_tmp;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_tmp;
+-----------+------+------+-----+---------+-------+
| Field     | Type | Null | Key | Default | Extra |
+-----------+------+------+-----+---------+-------+
| col_text1 | int  | YES  |     | NULL    |       |
+-----------+------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t_test(id int, col_int1 json, col_text1 tinyint);
mysql> CREATE TABLE t_tmp AS SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test UNION ALL SELECT lead(col_int1,2) OVER(PARTITION BY id) AS col_int1 FROM t_test;
mysql> DESC t_tmp;
+----------+------+------+-----+---------+-------+
| Field    | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| col_int1 | json | YES  |     | NULL    |       |
+----------+------+------+-----+---------+-------+
1 row in set (0.01 sec)
 
mysql> DROP TABLE t_tmp;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t_tmp AS SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test UNION ALL SELECT last_value(col_text1) OVER(PARTITION BY id) AS col_text1 FROM t_test;
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC t_tmp;
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| col_text1 | tinyint | YES  |     | NULL    |       |
+-----------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)
  • 使用CREATE TABLE AS语法创建表，指定DESC查看表结构时，存在以下差异：
    • AVG、MAX、MIN、BIT_AND、BIT_OR、BIT_XOR窗口函数，表字段类型与精度显示存在不一致的场景。GaussDB返回类型同MySQL5.7非窗口函数返回类型。
    • MAX、MIN、LAG、LEAD、FIRST_VALUE、LAST_VALUE函数，在MySQL 8.0返回FLOAT类型的场景下，GaussDB返回DOUBLE类型。
    • MySQL 8.0中：
      • 表中的列类型（Type）为BIGINT类型或INT类型时不显示宽度。
      • 表中的列类型（Type）的宽度为0时，显示宽度，如binary(0)。
    • GaussDB中：
      • 表中的列类型（Type）为BIGINT类型或INTEGER类型时会显示宽度。
      • 表中的列类型（Type）的宽度为0时，不显示宽度，如binary(0)只显示成binary。
  • 窗口函数的执行结果依赖于表数据的顺序，在一些场景下（如存在GROUP BY、WHERE、HAVING的场景），GaussDB与MySQL的表数据顺序有差异，会影响窗口函数的执行结果，例如：
    • GaussDB的行为： 
      -- 预置表数据。
m_db=# CREATE TABLE t1(id int,name varchar(20),age int);
CREATE TABLE
m_db=# INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
INSERT 0 7
m_db=# INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
INSERT 0 7

-- 表数据的顺序与MySQL有差异导致last_value的取值结果有差异。
m_db=# SELECT age, last_value(age) over() FROM t1 WHERE id > 0 GROUP BY age HAVING age > 10;
 age | last_value 
-----+------------
  78 |         85
  90 |         85
  92 |         85
  85 |         85
(4 rows)

m_db=# DROP TABLE IF EXISTS t1;
DROP TABLE
    • MySQL的行为： 
      # 预置表数据。
mysql> CREATE TABLE t1(id int,name varchar(20),age int);

Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t1(id, name,age) VALUES (1, 'zwt',90), (2, 'dda',85), (3, 'aab',90), (4, 'aac',78), (5, 'aad',85), (6, 'aae',92), (7, 'aaf',78);
Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0

# 表数据的顺序与GaussDB有差异导致last_value的取值结果有差异。
mysql> SELECT age, last_value(age) over() FROM t1 WHERE id > 0 GROUP BY age HAVING age > 10;
+------+------------------------+
| age  | last_value(age) over() |
+------+------------------------+
|   90 |                     92 |
|   85 |                     92 |
|   78 |                     92 |
|   92 |                     92 |
+------+------------------------+
4 rows in set (0.00 sec)

mysql> DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.10 sec)
父主题： 系统函数

相关文档