# 窗口函数
表1窗口函数列表 
| 函数名          | 与MySQL的差异                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
|:---|:---|
| LAG()            | - 偏移量N的取值范围不同： MySQL中，N只允许是在范围\[0, 2^63^-1\]整数值。 GaussDB中，N只允许是在范围\[0, 2^31^-1\]整数值。   - 偏移量N的取值形式不同： - MySQL中，取值形式如下： - 常量字面量的无符号整数。  - prepare语句中使用?声明的标记参数。  - 用户自定义的变量。  - 存储过程中的局部变量。    - GaussDB中，取值形式如下： - 常量字面量的无符号整数。  - 不支持在prepare语句中使用?声明的标记参数（prepare语句当前有差异）。  - 用户自定义的变量。  - 不支持使用存储过程中的局部变量（PLSQL当前不支持）。      - 该函数作为子查询结合CREATE TABLE AS使用时，单独执行该函数的子查询语句没有报错或告警时： - GaussDB在严格模式和宽松模式下，CREATE TABLE AS语句执行成功，建表成功。  - MySQL在严格模式下，CREATE TABLE AS语句执行可能报错，建表失败。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| LEAD()          | - 偏移量N的取值范围不同： MySQL中，N只允许是在范围\[0, 2^63^-1\]整数值。 GaussDB中，N只允许是在范围\[0, 2^31^-1\]整数值。   - 偏移量N的取值形式不同： - MySQL中，取值形式如下： - 常量字面量的无符号整数。  - prepare语句中使用?声明的标记参数。  - 用户自定义的变量。  - 存储过程中的局部变量。    - GaussDB中，取值形式如下： - 常量字面量的无符号整数。  - 不支持在prepare语句中使用?声明的标记参数（prepare语句当前有差异）。  - 用户自定义的变量。  - 不支持使用存储过程中的局部变量（PLSQL当前不支持）。      - 该函数作为子查询结合CREATE TABLE AS使用时，单独执行该函数的子查询语句没有报错或告警时： - GaussDB在严格模式和宽松模式下，CREATE TABLE AS语句执行成功，建表成功。  - MySQL在严格模式下，CREATE TABLE AS语句执行可能报错，建表失败。                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
| ROW_NUMBER()     | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| RANK()         | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 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; ```                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
| DENSE_RANK()    | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| FIRST_VALUE()  | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| LAST_VALUE()   | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
| PERCENT_RANK() | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| NTILE()       | -                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 窗口函数          | - 窗口函数兼容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值默认升序排在后面。   说明： 在GaussDB中，可通过指定NULLS FIRST或NULLS LAST进行排序，排序结果与MySQL的排序结果保持一致。   - 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中： - 表中的列类型（Type）为BIGINT类型或INT类型时不显示宽度。  - 表中的列类型（Type）的宽度为0时，显示宽度，如binary(0)。    - GaussDB中： - 表中的列类型（Type）为BIGINT类型或INTEGER类型时会显示宽度。  - 表中的列类型（Type）的宽度为0时，不显示宽度，如binary(0)只显示成binary。  - 表结构中标识NULL和Default值的列，功能暂未实现。      - 窗口函数的执行结果依赖于表数据的顺序，在一些场景下（如指定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) ``` 说明： 针对expr PRECEDING和expr FOLLOWING场景存在如下约束： - expr的值仅支持为非负数。  - 仅支持在指定为ROWS场景时使用，不支持在指定为RANGE场景使用。        |
   
