最近学习了oracle相关内容,其中对于top行分析,以及存储过程,触发器感觉比较有意思,今天与大家一一分享。
1. 什么是topN算法
将大量(比如几十万、甚至上百万)的对象进行排序,然后只需要取出最Top的前N名作为排行榜的数据,这即是一个TopN算法。
由于ORACLE不支持SELECT TOP语句,所以在ORACLE中经常是用ORDER BY跟ROWNUM的组合来实现SELECT TOP N的查询。
2. Rownum
Rownum是oracle中的一个伪列,其目的就是给查询的行标注行号。可以实现查询前n行,中间几行,最后几行的功能(根据业务功能定义行的排序)。
注意:
1)rownum不可以直接在前面加上表名或别名等。
2)rownum和where在同一层查询中,where条件之后使用rownum比较,只能使用<=,<不能使用>,>=,=(使用=,只能是where rownum=1才可以)。否则返回null。如果使用!=或<>,那么只是返回前n-1行,其他按照rownum工作原理推算。
3)当rownum和排序在一个语句中使用的时候,要注意,看看oracle有没有使用索引,如果使用了索引扫描,那么可能按照索引来组织数据,如果没有则是先生成行号,然后order by,则查询出来的结果乱序。可以采用嵌套查询,先在内层排序,在外层查询rownum。
3. Rownum工作原理
1.执行查询操作
2.将第一行的row num置为1
3.将得到的行的row num与条件相比较,如果不匹配,则抛弃行,如果匹配,则返回行
4.oracle获取下一行,然后将rownum增1
5.返回第3步
从这个原理可以知道,select rownum,name from emp where rownum>5;不返回行,因为首先执行查询select name from
emp,将第1行的rownum标为1,然后看where条件,为false,则抛弃行,执行第2行,还是rownum标为1,看where条件还为
false,所以永远是false,rownum不改变,所有的行都被抛弃,所以没有结果。
基于以上的问题,那么使用rownum的常用查询结构有:
4. TOPN简单应用
1). 获取前n行
选择员工,显示姓名,行号,按照姓名排序
select rownum,last_name from emp order by last_name;--不正确
如果要排序,那么需要在内层查询中排序好,然后外层查询引入rownum(两重嵌套查询)
select rownum,last_name from
(select last_name from s_emp order by last_name);
选择前10行,可以直接在两重嵌套的外层查询中引入rownum,并且where rownum<=
select rownum,last_name from
(select last_name from s_emp order by last_name)
where rownum<=10;
2).获取中间行数据
选择员工姓名,显示行号,按照姓名排序,显示第5条记录到第10条记录
select rn,last_name from --最外层用where条件判断
(select rownum rn,last_name --中间层查询加上行号
from (
select last_name from s_emp order by last_name --底层查询排序
)
)
where rn between 5 and 10;
也可以在中间层查询中加入最大的行号判断,<=,最外层用>=,上面的查询可以用下面的替换:
select rn,last_name from
(select rownum rn,last_name
from (
select last_name from s_emp order by last_name
)
where rownum<=10 --中间层查询把要查询的最大行号最过滤
)
where rn>=5;
3).取最大值的行
查找出薪资差距最大的部门,显示名称
select name
from dept
where id = (select dept_id
from (select dept_id, (max(salary) - min(salary)) salar //内层查询将薪水差计算作为别名排序
from s_emp
group by dept_id
order by salary desc)
where rownum = 1);