Oracle Top-N分析法

TOP-N分析法

TOP-N分析法就是通过TOP-N算法从研究对象中得到所需的N个数据，并从排序列表中选取最大或最小的N个数据，这就是一个TOP-N算法。

ROWNUM伪列（虚拟出来的列）

Oracle中没有像MySql中limit函数以及SQLServer中的top关键字，但是它有ROWNUM伪列，数据插入的顺序决定了查询数据的顺序，也决定了ROWNUM的顺序。

• Oracle数据库中特有的，其它数据库中没有这种机制。
• rownum是行号，行号从1开始，以1递增。
• rownum只在DQL语句当中存在
• rownum是专门为查询结果集生成的行号
• 查询结果集出来之后，再为查询结果集动态生成行号。
• rownum不能以任何表的名称作为前缀

ROWID伪列（物理存在的）

ROWID下数据的含义是数据库那一行数据存储在哪个磁道、哪个扇区、哪个空间，基于64位编码的18个字符，数据对象编号(6个字符)、文件编号(3个字符)、块编号(6个字符)、行编号(3个字符)。因为是物理地址，所以数据在计算机中是唯一的，即使有重复的数据。使用ROWID可以获取修改数据的权限

1、 非排序查询TOP-N分析

//正确演示
SELECT ROWNUM,ID,NAME
FROM STUDENT
WHERE ROWNUM<=3

//错误演示
SELECT ROWNUM,ID,NAME
FROM STUDENT
WHERE ROWNUM >=2 AND  ROWNUM <=4;
/*错误地方：
	 ROWNUM >=2 AND  ROWNUM <=4 查询没有结果
*/

//子查询错误演示
SELECT *
FROM (SELECT ROWNUM ,ID,NAME FROM STUDENT)
WHERE ROWNUM >=2 AND ROWNUM <=4;
/*错误地方：
	 ROWNUM >=2 AND  ROWNUM <=4 ROWNUM 是主查询虚表的伪列（重名）
*/

//子查询正确演示
SELECT *
FROM (SELECT ROWNUM AS NO,ID,NAME FROM STUDENT)
WHERE NO >=2 AND NO <=4;

2、 排序查询TOP-N分析

1. ROWNUM只适用于<=N的情况

当生成结果集时，Oracle首先会产生一条ROWNUM为1的记录(N是自然数)：

• <=N、=1：
  ROWNUM为1的记录1<=N，ROWNUM判断是true,保留数据；继续产生第二条数据，标识ROWNUM为2，如果2<=N，保留数据；继续产生数据，ROWNUM递增，当ROWNUM的序号不满足<=N时，ROWNUM判断是false，删除数据，所有临界点后面的ROWNUM判断都将不成立。最后将保留的数据输出。
• >N(N>1)、>=N(N>1)\BETWEEN…AND、=N(N>1)：
  ROWNUM为1的记录不符合条件，ROWNUM判断是false,记录被过滤掉(删掉)；那么同样会继续产生第二条数据，同样标识ROWNUM为1，该条记录继续被过滤掉(删掉)，后续生成的ROWNUM依然为1，因此上述符号不会有任何查询结果。

2、ROWNUM什么时候使用>/>=/=/between…and

• N是小于1的自然数
  >0、>=0或1、 between 0或1 and N：可以获取表中所有值
  =1：第一行数据
  (是不是发现只要包含第一条数据就可以查出数据)
• 使用子查询（行内视图）

//取ID 2到4名
//错误演示
SELECT ROWNUM AS NO,ID,NAME FROM STUDENT
WHERE NO >=2 AND NO <=4
ORDER BY ID;
/*错误原因：
	先查询结果再排序，结果是未排序的 2到4名。
*/

//正确演示
SELECT *
FROM (SELECT ROWNUM AS NO,ID,NAME FROM STUDENT ORDER BY ID)
WHERE NO >=2 AND NO <=4;

3、什么是行内视图

• 行内视图是sql语句中带有别名的子查询（主查询from子句中的子查询）
• 行内视图不是数据库的对象，不需要去创建，也不会被数据库保存