oracle 分析函数

1.和聚合函数相似，但对于每一组数据，聚合函数只返回一行，而分析函数对其中的每一行都有返回值。

2.这一组记录，称为分析函数的一个窗口（window）

3.由窗口决定了要处理数据的范围，该范围在物理上可以由指定的行数来确定，或者逻辑上由相对偏移量来确定。

4.分析函数总是在出了order by 之外其他子句运算后才执行的，所以他不能出现在where、group by等子句中，

只能出现在select和order by 子句中。

order 对于窗口的影响：

1.不含order by，默认的窗口是从结果集的第一行至最后一行

例：select deptno,sal,sum(sal) over() from emp;

2.含有order by，默认窗口是第一行至当前分组的最后一行

select deptno,sal,sum(sal) over(order by deptno) as sumsal from emp;

用于合计的函数：

select deptno,sal, sum(sal) over (partition by deptno) as sumsal,
　　   avg(sal) over (partition by deptno) as avgsal,

　　   count(*) over (partition by deptno) as count, 　

 　     max(sal) over (partition by deptno) as maxsal 　　

from emp;

 

 

 

 

sql分析函数-排名：

row_number() over(partition by  col order by  col) 没有重复的值

rank() over(partition by col order by col) 跳跃排序，有两个第二名，接下来是第四名

dense_rank() over(partition by col order by col) 连续排序，有两个第二名，接下来是第三名

sql分析函数-相邻：

lag(v,n,dv) 是取到排序后当前记录之前的记录

lead(v,n,dv) 是取到排序后当前记录之后的记录

其中v是必须的，可以是字符串抑或是整数，n表示位移，必须是0或者是整数，dv是

在没有取到对应值得默认值。n默认为1，dv默认为null

1.用于获取相邻行的数据，以便进行相关计算，例如同比，环比

SELECT EMPLOYEENO
      ,YEARMONTH
      ,SALARY
      ,MIN(SALARY) KEEP(DENSE_RANK FIRST ORDER BY YEARMONTH) OVER(PARTITION BY EMPLOYEENO) FIRST_SALARY -- 基比分析 salary/first_salary ,基比即与某一固定时期相比
      ,LAG(SALARY, 1, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_SAL -- 环比分析，与上个月份进行比较 
      ,LAG(SALARY, 12, 0) OVER(PARTITION BY EMPLOYEENO ORDER BY YEARMONTH) AS PREV_12_SAL -- 同比分析，与上个年度相同月份进行比较    
      ,SUM(SALARY) OVER(PARTITION BY EMPLOYEENO, SUBSTR(YEARMONTH, 1, 4) ORDER BY YEARMONTH RANGE UNBOUNDED PRECEDING) LJ --累计值
  FROM SALARYBYMONTH
 ORDER BY EMPLOYEENO
         ,YEARMONTH

2.实现重复数据只输出第一个的需求

select (

　　    case when deptno=lag(deptno,1,-1) over(partiion by deptno order by ename) 

　　　then null

　　　else deptno end　　

) deptno ,

lag(ename,1,'AAA') over(partition by deptno order by ename) lower_name

from emp;