oracle分析函数over.pptVIP

分 析 函 数 非常有用的SQL函数 计算运行的累计总数 查找一组内的% 前N个查询 计算正在流动的平均数 取另行的数据 计算运行的累计总数 SELECT ENAME,DEPTNO,SAL, SUM(SAL) OVER (ORDER BY DEPTNO,ENAME) RUNNING_T, SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME)DEPARTMENT_T, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY ENAME)SEQ FROM EMP ORDER BY DEPTNO,ENAME; 分析函数的语法 FUNCTION_NAME(argument,argument,…..) OVER (PARTITION clauseORDER clauseWindowing clause) SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY NAME) DEPARTMENT_T 两个例子的差异 SELECT ENAME,SAL,AVG(SAL) OVER() FROM EMP; SELECT ENAME,SAL,AVG(SAL) OVER (ORDER BY ENAME) FROM EMP ORDERY BY ENAME; 不同的ORDER BY SELECT ENAME,DEPTNO, SUM(SAL) OVER (ORDER BY ENAME,DEPTNO) SUM_ENAME_DEPTNO, SUM(SAL) OVER (ORDER BY DEPTNO,ENAME) SUM_DEPTNO_ENAME FORM EMP ORDER BY ENAME,DEPTNO WINDOWING 子句 给出一个定义变化或固定的数据窗口方法，分析函数将对这些数据进行操作。 WINDOWING 子句--固定数据窗口 SELECT DEPTNO,ENAME,SAL, SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY ENAME ROWS 2 PRECEDING) SLIDING_TOTAL FROM EMP ORDER BY DEPTNO,ENAME; WINDOWING 子句—活动数据窗口 SELECT ENAME,SAL,HIREDATE,HIREDATE-100 WINDOWTOP, FIRST_VALUE(ENAME) OVER(ORDER BY HIREDATE ASC RANGE 100 PRECEDING) ENAME_PREC, FIRST_VALUE(HIREDATE) OVER(ORDER BY HIREDATE ASC RANGE 100 PRECEDING) HIREDATE_PREC FORM EMP ORDER BY HIREDATE ASC; 三种指定范围的方法： UNBOUNDED PRECEDING—从当前分区第一行开始，结束于当前行 CURRENT ROW—从当前行开始（并结束） Numeric Expression PRECEDING—固定和活动的方式 常用分析函数(一) AVG COUNT DENSE_RANK FIRST_VALUE LAG(expression,offset,default) LAST_VALUE LEAD(expression,offset,default) 常用分析函数(二) MAX MIN RANK ROW_NUMBER SUM 范例1 SELECT * FROM (SELECT DEPTNO,ENAME,SAL, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) DR FROM EMP) WHERE DR=3 ORDER BY DEPTNO,SAL DESC; 范例2 SELECT DEPTNO,ENAME,SAL, DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC)DR, RANK() ORER(PARTITION BY DEPTNO ORDER BY SAL DESC) R, FROM EMP ORDER BY DEPTNO,SAL DESC; 访问当前行周围的行 SELECT DEPTNO,ENAME,HIREDATE, LAG(HIREDATE,1,NULL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE,ENAME) L