Oracle-Top-N分析

简介: Oracle-Top-N分析

Top-N,根据某一规则进行排序,然后取其前N行数据。

rownum:伪列,数据表中本身没有这样的列,是oracle数据库为每个数据表加上的列,可以标识别行号,默认情况下,rownum按主索引来排序,若没有主索引则自然排序。


对oracle进行分页必须使用rownum:

select employee_id,last_name,salary
from (select rownum rn,employee_id,last_name,salary 
from employees)e
where e.rn <=pageNo*pageSize and e.rn>(pageNo-1)*pageSize


(1)未进行规则排序时,表数据显示

select rownum, employee_id ,last_name,salary
from employees 


20161015175241105.jpg

(2)根据salary进行降序排列

select rownum, employee_id ,last_name,salary
from employees order by salary desc;

image.jpeg

可以发现,rownum (伪列)与employee_id存在一一对应关系。


(3)现在取salary排名前二十的数据

[由上图可知,前二十的salary在9600以上]


那么这样写对不对?

select rownum rn, employee_id ,last_name,salary
from employees 
where rownum <= 20
order by salary desc


20161015180119918.jpg


很显然,这样的结果是错的!!这里只是选取了表中rownum 前20的数据,然后根据salary 进行了降序排列。

但凡一个表,就存在rownum 伪列。那么我们可以先进行salary排序,然后以此为基表,再进行查找。代码如下:

select rownum rn2,rn1, employee_id,last_name,salary 
from (
select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc
)
--rn1 employees 表的伪列;
--rn2 新表的伪列;

20161015180745546.jpg

此时,rn1、rn2分别与employee_id有对应关系,我们可根据rn2的值取数据。

select rownum rn2,rn1, employee_id,last_name,salary 
from (
select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc
)
where rownum <= 20
--where rn2 <= 20 是不对的,where中不能使用外层rownum列的别名
--where rn1 <= 20 写法可以,基表中存在 rn1列



20161015182024300.jpg


此时取的为salary前20的数据。

有个有意思的现象,如果where rn1<= 20 呢?

select rownum rn2,rn1, employee_id,last_name,salary 
from (
select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc
)
where rn1 <= 20



20161015182149739.jpg

此时rn2 与employee_id 对应的关系与上图截然不同。


(4) 取10-20之间的数据

可能会这样想,修改一下 where就可以了。

select rownum rn2,rn1, employee_id,last_name,salary 
from (
select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc
)
where rownum <= 20 and rownum >10


20161015182415349.jpg


不好意思,无数据。!!!

【rownum 只可使用 < <=,不可使用> >=】


解决思路:将此时查出的数据作为基表,再进行查旬,将伪列 rn2 作为新表中的实列,即可进行 < 、<=、>、>=操作。

select rn2,employee_id,last_name,salary
from(
select rownum rn2,rn1, employee_id,last_name,salary 
from (select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc)
)
where rn2 <= 20 and rn2 >10
--注意此时的 rn2 ,为中间表的rownum。

20161015183000197.jpg

此时数据即为(10,20】


对比三个查询表的 rownum:

select rownum rn3, rn2,rn1,employee_id,last_name,salary
from(
select rownum rn2,rn1, employee_id,last_name,salary 
from (select rownum rn1, employee_id ,last_name,salary
from employees 
order by salary desc)
)
where rn2 <= 20 and rn2 >10



20161015183229593.jpg




目录
相关文章
|
2月前
|
Oracle NoSQL 关系型数据库
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
主流数据库对比:MySQL、PostgreSQL、Oracle和Redis的优缺点分析
195 2
|
6月前
|
SQL Oracle 前端开发
Oracle效率分析,Github标星25K+超火的前端实战项目
Oracle效率分析,Github标星25K+超火的前端实战项目
|
6月前
|
Oracle 关系型数据库
oracle基本笔记整理及案例分析2
oracle基本笔记整理及案例分析2
|
6月前
|
Oracle 关系型数据库
oracle基本笔记整理及案例分析1
oracle基本笔记整理及案例分析1
|
Oracle 关系型数据库 Java
分享一个 Oracle RAC 模式下客户端建立JDBC初始连接时因ONS造成应用启动时卡顿30秒问题的排查分析案例
分享一个 Oracle RAC 模式下客户端建立JDBC初始连接时因ONS造成应用启动时卡顿30秒问题的排查分析案例
|
存储 Oracle 算法
数据库数据恢复-ORACLE数据库常见故障的数据恢复可能性分析
ORACLE数据库常见故障: 1、ORACLE数据库无法启动或无法正常工作。 2、ORACLE数据库ASM存储破坏。 3、ORACLE数据库数据文件丢失。 4、ORACLE数据库数据文件部分损坏。 5、ORACLE数据库DUMP文件损坏。
|
存储 Oracle 关系型数据库
Oracle优化07-分析及动态采样-DBMS_STATS 包
Oracle优化07-分析及动态采样-DBMS_STATS 包
130 0
Oracle优化07-分析及动态采样-DBMS_STATS 包
|
Oracle 关系型数据库 索引
Toad Oracle Parttion表分析
当一个数据表的数据达到几十亿笔的时候,对整个表做表分析代价较大。
99 0
|
Oracle 前端开发 关系型数据库
使用隐含参数_disable_logging分析oracle写redo logfile的性能
oracle有一个隐含参数_disable_logging可以禁止日志的生成,这个参数当然不能在生产库使用,但我们可以将其因为与测试,例如,如果我们怀疑数据库写redo logfile存在性能问题,我们可以将这个参数设置为true,禁止写日志,看看oracle的性能提高了多少。
|
SQL 存储 Oracle
Oracle优化07-分析及动态采样-动态采样
Oracle优化07-分析及动态采样-动态采样
149 0