.Net程序员学用Oracle系列(22)：分析函数(OVER)

2022-10-15 14:54:32

1、函数语法
- 1.1、语法概述
- 1.2、窗口详解
  - 1.2.1、ROWS 窗口
  - 1.2.2、RANGE 窗口
2、函数用法
- 2.1、普通统计类函数
- 2.2、数据排序类函数
- 2.3、数据分布类函数
- 2.4、统计分析类函数
3、补充案例
4、总结

分析函数用于计算一组行的聚合值，由分析函数语法定义的行集叫做窗口，窗口的大小由物理行数或逻辑间隔决定。每一行上的滑动窗口都是已定义的，窗口决定了对某一行而言进行计算的行的范围。从查询结果和查询语法来看，分析函数与聚合函数主要有 3 点不同：

1、聚合函数每组只返回一条记录，而分析函数每组可返回多条记录。
2、在单个查询中，多个聚合函数只能作用于同一个分组，而不同分析函数可作用于不同分组。
3、在分组查询中，查询列表只能出现聚合函数或 GROUP BY 子句中出现过的字段或常量或表达式，而分析函数没有这些限制。

1、函数语法

我理解的分析函数无非也就是由一或多个普通的函数或子句按照一定的规则构成的复杂数据分析函数。换言之，分析函数内部有些函数或子句的语法规则之前已经讲述过了，这里将不再赘述，本节将着重讲述分析函数所特有的一些函数或子句的语法。

1.1、语法概述

有很多网站都把分析函数称之为窗口函数，又称 OVER 为开窗函数，还有些似是而非的概念我本人也不甚了解。我特地查阅过《Oracle Database SQL Reference 10g Release 2》Analytic Functions，语法如下：

analytic_function([arguments]) OVER([analytic_clause])

参数说明：

analytic_function：分析函数的名称。Oracle 10g R2 中内置了 30 个分析函数。
arguments：分析函数的参数。内置的分析函数一般带 0~3 个参数，参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。
OVER：用来标识函数是个分析函数。对于即可作为聚合函数又可作为分析函数的函数，Oracle 无法识别，必须用 OVER 来标识此函数为分析函数。但并不是说只可作为分析函数的函数就无需标识，是分析函数就必须用 OVER 关键字来标识。另外 OVER 后面的一对小括号也是必须的，即便括号中什么都不包含。
analytic_clause：用来确定分析规则。语法是：[query_partition_clause][order_by_clause[windowing_clause]]，语法中的 3 个子句都是可选的，但 windowing_clause 必须依赖于 order_by_clause 而存在。
- query_partition_clause：分组子句，用于确定窗口，与 GROUP BY 语句的语法类似。
- order_by_clause：排序子句，用于确定窗口规则，与 ORDER BY 语句的语法类似。
- windowing_clause：窗口范围子句，用于确定分组中当前的计算范围。

1.2、窗口详解

在《Oracle Database SQL Reference 10g Release 2》中给出 windowing_clause 语法示意图如下：

ROWS：用于指定窗口由物理行构成，即符合指定的数据行的范围。
RANGE：用于指定窗口由逻辑偏移量构成，即符合指定的逻辑条件的范围。
BETWEEN...AND：用于指定窗口的起始点和终结点。
UNBOUNDED PRECEDING：用于指明窗口开始于分组的第一行。
CURRENT ROW：作为起始点，指明窗口开始于当前行或当前行的值；作为终结点，指明窗口结束于当前行或当前行的值。
UNBOUNDED FOLLOWING：用于指明窗口结束于分组的最后一行。
value_expr：物理或逻辑偏移量的表达式。

无论是窗口大小是由物理行数(ROWS)确定，还是由逻辑间隔(RANGE)确定，在分组中窗口总是从上往下滑动。窗口范围可以由 BETWEEN...AND 限定，也可以不用 BETWEEN...AND，不用则表示窗口到当前行或值结束。

1.2.1、ROWS 窗口

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

1.2.2、RANGE 窗口

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

2、函数用法

Oracle 10g R2 中内置的分析函数有 38 个，本节按函数用途将其分为 4 类，分别是：普通统计函数、数据排序函数、数据分布函数及统计分析函数，并逐一给出函数释义和用法示例。

2.1、普通统计类函数

MAX：对组内的数据窗口中的字段或表达式求最大值。
MIN：对组内的数据窗口中的字段或表达式求最小值。
AVG：对组内的数据窗口中的字段或表达式求平均值。
SUM：对组内的数据窗口中的字段或表达式求合计值。
COUNT：对组内的数据窗口中的数据行进行累计计数。

示例 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;

2.2、数据排序类函数

RANK()：根据 ORDER BY 的排序结果，计算组间相对位置，可能跳号。组内数据按 ORDER BY 子句排序，每一行都会得到一个序号，从而形成一个序列，该序列从 1 开始，往后 ORDER BY 表达式的值每发生一次变化，该序列也随之加 1。值相同的行将得到相同的序号（NULL 被认为是相等的），后面行的序号将发生跳跃。如前两行的序号为 1，则没有序号 2，第 3 行将得到序号 3。
DENSE_RANK()：根据 ORDER BY 的排序结果，计算组间相对位置，不会跳号。DENSE_RANK 与 RANK 类似，也会得到一个首项为 1、公差为 1 的等差序列，值相同的行序号也相同（也认为 NULL 是相等的），但序号不会跳跃。如前两行的序号为 1，第 3 行将得到序号 2。
FIRST_VALUE(expression)：返回组内数据窗口的第一个值。
LAST_VALUE(expression)：返回组内数据窗口的最后一个值。
LAG(expression [, offset [, default] ])：可以访问结果集中当前行之前的行而不用进行自连接，这样就可以从组中与当前行一起选取当前行之前的行。offset 是一个正整数，默认值为 1，若不指定 offset，或 offset 的值超出窗口范围，就启用默认值。
LEAD(expression [, offset [, default] ])：与 LAG 相反，LEAD 可以访问结果集中当前行之后的行而不用进行自连接，这样就可以从组中与当前行一起选取当前行之后的行。offset 是一个正整数，默认值为 1，若不指定 offset，或 offset 的值超出窗口范围，就启用默认值。
ROW_NUMBER：返回有序组中一行的偏移量，按特定的排序分配行号。
FIRST：从紧凑排序后的结果集中筛选出排在最前面的一个值的行（可能是多行，因为值可能相等）。
LAST：从紧凑排序后的结果集中筛选出排在最后面的一个值的行（可能是多行，因为值可能相等）。

示例 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;

2.3、数据分布类函数

RATIO_TO_REPORT(expr)：计算当前行的值与组中所有行的值之和的比率。
NTILE(expr)：将一组有序的数据集分为若干桶，并为每一行分配相应的桶编号。expr 必须是能被解析成正整数的值或表达式，如果带小数，小数部分将被截取。桶编号从 1 开始到 TRUNC(expr)，每桶的数据行数最多相差 1。如果行数不能被桶数整除，那么靠前的桶将优先被填充，靠后的桶则会少一行数据。例如 expr=3，行数=16，则桶编号为 1 的有 6 行，桶编号为 2 或 3 的有 5 行。
CUME_DIST()：计算一组值的累计分布，并返回大于 0、小于或等于 1 的数，该数表示该行在组中的相对位置，值相等的相邻行会得到相同的累计分布值。
PERCENT_RANK()：与 CUME_DIST 函数类似，每行的值等于该行的行号先减去 1，再除以组中总行数减去 1，因此返回值总是大于或等于 0、小于或等于 1。
PERCENTILE_DISC(expr)：返回一个与分布百分比想对应的数值，分布百分比的计算方法参见 CUME_DIST 函数（计算时 NULL 会被忽略）。
PERCENTILE_CONT(expr)：返回一个与分布百分比想对应的数值，分布百分比的计算方法参见 PERCENT_RANK 函数（计算时 NULL 会被忽略）。
REGR_ (Linear Regression) Functions：这些线性回归函数适合最小二乘法回归线，共有 9 个不同的回归函数。
- REGR_SLOPE：返回斜率。相当于 COVAR_POP(expr1, expr2) / VAR_POP(expr2)。
- REGR_INTERCEPT：返回回归线的 y 截距。相当于 AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)。
- REGR_COUNT：返回用于填充回归线的非空数字对的数目。
- REGR_R2：返回回归线的决定系数。如果 VAR_POP(expr2) = 0，则为 NULL；如果 VAR_POP(expr1) = 0 且 VAR_POP(expr2) != 0，则为 1；如果 VAR_POP(expr1) > 0 且 VAR_POP(expr2) != 0，则为 POWER(CORR(expr1,expr),2)。
- REGR_AVGX：计算回归线自变量 (expr2) 的平均值。在去掉空对 (expr1,expr2) 后，相当于 AVG(expr2)。
- REGR_AVGY：计算回归线应变量 (expr1) 的平均值。在去掉空对 (expr1,expr2) 后，相当于 AVG(expr1)。
- REGR_SXX：在去掉空对 (expr1,expr2) 后，相当于 REGR_COUNT(expr1, expr2) * VAR_POP(expr2)。
- REGR_SYY：在去掉空对 (expr1,expr2) 后，相当于 REGR_COUNT(expr1, expr2) * VAR_POP(expr1)。
- REGR_SXY：在去掉空对 (expr1,expr2) 后，相当于 REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)。

示例 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;

2.4、统计分析类函数

CORR(expr1, expr2)：返回一对数的相关系数。相当于COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))。
COVAR_POP(expr1, expr2)：返回一对数总体协方差。相当于(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n。
COVAR_SAMP(expr1, expr2)：返回一对数的样本协方差。相当于(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)。
STDDEV([DISTINCT | ALL] expr)：计算当前行关于组的标准偏离。
STDDEV_POP(expr)：计算总体标准差，并返回总体方差的平方根。与 VAR_POP 函数的平方根相同。
STDDEV_SAMP(expr)：计算累积样本标准偏差，并返回样本方差的平方根。与 VAR_SAMP 函数的平方根相同。
VAR_POP(expr)：返回非空（即忽略 NULL）数对的总体方差。
VAR_SAMP(expr)：返回非空（即忽略 NULL）数对的样本方差。
VARIANCE([ DISTINCT | ALL ] expr)：如果 expr = 1，则返回 0；如果 expr > 1，则返回 VAR_SAMP。

3、补充案例

案例 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 中的所有分析函数。其中普通统计类函数和数据排序类函数我用过一些，因为除了解释函数用途还给出了一些示例。但数据分布类函数和统计分析类函数大多只给出了简单的释义，主要是因为在日常开发中极少用到，而且大部分我也从没用过。如果你想进一步了解的话请参考官方手册：Analytic Functions。

本文链接：http://www.cnblogs.com/hanzongze/p/oracle-over.html

版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！

码农公寓

1、函数语法

1.1、语法概述

1.2、窗口详解

1.2.1、ROWS 窗口

1.2.2、RANGE 窗口

2、函数用法

2.1、普通统计类函数

2.2、数据排序类函数

2.3、数据分布类函数

2.4、统计分析类函数

3、补充案例

总结

相关文章