字面意思,获取首/尾值
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;
2、例如:在TEST表中添加一列,标识每一个数据分区中薪水最高薪水值。
first_value(salary) 可根据列名调整,比如first_value(name),为高工资的人名
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; /*或者*/ select id, name, salary, first_value(salary) over(partition by id order by salary) as highest_sal_name from test order by id, name;
2、例如:在TEST表中添加一列,标识每一个数据分区中薪水最底薪水值。
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;
注意:last_value如果省略 rows between unbounded preceding and unbounded following 这段话,查询结果有出入。
可以这样去理解:last_value()默认统计范围是:rows between unbounded preceding and current row
select id, name, salary, last_value(salary) over(partition by id order by salary) as highest_sal_name from test order by id, name; /*等同于*/ select id, name, salary, last_value(salary) over(partition by id order by salary rows between unbounded preceding and current row) as highest_sal_name from test order by id, name;
如果对UNBOUNDED PRECEDING和UNBOUNDED FOLLOWING不熟悉,请参考Oracle官方文档“windowing_clause”http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i97640