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

Memory extender

Beautiful Day..

 
 
 

日志

 
 

分析函数 row_number() over | rank() over | dense_rank() over | lag() over的功能及简单用途  

2012-03-02 17:26:20|  分类: SQL&PL/SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
Connected as apps@DEV
 
--row_number() over (partition by ..order by ..)
--分组排序,相同数值不同号,不跳号
SQL> SELECT row_number() OVER(PARTITION BY deptno ORDER BY sal) rn, emp.*
  2    FROM emp;
 
        RN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         1  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
         2  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
         3  7839 KING       PRESIDENT       1981-11-17    5000.00               10
         1  7369 SMITH      CLERK      7902 1980-12-17     800.00               20
         2  7876 ADAMS      CLERK      7788 1983-1-12     1100.00               20
         3  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
         4  7788 SCOTT      ANALYST    7566 1982-12-9     3000.00               20
         5  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
         1  7900 JAMES      CLERK      7698 1981-12-3      950.00               30
         2  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
         3  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
         4  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
         5  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
         6  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 
14 rows selected

--dense_rank() over (partition by ..order by ..)
分组排序,相同数值同号,不跳号
SQL> SELECT dense_rank() OVER(PARTITION BY deptno ORDER BY sal) rn, emp.*
  2    FROM emp;
 
        RN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         1  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
         2  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
         3  7839 KING       PRESIDENT       1981-11-17    5000.00               10
         1  7369 SMITH      CLERK      7902 1980-12-17     800.00               20
         2  7876 ADAMS      CLERK      7788 1983-1-12     1100.00               20
         3  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
         4  7788 SCOTT      ANALYST    7566 1982-12-9     3000.00               20
         4  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
         1  7900 JAMES      CLERK      7698 1981-12-3      950.00               30
         2  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
         2  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
         3  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
         4  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
         5  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 
14 rows selected

--rank() over (partition by ..order by ..)
分组排序,相同数值同号,跳号
SQL> SELECT RANK() OVER(PARTITION BY deptno ORDER BY sal) rn, emp.*
  2    FROM emp;
 
        RN EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
         1  7934 MILLER     CLERK      7782 1982-1-23     1300.00               10
         2  7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10
         3  7839 KING       PRESIDENT       1981-11-17    5000.00               10
         1  7369 SMITH      CLERK      7902 1980-12-17     800.00               20
         2  7876 ADAMS      CLERK      7788 1983-1-12     1100.00               20
         3  7566 JONES      MANAGER    7839 1981-4-2      2975.00               20
         4  7788 SCOTT      ANALYST    7566 1982-12-9     3000.00               20
         4  7902 FORD       ANALYST    7566 1981-12-3     3000.00               20
         1  7900 JAMES      CLERK      7698 1981-12-3      950.00               30
         2  7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30
         2  7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30
         4  7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30
         5  7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30
         6  7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30
 
14 rows selected

--lag() over (partition by ..order by ..)
创建错位虚拟列可以用来实现连接多条数据字符为一条数据字符串
SQL> SELECT deptno, MAX(sys_connect_by_path(ename, ',')) tree
  2    FROM (SELECT LAG(rn, 1, NULL) OVER(PARTITION BY deptno ORDER BY rn) rn_p,
  3                 t1.*
  4            FROM (SELECT row_number() OVER(PARTITION BY deptno ORDER BY sal) rn,
  5                         emp.*
  6                    FROM emp) t1) t
  7  CONNECT BY PRIOR rn = rn_p
  8   START WITH rn = 1
  9   GROUP BY deptno;
 
DEPTNO TREE
------ --------------------------------------------------------------------------------
    10 ,SMITH,WARD,KING
    20 ,SMITH,WARD,MARTIN,TURNER,FORD
    30 ,SMITH,WARD,MARTIN,TURNER,FORD,BLAKE
 
SQL> 
--LAG() OVER() 这个例子这样写是有问题的,因为造出来的RN在每个DEPTNO下会有很多相同的值,这样connect by下来,就像迪卡尔积一样。。。最后再group by 取最长的值。。明显会不正确
所以,要找一列唯一值来进行LAG,或者ROW_NUMBER里面不要partition by deptno,改成partition by 1,或直接写个rownum,不必写分析函数

SELECT MAX(sys_connect_by_path(ename, ',')), deptno
  FROM (SELECT LAG(ename, 1, NULL) OVER(PARTITION BY deptno ORDER BY sal) ename_p,
               emp.*
          FROM emp) t
CONNECT BY PRIOR t.ename = t.ename_p
 START WITH t.ename_p IS NULL
 GROUP BY deptno;

正确结果:
1 ,MILLER,CLARK,KING 10
2 ,SMITH,ADAMS,JONES,SCOTT,FORD 20
3 ,JAMES,WARD,MARTIN,TURNER,ALLEN,BLAKE 30



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

历史上的今天

评论

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

页脚

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