Oracle分析函数

分析函数基本语法

	函数名(参数)  over( 分区子句 排序子句 开窗子句)

说明：
（1）over( )：开窗函数
（2）分区子句：partition by 字段
（3）排序子句：order by 字段
（4）开窗子句：三种开窗方式：rows、range、Specifying；使用开窗子句时一定要有排序子句
（5）分析函数是专门解决复杂报表统计，在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。

简单示例

1、查询各员工的工资，并附带所在部门最高工资

--查询各员工的工资，并附带所在部门最高工资  
SELECT E.EMPNO,
       E.ENAME,
       E.SAL,
       E.DEPTNO,
       MAX(E.SAL) OVER(PARTITION BY E.DEPTNO) MAX_SAL
  FROM TEMP_EMP E;

说明：
（1）聚合函数用group by分组，分组返回一个统计值。
（2）分析函数用partition by分组，每组每行都可以返回一个统计值。

2、查询各员工的工资，附带部门工资总和

--查询各员工的工资，附带部门工资总和
SELECT E.EMPNO,
       E.ENAME,
       E.SAL,
       E.DEPTNO,
       SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) SUM_SAL
  FROM TEMP_EMP E;

3、查询各员工的工资，附带部门按姓名排序的工资加和

  --查询各员工的工资，附带部门按姓名排序的工资加和
  SELECT E.EMPNO,
       E.ENAME,
       E.DEPTNO,
       E.SAL,
       SUM(SAL) OVER(PARTITION BY E.DEPTNO ORDER BY E.ENAME) SUM_SAL
  FROM TEMP_EMP E;

说明：
（1）此例中sum窗口中的记录而不是整个分组中的记录
（2）窗口指定到该分组中的第一行数据到当前行

4、LAG函数

SELECT E.DEPTNO,
        E.SAL A,
        LAG(E.SAL,1,E.SAL) OVER(ORDER BY E.DEPTNO) B 
   FROM TEMP_EMP E;

说明：
（1）LAG函数，在同一次查询中取出同一字段的前N行的数据作为独立的一列
（2）2450前无记录，则取其本身的值

5、LEAD函数

 SELECT E.DEPTNO,
        E.SAL A,
        LEAD(E.SAL,1,E.SAL) OVER(ORDER BY E.DEPTNO) B 
   FROM TEMP_EMP E;

说明：LEAD函数，在同一次查询中取出同一字段的后N行的数据作为独立的一列

6、LAG函数和LEAD函数的NULL

SELECT 
   E.DEPTNO, 
   E.SAL A, 
   LAG(E.SAL, 1, NULL) OVER(ORDER BY E.DEPTNO) B
  FROM TEMP_EMP E;

说明：按照DEPTNO排序后，只有2450前无记录，以NULL表示

SELECT 
   E.DEPTNO, 
   E.SAL A, 
   LAG(E.SAL, 1, NULL) OVER(PARTITION BY E.DEPTNO ORDER BY E.DEPTNO) B
  FROM TEMP_EMP E;

说明：分区子句PARTITION BY E.DEPTNO之后，每一组的第一条记录的前一条的值取NULL值

7、RANK函数

SELECT 
   E.EMPNO,
   E.ENAME, 
   E.SAL,
   E.DEPTNO,
   RANK() OVER(ORDER BY E.SAL) ERANK
  FROM TEMP_EMP E;

SELECT 
   E.EMPNO,
   E.ENAME, 
   E.SAL,
   E.DEPTNO,
   RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) ERANK
  FROM TEMP_EMP E;

8、ROW_NUMBER函数
与RANK相比，ROW_NUMBER函数排序无重复值

SELECT 
   E.EMPNO,
   E.ENAME, 
   E.SAL,
   E.DEPTNO,
   ROW_NUMBER() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL) ERANK
  FROM TEMP_EMP E;