oracle分析函数,keep and over解说

今天又学到了一个新的分析函数了，over（）函数早就接触过，但keep和over结合用还是第一次见，详情见下面内容了，呵~~

先看下面的测试例子:

--创建测试表

create table test as
select 1 ID, 111 mc, 1 sl from dual union
select 1, 222, 1 from dual union
select 1, 333, 2 from dual union
select 1, 555, 3 from dual union
select 1, 666, 3 from dual union
select 2 ,111 ,1 from dual union
select 2 ,222 ,1 from dual union
select 2 ,333, 2 from dual union
select 2 ,555, 2 from dual;

SQL> select * from test;

ID MC SL
---------- ---------- ----------
1 111 1
1 222 1
1 333 2
1 555 3
1 666 3
2 111 1
2 222 1
2 333 2
2 555 2

已选择9行。

SQL> select id,mc,sl,min(mc) keep(dense_rank first order by sl) over(partition by id) min_mc
2 from test;

ID MC SL MIN_MC
---------- ---------- ---------- ----------
1 111 1 111
1 222 1 111
1 333 2 111
1 555 3 111
1 666 3 111
2 111 1 111
2 222 1 111
2 333 2 111
2 555 2 111

已选择9行。

SQL> select id,mc,sl,min(mc) keep(dense_rank last order by sl) over(partition by id) min_mc
2 from test;

ID MC SL MIN_MC
---------- ---------- ---------- ----------
1 111 1 555
1 222 1 555
1 333 2 555
1 555 3 555
1 666 3 555
2 111 1 333
2 222 1 333
2 333 2 333
2 555 2 333

已选择9行。

SQL> select id,mc,sl,min(mc) keep(dense_rank first order by sl) over(partition by id) min_first_mc
2 ,max(mc) keep(dense_rank first order by sl) over(partition by id) max_first_mc,
3 min(mc) keep(dense_rank last order by sl) over(partition by id) min_last_mc,
4 max(mc) keep(dense_rank last order by sl) over(partition by id) max_last_mc
5 from test;

ID MC SL MIN_FIRST_MC MAX_FIRST_MC MIN_LAST_MC
---------- ---------- ---------- ------------ ------------ -----------
MAX_LAST_MC
-----------
1 111 1 111 222 555
666

1 222 1 111 222 555
666

1 333 2 111 222 555
666

ID MC SL MIN_FIRST_MC MAX_FIRST_MC MIN_LAST_MC
---------- ---------- ---------- ------------ ------------ -----------
MAX_LAST_MC
-----------
1 555 3 111 222 555
666

1 666 3 111 222 555
666

2 111 1 111 222 333
555

ID MC SL MIN_FIRST_MC MAX_FIRST_MC MIN_LAST_MC
---------- ---------- ---------- ------------ ------------ -----------
MAX_LAST_MC
-----------
2 222 1 111 222 333
555

2 333 2 111 222 333
555

2 555 2 111 222 333
555

已选择9行。

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

转载于:http://blog.itpub.net/219982/viewspace-590897/