oracle分析函数[rank,dense_rank and row_number]

最新推荐文章于 2024-10-25 15:29:33 发布

原创最新推荐文章于 2024-10-25 15:29:33 发布 · 122 阅读

0 ·

CC 4.0 BY-SA版权

本文详细介绍了Oracle数据库中rank(), dense_rank() 和 row_number() 函数的使用方法，并通过实例展示了如何利用这些函数进行数据排序和分析。特别是针对NULL值处理的不同选项进行了探讨。

版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明
 http://solecjj.blogbus.com/logs/11842680.html

rank(),dense_rank(),row_number() 是oracle 提供的统计函数,看看下面的例子就知道他们的作用了.

创建测试表并准备数据
CREATE TABLE test_rank_sales (
location_name VARCHAR2(20),
month_flag number,
sales NUMBER,
manager VARCHAR2(20),
create_stamp DATE);
/

insert into test_rank_sales values('SH',5,100000,'Kevin',sysdate);
/
insert into test_rank_sales values('SH',6,900000,'Kevin',sysdate);
/
insert into test_rank_sales values('SH',7,900000,'Kevin',sysdate);
/
insert into test_rank_sales values('HZ',5,100000,'JT',sysdate);
/
insert into test_rank_sales values('HZ',6,910000,'JT',sysdate);
/
insert into test_rank_sales values('HZ',7,890000,'JT',sysdate);
/
insert into test_rank_sales values('GZ',5,100000,'Miles',sysdate);
/
insert into test_rank_sales values('GZ',6,990000,'Miles',sysdate);
/
insert into test_rank_sales values('GZ',7,890000,'Miles',sysdate);
/
insert into test_rank_sales values('BJ',5,100000,'Collion',sysdate);
/
insert into test_rank_sales values('BJ',6,910000,'Collion',sysdate);
/
insert into test_rank_sales values('BJ',7,990000,'Collion',sysdate);
/

--1.No null value.
-- request 1 : 找出5,6,7月内销售业绩最好的manager

-- request 1 -- solution 1: complex data set

select a.manager
from (select manager, sum(sales) A_SALES
          from test_rank_sales
         group by manager) a,
       (select max(TEMP_SALES.TOTAL_SALES) B_SALES
          from (select manager, sum(sales) TOTAL_SALES
                  from test_rank_sales
                 group by manager) TEMP_SALES) b
where a.A_SALES = b.B_SALES

-- request 1 -- solution 2: rank()
select manager
from (select manager,
               sum(sales),
               rank() over(order by sum(sales) desc) as FINAL_ORDER
          from test_rank_sales
         group by manager)
where FINAL_ORDER = 1

-- request 2 : 找出5,6,7月内销售业绩第二好的manager是谁
-- notes : 第二好的mamager有两个人，SQL提供了三个等级排列函数:rank(),dense_rank()和row_number(),先执行下面的SCRIPT.

select manager,
       sum(sales),
       row_number() over(order by sum(sales) desc NULLS LAST) as ROW_NUMBER_ORDER,
       rank() over(order by sum(sales) desc NULLS LAST) as RANK_ORDER,
       dense_rank() over(order by sum(sales) desc NULLS LAST) as DENSE_RANK_ORDER
from test_rank_sales
group by manager
-- 执行后的结果
-- MANAGER SUM(SALES) ROW_NUMBER_ORDER RANK_ORDER DENSE_RANK_ORDER
-- Collion         2000000          1                                    1                        1
-- JT               1900000           2                                      2                        2
-- Kevin          1900000           3                                      2                         2
-- Miles          1880000           4                                  4                         3

-- request 2 -- solution : 自己想吧，上面的例子已经很清楚了。

--2. Null value.
insert into test_rank_sales values('XZ',5,null,'Tom',sysdate);
/
insert into test_rank_sales values('XZ',6,null,'Tom',sysdate);
/
insert into test_rank_sales values('XZ',7,null,'Tom',sysdate);
/

-- NULL FIRST --solution:

select manager,
       sum(sales),
       row_number() over(order by sum(sales) desc NULLS FIRST) as ROW_NUMBER_ORDER,
       rank() over(order by sum(sales) desc NULLS FIRST) as RANK_ORDER,
       dense_rank() over(order by sum(sales) desc NULLS FIRST) as DENSE_RANK_ORDER
from test_rank_sales
group by manager

-- NULL LAST --solution:
select manager,
       sum(sales),
       row_number() over(order by sum(sales) desc NULLS LAST) as ROW_NUMBER_ORDER,
       rank() over(order by sum(sales) desc NULLS LAST) as RANK_ORDER,
       dense_rank() over(order by sum(sales) desc NULLS LAST) as DENSE_RANK_ORDER
from test_rank_sales
group by manager