GBase 8a OLAP分析函数last_value的使用样例

语法

last_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)

完整last_value窗口用法

同样要注意null值的影响。

gbase> select *,last_value(val)over(partition by type order by id) maxOfType from td;
+------+------+---------+-----------+
| id   | type | val     | maxOfType |
+------+------+---------+-----------+
|    1 |    1 | 222.000 |   111.000 |
|    1 |    1 | 111.000 |   111.000 |
|    2 |    1 | 333.000 |   333.000 |
|    5 |    1 |    NULL |      NULL |
|    2 |    2 | 444.000 |   444.000 |
|    3 |    2 | 666.000 |   555.000 |
|    3 |    2 | 555.000 |   555.000 |
|    4 |    2 | 777.000 |   777.000 |
|   50 |    2 |    NULL |      NULL |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.35)

gbase> select *,last_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 |   333.000 |
|    1 |    1 | 222.000 |   111.000 |
|    1 |    1 | 111.000 |   111.000 |
|   50 |    2 |    NULL |      NULL |
|    4 |    2 | 777.000 |   777.000 |
|    3 |    2 | 555.000 |   666.000 |
|    3 |    2 | 666.000 |   666.000 |
|    2 |    2 | 444.000 |   444.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.34)

无partition 的last_value函数用法

所有数据参与排序

gbase> select *,last_value(val)over(order by id) maxOfType from td;
+------+------+---------+-----------+
| id   | type | val     | maxOfType |
+------+------+---------+-----------+
|    1 |    1 | 222.000 |   111.000 |
|    1 |    1 | 111.000 |   111.000 |
|    2 |    2 | 444.000 |   333.000 |
|    2 |    1 | 333.000 |   333.000 |
|    3 |    2 | 555.000 |   666.000 |
|    3 |    2 | 666.000 |   666.000 |
|    4 |    2 | 777.000 |   777.000 |
|    5 |    1 |    NULL |      NULL |
|   50 |    2 |    NULL |      NULL |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.07)

gbase> select *,last_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 |   777.000 |
|    3 |    2 | 666.000 |   555.000 |
|    3 |    2 | 555.000 |   555.000 |
|    2 |    1 | 333.000 |   444.000 |
|    2 |    2 | 444.000 |   444.000 |
|    1 |    1 | 111.000 |   222.000 |
|    1 |    1 | 222.000 |   222.000 |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.09)

有partition无order的last_value用法

每个窗口自然顺序。

gbase> select *,last_value(val)over(partition by type) maxOfType from td;
+------+------+---------+-----------+
| id   | type | val     | maxOfType |
+------+------+---------+-----------+
|    1 |    1 | 222.000 |   111.000 |
|    2 |    1 | 333.000 |   111.000 |
|    5 |    1 |    NULL |   111.000 |
|    1 |    1 | 111.000 |   111.000 |
|    3 |    2 | 666.000 |      NULL |
|    4 |    2 | 777.000 |      NULL |
|    3 |    2 | 555.000 |      NULL |
|    2 |    2 | 444.000 |      NULL |
|   50 |    2 |    NULL |      NULL |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.10)

没有partion和order的last_value用法

所有数据一个窗口，选自然顺序的第一个。 这个用法像没啥意义。

gbase> select *,last_value(val)over() maxOfType from td;
+------+------+---------+-----------+
| id   | type | val     | maxOfType |
+------+------+---------+-----------+
|    1 |    1 | 111.000 |      NULL |
|    1 |    1 | 222.000 |      NULL |
|    2 |    1 | 333.000 |      NULL |
|    2 |    2 | 444.000 |      NULL |
|    3 |    2 | 555.000 |      NULL |
|    3 |    2 | 666.000 |      NULL |
|    4 |    2 | 777.000 |      NULL |
|    5 |    1 |    NULL |      NULL |
|   50 |    2 |    NULL |      NULL |
+------+------+---------+-----------+
9 rows in set (Elapsed: 00:00:00.06)