您的位置:首页技术开发数据库教程 → 分析函数之LAST_VALUE函数

分析函数之LAST_VALUE函数

时间:2010/1/21 10:54:00来源:本站整理作者:不详我要评论(0)

我们通过这个文章来看一下分析函数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条数据。
sec@ora10g> create table t (group_id number(10), name varchar2(10), salary int);
sec@ora10g> insert into t values (1,'Tom',1200);
sec@ora10g> insert into t values (2,'Kary',2400);
sec@ora10g> insert into t values (2,'Joe',800);
sec@ora10g> insert into t values (3,'Andy',100);
sec@ora10g> insert into t values (3,'Ellen',200);
sec@ora10g> insert into t values (3,'Erick',300);
sec@ora10g> insert into t values (3,'Hou',400);
sec@ora10g> insert into t values (3,'Mary',500);
sec@ora10g> insert into t values (3,'Secooler',600);
sec@ora10g> commit;

2)T表全貌
sec@ora10g> 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表中添加一列,标识每一个数据分区中薪水最高的人名。
sec@ora10g> col highest_sal_name for a16
sec@ora10g> 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                                  100Secooler
         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”的使用,若省略效果如下。
sec@ora10g> 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表中添加一列,标识每一个数据分区中薪水最高的薪水值。
sec@ora10g> col highest_sal_name for 9999
sec@ora10g> 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 --
 

相关视频

    没有数据

相关阅读 数据库流行度排行2019年9月 数据库排行榜2019年最新版fifa online4数据库大全 fifa online4球员数据库在哪myeclipse怎么连接到数据库 myeclipse连接到数据库方法PLSQL Developer怎么导出数据库PLSQL Developer配置Oralce11g连接plsql developer怎么使用 plsql developer使用教程plsql developer怎么连接数据库 plsql developer连接数据库教程mysql数据库root密码忘记的修改方法

文章评论
发表评论

热门文章 oracle10g安装图解(wi

最新文章 数据库流行度排行2019oracle10g安装图解(wi SQL2008全部数据导出导入两种方法SQL2005新建复制“找不到存储过程 错误:28Dos远程登录mysql数据库详细图文教程mysql怎么开启远程登录功能

人气排行 mysql自动定时备份数据库的最佳方法-支持wiVisual Foxpro 6.0安装向导图文教程SQL Server 2008 安装图文教程SQL2008全部数据导出导入两种方法SQL 2000/2005/2008 的收缩日志方法,和清理mysql出 Can't connect to MySQL server onoracle10g安装图解(win7)sql2005安装图解_(sql server2005)安装教程