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