分析函数例子

SELECT EMP.ENAME 姓名,
       EMP.SAL 工资,
       EMP.DEPTNO 部门编号,
       ROW_NUMBER() OVER(PARTITION BY EMP.DEPTNO ORDER BY EMP.SAL DESC) 工资在部门内排行,
       NTILE(2) OVER(ORDER BY EMP.SAL DESC) 按工资二等分,
       AVG(EMP.SAL) OVER(PARTITION BY EMP.DEPTNO) 部门薪水均值,
       SUM(EMP.SAL) OVER(PARTITION BY EMP.DEPTNO) 部门薪水总额,
       COUNT(DISTINCT EMPNO) OVER(PARTITION BY EMP.DEPTNO) 部门有几个员工,
       DENSE_RANK() OVER(PARTITION BY EMP.DEPTNO ORDER BY EMP.SAL DESC) 员工在部门内薪水排行,
       DENSE_RANK() OVER(ORDER BY EMP.SAL DESC) 员工在公司内薪水排行,
       MIN(EMP.SAL) OVER(PARTITION BY EMP.DEPTNO) 部门的最低薪水,
       MIN(EMP.SAL) KEEP(DENSE_RANK FIRST ORDER BY EMP.SAL) OVER(PARTITION BY EMP.DEPTNO) 部门的最低薪水,
       FIRST_VALUE(EMP.SAL) OVER(PARTITION BY EMP.DEPTNO ORDER BY EMP.SAL) 部门的最低薪水,
       MAX(EMP.SAL) OVER(PARTITION BY EMP.DEPTNO) 部门的最高薪水,
       MAX(EMP.SAL) KEEP(DENSE_RANK LAST ORDER BY EMP.SAL) OVER(PARTITION BY EMP.DEPTNO) 部门的最高薪水,
       FIRST_VALUE(EMP.SAL) OVER(PARTITION BY EMP.DEPTNO ORDER BY EMP.SAL DESC) 部门的最高薪水,
       LAG(EMP.ENAME, 1, '00') OVER(ORDER BY EMP.SAL DESC) 薪水在自己前一位的人,
       LEAD(EMP.ENAME, 1, '00') OVER(ORDER BY EMP.SAL DESC) 薪水在自己后一位的人
  FROM SCOTT.EMP
 ORDER BY EMP.SAL DESC;
​

结果

|-
| 姓名 | 工资 | 部门编号 | 工资在部门内排行 | 按工资二等分 | 部门薪水均值 | 部门薪水总额 | 部门有几个员工 | 员工在部门内薪水排行 | 员工在公司内薪水排行 | 部门的最低薪水 | 部门的最低薪水 | 部门的最低薪水 | 部门的最高薪水 | 部门的最高薪水 | 部门的最高薪水 | 薪水在自己前一位的人 | 薪水在自己后一位的人 |
| KING | 5000.00 | 10 | 1 | 1 | 2916.666667 | 8750 | 3 | 1 | 1 | 1300 | 1300 | 1300 | 5000 | 5000 | 5000 | 00 | FORD |
| FORD | 3000.00 | 20 | 2 | 1 | 2175 | 10875 | 5 | 1 | 2 | 800 | 800 | 800 | 3000 | 3000 | 3000 | KING | SCOTT |
| SCOTT | 3000.00 | 20 | 1 | 1 | 2175 | 10875 | 5 | 1 | 2 | 800 | 800 | 800 | 3000 | 3000 | 3000 | FORD | JONES |
| JONES | 2975.00 | 20 | 3 | 1 | 2175 | 10875 | 5 | 2 | 3 | 800 | 800 | 800 | 3000 | 3000 | 3000 | SCOTT | BLAKE |
| BLAKE | 2850.00 | 30 | 1 | 1 | 1566.666667 | 9400 | 6 | 1 | 4 | 950 | 950 | 950 | 2850 | 2850 | 2850 | JONES | CLARK |
| CLARK | 2450.00 | 10 | 2 | 1 | 2916.666667 | 8750 | 3 | 2 | 5 | 1300 | 1300 | 1300 | 5000 | 5000 | 5000 | BLAKE | ALLEN |
| ALLEN | 1600.00 | 30 | 2 | 1 | 1566.666667 | 9400 | 6 | 2 | 6 | 950 | 950 | 950 | 2850 | 2850 | 2850 | CLARK | TURNER |
| TURNER | 1500.00 | 30 | 3 | 2 | 1566.666667 | 9400 | 6 | 3 | 7 | 950 | 950 | 950 | 2850 | 2850 | 2850 | ALLEN | MILLER |
| MILLER | 1300.00 | 10 | 3 | 2 | 2916.666667 | 8750 | 3 | 3 | 8 | 1300 | 1300 | 1300 | 5000 | 5000 | 5000 | TURNER | WARD |
| WARD | 1250.00 | 30 | 5 | 2 | 1566.666667 | 9400 | 6 | 4 | 9 | 950 | 950 | 950 | 2850 | 2850 | 2850 | MILLER | MARTIN |
| MARTIN | 1250.00 | 30 | 4 | 2 | 1566.666667 | 9400 | 6 | 4 | 9 | 950 | 950 | 950 | 2850 | 2850 | 2850 | WARD | ADAMS |
| ADAMS | 1100.00 | 20 | 4 | 2 | 2175 | 10875 | 5 | 3 | 10 | 800 | 800 | 800 | 3000 | 3000 | 3000 | MARTIN | JAMES |
| JAMES | 950.00 | 30 | 6 | 2 | 1566.666667 | 9400 | 6 | 5 | 11 | 950 | 950 | 950 | 2850 | 2850 | 2850 | ADAMS | SMITH |
| SMITH | 800.00 | 20 | 5 | 2 | 2175 | 10875 | 5 | 4 | 12 | 800 | 800 | 800 | 3000 | 3000 | 3000 | JAMES | 00 |