分析函数——rank()、dense_rank()与row_number()

最新推荐文章于 2022-03-28 15:19:33 发布

cryangel8023

最新推荐文章于 2022-03-28 15:19:33 发布

阅读量128

点赞数

在itpub上经常会看到这样的貌似的问题......

问题的提出(yuanfang2008)

在ORACLE如何用SQL实现最接近某一个值且大于等于这个值的一条记录.

例如有如下记录:
COL1 COL2 COL3
A 1 2007/1/29 上午 06:36:43
A 2 2007/1/30 下午 04:06:39
B 3 2007/1/29上午04:13:55
B 4 2007/1/28下午11:34:41
C 5 2007/1/30下午04:06:39
C 6 2007/1/29上午05:40:32
D 7 2007/1/29上午05:22:17
D 8 2007/1/28下午11:34:43
A 9 2007/1/30下午03:59:38

要求以COL1分组,取出每一分组内,COL3最接近于给出值且大于等于给出值的相应的COL2的值.

例如:当我给出时间为2007/1/30 下午 04:00:39时,希望得到:

A 2 2007/1/30 下午 04:06:39
C 5 2007/1/30下午04:06:39

我在第一次解答的时候使用了first_value，那时对first_value和last_value还没有理解透。不过fangjinde使用了下面的方法解决了，我在这收集下，做为这个类型问题的解决方案。

测试数据如下：

create table test_10 (col1 varchar2(10),col2 varchar2(10),col3 date);
insert into test_10 values ('A','1',to_date('2007/1/29 上午 06:36:43','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('A','2',to_date('2007/1/30 下午 04:06:39','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('B','3',to_date('2007/1/29上午04:13:55','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('B','4',to_date('2007/1/28下午11:34:41','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('C','5',to_date('2007/1/30下午04:06:39','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('C','6',to_date('2007/1/29上午05:40:32','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('D','7',to_date('2007/1/29上午05:22:17','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('D','8',to_date('2007/1/28下午11:34:43','yyyy-mm-dd am hh:mi:ss'));
insert into test_10 values ('A','9',to_date('2007/1/30下午03:59:38','yyyy-mm-dd am hh:mi:ss'));
commit;

Select col1,col2,to_char(col3,'yyyy-mm-dd am hh:mi:ss') col3 From test_10 Order By col1,col3

1 A 1 2007-01-29 上午 06:36:43
2 A 9 2007-01-30 下午 03:59:38
3 A 11 2007-01-30 下午 04:06:39
4 A 2 2007-01-30 下午 04:06:39
5 A 10 2007-01-30 下午 04:09:38
6 B 4 2007-01-28 下午 11:34:41
7 B 3 2007-01-29 上午 04:13:55
8 C 6 2007-01-29 上午 05:40:32
9 C 5 2007-01-30 下午 04:06:39
10 D 8 2007-01-28 下午 11:34:43
11 D 7 2007-01-29 上午 05:22:17

使用fangjinde的方法如下：

SQL> select col1, col2,col3 from (
2 select col1,col2,to_char(col3,'yyyy-mm-dd hh24:mi:ss') col3,
3 rank() over(partition by col1 order by col3) rn
4 from test_10
5 where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss')
6 ) where rn = 1
7 /

COL1 COL2 COL3
---------- ---------- -------------------
A 2 2007-01-30 16:06:39
C 5 2007-01-30 16:06:39

这样没有问题，可以如果我在A组内存在2条col3一样的记录会是什么样呢？

insert into test_10 values ('A','11',to_date('2007/1/30 下午 04:06:39','yyyy-mm-dd am hh:mi:ss'));

COL1 COL2 COL3
---------- ---------- -------------------
A 11 2007-01-30 16:06:39
A 2 2007-01-30 16:06:39
C 5 2007-01-30 16:06:39

主要原因是因为使用rank()或dense_rank()函数，对于相同的记录会生成一样的序列，而2个函数的唯一区别就是rank()是跳号的。

例子如下：

select col1,col2,to_char(col3,'yyyy-mm-dd hh24:mi:ss') col3,
rank() over(partition by col1 order by col3) rn
from test_10
where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss');

1 A 11 2007-01-30 16:06:39 1
2 A 2 2007-01-30 16:06:39 1
3 A 10 2007-01-30 16:09:38 3 --注意
4 C 5 2007-01-30 16:06:39 1

select col1,col2,to_char(col3,'yyyy-mm-dd hh24:mi:ss') col3,
dense_rank() over(partition by col1 order by col3) rn
from test_10
where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss');

1 A 11 2007-01-30 16:06:39 1
2 A 2 2007-01-30 16:06:39 1
3 A 10 2007-01-30 16:09:38 2 --注意
4 C 5 2007-01-30 16:06:39 1

那么想这样的不是排列名次的完全可以使用row_bumber()来替换了。

select col1,col2,to_char(col3,'yyyy-mm-dd hh24:mi:ss') col3,
row_number() over(partition by col1 order by col3) rn
from test_10
where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss');

1 A 11 2007-01-30 16:06:39 1
2 A 2 2007-01-30 16:06:39 2
3 A 10 2007-01-30 16:09:38 3 --注意
4 C 5 2007-01-30 16:06:39 1

这样就清楚了，上面的例子如果要使会存在A组内2条日期一样的纪录，那么就应该改为使用row_number()来处理了。

select col1, col2,col3 from (
select col1,col2,to_char(col3,'yyyy-mm-dd hh24:mi:ss') col3,
row_number() over(partition by col1 order by col3) rn
from test_10
where col3 >= to_date('2007/1/30 下午 04:00:39','yyyy-mm-dd am hh:mi:ss')
) where rn = 1

1 A 11 2007-01-30 16:06:39
2 C 5 2007-01-30 16:06:39

这样可以取出第一条数据，那么如何保留第一条数据而删除其他的数据呢？通过row_number()可以实现。

delete test where rowid in (select rowid from (
select rowid, row_number() over(partition by id order by price) rn from test
) where rn > 1 );

希望对大家能有帮助，如果有不对的请大家指正！

[@more@]

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

转载于:http://blog.itpub.net/7659204/viewspace-897091/