注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

分析函数案例1——排名次  

2014-04-07 18:34:18|  分类: sql与pl/sql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

--源于梁敬彬的sql优化课

drop table emp purge;

CREATE TABLE emp
(
  emp_id    NUMBER(6),
  ename  VARCHAR2(45),
  dept_id   NUMBER(4),
  hire_date DATE,
  sal    NUMBER(8,2)
);

--创建emp数据
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (101, 'Tom',    20,  TO_DATE('21-09-1989', 'DD-MM-YYYY'), 2000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (102, 'Mike',   20,  TO_DATE('13-01-1993', 'DD-MM-YYYY'), 8000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (120, 'John',   50,  TO_DATE('18-07-1996', 'DD-MM-YYYY'), 1000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (121, 'Joy',    50,  TO_DATE('10-04-1997', 'DD-MM-YYYY'), 1000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (122, 'Rich',   50,  TO_DATE('01-05-1995', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (123, 'Kate',   50,  TO_DATE('10-10-1997', 'DD-MM-YYYY'), 4000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (124, 'Jess',   50,  TO_DATE('16-11-1999', 'DD-MM-YYYY'), 7000);
INSERT INTO emp (emp_id, ename, dept_id, hire_date, sal) VALUES (100, 'Stev',   10,  TO_DATE('01-01-1990', 'DD-MM-YYYY'), 7000);
COMMIT;

 

set linesize 2000
set pagesize 2000
col emp_id format 999
col dept_id format 99
col sal format 9999
col ename format a5
col hire_date FORMAT DATE


SELECT * from emp;

EMP_ID ENAME DEPT_ID HIRE_DATE        SAL
------ ----- ------- -------------- -----
   101 Tom        20 21-9月 -89      2000
   102 Mike       20 13-1月 -93      8000
   120 John       50 18-7月 -96      1000
   121 Joy        50 10-4月 -97      1000
   122 Rich       50 01-5月 -95      4000
   123 Kate       50 10-10月-97      4000
   124 Jess       50 16-11月-99      7000
   100 Stev       10 01-1月 -90      7000
  
 

-----案例1之取各组的最高或者最低收入者
 
--找各组收入最低的
SELECT emp.*, row_number() OVER(PARTITION BY dept_id ORDER BY sal) AS RN  FROM emp;

EMP_ID ENAME DEPT_ID HIRE_DATE         SAL         RN
------ ----- ------- --------------- ----- ----------
   100 Stev       10 01-JAN-90        7000          1
   101 Tom        20 21-SEP-89        2000          1
   102 Mike       20 13-JAN-93        8000          2
   120 John       50 18-JUL-96        1000          1
   122 RICH       50 01-MAY-95        3000          2
   121 Joy        50 10-APR-97        4000          3
   123 Kate       50 10-OCT-97        5000          4
   124 Jess       50 16-NOV-99        6000          5

 
SELECT  emp_id, ename, dept_id, hire_date,sal
  FROM (SELECT emp.*, row_number() OVER(PARTITION BY dept_id ORDER BY sal) AS N  FROM emp)
 WHERE N = 1;
 
EMP_ID ENAME DEPT_ID HIRE_DATE         SAL
------ ----- ------- --------------- -----
   100 Stev       10 01-JAN-90        7000
   101 Tom        20 21-SEP-89        2000
   120 John       50 18-JUL-96        1000

 

--找各组收入最高的
SELECT emp.*, row_number() OVER(PARTITION BY dept_id ORDER BY sal desc) AS RN  FROM emp;

EMP_ID ENAME DEPT_ID HIRE_DATE         SAL         RN
------ ----- ------- --------------- ----- ----------
   100 Stev       10 01-JAN-90        7000          1
   102 Mike       20 13-JAN-93        8000          1
   101 Tom        20 21-SEP-89        2000          2
   124 Jess       50 16-NOV-99        6000          1
   123 Kate       50 10-OCT-97        5000          2
   121 Joy        50 10-APR-97        4000          3
   122 RICH       50 01-MAY-95        3000          4
   120 John       50 18-JUL-96        1000          5

8 rows selected.


SELECT  emp_id, ename, dept_id, hire_date,sal
  FROM (SELECT emp.*, row_number() OVER(PARTITION BY dept_id ORDER BY sal desc) AS RN  FROM emp)
 WHERE RN = 1;

EMP_ID ENAME DEPT_ID HIRE_DATE        SAL 
------ ----- ------- -------------- ----- -
   100 Stev       10 01-1月 -90      7000 
   102 Mike       20 13-1月 -93      8000 
   124 Jess       50 16-11月-99      7000   
   
 
 
---如果要考虑并列,就须要用下面的,就要用RANK或DENSE_RANK(以下例子顺便 开启set autotrace on 来看看分析函数和非分析函数写法的性能差异)
 
SET autotrace ON
SET linesize 1000
SET pagesize 2000
 
--找各组收入最低的
--用分析函数实现
SELECT  emp_id, ename, dept_id, hire_date,sal
  FROM (SELECT emp.*,
               dense_rank() OVER(PARTITION BY dept_id ORDER BY sal ) AS  N
          FROM emp)
 WHERE N = 1;

EMP_ID ENAME DEPT_ID HIRE_DATE        SAL
------ ----- ------- -------------- -----
   100 Stev       10 01-1月 -90      7000
   101 Tom        20 21-9月 -89      2000
   120 John       50 18-7月 -96      1000
   121 Joy        50 10-4月 -97      1000
  


分析函数写法的执行计划
---------------------------------------------------------------------------------
Plan hash value: 3291446077
---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     8 |   680 |     4  (25)| 00:00:01 |
|*  1 |  VIEW                    |      |     8 |   680 |     4  (25)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |     8 |   576 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL     | EMP  |     8 |   576 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
统计信息
----------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        786  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          4  rows processed
         
         
---这用普通语句来实现,如下:
WITH t as
 (SELECT dept_id, min(sal) as min_sal FROM emp GROUP BY dept_id)
select emp.emp_id, emp.ename, emp.dept_id, emp.hire_date,emp.sal
  from emp, t
 where emp.dept_id = t.dept_id
   and emp.sal = t.min_sal;

EMP_ID ENAME DEPT_ID HIRE_DATE        SAL
------ ----- ------- -------------- -----
   101 Tom        20 21-9月 -89      2000
   121 Joy        50 10-4月 -97      1000
   120 John       50 18-7月 -96      1000
   100 Stev       10 01-1月 -90      7000
  
普通SQL写法的执行计划
-----------------------------------------------------------------------------
Plan hash value: 2230095667
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    98 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    98 |     8  (25)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | EMP  |     8 |   576 |     3   (0)| 00:00:01 |
|   3 |   VIEW               |      |     8 |   208 |     4  (25)| 00:00:01 |
|   4 |    HASH GROUP BY     |      |     8 |   208 |     4  (25)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| EMP  |     8 |   208 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------
统计信息
-------------------------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        786  bytes sent via SQL*Net to client
        415  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed       
 


观察一下分析函数写法和普通写法的性能差异
 
         
-----案例2之取各组的最高收入头几名者(这个就略过不说了,太简单了,比如头3名,就是上述的where n=1改为n<=3)

--当然,案例1中的取最高改成直接取第几名也可以,直接N=几就好了。

  评论这张
 
阅读(122)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018