GBase 8a的NTH_VALUE返回当前窗口的指定n行的值,对比first_value则取的是第一个值。本文介绍NTH_VALUE的语法和使用样例。
目录导航
语法
NTH_VALUE(field,num) OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [asc/desc], col_name2 [asc/desc],…)
说明
field为指定的列,与first_value一样
num是截至窗口当前行,指定第几行。如果当前行小于指定num行,则返回null. 该参数必须大于0。
样例
数据
gbase> create table td(id int, type int,val decimal(20,3));
Query OK, 0 rows affected (Elapsed: 00:00:00.17)
gbase> insert into td values(1,1,111),(1,1,222),(2,1,333),(2,2,444),(3,2,555),(3,2,666),(4,2,777);
Query OK, 7 rows affected (Elapsed: 00:00:00.12)
Records: 7 Duplicates: 0 Warnings: 0
gbase> insert into td values(5,1,null),(50,2,null);
Query OK, 2 rows affected (Elapsed: 00:00:00.09)
Records: 2 Duplicates: 0 Warnings: 0
gbase> select * from td;
+------+------+---------+
gbase> select * from td order by type;
+------+------+---------+
| id | type | val |
+------+------+---------+
| 1 | 1 | 222.000 |
| 2 | 1 | 333.000 |
| 5 | 1 | NULL |
| 1 | 1 | 111.000 |
| 3 | 2 | 666.000 |
| 4 | 2 | 777.000 |
| 3 | 2 | 555.000 |
| 2 | 2 | 444.000 |
| 50 | 2 | NULL |
+------+------+---------+
9 rows in set (Elapsed: 00:00:00.04)
正常NTH_VALUE样例
num为1时,与first_value完全相同,返回第一行。
num为2,则第一行数据找不到第二行,所以返回null
num为3时,则第一行和第二行都找不到第三行,都返回null
gbase> select *,nth_value(val,1)over(partition by type order by id) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | 111.000 |
| 1 | 1 | 222.000 | 111.000 |
| 2 | 1 | 333.000 | 111.000 |
| 5 | 1 | NULL | 111.000 |
| 2 | 2 | 444.000 | 444.000 |
| 3 | 2 | 555.000 | 444.000 |
| 3 | 2 | 666.000 | 444.000 |
| 4 | 2 | 777.000 | 444.000 |
| 50 | 2 | NULL | 444.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.12)
gbase> select *,nth_value(val,2)over(partition by type order by id) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | NULL |
| 1 | 1 | 222.000 | 222.000 |
| 2 | 1 | 333.000 | 222.000 |
| 5 | 1 | NULL | 222.000 |
| 2 | 2 | 444.000 | NULL |
| 3 | 2 | 555.000 | 555.000 |
| 3 | 2 | 666.000 | 555.000 |
| 4 | 2 | 777.000 | 555.000 |
| 50 | 2 | NULL | 555.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.07)
gbase> select *,nth_value(val,3)over(partition by type order by id) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | NULL |
| 1 | 1 | 222.000 | NULL |
| 2 | 1 | 333.000 | 333.000 |
| 5 | 1 | NULL | 333.000 |
| 2 | 2 | 444.000 | NULL |
| 3 | 2 | 555.000 | NULL |
| 3 | 2 | 666.000 | 666.000 |
| 4 | 2 | 777.000 | 666.000 |
| 50 | 2 | NULL | 666.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.12)
其它例子
gbase> select *,nth_value(val,3)over(partition by type) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 222.000 | NULL |
| 2 | 1 | 333.000 | NULL |
| 5 | 1 | NULL | NULL |
| 1 | 1 | 111.000 | NULL |
| 3 | 2 | 666.000 | 555.000 |
| 4 | 2 | 777.000 | 555.000 |
| 3 | 2 | 555.000 | 555.000 |
| 2 | 2 | 444.000 | 555.000 |
| 50 | 2 | NULL | 555.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.06)
gbase> select *,nth_value(val,3)over(order by id) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 111.000 | NULL |
| 1 | 1 | 222.000 | NULL |
| 2 | 1 | 333.000 | 333.000 |
| 2 | 2 | 444.000 | 333.000 |
| 3 | 2 | 555.000 | 333.000 |
| 3 | 2 | 666.000 | 333.000 |
| 4 | 2 | 777.000 | 333.000 |
| 5 | 1 | NULL | 333.000 |
| 50 | 2 | NULL | 333.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.33)