- 浏览: 786325 次
- 性别:
- 来自: 广州
文章分类
最新评论
分析函数是用于计算一组中多行的聚合值,与聚合函数的区别在于聚合函数只返回一个值,而分析函数能返回多个值
分析函数是一个查询语句中除了排序之外的最后操作,先通过连接,过滤,分组等操作之后再进行的操作
analytic_function::=
Description of the illustration analytic_function.gif
analytic_clause::=
Description of the illustration analytic_clause.gif
query_partition_clause::=
Description of the illustration query_partition_clause.gif
order_by_clause::=
Description of the illustration order_by_clause.gif
windowing_clause ::=
Description of the illustration windowing_clause.gif
The semantics of this syntax are discussed in the sections that follow.
analytic_function
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
arguments
Analytic functions take 0 to 3 arguments. The arguments can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that datatype. The return type is also that datatype, unless otherwise noted for an individual function.
See Also:
"Numeric Precedence " for information on numeric precedence and Table 2-11, "Implicit Type Conversion Matrix" for more information on implicit conversion
analytic_clause
Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
Notes on the analytic_clause:
You cannot specify any analytic function in any part of the analytic_clause. That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.
You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION .
query_partition_clause
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses) or a partitioned outer join (in the outer_join_clause), use the lower branch of the syntax (with parentheses).
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
order_by_clause
Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.
Restriction on the ORDER BY Clause
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.
ASC | DESC
Specify the ordering sequence (ascending or descending). ASC is the default.
NULLS FIRST | NULLS LAST
Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.
See Also:
order_by_clause of SELECT for more information on this clause
windowing_clause
Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).
ROWS | RANGE
These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.
ROWS specifies the window in physical units (rows).
RANGE specifies the window as a logical offset.
You cannot specify this clause unless you have specified the order_by_clause.
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.
BETWEEN ... AND
Use the BETWEEN ... AND clause to specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.
If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.
UNBOUNDED PRECEDING
Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
UNBOUNDED FOLLOWING
Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.
CURRENT ROW
As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING.
As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING.
value_expr PRECEDING or value_expr FOLLOWING
For RANGE or ROW:
If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.
If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.
If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
See Also:
NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals
If you specified ROWS:
value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
If value_expr is part of the start point, then it must evaluate to a row before the end point.
If you specified RANGE:
value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Please refer to "Literals " for information on interval literals.
You can specify only one expression in the order_by_clause
If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.
If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause.
AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
See Also:
Oracle Data Warehousing Guide for more information on these functions and for scenarios illustrating their use
Object Reference Functions
Object reference functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:
DEREF
MAKE_REF
REF
REFTOHEX
VALUE
分析函数是一个查询语句中除了排序之外的最后操作,先通过连接,过滤,分组等操作之后再进行的操作
analytic_function::=
Description of the illustration analytic_function.gif
analytic_clause::=
Description of the illustration analytic_clause.gif
query_partition_clause::=
Description of the illustration query_partition_clause.gif
order_by_clause::=
Description of the illustration order_by_clause.gif
windowing_clause ::=
Description of the illustration windowing_clause.gif
The semantics of this syntax are discussed in the sections that follow.
analytic_function
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
arguments
Analytic functions take 0 to 3 arguments. The arguments can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that datatype. The return type is also that datatype, unless otherwise noted for an individual function.
See Also:
"Numeric Precedence " for information on numeric precedence and Table 2-11, "Implicit Type Conversion Matrix" for more information on implicit conversion
analytic_clause
Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
Notes on the analytic_clause:
You cannot specify any analytic function in any part of the analytic_clause. That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.
You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION .
query_partition_clause
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr. If you omit this clause, then the function treats all rows of the query result set as a single group.
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses) or a partitioned outer join (in the outer_join_clause), use the lower branch of the syntax (with parentheses).
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause, then the function computations are parallelized as well.
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
order_by_clause
Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.
Restriction on the ORDER BY Clause
When used in an analytic function, the order_by_clause must take an expression (expr). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.
ASC | DESC
Specify the ordering sequence (ascending or descending). ASC is the default.
NULLS FIRST | NULLS LAST
Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.
See Also:
order_by_clause of SELECT for more information on this clause
windowing_clause
Some analytic functions allow the windowing_clause. In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).
ROWS | RANGE
These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.
ROWS specifies the window in physical units (rows).
RANGE specifies the window as a logical offset.
You cannot specify this clause unless you have specified the order_by_clause.
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.
BETWEEN ... AND
Use the BETWEEN ... AND clause to specify a start point and end point for the window. The first expression (before AND) defines the start point and the second expression (after AND) defines the end point.
If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.
UNBOUNDED PRECEDING
Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
UNBOUNDED FOLLOWING
Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.
CURRENT ROW
As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the end point cannot be value_expr PRECEDING.
As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING.
value_expr PRECEDING or value_expr FOLLOWING
For RANGE or ROW:
If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.
If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.
If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
See Also:
NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals
If you specified ROWS:
value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
If value_expr is part of the start point, then it must evaluate to a row before the end point.
If you specified RANGE:
value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Please refer to "Literals " for information on interval literals.
You can specify only one expression in the order_by_clause
If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.
If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause.
AVG *
CORR *
COVAR_POP *
COVAR_SAMP *
COUNT *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
MAX *
MIN *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *
See Also:
Oracle Data Warehousing Guide for more information on these functions and for scenarios illustrating their use
Object Reference Functions
Object reference functions manipulate REFs, which are references to objects of specified object types. The object reference functions are:
DEREF
MAKE_REF
REF
REFTOHEX
VALUE
发表评论
-
Oracle 10g 的clusterware 32位 下载地址
2013-04-19 23:03 1215Oracle 10g 的clusterware 32位 下载地 ... -
oracle 分析函数 RANK()
2013-04-11 00:05 1100RANK()既是一个聚合函数,也是一个分析函数 其具体的语法 ... -
批量执行 bulk collect与forall用法
2013-04-08 23:49 1395BULK COLLECT 子句会批量检 ... -
pl/sql集合类型
2013-03-26 10:12 1576--集合类型 /* 单行单列的数据,使用标量变量 单行 ... -
oracle 行链接与行迁移
2013-03-16 01:06 1105表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放 ... -
oracle Health Monitor
2013-01-20 00:02 1614About Health Monitor Beginning ... -
oracle moving window size与 AWR retention period关系
2013-01-19 15:58 8488转自: http://tomszrp.itpub.net/po ... -
Oracle11.2新特性之INSERT提示IGNORE_ROW_ON_DUPKEY_INDEX
2013-01-12 00:20 2916insert提示IGNORE_ROW_ON_DUPKEY_IN ... -
oracle 11g新特性Flashback data archive
2013-01-09 22:52 30871. 什么是flashback data archive F ... -
RMAN List和report 命令
2012-12-25 00:07 2900LIST 命令 使用RMAN LIST 命令显示有关资料档案库 ... -
oracle ASM中ASM_POWER_LIMIT参数
2012-12-24 23:46 6432ASM_POWER_LIMIT 该初始化参数用于指定ASM例程 ... -
oracle I/O 从属进程
2012-12-24 23:24 1429I/O 从属进程 I/O从 ... -
easy connect 之 ORA-12154: TNS: 无法解析指定的连接标识符
2012-12-19 23:43 5641用easy connect连接出现“tns无法解析指定的连接标 ... -
Flashback Database --闪回数据库
2012-12-19 23:38 1393Flashback 技术是以Undo segment中的内容为 ... -
Oracle 11g新特性:Automatic Diagnostic Repository
2012-12-19 22:35 1395Oracle Database 11g的FDI(Fault D ... -
RMAN配置中通道(CHANNEL)相关参数 PARALLELISM 、FILESPERSET的关系
2012-12-19 22:09 2700RMAN配置中通道(CHANNEL)相 ... -
oracle 空间RESUMABLE
2012-12-14 22:05 3062空间RESUMABLE操作 转 Oracle从9i开始 ... -
oracle 创建视图 with check option
2012-12-13 23:14 1532我们来看下面的例子: create or replace vi ... -
flashback transaction闪回事务查询
2012-11-26 22:00 1505闪回事务查询有别于闪回查询的特点有以下3个: (1) ... -
pl/sql连不上oracle数据库
2012-11-21 22:56 3752pl/sql 9.2版本连不上oracle数据库 当系统安装 ...
相关推荐
### Oracle分析函数详解 #### 一、Oracle分析函数概述 Oracle分析函数是在处理大量数据时极为有用的一套工具,主要用于在线分析处理(OLAP)场景。这类函数可以在多个级别上进行数据聚合,并支持复杂的排序、分组...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...
Oracle分析函数是数据库管理系统Oracle中的一个强大特性,它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用,因为它们可以基于分组计算聚合值,并为每个分组返回多行,而不...
Oracle 分析函数(用法+实例) Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用,属于 Oracle 的一大亮点。分析函数可以分为四大类:排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
Oracle 分析函数是一种强大的SQL工具,它允许你在处理数据时执行复杂的分析操作,而不像聚合...参考书籍如Tom Kyte的《Expert One-on-One》和Oracle 9i SQL Reference等,都是深入学习和理解Oracle分析函数的宝贵资源。
Oracle 分析函数详解 Oracle 分析函数是 Oracle 数据库中的一种强大功能,能够帮助用户快速进行数据分析和处理。在本文中,我们将对 Oracle 分析函数进行详细的介绍,并对其各个函数进行解释。 一、总体介绍 ...
Oracle 分析函数是一种高级SQL功能,它允许在单个查询中对数据集进行复杂的分析,无需额外的编程或多次数据库交互。分析函数处理的结果通常基于数据的分组、排序或特定窗口,为统计汇总和复杂的数据分析提供了便利。...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性,它允许用户在单个查询中对一组行进行计算,而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力,提高了查询性能。以下是对Oracle分析...
Oracle分析函数是数据库管理系统Oracle中的一种高级特性,用于处理和分析数据集,提供了一种高效的方式来执行聚合操作,而无需多次查询数据库。分析函数能够直接在单次查询中完成复杂的数据计算,包括排序、分组、...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它自Oracle 8.1.6版本开始引入,并在后续版本中不断完善和发展。这类函数的主要用途在于能够针对一组数据执行复杂的聚合计算,并且不同于...
Oracle 分析函数是数据库查询中的强大工具,它们允许在单个SQL语句中对结果集进行复杂的计算和分组操作,极大地简化了数据分析的过程。在Oracle 8i版本之后引入,分析函数为处理大量数据提供了高效的方法,避免了...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性,自8.1.6版本引入,极大地扩展了SQL在数据处理和分析上的能力。分析函数的主要作用是进行聚合操作,但与传统的聚合函数(如SUM, COUNT, AVG等)不同,它们...
ORACLE分析函数 ORACLE分析函数是数据库管理系统中的一种功能强大且灵活的分析工具,能够对数据进行复杂的分析和处理。通过使用分析函数,开发者可以更加方便地实现业务逻辑,提高查询效率和数据处理速度。 在本...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具,它允许用户对分组数据执行复杂的计算,并且能够返回多个结果行。这与传统的聚合函数(如`SUM`、`COUNT`等)形成鲜明对比,后者通常只针对...
Oracle分析函数是数据库管理系统Oracle中一组强大的工具,用于处理集合数据,特别是在复杂的报表和数据分析场景中。它们允许用户在单个SQL查询中执行聚合操作,同时保持行的原始顺序,这是传统的GROUP BY函数无法...
Oracle分析函数是数据库管理系统Oracle中的一个重要特性,自8.1.6版本开始引入,它们用于执行基于组的聚合计算,并且为每个组返回多行结果,而不仅仅是单行。这使得分析函数在数据分析和报表生成方面非常有用,能够...