ORACLE里的分析函数（用处作为知识层面的扩展）

oracle8开始出现的分析函数
这是扩充一些新思路
这次不一定是百分百在项目开发中运用
为了扩充知识
在分析函数之中也可以使用若干个统计函数，count（）等进行操作

分析函数的语法？
(分区)
函数名称（【参数】）OVER
PARTITION BY 子句字段
【ORDER BY 子句 字段，...【ASC|DESC|NULLS FIRST|NULLSLAST】】
[WINDOWING 子句]

这个只是简单查询，但是这个SELECT 子句里面是不可能+sum(sal)
select deptno,ename,sal
from emp

分区以deptno:
select deptno,ename,sal,
sum(sal) over(PARTITION by deptno ) sum
from emp

如果没有分区，把所有当成一个区
select deptno,ename,sal,
sum(sal) over() sum
from emp

使用PARTITION by 写几个分区,deptno,job
select deptno, job,ename,sal,
sum(sal) over(PARTITION by deptno, job ) sum
from emp

--要看结果，结果是我们分析的唯一方式

来看看order by 子句,按部门编号分区，按工资来排列，rank（）是编号操作
select deptno, job,ename,sal,
rank() over(PARTITION by deptno order by sal desc) rk
from emp

可以按照多个条件排序：
select deptno, job,ename,sal,hiredate,
rank() over(PARTITION by deptno order by sal ,hiredate desc) rk
from emp

例子：
select deptno , ename, sal , comm,
rank() over ( order by comm desc ) rk,
sum(sal) over (order by comm desc) sum
from emp

如果加上nulls last可以将空放到底下
select deptno , ename, sal , comm,
rank() over ( order by comm desc nulls last) rk,
sum(sal) over (order by comm desc nulls last) sum
from emp

这些不算麻烦，最麻烦的是WINDOWING
是分窗子句，有两种实现方式：
值域窗、行窗
这些子句能干什么使呢？

验证RANGER子句
这是一个逻辑的偏移量
select deptno,ename, sal,
sum(sal) over (partition by deptno order by sal range 300 preceding ) sum
from emp

向下匹配：
select deptno,ename, sal,
sum(sal) over (partition by deptno order by sal
range between 0 preceding and 300 following ) sum
from emp

当前行的匹配
select deptno,ename, sal,
sum(sal) over (partition by deptno order by sal
range between 0 preceding and current row ) sum
from emp

不设置边界
select deptno,ename, sal,
sum(sal) over (partition by deptno order by sal
range between unbounded preceding and current row ) sum
from emp

物理偏移
设置2行物理偏移，当前行与前两个统计数
select deptno,ename, sal,
sum(sal) over (partition by deptno order by sal
rows 2 preceding ) sum
from emp
当采用当前行与前两行数据计算得来

查询行的范围：
select deptno,ename, sal,
sum(sal) over (partition by deptno order by sal
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) sum
from emp
此时，按照部门分区，进行求和道理是相同的。


下面做几个分析函数范例分析：

查询雇员编号是7369，雇员姓名，职位，基本工资，部门编号、部门人数、平均工资、最高工资、最低工资、总工资
第一眼看，大家都会选择用子查询。
也可以用分析函数

第一步骤：
select empno,ename, job, sal, deptno ,
count(empno) over( partition by deptno ) count ,
round( avg( sal) over ( partition by deptno ),2) avg ,
sum( sal) over ( partition by deptno ) sum ,
max( sal) over ( partition by deptno ) max ,
min( sal) over ( partition by deptno ) min
from emp

第二步：
select *
from 
(
select empno,ename, job, sal, deptno ,
count(empno) over( partition by deptno ) count ,
round( avg( sal) over ( partition by deptno ),2) avg ,
sum( sal) over ( partition by deptno ) sum ,
max( sal) over ( partition by deptno ) max ,
min( sal) over ( partition by deptno ) min
from emp 
)
where empno=7369

 

查询每个雇员的编号、姓名、基本工资、部门名称、位置、此部门的平均工资、最高、最低工资，确定所需要的数据表

第一步，多表关联
select e.empno,e.ename,e.sal ,d.dname,d.loc
from emp e,dept d
where e.deptno=d.deptno

第二步加入统计信息
select e.empno,e.ename,e.sal ,d.dname,d.loc,
round(avg(sal) over (partition by e.deptno order by sal
range between unbounded preceding and unbounded following),2) avg_salary,
max(sal) over (partition by e.deptno order by sal
range between unbounded preceding and unbounded following) max_salary,
min(sal) over (partition by e.deptno order by sal
range between unbounded preceding and unbounded following) min_salary
from emp e,dept d
where e.deptno=d.deptno

 

等级函数的主要功能是为数据进行排序
select deptno , ename, sal,
rank( ) over ( partition by deptno order by sal ) rank_result,
dense_rank() over ( partition by deptno order by sal ) dense_rank_result
from emp
如果使用rank（）号码相同，就会跳出下个号码
如果使用 dense_rank()，就会不会跳出号码

生成行号，在每个分区里面都有生成
select deptno , ename, sal,
row_number() over ( partition by deptno order by sal ) row_result_deptno,--每个分区排列
row_number() over ( order by sal) row_result_sal --记录号
from emp

KEEP语句:是保留满足条件的数据
select deptno,
max(sal) keep ( dense_rank first order by sal desc) max_salary ,
min(sal) keep ( dense_rank first order by sal desc) min_salary
from emp
group by deptno


取出首尾行的记录
OVER()是声明一个数据集合，而利用FIRST_VALUE() 或LAST_VALUE()函数
select deptno,empno,ename,sal,
first_value(sal) over( partition by deptno order by sal
range between unbounded preceding and unbounded following) frist_value,
last_value(sal) over( partition by deptno order by sal
range between unbounded preceding and unbounded following) last_value
from emp
where deptno=10

报表函数
CUME_DIST(）函数他是计算相对位置，比如，假设分区有5条记录，那么这些记录会按照
这是相对位置的计算，是语法上层次上的运用
select deptno, ename, sal,
cume_dist() over(partition by deptno order by sal ) cume
from emp
where deptno in (10,20)

 

NTILE（）函数是针对数据分区中的有序结果集
select deptno , sal ,
sum(sal ) over (partition by deptno order by sal ) sum_result ,
ntile(3) over (partition by deptno order by sal ) ntile_result_a ,
ntile(6) over (partition by deptno order by sal ) ntile_result_b
from emp

 

RATIO_TO_REPORT()函数,这里还可以显示百分号
select deptno, sum(sal),
round( RATIO_TO_REPORT (sum(sal) )over( ) ,5) rate ,
round(RATIO_TO_REPORT (sum(sal) )over( ) ,5)*100||'%' precent
from emp
group by deptno