分析函数用于计算一组行的聚合值,由分析函数语法定义的行集叫做窗口,窗口的大小由物理行数或逻辑间隔决定。每一行上的滑动窗口都是已定义的,窗口决定了对某一行而言进行计算的行的范围。从查询结果和查询语法来看,分析函数与聚合函数主要有 3 点不同:
我理解的分析函数无非也就是由一或多个普通的函数或子句按照一定的规则构成的复杂数据分析函数。换言之,分析函数内部有些函数或子句的语法规则之前已经讲述过了,这里将不再赘述,本节将着重讲述分析函数所特有的一些函数或子句的语法。
有很多网站都把分析函数称之为窗口函数,又称 OVER 为开窗函数,还有些似是而非的概念我本人也不甚了解。我特地查阅过 {aa12aa},语法如下:
- analytic_function([arguments]) OVER([analytic_clause])
参数说明:
,语法中的 3 个子句都是可选的,但 windowing_clause 必须依赖于 order_by_clause 而存在。
- [query_partition_clause][order_by_clause[windowing_clause]]
在 {aa10aa} 中给出 windowing_clause 语法示意图如下:
无论是窗口大小是由物理行数 (ROWS) 确定,还是由逻辑间隔 (RANGE) 确定,在分组中窗口总是从上往下滑动。窗口范围可以由 BETWEEN...AND 限定,也可以不用 BETWEEN...AND,不用则表示窗口到当前行或值结束。
ROWS 窗口是由分组排序后分组中若干连续的行所构成的窗口。在 ROWS 窗口中 value_expr 是物理偏移量,它必须是常量或值为非负数的表达式。合法的 ROWS 窗口范围定义共有 16 种,列举如下:
1、窗口开始于分组第一行,结束于分组最后一行。
- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
2、窗口开始于分组第一行,结束于当前行。
- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- ROWS UNBOUNDED PRECEDING
3、窗口开始于分组第一行,结束于当前行的前 value_expr 行。
- ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING
4、窗口开始于分组第一行,结束于当前行的后 value_expr 行。
- ROWS BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING
5、窗口开始于当前行,结束于分组最后一行。
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
6、窗口开始于当前行,结束于当前行。
- ROWS BETWEEN CURRENT ROW AND CURRENT ROW
- ROWS CURRENT ROW
7、窗口开始于当前行,结束于当前行的前 value_expr 行。
- ROWS BETWEEN CURRENT ROW AND value_expr PRECEDING
8、窗口开始于当前行,结束于当前行的后 value_expr 行。
- ROWS BETWEEN CURRENT ROW AND value_expr FOLLOWING
9、窗口开始于当前行的前 value_expr 行,结束于当前行。
- ROWS BETWEEN value_expr PRECEDING AND CURRENT ROW
- ROWS value_expr PRECEDING
10、窗口开始于当前行的前 value_expr1 行,结束于当前行的前 value_expr2 行。前提是要满足 value_expr1 >= value_expr2。
- ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING
11、窗口开始于当前行的前 value_expr1 行,结束于当前行的后 value_expr2 行。
- ROWS BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING
12、窗口开始于当前行的后 value_expr 行,结束于分组最后一行。
- ROWS BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING
13、窗口开始于当前行的后 value_expr1 行,结束于当前行的后 value_expr2 行。前提是要满足 value_expr1 <= value_expr2。
- ROWS BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING
RANGE 窗口是由分组排序后分组中满足指定逻辑条件的行所构成的窗口。在 RANGE 窗口中 value_expr 为逻辑偏移量,它必须是常量或值为非负数的表达式或间隔值。当 value_expr 值是一个数字时,排序字段必须是数字或日期类型;当 value_expr 值是一个间隔值时,排序字段必须是一个日期类型。合法的 RANGE 窗口范围定义也有 16 种,列举如下:
1、升序排序时,表达式介于第一行的值和最后一行的值之间;降序排序时,表达式介于最后一行的值和第一行的值之间。
- RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
2、升序排序时,表达式介于第一行的值和当前行的值之间;降序排序时,表达式介于当前行的值和第一行的值之间。若不指定窗口,则默认为该窗口。
- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- RANGE UNBOUNDED PRECEDING
3、升序排序时,表达式介于第一行的值和当前行的值 -value_expr 之间;降序排序时,表达式介于当前行的值 -value_expr 和第一行的值之间。
- RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr PRECEDING
4、升序排序时,表达式介于第一行的值和当前行的值 +value_expr 之间;降序排序时,表达式介于当前行的值 +value_expr 和第一行的值之间。
- RANGE BETWEEN UNBOUNDED PRECEDING AND value_expr FOLLOWING
5、升序排序时,表达式介于当前行的值和最后一行的值之间;降序排序时,表达式介于最后一行的值和当前行的值之间。
- RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
6、表达式等于当前行的值。
- RANGE BETWEEN CURRENT ROW AND CURRENT ROW
- RANGE CURRENT ROW
7、表达式介于当前行的值和当前行的值 +value_expr 之间。
- RANGE BETWEEN CURRENT ROW AND value_expr FOLLOWING
8、表达式介于当前行的值 -value_expr 和最后一行的值之间。
- RANGE BETWEEN value_expr PRECEDING AND UNBOUNDED FOLLOWING
9、表达式介于当前行的值 -value_expr 和当前行的值之间。
- RANGE BETWEEN value_expr PRECEDING AND CURRENT ROW
- RANGE value_expr PRECEDING
10、表达式介于当前行的值 -value_expr1 和当前行的值 -value_expr2 之间。前提是要满足 value_expr1 >= value_expr2。
- RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 PRECEDING
11、表达式介于当前行的值 -value_expr1 和当前行的值 +value_expr2 之间。
- RANGE BETWEEN value_expr1 PRECEDING AND value_expr2 FOLLOWING
12、升序排序时,表达式介于当前行的值 +value_expr 和最后一行的值之间;降序排序时,表达式介于最后一行的值和当前行的值 +value_expr 之间。
- RANGE BETWEEN value_expr FOLLOWING AND UNBOUNDED FOLLOWING
13、表达式介于当前行的值 +value_expr1 和当前行的值 +value_expr2 之间。前提是要满足 value_expr1 <= value_expr2。
- RANGE BETWEEN value_expr1 FOLLOWING AND value_expr2 FOLLOWING
Oracle 10g R2 中内置的分析函数有 38 个,本节按函数用途将其分为 4 类,分别是:普通统计函数、数据排序函数、数据分布函数及统计分析函数,并逐一给出函数释义和用法示例。
示例 1:
- SELECT t.staff_name,
- t.dept_code,
- t.base_salary,
- t.post_salary,
- MAX(t.post_salary) OVER(PARTITION BY t.dept_code) max_salary,
- --部门最高岗位工资MIN(t.post_salary) OVER(PARTITION BY t.dept_code) min_salary,
- --部门最低岗位工资AVG(t.post_salary) OVER(PARTITION BY t.dept_code) avg_salary,
- --部门平均岗位工资SUM(t.post_salary) OVER(PARTITION BY t.dept_code) sum_salary,
- --部门岗位工资之和COUNT(t.post_salary) OVER(PARTITION BY t.dept_code) cnt_salary--部门工资份数FROM demo.t_staff t ORDER BY t.dept_code,
- t.post_salary;
示例 2:
- SELECT t.staff_name,t.dept_code,t.base_salary,t.post_salary,
- -- 部门内截至当前行的最高岗位工资
- MAX(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS UNBOUNDED PRECEDING) max_salary,
- -- 部门内当前行至最后一行的最低岗位工资
- MIN(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS CURRENT ROW) min_salary,
- -- 部门内第一行至前一行的平均岗位工资
- AVG(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) avg_salary,
- -- 部门内第一行至后一行的岗位工资之和
- SUM(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) sum_salary,
- -- 部门内截至当前行的工资份数
- COUNT(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary ROWS UNBOUNDED PRECEDING) cnt_salary
- FROM demo.t_staff t
- ORDER BY t.dept_code,t.post_salary;
示例 1:
- SELECT t.dept_code,
- t.staff_name,
- t.post_salary,
- RANK() OVER(ORDER BY t.dept_code, t.post_salary) rank,
- DENSE_RANK() OVER(ORDER BY t.dept_code, t.post_salary) dense_rank,
- FIRST_VALUE(t.post_salary) OVER(ORDER BY t.dept_code, t.post_salary) fist_value,
- LAST_VALUE(t.post_salary) OVER(ORDER BY t.dept_code, t.post_salary) last_value,
- LAG(t.post_salary, 1, 0) OVER(ORDER BY t.dept_code, t.post_salary) lag_value,
- LEAD(t.post_salary, 1, 0) OVER(ORDER BY t.dept_code, t.post_salary) lead_value,
- ROW_NUMBER() OVER(ORDER BY t.dept_code, t.post_salary) row_number FROM demo.t_staff t ORDER BY t.dept_code,
- t.post_salary;
示例 2:
- SELECT t.dept_code,
- t.staff_name,
- t.birthday,
- t.post_salary,
- RANK() OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) rank,
- DENSE_RANK() OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) dense_rank,
- FIRST_VALUE(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) fist_value,
- LAST_VALUE(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) last_value,
- LAG(t.post_salary, 1, 0) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code, t.post_salary) lag_value,
- LEAD(t.post_salary, 1, 0) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code, t.post_salary) lead_value,
- ROW_NUMBER() OVER(PARTITION BY t.dept_code ORDER BY t.dept_code, t.post_salary) row_number FROM demo.t_staff t ORDER BY t.dept_code,
- t.post_salary;
示例 3:
- SELECT t.staff_name,
- t.gender,
- t.dept_code,
- t.post_salary,
- MIN(t.post_salary) KEEP(DENSE_RANK FIRST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) first_value1,
- MAX(t.post_salary) KEEP(DENSE_RANK FIRST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) first_value2,
- MIN(t.post_salary) KEEP(DENSE_RANK LAST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) last_value1,
- MAX(t.post_salary) KEEP(DENSE_RANK LAST ORDER BY t.dept_code) OVER(PARTITION BY t.gender) last_value2 FROM demo.t_staff t ORDER BY t.gender,
- t.dept_code;
示例 1:
- SELECT t.staff_name,
- t.dept_code,
- t.post_salary,
- RATIO_TO_REPORT(t.post_salary) OVER() ratio_to_report,
- NTILE(3) OVER(ORDER BY t.dept_code, t.post_salary) ntile,
- CUME_DIST() OVER(ORDER BY t.dept_code, t.post_salary) cume_dist,
- PERCENT_RANK() OVER(ORDER BY t.dept_code, t.post_salary) percent_rank FROM demo.t_staff t ORDER BY t.dept_code,
- t.post_salary;
示例 2:
- SELECT t.staff_name,
- t.dept_code,
- t.post_salary,
- RATIO_TO_REPORT(t.post_salary) OVER(PARTITION BY t.dept_code) ratio_to_report,
- NTILE(3) OVER(PARTITION BY t.dept_code ORDER BY t.dept_code, t.post_salary) ntile,
- CUME_DIST() OVER(PARTITION BY t.dept_code ORDER BY t.dept_code, t.post_salary) cume_dist,
- PERCENT_RANK() OVER(ORDER BY t.dept_code, t.post_salary) percent_rank,
- PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY t.post_salary) OVER(PARTITION BY t.dept_code) percentile_disc,
- PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY t.post_salary) OVER(PARTITION BY t.dept_code) percentile_cont FROM demo.t_staff t ORDER BY t.dept_code,
- t.post_salary;
。
- COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
。
- (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n
。
- (SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
案例 1,逐行累加(在实现诸如冲销、抵扣之类的功能时,该案例的写法非常有用):
- WITH t AS(
- SELECT 1 val FROM DUAL UNION ALL
- SELECT 3 FROM DUAL UNION ALL
- SELECT 5 FROM DUAL UNION ALL
- SELECT 7 FROM DUAL UNION ALL
- SELECT 9 FROM DUAL
- )
- SELECT t.val,
- SUM(t.val) OVER(ORDER BY ROWNUM ROWS UNBOUNDED PRECEDING) cur_sum_val,
- SUM(t.val) OVER(ORDER BY ROWNUM ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) pre_sum_val
- FROM t;
结果:
- VAL CUR_SUM_VAL PRE_SUM_VAL
- ---------- ----------- -----------
- 1 1
- 3 4 1
- 5 9 4
- 7 16 9
- 9 25 16
案例 2,取 A、B 两级中数值最小的行(在实现阶段性进度计划统计分析报表时,该案例的写法非常有用):
- WITH t AS(
- SELECT 'A' grade, 1 val FROM DUAL UNION ALL
- SELECT 'A',3 FROM DUAL UNION ALL
- SELECT 'A',5 FROM DUAL UNION ALL
- SELECT 'B',7 FROM DUAL UNION ALL
- SELECT 'B',9 FROM DUAL
- )
- SELECT t2.grade,t2.val FROM(
- SELECT t.grade,t.val,RANK() OVER(PARTITION BY t.grade ORDER BY val) rank FROM t
- ) t2 WHERE t2.rank=1;
结果:
- GRADE VAL
- ----- ----------
- A 1
- B 7
案例 3,查出各部门最年长和最年轻的员工信息:
- SELECT DISTINCT t1.dept_code,t2.enum_name dept_name,
- FIRST_VALUE(t1.staff_name) OVER(PARTITION BY t1.dept_code ORDER BY t1.birthday) max_older_name,
- FIRST_VALUE(t1.staff_name) OVER(PARTITION BY t1.dept_code ORDER BY t1.birthday DESC) min_young_name
- FROM demo.t_staff t1
- LEFT JOIN demo.t_field_enum t2 ON t2.field_code='DEPT' AND t1.dept_code=t2.enum_code
- ORDER BY t1.dept_code; -- 注意:此处一旦用了 DISTINCT,ORDER BY 列表中的字段就必须是 SELECT 列表中出现过的
结果:
- DEPT_CODE DEPT_NAME MAX_OLDER_NAME MIN_YOUNG_NAME
- ------------------------ ---------------------------- ---------------------------- ---------------------------
- 010101 研发一部 小明 王二
- 010102 研发二部 小萨 小林
- 010103 研发三部 韩三 小玲
- 010104 测试部 小梅 小燕
- 010201 实施一部 小军 小红
- 010202 实施二部 小飞 小飞
严格来说本文应该算是分析函数的入门篇,因为只讲述了分析函数的语法和简单用法。尽管窗口规则比较多,但只要稍作分析,就会发现其实并不复杂,重点是要能结合实际情况灵活运用。最好的参考就是补充案例一,我本人当初也是在网上看到一条类似补充案例一中的 SQL 语句后才开始我的分析函数之旅的!巧的是没过多久公司财务要求在报销流程中增加智能冲销功能,恰好用上!
本文列出了 Oracle 10g 中的所有分析函数。其中普通统计类函数和数据排序类函数我用过一些,因为除了解释函数用途还给出了一些示例。但数据分布类函数和统计分析类函数大多只给出了简单的释义,主要是因为在日常开发中极少用到,而且大部分我也从没用过。如果你想进一步了解的话请参考官方手册:{aa0aa}。
来源: http://www.cnblogs.com/hanzongze/p/Oracle-Over.html