传智播客学习之topN算法

 

 

最近学习了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);