Oracle 分析函数 开窗函数 11g

Oracle 分析函数 开窗函数

分析函数有两种，一种是排名函数在前面介绍过：http://blog.csdn.net/i99121570/article/details/14000147
另一种是聚合分析函数，在这里介绍一下：

以emp表为例：

--sum:
select job,sal
,sum(sal) over(order by sal) sum1      --累加和
,sum(sal) over(partition by job）sum2  --部门工资总和
,sum(sal) over(partition by job order by sal) sum3  --按部门累加,下一个值相同则显示相同值的和
from emp;

JOB              SAL       SUM1       SUM2       SUM3
--------- ---------- ---------- ---------- ----------
CLERK            800        800       4150        800
CLERK            950       1750       4150       1750
CLERK           1100       2850       4150       2850
SALESMAN        1250       5350       5600       2500
SALESMAN        1250       5350       5600       2500
CLERK           1300       6650       4150       4150
SALESMAN        1500       8150       5600       4000
SALESMAN        1600       9750       5600       5600
MANAGER         2450      12200       8275       2450
MANAGER         2850      15050       8275       5300
MANAGER         2975      18025       8275       8275
ANALYST         3000      24025       6000       6000
ANALYST         3000      24025       6000       6000
PRESIDENT       5000      29025       5000       5000

--avg,min,max一般只需要这个:
select job,sal
,trunc(avg(sal) over(partition by job))s2  --部门平均工资
,trunc(max(sal) over(partition by job))s2  --部门最高工资
,trunc(min(sal) over(partition by job))s3  --部门最低工资
from emp;

JOB              SAL         S2         S2         S3
--------- ---------- ---------- ---------- ----------
ANALYST         3000       3000       3000       3000
ANALYST         3000       3000       3000       3000
CLERK           1300       1037       1300        800
CLERK            950       1037       1300        800
CLERK            800       1037       1300        800
CLERK           1100       1037       1300        800
MANAGER         2850       2758       2975       2450
MANAGER         2975       2758       2975       2450
MANAGER         2450       2758       2975       2450
PRESIDENT       5000       5000       5000       5000
SALESMAN        1500       1400       1600       1250
SALESMAN        1250       1400       1600       1250
SALESMAN        1250       1400       1600       1250
SALESMAN        1600       1400       1600       1250

select sal,job
,count(*) over() cnt  --总和  与from之后加group by job 结果一致
,count(*) over(partition by null) cnt1  --和上面的一致
,count(0) over(partition by job ) cnt2  --partition by分组 显示每组的个数
,count(*) over(order by sal asc)  cnt3--排名函数 遇到相同值 并列显示大值
,count(0) over(partition by job order by sal) cnt4 --在cnt3的基础上，增加分组
from emp ;

   SAL JOB              CNT       CNT1       CNT2       CNT3       CNT4
------ --------- ---------- ---------- ---------- ---------- ----------
   800 CLERK             14         14          4          1          1
   950 CLERK             14         14          4          2          2
  1100 CLERK             14         14          4          3          3
  1250 SALESMAN          14         14          4          5          2
  1250 SALESMAN          14         14          4          5          2
  1300 CLERK             14         14          4          6          4
  1500 SALESMAN          14         14          4          7          3
  1600 SALESMAN          14         14          4          8          4
  2450 MANAGER           14         14          3          9          1
  2850 MANAGER           14         14          3         10          2
  2975 MANAGER           14         14          3         11          3
  3000 ANALYST           14         14          2         13          2
  3000 ANALYST           14         14          2         13          2
  5000 PRESIDENT         14         14          1         14          1

WITH T AS (
SELECT 1 N FROM DUAL UNION ALL
SELECT 3 N FROM DUAL UNION ALL
SELECT 4 N FROM DUAL UNION ALL
SELECT 7 N FROM DUAL UNION ALL
SELECT 10 N FROM DUAL UNION ALL
SELECT 11 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 12 N FROM DUAL UNION ALL
SELECT 19 N FROM DUAL UNION ALL
SELECT 20 N FROM DUAL
)
select N
,count(0) over(order by N range between 2 preceding and 2 following) M1 --对于N=10 M的值就是:10-2<=N<=10+2对应的的记录数
,count(*) over(order by N rows between 2 preceding and 2 following） M2 --每行对应的数据窗口是之前2行，之后2行
/* range between current row and 2 following 表示下边界就是自身
   下面的四个等效 ，数据窗口都是无边界，也就是所有值
*/
,count(*) over(order by N rows between unbounded preceding and unbounded following)  M3
,count(0) over(order by N range between unbounded preceding and unbounded following) M4
,count(*) over(partition by null) M5
,count(0) over() M6
from t;
 

    N         M1         M2         M3         M4         M5         M6
----- ---------- ---------- ---------- ---------- ---------- ----------
    1          2          3         10         10         10         10
    3          3          4         10         10         10         10
    4          2          5         10         10         10         10
    7          1          5         10         10         10         10
   10          4          5         10         10         10         10
   11          4          5         10         10         10         10
   12          4          5         10         10         10         10
   12          4          5         10         10         10         10
   19          2          4         10         10         10         10
   20          2          3         10         10         10         10