分析函数案例

一、创建表

 

      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);
/

commit;

 

 

三、找出5,6,7月内销售业绩最好的manager

 

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

 

SQL> select a.manager
  2    from (select manager, sum(sales) A_SALES
  3            from test_rank_sales
  4           group by manager) a,
  5         (select max(TEMP_SALES.TOTAL_SALES) B_SALES
  6            from (select manager, sum(sales) TOTAL_SALES
  7                    from test_rank_sales
  8                   group by manager) TEMP_SALES) b
  9   where a.A_SALES = b.B_SALES
 10  ;
 
MANAGER
--------------------
Collion
 
SQL>

另外一种解决方案：

 

SQL> select manager
  2    from (select manager,
  3                 sum(sales),
  4                 rank() over(order by sum(sales) desc) as FINAL_ORDER
  5            from test_rank_sales
  6           group by manager)
  7   where FINAL_ORDER = 1
  8  ;
 
MANAGER
--------------------
Collion

 

注：rank()根据order by排序结果，计算组间相对位置，有跳号，它是一个数据排序的函数。

SQL>   select manager,
  2                 sum(sales) ,
  3                rank() over( order by sum(sales) desc) as FINAL_ORDER
  4            from test_rank_sales
  5           group by manager;
 
MANAGER              SUM(SALES) FINAL_ORDER
-------------------- ---------- -----------
Collion                 2000000           1
Miles                   1980000           2
JT                      1900000           3
Kevin                   1900000           3
 
SQL>