Oracle之分页与开窗函数

分页与开窗函数

1、伪列

Oracle中没有limit，所以Oracle中使用伪列进行分页

• 伪列：rownum 它是 orcale 数据库内置列，任何表都可以使用，它的作用是显示表中数据的行号。是对结果集加的一个伪列，即先查到结果集之后再加上去的一个列。简单说就是 rownum 是对符合条件结果的序列号。它总是从1开始排起的。

select emp.*, rownum from emp;

-- 若想进行排序，要先进行排序，然后再添加伪列
select emp.*, rownum from emp order by sal;	-- false
select t.*, rownum r from (select * from emp order by sal) t; -- true

-- 伪列可以直接小于或小于等于一个值
select emp.*, rownum from emp where rownum<5; -- true
select emp.*, rownum from emp where rownum<=5; -- true

-- 伪列不能直接大于或大于等于一个值
select emp.*, rownum from emp where rownum>5; -- false
select emp.*, rownum from emp where rownum>=5; -- false

-- 伪列不能直接等于一个不等于1的值
select emp.*, rownum from emp where rownum=1; -- true
select emp.*, rownum from emp where rownum=5; -- false

-- 查询5到10名的员工信息
select * from (select emp.*, rownum r from emp) e
where e.r>=5 and e.r<=10;
select * from (select emp.*, rownum r from emp) e
where e.r between 5 and 10;

2、开窗函数

若要对数据进行分组，之后对分组后的数据进行组内排序，然后组内添加伪列，rownum就不能满足要求了

例如：查询各科成绩前三名的记录。这需要对数据按科目进行分组，分组后的数据不能聚合成一条，然后对分组后的数据在组内进行排序，最后添加伪列。这时group by 就不能满足要求，此时可以使用开窗函数。

• 开窗函数：查询中的一种方法，它将查询的结果划分到不同的窗口中，并对每个窗口应用特定的计算操作，窗口函数在查询结果集的每一行上进行计算，并且可以提供项目聚合信息，不需要group by 分组。

• row_number、dense_rank、rank

• first_value、last_value、ntile

• percent_rank、cume_dist

• lead、lag

• count、max、min、sum、avg

语法：方法名() over ([partition by 分组列] ordre by 排序列)

• over ([partition by 分组列] ordre by 排序列)：over 子句中的分组partition by和group by的分组不同，它不会把数据聚合成一条，在 over 子名中可以省略
• row_number()：需要和 over 分析函数联用，排序的序号和 rownum 伪列相同，序号连续，不考虑值相等的情况，同 rownum一样
• dense_rank()：可以用来做排序，它序号连续，考虑重复数据，如果值相等序号就相同
• rank()：可以用来做排序，它序号不连续，考虑重复数据，如果值相等序号相同
• percent_rank():百分比排名
• cume_dis():占比
• first_value():分组后第一条
• last_value():分组后最后一条，如果排序，需要在order by 排序字段 后面加上 rows between unbounded preceding and unbounded following 这句话
• ntile(n):将结果分成n组
• lead(sal, n, ’默认值’):从当前条数向后n条取值
• lag(sal, n, ’默认值’):从当前条数向前n条取值

-- 序号连续，不考虑重复数据
select e.*, row_number() over (order by sal desc) r from emp e;
-- 序号连续，考虑重复数据
select e.*, dense_rank() over (order by sal desc) r from emp e;
-- 序号不连续，考虑重复数据
select e.*, rank() over (order by sal desc) r from emp e;

-- 分组排序后添加伪列，这里分组不会把数据聚合成一条
select e.*, row_number() over (partition by deptno order by sal desc) r from emp e;
select e.*, dense_rank() over (partition by deptno order by sal desc) r from emp e;
select e.*, rank() over (partition by deptno order by sal desc) r from emp e;

-- 查询各科成绩前三名的记录:(不考虑成绩并列情况)
select s.sno, s.cno, s.score, s.r
from (select sc.*, row_number() over (partition by cno order by score desc) r from sc) s
where s.r<=3;


select e.*, count(*) over (partition by deptno) from emp e;
select e.*, sum(sal) over (partition by deptno) from emp e;
select e.*, max(sal) over (partition by deptno) from emp e;
select e.*, min(sal) over (partition by deptno) from emp e;

select e.deptno, e.sal, first_value(sal) over (partition by deptno order by sal desc)
from emp e;
select e.deptno, e.sal, last_value(sal) over (partition by deptno)
from emp e;
select e.deptno, e.sal, last_value(sal)
        over (partition by deptno order by sal desc
            rows between unbounded preceding and unbounded following)
from emp e;

select e.*, percent_rank() over (order by sal desc) s from emp e;
select e.*, percent_rank() over (partition by deptno order by sal desc) s from emp e;
select e.*, cume_dist() over (partition by deptno order by sal desc) s from emp e;

select e.*, ntile(2) over (partition by deptno order by sal) s from emp e;
select e.*, lag(sal,1) over (partition by deptno order by sal) s from emp e;
select e.*, lead(sal,1) over (partition by deptno order by sal) s from emp e;