【Analytic】分析函数之LAST_VALUE函数

我们通过这个文章来看一下分析函数LAST_VALUE的功效。

【Analytic】分析函数之MIN函数：http://space.itpub.net/519536/viewspace-624736

【Analytic】分析函数之MAX函数：http://space.itpub.net/519536/viewspace-624749

【Analytic】分析函数之AVG函数：http://space.itpub.net/519536/viewspace-624799

【Analytic】分析函数之ROW_NUMBER函数：http://space.itpub.net/519536/viewspace-624886

【Analytic】分析函数之RANK函数：http://space.itpub.net/519536/viewspace-624985

【Analytic】分析函数之DENSE_RANK函数：http://space.itpub.net/519536/viewspace-625115

【Analytic】分析函数之COUNT函数：http://space.itpub.net/519536/viewspace-625191

【Analytic】分析函数之FIRST_VALUE函数：http://space.itpub.net/519536/viewspace-625280

1.万变不离其宗，先看LAST_VALUE分析函数的10g语法描述。

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions073.htm#i83648

LAST_VALUE(expr [ IGNORE NULLS ])

   OVER (analytic_clause)

2.通过实验看一下分析函数LAST_VALUE的使用方法

1）创建测试表T，并初始化9条数据。

[email protected]> create table t (group_id number(10), name varchar2(10), salary int);

[email protected]> insert into t values (1,'Tom',1200);

[email protected]> insert into t values (2,'Kary',2400);

[email protected]> insert into t values (2,'Joe',800);

[email protected]> insert into t values (3,'Andy',100);

[email protected]> insert into t values (3,'Ellen',200);

[email protected]> insert into t values (3,'Erick',300);

[email protected]> insert into t values (3,'Hou',400);

[email protected]> insert into t values (3,'Mary',500);

[email protected]> insert into t values (3,'Secooler',600);

[email protected]> commit;

2）T表全貌

[email protected]> select * from t order by group_id,name;

  GROUP_ID NAME                               SALARY

---------- ------------------------------ ----------

         1 Tom                                  1200

         2 Joe                                   800

         2 Kary                                 2400

         3 Andy                                  100

         3 Ellen                                 200

         3 Erick                                 300

         3 Hou                                   400

         3 Mary                                  500

         3 Secooler                              600

9 rows selected.

共三组数据，group_id分别是1、2和3。第1组有一个人，第2组有两个人，第3组有六个人。最后一列是每个人的薪水值。

3）LAST_VALUE分析函数的简单用法

（1）在T表中添加一列，标识每一个数据分区中薪水最高的人名。

[email protected]> col highest_sal_name for a16

[email protected]> select group_id, name, salary, LAST_VALUE(name) OVER (partition by group_id order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from t order by group_id, name;

  GROUP_ID NAME                               SALARY HIGHEST_SAL_NAME

---------- ------------------------------ ---------- ----------------

         1 Tom                                  1200 Tom

         2 Joe                                   800 Kary

         2 Kary                                 2400 Kary

         3 Andy                                  100 Secooler

         3 Ellen                                 200 Secooler

         3 Erick                                 300 Secooler

         3 Hou                                   400 Secooler

         3 Mary                                  500 Secooler

         3 Secooler                              600 Secooler

9 rows selected.

注意其中“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”的使用，若省略效果如下。

[email protected]> select group_id, name, salary, LAST_VALUE(name) OVER (partition by group_id order by salary) as highest_sal_name from t order by group_id,name;

  GROUP_ID NAME                               SALARY HIGHEST_SAL_NAME

---------- ------------------------------ ---------- ----------------

         1 Tom                                  1200 Tom

         2 Joe                                   800 Joe

         2 Kary                                 2400 Kary

         3 Andy                                  100 Andy

         3 Ellen                                 200 Ellen

         3 Erick                                 300 Erick

         3 Hou                                   400 Hou

         3 Mary                                  500 Mary

         3 Secooler                              600 Secooler

9 rows selected.

显然这不是我们想要的效果:(，这是为什么呢~~~？给您一次思考和回答的机会。

如果对UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING不熟悉，请参考Oracle官方文档“windowing_clause”http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i97640。

（2）在T表中添加一列，标识每一个数据分区中薪水最高的薪水值。

[email protected]> col highest_sal_name for 9999

[email protected]> select group_id, name, salary, LAST_VALUE(SALARY) OVER (partition by group_id order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from t order by group_id, name;

  GROUP_ID NAME                               SALARY HIGHEST_SAL_NAME

---------- ------------------------------ ---------- ----------------

         1 Tom                                  1200             1200

         2 Joe                                   800             2400

         2 Kary                                 2400             2400

         3 Andy                                  100              600

         3 Ellen                                 200              600

         3 Erick                                 300              600

         3 Hou                                   400              600

         3 Mary                                  500              600

         3 Secooler                              600              600

9 rows selected.

3.小结

分析函数LAST_VALUE可以非常便利并迅速的的得到排序后的最后一条数据的各种信息。但，细节之处不容忽视，细心使用。

有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

Good luck.

secooler

10.01.16

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-625287/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-625287/