更新日期: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-09-15)
南大通用数据技术股份有限公司
877
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-09-15)
南大通用数据技术股份有限公司
878
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
|
+----+------------+-------+-------------+-----------+
与Oracle 数据库DBLINK 配置注意事项
问题现象
关于GBase 8a MPP Cluster 集群与Oracle 数据库DBLINK 配置dataSource_dbna
me 注意事项。
解决方法
在GBas 8a MPP Cluster 集群与Oracle 数据库配置DBLINK。dblink 配置文
件参数:dataSource_dbname=orcl ,此处应填写Oracle 的SID_NAME,而不
是SERVICE_NAME;
大多数情况下,SID_NAME 和SERVICE_NAME 是一致的,但也有不一致的
情况。在这种情况下,需要登录oracle 数据库,执行如下操作查看:
SID_NAME: select INSTANCE_NAME from v$instance