Oracle中rownum、rowid、row_number()、rank()、dense_rank()

    http://blog.csdn.net/tomatofly/article/details/5939045
    首先来说一下rownum与rowid含义：

顾名思义rownum就是行数/行号，而rowid就是编码/编号/唯一识别号，所以他是类似“AAAR8gAAEAAAAErAAK”的编号，注意他是没有先后顺序的，也就是说他和数据入库时间没有任何关系，打个比方：他就像磁盘、内存存储数据用的是16进制的地址一样。



他们都是伪列，可以理解成表中的一个列只是他们并不是你创建的。同样是伪列区别是什么呢？



rowid是你录入数据时有数据库自动为这条记录添加的唯一的18位编号是一个物理编号用于找到这条记录（顺便说一句这也是为什么数据优调的时候强调尽量使用rowid的原因），他是不会随着查询而改变的 除非在表发生移动(比如表空间变化，数据导入/导出以后)，才会发生变化。



rownum是根据sql查询后得到的结果自动加上去的，但是他却不受到sql中order by排序的影响，因为他和rowid的顺序一样是系统按照记录插入时的顺序给记录排的号（顺序的、无跳跃）。 但是如果你想让rownum和order by一样的顺序 那么可以使用子查询，形如：select rownum,t.* from (select * from 表空间名 order by 字段名) t  这样的话rownum就是根据该字段进行排序的编号了，为什么会这样呢，本人理解：rownum是根据表记录输出的行号，与筛选语句、排序语句都无关所以当用子查询时等于生成了一个表于是就按照这张表从1开始排序了。 同样，也可以用下面要提得到的分析函数中的row_number() over(order by 需要排序的字段名)。

 

值得一提的是MSSQL是没有rownum和rowid的。

下面说说分析函数row_number()、rank()、dense_rank()



ROW_NUMBER()：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。 row_number()和rownum差不多，功能更强一点（可以在各个分组内从1开时排序），因为row_number()是分析函数而rownum是伪列所以row_number()一定要over而rownum不能over。



RANK()：
Rank函数返回一个唯一的值，除非遇到相同的数据，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。rank()是跳跃排序，有两个第二名时接下来就是第四名（同样是在各个分组内）。





DENSE_RANK()：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据，此时所有相同数据的排名都是一样的。
dense_rank()是连续排序，有两个第二名时仍然跟着第三名。他和row_number的区别在于row_number是没有重复值的。



下面举个例子：



【1】测试环境：
SQL> desc user_order;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
REGION_ID                                          NUMBER(2)
CUSTOMER_ID                                     NUMBER(2)
CUSTOMER_SALES                               NUMBER


【2】测试数据：
SQL> select * from user_order order by customer_sales;

REGION_ID CUSTOMER_ID CUSTOMER_SALES
---------- ----------- --------------
         5           1              151162
        10          29             903383
         6           7              971585
        10          28            986964
         9          21           1020541
         9          22           1036146
         8          16           1068467
         6           8            1141638
         5           3            1161286
         5           5            1169926
         8          19           1174421
         7          12           1182275
         7          11           1190421
         6          10           1196748
         6           9            1208959
        10          30          1216858
         5             2                1224992
           9             24              1224992
           9             23              1224992
           8          18           1253840
         7          15           1255591
         7          13           1310434
        10          27          1322747
         8          20           1413722
         6           6            1788836
        10          26          1808949
         5           4            1878275
         7          14           1929774
         8          17           1944281
         9          25           2232703

30 rows selected.



【3】row_number()、rank()、dense_rank()这三个分析函数的区别实例



SQL> select region_id, customer_id, sum(customer_sales) total,
  2         rank() over(order by sum(customer_sales) desc) rank,
  3         dense_rank() over(order by sum(customer_sales) desc) dense_rank,
  4         row_number() over(order by sum(customer_sales) desc) row_number
  5    from user_order
  6   group by region_id, customer_id;

REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
         
         8          18                1253840         11         11         11
         5           2                 1224992         12         12         12
         9          23                1224992         12         12         13
         9          24                1224992         12         12         14
        10          30               1216858         15           13            15
 

30 rows selected.



请注意上面的绿色高亮部分，这里生动的演示了3种不同的排名策略：

①对于第一条相同的记录，3种函数的排名都是一样的：12

②当出现第二条相同的记录时，Rank和Dense_rank依然给出同样的排名12；而row_number则顺延递增为13，依次类推至第三条相同的记录

③当排名进行到下一条不同的记录时，可以看到Rank函数在12和15之间空出了13,14的排名，因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增

比较上面3种不同的策略，我们在选择的时候就要根据客户的需求来定夺了：

①假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险

②假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录



    http://www.cnblogs.com/lizw/archive/2007/04/26/729004.html

over(partition by..) 的运用

oracle的分析函数over 及开窗函数
一：分析函数over
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是
对于每个组返回多行，而聚合函数对于每个组只返回一行。
下面通过几个例子来说明其应用。                                     
1：统计某商店的营业额。      
     date       sale
     1           20
     2           15
     3           14
     4           18
     5           30
    规则：按天统计：每天都统计前面几天的总额
    得到的结果：
    DATE   SALE       SUM
    ----- -------- ------
    1      20        20           --1天         
    2      15        35           --1天＋2天         
    3      14        49           --1天＋2天＋3天         
    4      18        67            .        
    5      30        97            .
   
2:统计各班成绩第一名的同学信息
    NAME   CLASS S                       
    ----- ----- ----------------------
    fda    1      80                   
    ffd    1      78                   
    dss    1      95                   
    cfe    2      74                   
    gds    2      92                   
    gf     3      99                   
    ddd    3      99                   
    adf    3      45                   
    asdf   3      55                   
    3dd    3      78            
 
    通过： 
    --
    select * from                                                                     
    (                                                                          
    select name,class,s,rank()over(partition by class order by s desc) mm from t2
    )                                                                          
    where mm=1
    --
    得到结果：
    NAME   CLASS S                       MM                                                                                      
    ----- ----- ---------------------- ----------------------
    dss    1      95                      1                    
    gds    2      92                      1                    
    gf     3      99                      1                    
    ddd    3      99                      1        
 
    注意：
    1.在求第一名成绩的时候，不能用row_number()，因为如果同班有两个并列第一，row_number()只返回一个结果        
    2.rank()和dense_rank()的区别是：
      --rank()是跳跃排序，有两个第二名时接下来就是第四名
      --dense_rank()l是连续排序，有两个第二名时仍然跟着第三名
   
   
3.分类统计 (并显示信息)
    A   B   C                    
    -- -- ----------------------
    m   a   2                    
    n   a   3                    
    m   a   2                    
    n   b   2                    
    n   b   1                    
    x   b   3                    
    x   b   2                    
    x   b   4                    
    h   b   3
   select a,c,sum(c)over(partition by a) from t2              
   得到结果：
   A   B   C        SUM(C)OVER(PARTITIONBYA)    
   -- -- ------- ------------------------
   h   b   3        3                      
   m   a   2        4                      
   m   a   2        4                      
   n   a   3        6                      
   n   b   2        6                      
   n   b   1        6                      
   x   b   3        9                      
   x   b   2        9                      
   x   b   4        9                      

   如果用sum，group by 则只能得到
   A   SUM(C)                          
   -- ----------------------
   h   3                    
   m   4                    
   n   6                    
   x   9                    
   无法得到B列值     

＝＝＝＝＝
select * from test

数据:
A B C
1 1 1
1 2 2
1 3 3
2 2 5
3 4 6


---将B栏位值相同的对应的C 栏位值加总
select a,b,c, SUM(C) OVER (PARTITION BY B) C_Sum
from test

A B C C_SUM
1 1 1 1
1 2 2 7
2 2 5 7
1 3 3 3
3 4 6 6



---如果不需要已某个栏位的值分割,那就要用 null

eg: 就是将C的栏位值summary 放在每行后面

select a,b,c, SUM(C) OVER (PARTITION BY null) C_Sum
from test

A B C C_SUM
1 1 1 17
1 2 2 17
1 3 3 17
2 2 5 17
3 4 6 17



求个人工资占部门工资的百分比

SQL> select * from salary;

NAME DEPT SAL
---------- ---- -----
a 10 2000
b 10 3000
c 10 5000
d 20 4000

SQL> select name,dept,sal,sal*100/sum(sal) over(partition by dept) percent from salary;

NAME DEPT SAL PERCENT
---------- ---- ----- ----------
a 10 2000 20
b 10 3000 30
c 10 5000 50
d 20 4000 100

二：开窗函数         
      开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
1：   
   over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
   over（partition by deptno）按照部门分区
2：
over（order by salary range between 5 preceding and 5 following）
   每行对应的数据窗口是之前行幅度值不超过5，之后行幅度值不超过5
   例如:对于以下列
     aa
     1
     2
     2
     2
     3
     4
     5
     6
     7
     9
 
   sum(aa)over（order by aa range between 2 preceding and 2 following）
   得出的结果是
            AA                       SUM
            ---------------------- -------------------------------------------------------
            1                       10                                                    
            2                       14                                                    
            2                       14                                                    
            2                       14                                                    
            3                       18                                                    
            4                       18                                                    
            5                       22                                                    
            6                       18                                                              
            7                       22                                                              
            9                       9                                                               
           
   就是说，对于aa=5的一行 ，sum为   5-1<=aa<=5+2 的和
   对于aa=2来说 ，sum=1+2+2+2+3+4=14     ；
   又如 对于aa=9 ，9-1<=aa<=9+2 只有9一个数，所以sum=9    ；
            
3：其它：
     over（order by salary rows between 2 preceding and 4 following）
          每行对应的数据窗口是之前2行，之后4行
4：下面三条语句等效：         
     over（order by salary rows between unbounded preceding and unbounded following）
          每行对应的数据窗口是从第一行到最后一行，等效：
     over（order by salary range between unbounded preceding and unbounded following）
           等效
     over(partition by null)




sum(nid) over(partition by v1 order by nid)(2007-05-16 16:22:48)
  分类：sql语句


SQL> select n1,v1,nid,sum(nid) over(order by nid) as sum
  2  from t1;

        N1 V1                NID        SUM
---------- ---------- ---------- ----------
         1 aa                 61         61
         2 aa                 62        123
         3 aa                 63        186
         4 aa                 64        250

取nid列的累积和，即下面以emp表为例的按部门“连续”求总和

==================================================================

按v1分组取nid的和

SQL> select v1,sum(nid) over (partition by v1 order by v1) as sum_nid from t1;

V1            SUM_NID
---------- ----------
aa                187
aa                187
aa                187
bb                 83

按v1分组取nid的和，并重复行只显示一行

SQL> select distinct * from (select v1,sum(nid) over (partition by v1) as sum_nid from t1);

V1            SUM_NID
---------- ----------
aa                187
bb                 83

==================================================================

再以emp为例

使用子分区查出各部门薪水连续的总和。注意按部门分区 over(...)条件的不同
sum(sal) over (partition by deptno order by ename) 按部门“连续”求总和
sum(sal) over (partition by deptno) 按部门求总和
sum(sal) over (order by deptno，ename) 不按部门“连续”求总和
sum(sal) over () 不按部门，求所有员工总和，效果等同于sum(sal)。



sql> break on deptno skip 1  -- 为效果更明显，把不同部门的数据隔段显示。

SQL> select deptno,ename,sal,
  2      sum(sal) over (partition by deptno order by ename) 部门连续求和,
  3      sum(sal) over (partition by deptno) 部门总和,
  4      100*round(sal/sum(sal) over (partition by deptno),4) 部门份额,
  5      sum(sal) over () 总和,
  6      sum(sal) over (order by deptno,ename) 连续求和,
  7      100*round(sal/sum(sal) over (),4) 总份额
  8  from emp;

    DEPTNO ENAME             SAL 部门连续求和   部门总和   部门份额       总和   连续求和     总份额

---------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------

        10 CLARK            2450         2450       8750         28      29025       2450       8.44

           KING             5000         7450       8750      57.14      29025       7450      17.23

           MILLER           1300         8750       8750      14.86      29025       8750       4.48


        20 ADAMS            1100         1100      10875      10.11      29025       9850       3.79

           FORD             3000         4100      10875      27.59      29025      12850      10.34

           JONES            2975         7075      10875      27.36      29025      15825      10.25

           SCOTT            3000        10075      10875      27.59      29025      18825      10.34

           SMITH             800        10875      10875       7.36      29025      19625       2.76


        30 ALLEN            1600         1600       9400      17.02      29025      21225       5.51

           BLAKE            2850         4450       9400      30.32      29025      24075       9.82

           JAMES             950         5400       9400      10.11      29025      25025       3.27

           MARTIN           1250         6650       9400       13.3      29025      26275       4.31

           TURNER           1500         8150       9400      15.96      29025      27775       5.17

           WARD             1250         9400       9400       13.3      29025      29025       4.31



已选择14行。



综合的例子，求和规则有按部门分区的，有不分区的例子

SQL> select deptno,ename,sum(sal) over(partition by deptno order by sal) as sum_sal,
  2         sum(sal) over(order by deptno,sal) as sum_dept_sal
  3  from emp;

    DEPTNO ENAME         SUM_SAL SUM_DEPT_SAL
---------- ---------- ---------- ------------
        10 MILLER           1300         1300
           CLARK            3750         3750
           KING             8750         8750

        20 SMITH             800         9550
           ADAMS            1900        10650
           JONES            4875        13625
           SCOTT           10875        19625
           FORD            10875        19625

        30 JAMES             950        20575
           WARD             3450        23075
           MARTIN           3450        23075
           TURNER           4950        24575
           ALLEN            6550        26175
           BLAKE            9400        29025


已选择14行。

来一个逆序的，即部门从大到小排列，部门里各员工的薪水从高到低排列，累计和的规则不变。

SQL> select deptno,ename,sal,
  2         sum(sal) over (partition by deptno order by deptno desc,sal desc) as sum_sal_order,
  3         sum(sal) over (order by deptno desc,sal desc) as sum
  4  from emp;

    DEPTNO ENAME             SAL SUM_SAL_ORDER        SUM
---------- ---------- ---------- ------------- ----------
        30 BLAKE            2850          2850       2850
           ALLEN            1600          4450       4450
           TURNER           1500          5950       5950
           WARD             1250          8450       8450
           MARTIN           1250          8450       8450
           JAMES             950          9400       9400

        20 SCOTT            3000          6000      15400
           FORD             3000          6000      15400
           JONES            2975          8975      18375
           ADAMS            1100         10075      19475
           SMITH             800         10875      20275

        10 KING             5000          5000      25275
           CLARK            2450          7450      27725
           MILLER           1300          8750      29025


已选择14行。



Oracle高级查询之OVER (PARTITION BY ..)

为了方便大家学习和测试，所有的例子都是在Oracle自带用户Scott下建立的。

注：标题中的红色order by是说明在使用该方法的时候必须要带上order by。

一、rank()/dense_rank() over(partition by ...order by ...)
现在客户有这样一个需求，查询每个部门工资最高的雇员的信息，相信有一定oracle应用知识的同学都能写出下面的SQL语句：
[sql] view plaincopy?

    select e.ename, e.job, e.sal, e.deptno 
      from scott.emp e, 
           (select e.deptno, max(e.sal) sal from scott.emp e group by e.deptno) me 
     where e.deptno = me.deptno 
       and e.sal = me.sal; 

在满足客户需求的同时，大家应该习惯性的思考一下是否还有别的方法。这个是肯定的，就是使用本小节标题中rank() over(partition by...)或dense_rank() over(partition by...)语法，SQL分别如下：
[sql] view plaincopy?

    select e.ename, e.job, e.sal, e.deptno 
      from (select e.ename, 
                   e.job, 
                   e.sal, 
                   e.deptno, 
                   rank() over(partition by e.deptno order by e.sal desc) rank 
              from scott.emp e) e 
     where e.rank = 1; 

[sql] view plaincopy?

    select e.ename, e.job, e.sal, e.deptno 
      from (select e.ename, 
                   e.job, 
                   e.sal, 
                   e.deptno, 
                   dense_rank() over(partition by e.deptno order by e.sal desc) rank 
              from scott.emp e) e 
     where e.rank = 1; 

为什么会得出跟上面的语句一样的结果呢？这里补充讲解一下rank()/dense_rank() over(partition by e.deptno order by e.sal desc)语法。
over:  在什么条件之上。
partition by e.deptno:  按部门编号划分（分区）。
order by e.sal desc:  按工资从高到低排序（使用rank()/dense_rank() 时，必须要带order by否则非法）
rank()/dense_rank():  分级
整个语句的意思就是：在按部门划分的基础上，按工资从高到低对雇员进行分级，“级别”由从小到大的数字表示（最小值一定为1）。

那么rank()和dense_rank()有什么区别呢？
rank():  跳跃排序，如果有两个第一级时，接下来就是第三级。
dense_rank():  连续排序，如果有两个第一级时，接下来仍然是第二级。

小作业：查询部门最低工资的雇员信息。

二、min()/max() over(partition by ...)

现在我们已经查询得到了部门最高/最低工资，客户需求又来了，查询雇员信息的同时算出雇员工资与部门最高/最低工资的差额。这个还是比较简单，在第一节的groupby语句的基础上进行修改如下：
[sql] view plaincopy?

    select e.ename, 
             e.job, 
             e.sal, 
             e.deptno, 
             e.sal - me.min_sal diff_min_sal, 
             me.max_sal - e.sal diff_max_sal 
        from scott.emp e, 
             (select e.deptno, min(e.sal) min_sal, max(e.sal) max_sal 
                from scott.emp e 
               group by e.deptno) me 
       where e.deptno = me.deptno 
       order by e.deptno, e.sal; 

上面我们用到了min()和max()，前者求最小值，后者求最大值。如果这两个方法配合over(partition by ...)使用会是什么效果呢？大家看看下面的SQL语句：
[sql] view plaincopy?

    select e.ename, 
           e.job, 
           e.sal, 
           e.deptno, 
           nvl(e.sal - min(e.sal) over(partition by e.deptno), 0) diff_min_sal, 
           nvl(max(e.sal) over(partition by e.deptno) - e.sal, 0) diff_max_sal 
      from scott.emp e; 

这两个语句的查询结果是一样的，大家可以看到min()和max()实际上求的还是最小值和最大值，只不过是在partition by分区基础上的。

小作业：如果在本例中加上order by，会得到什么结果呢？

三、lead()/lag() over(partition by ... order by ...)

中国人爱攀比，好面子，闻名世界。客户更是好这一口，在和最高/最低工资比较完之后还觉得不过瘾，这次就提出了一个比较变态的需求，计算个人工资与比自己高一位/低一位工资的差额。这个需求确实让我很是为难，在groupby语句中不知道应该怎么去实现。不过。。。。现在我们有了over(partition by ...)，一切看起来是那么的简单。如下：
[sql] view plaincopy?

    select e.ename, 
           e.job, 
           e.sal, 
           e.deptno, 
           lead(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lead_sal, 
           lag(e.sal, 1, 0) over(partition by e.deptno order by e.sal) lag_sal, 
           nvl(lead(e.sal) over(partition by e.deptno order by e.sal) - e.sal, 
               0) diff_lead_sal, 
           nvl(e.sal - lag(e.sal) over(partition by e.deptno order by e.sal), 0) diff_lag_sal 
      from scott.emp e;  

看了上面的语句后，大家是否也会觉得虚惊一场呢（惊出一身冷汗后突然鸡冻起来，这样容易感冒）？我们还是来讲解一下上面用到的两个新方法吧。
lead(列名,n,m):  当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。
lag(列名,n,m):  当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

下面再列举一些常用的方法在该语法中的应用（注：带order by子句的方法说明在使用该方法的时候必须要带order by）：
[sql] view plaincopy?

    select e.ename, 
           e.job, 
           e.sal, 
           e.deptno, 
           first_value(e.sal) over(partition by e.deptno) first_sal, 
           last_value(e.sal) over(partition by e.deptno) last_sal, 
           sum(e.sal) over(partition by e.deptno) sum_sal, 
           avg(e.sal) over(partition by e.deptno) avg_sal, 
           count(e.sal) over(partition by e.deptno) count_num, 
           row_number() over(partition by e.deptno order by e.sal) row_num 
      from scott.emp e; 


重要提示：大家在读完本片文章之后可能会有点误解，就是OVER (PARTITION BY ..)比GROUP BY更好，实际并非如此，前者不可能替代后者，而且在执行效率上前者也没有后者高，只是前者提供了更多的功能而已，所以希望大家在使用中要根据需求情况进行选择。