oracle 分析函数解析,分析函数详解

Introduction

Probably the easiest way to understand analytic functions is to start by looking at aggregate functions.

An aggregate function, as the name suggests, aggregates data from several rows into a single result row.

select avg(sal) from emp;

*统计函数：统计多行数据的信息到一行

The GROUP BY clause allows us to apply aggregate functions to subsets of rows.

select deptno,avg(sal) from emp group by deptno

*分组函数用于将分散的多行数据分为多个集合

*统计函数用于统计多行的数据到一行，它会减少返回的行数

Analytic functions also operate on subsets of rows, similar to aggregate functions in GROUP BY queries, but they do not reduce the number of rows returned by the query.

select empno,deptno,sal,avg(sal) over(partiton by deptno) as avg_dept_sal from emp;

*分析函数的作用类似于分组函数和统计函数的组合：

分析函数用于对分组后的数据进行统计；

不同于统计函数和分组函数的是，分析函数并不会减少返回的行数

分析函数不会减少返回的行数的原因是因为分析函数是在所有的join连接，where条件，group by操作和having条件全部执行完毕之后才会执行。

Analytic Function Syntax

There are some variations in the syntax of the individual analytic functions, but the basic syntax for an analytic function is as follows.

analytic_funceion ([arguments]) over (analytic_clause)

The analytic_clause breaks down into the following optional elements.

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

query_partition_clause

The query_partition_clause divides the result set into partitions, or groups, of data.

If the query_partition_clause is omitted, the whole result set is treated as a single partition.

select empno,deptno,sal,avg(sal) over() as avg_sal from emp;

select empno,deptno,sal,avg(sal) over(partition by deptno) as avg_dept_sal from emp;

*如果没有指定query_partition_clause选项，那么整个结果集只会被当做一个分区处理

order_by_clause

The order_by_clause is used to order rows, or siblings, within a partition.

select empno,deptno,sql,first_value(sal ignore nulls) over (partition by deptno order by sal asc nulls last) as first_val_in_dept from emp;

select empno,deptno,sal,first_value(sal ignore nulls) over (partition by deptno) as first_sal_in_dept from emp;

windowing_clause

The windowing_clause is an extension of the order_by_clause and as such, it can only be used if an order_by_clause is present.

The windowing_clause has two basic forms.

RANGE BETWEEN start_point AND end_point

ROWS BETWEEN start_point AND end_point

Possible values for "start_point" and "end_point" are:

UNBOUNDED PRECEDING : The window starts at the first row of the partition. Only available for start points.

UNBOUNDED FOLLOWING : The window ends at the last row of the partition. Only available for end points.

CURRENT ROW : The window starts or ends at the current row. Can be used as start or end point.

value_expr PRECEDING : A physical or logical offset before the current row using a constant or expression that evaluates to a positive numerical value. When used with RANGE, it can also be an interval literal if the order_by_clause uses a DATE column.

value_expr FOLLOWING : As above, but an offset after the current row.

*windowing_clause只有在order_by_clause出现的时候才能使用

Using Analytic Functions

AVG *

CLUSTER_DETAILS

CLUSTER_DISTANCE

CLUSTER_ID

CLUSTER_PROBABILITY

CLUSTER_SET

CORR *

COUNT *

COVAR_POP *

COVAR_SAMP *

CUME_DIST

DENSE_RANK

FEATURE_DETAILS

FEATURE_ID

FEATURE_SET

FEATURE_VALUE

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *

LEAD

LISTAGG

MAX *

MIN *

NTH_VALUE *

NTILE

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

PREDICTION

PREDICTION_COST

PREDICTION_DETAILS

PREDICTION_PROBABILITY

PREDICTION_SET

RANK

RATIO_TO_REPORT

REGR_ (Linear Regression) Functions *

ROW_NUMBER

STDDEV *

STDDEV_POP *

STDDEV_SAMP *

SUM *

VAR_POP *

VAR_SAMP *

VARIANCE *

英文参考：http://oracle-base.com/articles/misc/analytic-functions.php