常用oracle 分析函数总结

 count( *{【distinct】| all | expr } ) over( analytic_clause (分区子句)) ;

sum() | avg() | max和min | rank() 和 dense_rank() | first()和last() | lag() 和 lead()

dense_rank() 和 rank() 的区别：

1.rank()  1,2,2,4 ;

2.dense_rank() 1,2,2,3

总结：rank() 遇到重复的会跳号 ，而dense_rank（）不跳号；

1.keep（）

练习题1.查询员工薪资中最高和最低的员工编号？

SELECT MIN(EMPNO) KEEP(DENSE_RANK FIRST ORDER BY SUM(SAL) DESC NULLS LAST) AS 首位,
       MAX(EMPNO) KEEP(DENSE_RANK LAST ORDER BY SUM(SAL) DESC NULLS LAST) AS 末尾
  FROM EMP
 WHERE SAL IS NOT NULL
   AND DEPTNO IS NOT NULL
 GROUP BY EMPNO;

（1）min(empno) 用来限制first和last出现多个值得情况下返回唯一记录，与group by empno 对应；

（2）first 与 last 与dense_rank 搭配使用，返回排列规则下的首条和末条记录。

（3）order sum(sal) 按薪资大小进行排序，nulls last 指定null 列排在最后 ；

（4）keep 用于告诉oracle用于保存符合keep后面的语句记录 ；

2.LAG() 和 LEAD() ;

   LAG(col【,n】【,n】)用于返回指定列前n行的值,如果前n行已经超过了比较的范围，则返回n2，如果不指定n2，则默认返回 null ，如果不指定n 则默认为1 ；

   LEAD() 后n行 ；

练习题2.使用LAG和LEAD函数查找当前员工的前一个员工的薪水和后一个员工的薪水 ？

SELECT ENAME,
       HIREDATE,
       SAL,
       DEPTNO,
       LAG(SAL, 1, 0) OVER(ORDER BY HIREDATE) AS 前一个员工,
       LEAD(SAL, 1, 0) OVER(ORDER BY HIREDATE) AS 后一个员工
  FROM EMP
 WHERE DEPTNO = 30

说明：lag（）表示上一个 ，lead（） 表示下一个 ；因为JAMES的sal（950）没有后一个了所以是0 ；