GBase 8a的First_value函数,返回每组窗口里的第一个值,类似row_number是等于1的。本文介绍first_value的函数用法并给出使用例子。
目录导航
语法
First_value(XXX) OVER([PARTITION BY col_name1,col_name2,…] ORDER BY col_name1 [asc/desc], col_name2 [asc/desc],…)
说明
对每个窗口(也可以不指定partion,所有数据一个窗口),进指定列的排序(如不排序,则默认顺序),然后返回【截止到当前行的】第一行的值。
参数可以是字段,表达式等。
注意null值在排序中的影响。如果不确定,建议对字段做提前处理,比如nvl函数。
样例
测试数据
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;
+------+------+---------+
| id | type | val |
+------+------+---------+
| 1 | 1 | 111.000 |
| 1 | 1 | 222.000 |
| 2 | 1 | 333.000 |
| 2 | 2 | 444.000 |
| 3 | 2 | 555.000 |
| 3 | 2 | 666.000 |
| 4 | 2 | 777.000 |
| 5 | 1 | NULL |
| 50 | 2 | NULL |
+------+------+---------+
9 rows in set (Elapsed: 00:00:00.03)
完整first_value窗口用法
gbase> select *,first_value(val)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.34)
gbase> select *,first_value(val)over(partition by type order by id desc) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 5 | 1 | NULL | NULL |
| 2 | 1 | 333.000 | NULL |
| 1 | 1 | 111.000 | NULL |
| 1 | 1 | 222.000 | NULL |
| 50 | 2 | NULL | NULL |
| 4 | 2 | 777.000 | NULL |
| 3 | 2 | 555.000 | NULL |
| 3 | 2 | 666.000 | NULL |
| 2 | 2 | 444.000 | NULL |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.14)
无partition 的first_value函数用法
所有数据排序后,取第一个值。
gbase> select *,first_value(val)over(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 |
| 2 | 2 | 444.000 | 111.000 |
| 3 | 2 | 555.000 | 111.000 |
| 3 | 2 | 666.000 | 111.000 |
| 4 | 2 | 777.000 | 111.000 |
| 5 | 1 | NULL | 111.000 |
| 50 | 2 | NULL | 111.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.10)
gbase> select *,first_value(val)over(order by id desc) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 50 | 2 | NULL | NULL |
| 5 | 1 | NULL | NULL |
| 4 | 2 | 777.000 | NULL |
| 3 | 2 | 555.000 | NULL |
| 3 | 2 | 666.000 | NULL |
| 2 | 1 | 333.000 | NULL |
| 2 | 2 | 444.000 | NULL |
| 1 | 1 | 111.000 | NULL |
| 1 | 1 | 222.000 | NULL |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.05)
有partition无order的first_value用法
每个窗口返回自然顺序的第一个。由于数据库内并行,无法保障结果可重现。
gbase> select id,type,first_value(val)over(partition by type) maxOfType from td;
+------+------+-----------+
gbase> select *,first_value(val)over(partition by type) maxOfType from td;
+------+------+---------+-----------+
| id | type | val | maxOfType |
+------+------+---------+-----------+
| 1 | 1 | 222.000 | 222.000 |
| 2 | 1 | 333.000 | 222.000 |
| 5 | 1 | NULL | 222.000 |
| 1 | 1 | 111.000 | 222.000 |
| 3 | 2 | 666.000 | 666.000 |
| 4 | 2 | 777.000 | 666.000 |
| 3 | 2 | 555.000 | 666.000 |
| 2 | 2 | 444.000 | 666.000 |
| 50 | 2 | NULL | 666.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.09)
没有partion和order的first_value用法
所有数据一个窗口,选自然顺序的第一个。 这个用法像没啥意义。
gbase> select *,first_value(val)over() 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 |
| 2 | 2 | 444.000 | 111.000 |
| 3 | 2 | 555.000 | 111.000 |
| 3 | 2 | 666.000 | 111.000 |
| 4 | 2 | 777.000 | 111.000 |
| 5 | 1 | NULL | 111.000 |
| 50 | 2 | NULL | 111.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.08)