oracle分析函数系列之first_value/last_value:在记录集中查找第一条记录和最后一条记录

注意:与max,min的区别,虽然也可以实现,但只是针对数字字段.

1、初始化原始数据:
create table test (id number(2), name varchar2(10), salary number(6,2));
insert into test values (1,'Tom',120);
insert into test values (2,'Ellen',240);
insert into test values (2,'Joe',80);
insert into test values (3,'Andy',300);
insert into test values (3,'Kary',500);
insert into test values (3,'Erick',1300);
insert into test values (3,'Hou',40);
insert into test values (3,'Mary',200);
insert into test values (3,'Secooler',800);
commit;
select * from test order by ID,name;

 ID NAME         SALARY
--- ---------- --------
  1 Tom          120.00
  2 Ellen        240.00
  2 Joe           80.00
  3 Andy         300.00
  3 Erick       1300.00
  3 Hou           40.00
  3 Kary         500.00
  3 Mary         200.00
  3 Secooler     800.00


2、LAST_VALUE分析函数的简单用法
(1)在TEST表中添加一列,标识每一个数据分区中薪水最高的人名。
select ID, name, salary, LAST_VALUE(name) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;

 ID NAME         SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
  1 Tom          120.00 Tom
  2 Ellen        240.00 Ellen
  2 Joe           80.00 Ellen
  3 Andy         300.00 Erick
  3 Erick       1300.00 Erick
  3 Hou           40.00 Erick
  3 Kary         500.00 Erick
  3 Mary         200.00 Erick
  3 Secooler     800.00 Erick

注意其中“ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING”的使用,若省略效果如下。
select ID, name, salary, LAST_VALUE(name) OVER (partition by ID order by salary) as highest_sal_name from test order by ID,name;

 ID NAME         SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
  1 Tom          120.00 Tom
  2 Ellen        240.00 Ellen
  2 Joe           80.00 Joe
  3 Andy         300.00 Andy
  3 Erick       1300.00 Erick
  3 Hou           40.00 Hou
  3 Kary         500.00 Kary
  3 Mary         200.00 Mary
  3 Secooler     800.00 Secooler

显然这不是我们想要的效果:(,这是为什么呢~~~?给您一次思考和回答的机会。
如果对UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING不熟悉,请参考Oracle官方文档“windowing_clause”http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i97640。

在TEST表中添加一列,标识每一个数据分区中薪水最高的薪水值。
col highest_sal_name for 9999
select ID, name, salary, LAST_VALUE(SALARY) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;

 ID NAME         SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
  1 Tom          120.00              120
  2 Ellen        240.00              240
  2 Joe           80.00              240
  3 Andy         300.00             1300
  3 Erick       1300.00             1300
  3 Hou           40.00             1300
  3 Kary         500.00             1300
  3 Mary         200.00             1300
  3 Secooler     800.00             1300

3、与之相对应的是FIRST_VALUE函数
select ID, name, salary, FIRST_VALUE(name) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;
 ID NAME         SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
  1 Tom          120.00 Tom
  2 Ellen        240.00 Joe
  2 Joe           80.00 Joe
  3 Andy         300.00 Hou
  3 Erick       1300.00 Hou
  3 Hou           40.00 Hou
  3 Kary         500.00 Hou
  3 Mary         200.00 Hou
  3 Secooler     800.00 Hou

select ID, name, salary, FIRST_VALUE(SALARY) OVER (partition by ID order by salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as highest_sal_name from test order by ID, name;
 ID NAME         SALARY HIGHEST_SAL_NAME
--- ---------- -------- ----------------
  1 Tom          120.00              120
  2 Ellen        240.00               80
  2 Joe           80.00               80
  3 Andy         300.00               40
  3 Erick       1300.00               40
  3 Hou           40.00               40
  3 Kary         500.00               40
  3 Mary         200.00               40
  3 Secooler     800.00               40

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值