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

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

分析函数案例5——高频数获取  

2014-04-07 19:59:40|  分类: 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'), 4000);
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'), 6000);
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


SQL> 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           4000
       122 Rich                50 01-5月 -95           4000
       123 Kate                50 10-10月-97           4000
       124 Jess                50 16-11月-99           6000
       100 Stev                10 01-1月 -90           7000
      
--求出现频率最高的工资水平是多少?
SET autotrace ON
SET linesize 1000
SET pagesize 2000

--分析:
SELECT sal,COUNT(*) repeat_num FROM emp GROUP BY sal;

       SAL REPEAT_NUM
---------- ----------
      1000          1
      4000          3
      2000          1
      8000          1
      6000          1
      7000          1
     
SQL> select sal, rank() over(order by repeat_num desc) from (SELECT sal,COUNT(*) repeat_num FROM emp GROUP BY sal);

  SAL RANK()OVER(ORDERBYREPEAT_NUMDESC)
----- ---------------------------------
 4000                                 1
 7000                                 2
 8000                                 2
 6000                                 2
 1000                                 2
 2000                                 2

6 rows selected.  

     
SELECT sal FROM (
    SELECT sal,RANK() OVER(ORDER BY repeat_num DESC) rank_repeat_num  FROM
   (SELECT sal,COUNT(*) repeat_num FROM emp GROUP BY sal)
  ) 
WHERE rank_repeat_num=1;

  SAL
-----
 4000

用分析函数写法的执行计划
----------------------------------------------------------
Plan hash value: 214435687
---------------------------------------------------------------------------------
| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |     8 |   208 |     5  (40)| 00:00:01 |
|*  1 |  VIEW                    |      |     8 |   208 |     5  (40)| 00:00:01 |
|*  2 |   WINDOW SORT PUSHED RANK|      |     8 |   104 |     5  (40)| 00:00:01 |
|   3 |    HASH GROUP BY         |      |     8 |   104 |     5  (40)| 00:00:01 |
|   4 |     TABLE ACCESS FULL    | EMP  |     8 |   104 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RANK_REPEAT_NUM"=1)
   2 - filter(RANK() OVER ( ORDER BY COUNT(*) DESC )<=1)
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        418  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)
          1  rows processed


---不用分析函数,写法比较麻烦,很繁琐

select sal
  from (SELECT sal, COUNT(*) as repeat_num FROM emp GROUP BY sal) t
 where t.repeat_num =
       (select max(repeat_num)
          from (SELECT sal, COUNT(*) as repeat_num FROM emp GROUP BY sal));

  SAL
-----
 4000
         
用普通写法的执行计划
-----------------------------------------------------------------------------
Plan hash value: 3118377657
------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     8 |   104 |     4  (25)| 00:00:01 |
|*  1 |  FILTER               |      |       |       |            |          |
|   2 |   HASH GROUP BY       |      |     8 |   104 |     4  (25)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | EMP  |     8 |   104 |     3   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE      |      |     1 |    13 |            |          |
|   5 |    VIEW               |      |     8 |   104 |     4  (25)| 00:00:01 |
|   6 |     SORT GROUP BY     |      |     8 |   104 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL| EMP  |     8 |   104 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(COUNT(*)= (SELECT MAX("REPEAT_NUM") FROM  (SELECT "SAL"
              "SAL",COUNT(*) "REPEAT_NUM" FROM "EMP" "EMP" GROUP BY "SAL")
              "from$_subquery$_003"))
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        418  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)
          1  rows processed         
         

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

历史上的今天

在LOFTER的更多文章

评论

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

页脚

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