oracle 分析函数的使用

1.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 需要排序的字段名)。

 

2.分析函数row_number()、rank()、dense_rank()

   建立测试数据：

 

create table test_20120213(
id number primary key,
name varchar2(30),
value number
);

insert into test_20120213 values (1,'a1',1) ;
insert into test_20120213 values (2,'a1',1) ;
insert into test_20120213 values (3,'a2',2);
insert into test_20120213 values (4,'a3',3);
insert into test_20120213 values (5,'a4',4);
insert into test_20120213 values (6,'a4',5);
insert into test_20120213 values (7,'a5',6);
insert into test_20120213 values (8,'a1',2);

 

 

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是没有重复值的。

例子：

select id,name,row_number() over (partition by name order by value) row_number,
               rank() over (partition by name order by value)  rank,
               DENSE_RANK() over (partition by name order by value) DENSE_RANK
from test_20120213

 

结果：

id   name   row     rank   dense_rank

1 a1 1 1 1

2 a1 2 1 1

8 a1 3 3 2

3 a2 1 1 1

4 a3 1 1 1

5 a4 1 1 1

6 a4 2 2 2

7 a5 1 1 1