oracle分析函数

1. 分析函数：

 

计算运行的累计总数
前N个查询
计算正在流动的平均数
取另行的数据

常用分析函数

•  AVG  计算组内平均值
•  COUNT对组内数据进行计数
•  DENSE_RANK 根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，有重复值时序号不跳号
•  RANK根据 order by 子句表达式的值，从查询返回的每一行，计算和其他行的相对位置，序号从 1 开始，但是有重复值时序号是跳号的
•  FIRST_VALUE返回组内第一个值
•  LAG(expression,<offset>,<default>)　在同一行中显示后n行的数据
•  LAST_VALUE返回组内最后一个值
•  LEAD(expression,<offset>,<default>)　在同一行中显示前n行的数据
•  MAX返回组内最大值
•  MIN返回组内最小值
•  ROW_NUMBER返回有序组中的一行的偏移量，也就是对应的序号
•  SUM计算组中表达式的累计和
•  CUME_DIST() 计算一行在组中的相对位置，值的范围( 0,1 ]

语法

Analytic-Function(<Argument>,<Argument>,...)
OVER (
  <Query-Partition-Clause> -- 分区子句
  <Order-By-Clause>  -- 分区排序子句
  <Windowing-Clause> -- 窗口子句
)

 
分析函数的 top-n 查询：

select b.* from (select row_number() over ( order by sal desc) rn,a.* from emp a) b
where rn between 3 and 10;

用分析函数进行细粒度排列：

select * from (select a.*,
RANK() over ( order by sal desc) case1,
DENSE_RANK() over ( order by sal desc) case2,
row_number() over ( order by sal desc) case3
from emp a)  where case3<=10;
 
select * from (select a.*,
RANK() over ( order by sal desc) case1,
DENSE_RANK() over ( order by sal desc) case2,
row_number() over ( order by sal desc) case3
from emp a)  where case3 between 5 and 10;

计算运行的累计总数

SELECT ENAME,DEPTNO,SAL,
SUM(SAL) OVER (ORDER BY DEPTNO,ENAME) RUNNING_T,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME) DEPARTMENT_T,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY ENAME) SEQ
FROM SCOTT.SCOTT.EMP
ORDER BY DEPTNO,ENAME;

 

如部门内，按照员工号升序排列，计算每个员工的工资与其前面员工工资的累加值，所有员工工资的逐步累加值，部门员工工资累计：

select empno,deptno,sal,
sum(sal) over (partition by deptno order by empno) as dept_sal_up,
sum(sal) over (order by empno) as all_sal_up,
sum(sal) over (partition by deptno) as dept_sal_all
from emp;

 

统计每个部门工资前三名的人员信息（重复人员也展现）

select ename,sal,deptno from
  (select ename,sal,deptno,
   dense_rank() over 
     (partition by deptno order by sal desc ) as seq_num
  from emp) a
where seq_num<=3;

 

查询每个部门工资高和最低的人

一般查询sql

select max(sal),min(sal),deptno from emp group by deptno;

 

但是无法查询对应人员名称，通过分析函数可以变通实现

select distinct deptno,
first_value(ename||':'||sal) over 
    ( partition by deptno order by sal) asfirst,
first_value(ename||':'||sal) over 
    ( partition by deptno order by sal desc) aslast
from emp;
 
select distinct deptno,
last_value(ename||':'||sal) over 
   ( partition by deptno order by sal) asfirst,
last_value(ename||':'||sal) over 
   ( partition by deptno order by sal desc) aslast
from emp;

 

 

在使用分析函数的时候，缺省的 WINDOWING 范围是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ，

当使用 last_ 分析函数的时候，在进行比较的时候从当前行向前进行比较，

所以前面的语句执行的结果是正确，但不是预期的。下面可能是预期得到的结果：

select distinct deptno,
last_value(ename||':'||sal) over 
   ( partition by deptno order by sal 
     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) asfirst,
last_value(ename||':'||sal) over 
   ( partition by deptno order by sal desc 
     RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) aslast
from emp;

 

计算各部门工资最少和最多

select  distinct
   max(sal) over (partition by deptno),
   min(sal) over (partition by deptno),
   deptno 
from emp;

使用聚集函数：

select     max(sal),    min(sal) ,    deptno from emp group by deptno;

 

查看过两者的执行计划，采用分析函数多做了一次排序，成本较高。简单的统计用聚集函数较好。

NULL与排序

•      NULL会影响分析函数的结果,默认时,NULL比任何其它值都大.
•      解决办法:
–     去除空值,--WHERE COMM IS NOT NULL;
–     在ORDER BY 子句中用NULL LAST;
–     (ORDER BY COMM DESC NULL LAST)

 

访问当前行周围的行

SELECT DEPTNO,ENAME,HIREDATE,
LAG(HIREDATE,1,NULL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE,ENAME) PRIOR_HIRE,
LEAD(HIREDATE,1,NULL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE,ENAME) NEXT_HIRE
FROM SCOTT.EMP
ORDER BY DEPTNO,HIREDATE;

 

行列转换

select ename,deptno,dense_rank() over (partition by deptno order by sal desc) as seq_num from emp;

 
ENAME          DEPTNO    SEQ_NUM
---------- ---------- ----------
KING               10          1
CLARK              10          2
MILLER             10          3
SCOTT              20          1
FORD               20          1
JONES              20          2
ADAMS              20          3
SMITH              20          4
BLAKE              30          1
ALLEN              30          2
TURNER             30          3
MARTIN             30          4
WARD               30          4
JAMES              30          5

 

 

已选择 14 行。

行列转换，将上面的改为列式

select deptno, min(decode(seq_num,1,ename,null)) as highest,
min(decode(seq_num,2,ename,null)) as sec_highest,
min(decode(seq_num,3,ename,null)) as third_highest from
(select ename,deptno,seq_num from
(select ename,deptno,dense_rank() over (partition by deptno order by sal desc ) as seq_num
from emp) a
where seq_num<=3) b
group by b.deptno;

 

    DEPTNO HIGHEST    SEC_HIGHES THIRD_HIGH
---------- ---------- ---------- ----------
        10 KING       CLARK      MILLER
        20 FORD       JONES      ADAMS
        30 BLAKE      ALLEN      TURNER