oracle分析函数

简单记录一下oracle 分析函数的使用，随用随更新。

1）max(xx) over(partition by xx) /min(xx) over (partition by xx)

scott/tiger连接：

SQL>SELECT emp.*, max(sal) over(partition by deptno) maxsal FROM EMP;

说明：检索员工所在部门的最高工资。

SQL> SELECT emp.*, max(sal) over(partition by deptno) maxsal FROM EMP;
 
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO     MAXSAL
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
 7782 CLARK      MANAGER    7839 1981-6-9      2450.00               10       5000
 7839 KING         PRESIDENT       1981-11-17    5000.00               10       5000
 7934 MILLER     CLERK      7782 1982-1-23     1300.00               10       5000
 7566 JONES      MANAGER    7839 1981-4-2      2975.00               20       3000
 7902 FORD       ANALYST    7566 1981-12-3     3000.00               20       3000
 7876 ADAMS      CLERK      7788 1987-5-23     1100.00               20       3000
 7369 SMITH      CLERK      7902 1980-12-17     800.00               20       3000
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20       3000
 7521 WARD       SALESMAN   7698 1981-2-22     1250.00    500.00     30       2850
 7844 TURNER     SALESMAN   7698 1981-9-8      1500.00      0.00     30       2850
 7499 ALLEN      SALESMAN   7698 1981-2-20     1600.00    300.00     30       2850
 7900 JAMES      CLERK      7698 1981-12-3      950.00               30       2850
 7698 BLAKE      MANAGER    7839 1981-5-1      2850.00               30       2850
 7654 MARTIN     SALESMAN   7698 1981-9-28     1250.00   1400.00     30       2850
 
14 rows selected

2）PERCENTILE_CONT(n) WITHIN GROUP (ORDER BY SAL DESC ) 此函数为聚合函数。

例如：PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SAL DESC )

0.5表示取中间数，测试观察便知，n的取值范围0-1。

SQL>SELECT deptno, PERCENTILE_CONT(0) WITHIN GROUP( ORDER BY SAL DESC) ME
    2       FROM EMP
    3     group by deptno;

DEPTNO         ME
---- ----------
    10       5000
    20       3000
    30       2850

说明：按部门分组，检索该部门工资最高的记录，由于此函数为聚合函数，所以和第一个介绍的分析函数还有些区别。测试查询便知。

3）PERCENTILE_CONT(n) WITHIN GROUP( ORDER BY xx DESC) OVER(PARTITION BY XX)

例如：PERCENTILE_CONT(0) WITHIN GROUP( ORDER BY SAL DESC) over(partition by deptno) 非聚合函数

SQL>SELECT emp.*, PERCENTILE_CONT(0) WITHIN GROUP( ORDER BY SAL DESC) over(partition by  deptno)    ME   FROM EMP;

SQL> SELECT emp.*, PERCENTILE_CONT(0) WITHIN GROUP( ORDER BY SAL DESC) over(partition by  deptno)    ME   FROM EMP;

测试结果图如下：
 

4）PERCENT_RANK() OVER([partition_clause] order_by_clause)

例如：PERCENT_RANK() OVER(partition by deptno order by sal desc)

5）NTILE(n) OVER([partition_clause] order_by_clause)

举例：NTILE(5) OVER(order by sal desc)

说明：按照你指定的组数(n)对记录做分组

注意：如果不能按照你指定的分组数平均分配记录数，则最后的组数的记录数将和之前的不一样多。