示例 SQL> select stddev(sal) stddev from emp; STDDEV ---------- 1182.50322 SUM()
语法 说明 示例 SUM([DISTINCT | ALL] col) 返回所选字段值的总和。 ROLLUP()
语法 说明 ROLLUP(列) 根据某一列进行分组，并得出小计和总计值。ROLLUP()函数在作分组报表时很有用，但其功能也可以通过分析函数来实现。 示例 先看看一般汇总的结果： select group_id,sum(salary) from group_test group by group_id; GROUP_ID SUM(SALARY) ---------- ----------- 30 12000 20 8000 40 16000 10 4000 再看看通过rollup函数汇总的结果： select group_id,sum(salary) from group_test group by rollup(group_id); GROUP_ID SUM(SALARY) ---------- ----------- 10 4000 20 8000 30 12000 40 16000 40000 下面是对两列进行分组汇总的结果： select GROUP_ID JOB SUM(SALARY) group_id,job,sum(salary) from group_test group by rollup(group_id, job); ---------- ---------- ----------- 10 Coding 1000 10 Director 1000 10 Architect 1000 10 Programmer 1000 10 4000 20 Coding 2000 20 Director 2000 20 Architect 2000 20 Programmer 2000 20 8000 30 Coding 3000 30 Director 3000 30 Architect 3000 30 Programmer 3000 30 12000 40 Coding 4000 40 Director 4000 40 Architect 4000 40 Programmer 4000 40 16000 40000 VARIANCE()
语法 说明 示例 VARIANCE([DISTINCT | ALL] col) 返回所选字段的统计方差。 SQL> select variance(sal) variance from emp; VARIANCE ---------- 1398313.87 分析函数
分析函数是Oracle特有的函数，它通常用于计算基于组的某种汇总值。和聚合函数的不同之处在于，分析函数对于每个组能够返回多行，而聚合函数对于每个组只返回一行。分析函数的功能非常强大，可以让用户在简单的查询中回答过去必须要编写过程代码，或者效率低下的SQL查询才能够回答的问题。这些函数使用如下的语法定义：
function_name(parameter, parameter, …)
over(
在上面的语法定义中：
? ? ? ? ? ?
function_name：分析析函数的名称； parameter：函数的输入参数；
over：是分析函数的关键词，如果让聚集函数SUM()与OVER结合，就会将其当作分析函数进行处理。它后面括号中的内容是分析函数将要处理的数据片段；
partition-clause：可选的子句，当提供这个子句的时候，会将结果集分组，然后分析函数会在各个分离的组上执行；
order-by-clause：这个子句会对各组数据进行排序；
window-clause：这个子句可以让用户指定函数正在处理的组中的窗口。窗口可以是数据值的范围（范围窗口），或者也可以是相对当前行的偏移（行窗口）。
ROW_NUMBER()
语法 功能 示例1 ROW_NUMBER() OVER(partition_clause, orderby_clause, window_clause) 该函数用于对某一个结果集进行分组，并按规则对组内的数据进行排序，最后生成组内序列号。 SELECT ROW_NUMBER () OVER (PARTITION BY department_id ORDER BY employee_id) AS in_depart_id, department_id, last_name, employee_id FROM hr.employees IN_DEPART_ID 1 1 2 1 2 3 4 5 6 1 1 2 3 4 5 6 从上面的例子中可以看到，ROW_NUMBER先是对部门进行分组，然后在部门内部按照员工号进行排序，最后根据部门内员工的数量生成序列号，该序列号只在一个部门内有效。 DEPARTMENT_ID LAST_NAME EMPLOYEE_ID 200 201 202 114 115 116 117 118 119 203 120 121 122 123 124 125 10 Whalen 20 Hartstein 20 Fay 30 Raphaely 30 Khoo 30 Baida 30 Tobias 30 Himuro 30 Colmenares 40 Mavris 50 Weiss 50 Fripp 50 Kaufling 50 Vollman 50 Mourgos 50 Nayer
SUM() OVER()
语法 SUM(field) OVER(partition_clause, orderby_clause, window_clause) 说明 对结果集中的某一字段求和，被求和的每一个字段都是前面所有字段之和。该函数使用户能够看到字段的累积求和过程。这个函数的优点表现在，查询中可以包含任意数量的分析函数，而且每个分析函数都能够有它自己的partition_clause子句和组。在做报表时，这个函数尤其有用。 示例1 下面的例子用了一个分析函数。其中窗口子句“RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW”是默认的，即使不写它，Oracle也会采用。 break on dname skip1 ----按照dname分段显示，空1格 SELECT dname, ename, sal, SUM(sal) OVER(PARTITION BY dname ORDER BY dname, ename) running_total FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY dname, ename / DNAME ENAME SAL RUNNING_TOTAL -------------- -------------------- ---------- ------------- ACCOUNTING CLARK 2450 2450 KING 5000 7450 MILLER 1300 8750 RESEARCH ADAMS 1100 1100 FORD 3000 4100 JONES 2975 7075 SCOTT 3000 10075 SMITH 800 10875 SALES ALLEN 1600 1600 BLAKE 2850 4450 JAMES 950 5400 MARTIN 1250 6650 TURNER 1500 8150 WARD 1250 9400 示例2 下面这个例子使用了两个分析函数，同时采用了默认的窗口子句。 break on dname skip1 SELECT dname, ename, sal, SUM(sal) OVER(PARTITION BY dname ORDER BY dname, ename) dept_running_total, SUM(sal) OVER(ORDER BY dname, ename) running_total FROM emp, dept WHERE emp.deptno = dept.deptno ORDER BY dname, ename / DNAME ENAME SAL DEPT_RUNNING_TOTAL RUNNING_TOTAL -------------- ---------- ---------- ------------------ ------------- ACCOUNTING CLARK 2450 2450 2450 KING 5000 7450 7450 MILLER 1300 8750 8750 RESEARCH ADAMS 1100 1100 9850 FORD 3000 4100 12850 JONES 2975 7075 15825 SCOTT 3000 10075 18825 SMITH 800 10875 19625 SALES ALLEN 1600 1600 21225 BLAKE 2850 4450 24075 JAMES 950 5400 25025 MARTIN 1250 6650 26275 TURNER 1500 8150 27775 WARD 1250 9400 29025 LEAD() OVER()
语法 LEAD(field, next_number, default_value) OVER(partition_clause, orderby_clause, window_clause) 说明 LEAD() OVER()函数先查询一组值，然后获取当前记录之后的第N个记录。 Field是一个字段或表达式，它为搜索提供依据。 Next_number是一个数字，它表示向前推进多少行。 Default_value表示当没有找到相关记录时将采用的默认值。 如果为LEAD() OVER()提供的next_number为负数，则它与LAG() OVER()完全相同。 示例1 SELECT ename, sal, LEAD(ename, 1, 'N/A') OVER(ORDER BY sal) next_ename, LEAD(sal, 1, null) OVER(ORDER BY sal) next_sal, LEAD(ename, 2, 'N/A') OVER(ORDER BY sal) next_ename, LEAD(sal, 2, null) OVER(ORDER BY sal) next_sal FROM emp ORDER BY sal / ENAME SAL NEXT_ENAME NEXT_SAL NEXT_ENAME NEXT_SAL ---------- ---------- ---------- ---------- ---------- ---------- SMITH 800 JAMES 950 ADAMS 1100 JAMES 950 ADAMS 1100 WARD 1250 ADAMS 1100 WARD 1250 MARTIN 1250 WARD 1250 MARTIN 1250 MILLER 1300 MARTIN 1250 MILLER 1300 TURNER 1500 MILLER 1300 TURNER 1500 ALLEN 1600 TURNER 1500 ALLEN 1600 CLARK 2450 ALLEN 1600 CLARK 2450 BLAKE 2850 CLARK 2450 BLAKE 2850 JONES 2975