返回首页

gbase数据、南大通用产品文档：GBase8aNTH_VALUE

更新日期：2024年09月11日

函数说明
nth_value 函数区别于first_value 和last_value，nth_value 函数可以指定取值的偏
移量。
nth_value 函数支持两个参数，第一个参数与first_value 和last_value 完全相同；
第二个参数为偏移量，要求一个大于0 的常量参数，不支持NULL 值和字段。
示例
gbase> SELECT *, nth_value(totalamount, 2) over (partition by uname order
by dt) as nth_value from tt;
+----+------------+-------+-------------+-----------+
| id | dt
| uname | totalamount | nth_value |
+----+------------+-------+-------------+-----------+
|
2 | 2016-06-05 | A
|
148 |
135 |
|
1 | 2016-06-05 | A
|
135 |
135 |
|
4 | 2016-06-02 | B
|
153 |
120 |
|
3 | 2016-06-02 | B
|
120 |
120 |
|
5 | 2016-06-10 | B
|
198 |
120 |
|
8 | 2016-02-05 | C
|
NULL |
NULL |
|
6 | 2016-08-05 | C
|
201 |
201 |
|
9 | 2016-08-06 | C
|
NULL |
201 |
|
7 | 2016-08-09 | C
|
129 |
201 |
| 14 | 2016-07-02 | D
|
172 |
NULL |
| 13 | 2016-09-01 | D
|
165 |
165 |
| 15 | NULL
| D
|
149 |
165 |
| 10 | 2016-06-01 | NULL
|
125 |
NULL |
| 11 | 2016-07-02 | NULL
|
131 |
131 |
| 12 | 2016-08-03 | NULL
|
152 |
131 |
+----+------------+-------+-------------+-----------+
gbase> SELECT *, nth_value(totalamount, NULL) over (partition by uname
order by dt) as nth_value from tt;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: argument[2] of
nth_value is out of range
gbase> SELECT *, nth_value(totalamount, 0) over (partition by uname order
by dt) as nth_value from tt;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: argument[2] of
nth_value is out of range
gbase> SELECT *, nth_value('const', 2) over (partition by uname order by dt)
as nth_value from tt;
+----+------------+-------+-------------+-----------+

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
882
gbase> SELECT *, nth_value(totalamount, 2) over (partition by uname order
by dt) as nth_value from tt;
+----+------------+-------+-------------+-----------+
| id | dt
| uname | totalamount | nth_value |
+----+------------+-------+-------------+-----------+
|
2 | 2016-06-05 | A
|
148 |
135 |
|
1 | 2016-06-05 | A
|
135 |
135 |
|
4 | 2016-06-02 | B
|
153 |
120 |
|
3 | 2016-06-02 | B
|
120 |
120 |
|
5 | 2016-06-10 | B
|
198 |
120 |
|
8 | 2016-02-05 | C
|
NULL |
NULL |
|
6 | 2016-08-05 | C
|
201 |
201 |
|
9 | 2016-08-06 | C
|
NULL |
201 |
|
7 | 2016-08-09 | C
|
129 |
201 |
| 14 | 2016-07-02 | D
|
172 |
NULL |
| 13 | 2016-09-01 | D
|
165 |
165 |
| 15 | NULL
| D
|
149 |
165 |
| 10 | 2016-06-01 | NULL
|
125 |
NULL |
| 11 | 2016-07-02 | NULL
|
131 |
131 |
| 12 | 2016-08-03 | NULL
|
152 |
131 |
+----+------------+-------+-------------+-----------+
gbase> SELECT *, nth_value(totalamount, NULL) over (partition by uname
order by dt) as nth_value from tt;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: argument[2] of
nth_value is out of range
gbase> SELECT *, nth_value(totalamount, 0) over (partition by uname order
by dt) as nth_value from tt;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: argument[2] of
nth_value is out of range
| id | dt
| uname | totalamount | nth_value |
+----+------------+-------+-------------+-----------+
|
2 | 2016-06-05 | A
|
148 | const
|
|
1 | 2016-06-05 | A
|
135 | const
|
|
4 | 2016-06-02 | B
|
153 | const
|
|
3 | 2016-06-02 | B
|
120 | const
|
|
5 | 2016-06-10 | B
|
198 | const
|
|
8 | 2016-02-05 | C
|
NULL | NULL
|
|
6 | 2016-08-05 | C
|
201 | const
|
|
9 | 2016-08-06 | C
|
NULL | const
|
|
7 | 2016-08-09 | C
|
129 | const
|
| 14 | 2016-07-02 | D
|
172 | NULL
|
| 13 | 2016-09-01 | D
|
165 | const
|
| 15 | NULL
| D
|
149 | const
|
| 10 | 2016-06-01 | NULL
|
125 | NULL
|

GBase 8a MPP Cluster 产品手册
5 数据库管理指南
文档版本953（2022-04-10）
南大通用数据技术股份有限公司
883
gbase> SELECT *, nth_value(totalamount, 2) over (partition by uname order
by dt) as nth_value from tt;
+----+------------+-------+-------------+-----------+
| id | dt
| uname | totalamount | nth_value |
+----+------------+-------+-------------+-----------+
|
2 | 2016-06-05 | A
|
148 |
135 |
|
1 | 2016-06-05 | A
|
135 |
135 |
|
4 | 2016-06-02 | B
|
153 |
120 |
|
3 | 2016-06-02 | B
|
120 |
120 |
|
5 | 2016-06-10 | B
|
198 |
120 |
|
8 | 2016-02-05 | C
|
NULL |
NULL |
|
6 | 2016-08-05 | C
|
201 |
201 |
|
9 | 2016-08-06 | C
|
NULL |
201 |
|
7 | 2016-08-09 | C
|
129 |
201 |
| 14 | 2016-07-02 | D
|
172 |
NULL |
| 13 | 2016-09-01 | D
|
165 |
165 |
| 15 | NULL
| D
|
149 |
165 |
| 10 | 2016-06-01 | NULL
|
125 |
NULL |
| 11 | 2016-07-02 | NULL
|
131 |
131 |
| 12 | 2016-08-03 | NULL
|
152 |
131 |
+----+------------+-------+-------------+-----------+
gbase> SELECT *, nth_value(totalamount, NULL) over (partition by uname
order by dt) as nth_value from tt;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: argument[2] of
nth_value is out of range
gbase> SELECT *, nth_value(totalamount, 0) over (partition by uname order
by dt) as nth_value from tt;
ERROR 1733 (HY000): (GBA-01EX-700) Gbase general error: argument[2] of
nth_value is out of range
| 11 | 2016-07-02 | NULL
|
131 | const
|
| 12 | 2016-08-03 | NULL
|
152 | const
|
+----+------------+-------+-------------+-----------+

取值：[0|1|2]
默认值：0
说明：控制日期类型和数字计算的行为。
0. 日期类型
1. 兼容老版本，结果是int 类型
2. 报错
默认关闭。
修改方式：
可使用set 语句修改值也可在配置文件中修改值。
适用于session、
global
范围均可。

GBASE
 结构说明
该结构代表与数据库连接的句柄。不建议对GBase 结构进行拷贝，不保证
这样的拷贝会有用。