Oracle 10gR2分析函数汇总
(Translated By caizhuoyi 2008‐9‐19)
说明：
1、 原文中底色为黄的部分翻译存在商榷之处，请大家踊跃提意见；
2、 原文中淡蓝色字体的文字，不宜翻译，保持原样。
1. ANALYTIC FUNCTIONSAnalytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
分析函数通过将行分组后，再计算这些分组的值。它们与聚集函数不同之处在于能够对每一分组返回多行值。分析函数根据analytic_claues（分析子句）将行分组，一个分组称为一个窗口。每一行都对应有一个在行上滑动的窗口。该窗口确定当前行的计算范围。窗口大小可以用多个物理行进行度量，也可以使用逻辑区间进行度量，比如时间。
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
分析函数是查询中除需要在最终处理的order by子句之外最后执行的操作。所有连接和所有where，group by，和having子句都要在处理分析函数之前进行计算。因此，分析函数只能用于选择列或order by子句中。
cumulative, moving, centered, and
Analytic functions are commonly used to compute
reporting aggregates.
分析函数通常用于计算数据累积值，数据移动值、数据中间值，和输出集合报表。
analytic_function::=
analytic_function([ arguments ])
OVER (analytic_clause)
analytic_clause::=
[ query_partition_clause ]
[ order_by_clause [ windowing_clause ] ]
query_partition_clause::=
PARTITION BY
{ value_expr[, value_expr ]...
| ( value_expr[, value_expr ]... )
}
order_by_clause::=
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ] ]... windowing_clause ::=
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
} }
The semantics of this syntax are discussed in the sections that follow.
以下各节将讨论分析函数语法的语义。
1.1 analytic_functionSpecify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
Analytic_function指定分析函数的名称。(请参阅以下语义论述中的分析函数列表)
1.2 ArgumentsAnalytic 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
est numeric precedence
determines the argument with the high and implicitly converts the
remaining arguments to that datatype. The return type is also that datatype, unless
otherwise noted for an individual function. 分析函数可取0-3个参数。参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。Oracle根据最高数字优先级别确定函数参数，并且隐式地将需要处理的参数转换为数字类型。函数的返回类型也为数字类型，除非此函数另有说明。
See Also:
"Numeric Precedence" for information on numeric precedence and Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion
请参阅：
"Numeric Precedence"可获取数字优先级的相关信息，参阅表2-10—-隐式类型转换矩阵，可获取隐式转换的更多信息。
1.3 analytic_clauseUse 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.
Over Analytic_clause用以指明函数操作的是一个查询结果集。也就是说分析函数是在 from,where,group by,和having子句之后才开始进行计算的。因此在选择列或order by子句中可以使用分析函数。为了过滤分析函数计算的查询结果，可以将它作为子查询嵌套在外部查询中，然后在外部查询中过滤其查询结果。
Notes on the analytic_clause: The following notes apply to the analytic_clause:
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.
Analytic_clause中不能包含其他任何分析函数。也就是说，分析函数不能嵌套。然而，可以在一个子查询中应用分析函数，并且通过它计算另外的分析函数。
? You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION.
用户自定义分析函数和内置函数分析函数都可以使用over analytic_clause。参见create function。
1.4 query_partition_clauseUse 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.
Partition by子句根据一个或多个value_expr将查询结果集分成若干组。若不使用该子句，那末函数将查询结果集的所有行当作一个组。
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).
在分析函数中使用query_partition_clause，应该使用语法图中上分支中的语法(不带圆括号).在model查询(位于model_column_clauses中)或被分隔的外部连接(位于 outer_join_clause中)中使用该子句，应该使用语法图中下分支中的语法(带有圆括号)。
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
在同一查询中可以使用多个分析函数，它们可以有相同或不同的partition by键值。
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.
若被查询的对象具有并行特性，并且分析函数中包含query_partition_clause，那末函数的计算也是并行的。
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
value_expr的有效值包括常量，表列，非分析函数，函数表达式，或者前面这些元素的任意组合表达式。
1.5 order_by_clauseUse 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.
Order_by_clause用以指定分组中数据的排序形式。除PERCENTILE_CONT和 PERCENTILE_DISC之外(它们只能取唯一的键值)外的分析函数，分组中可以使用多个键值对值进行排序，每个键值在value_expr中定义，并且被排序序列限定。
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 beha����,����vior.
只要使用order_by_clause后，仍存在值相同的行，则每一行都会返回相同的结果。相关行为的例子请参阅考sum分析函数的例子。
Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:
Order by子句的限制：下面是使用order by子句的一些限制：
? 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.
分析函数中的order_by_clause必须是一个表达式(expr)。Sibling关键字在此处是非法的(它仅仅与层次查询有关)。位置(position)和列别名（c_alias）也是非法的。除此之外，order_by_clause的用法与整个查询或子查询中的相同。
? An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDER BY clause if it specifies either of these two windows:
当分析函数使用range关键字限定窗口时，若使用的窗口是下列两个窗口之一，那末可以在分析函数的order by子句中使用多个排序键值。
o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The short form of this is RANGE UNBOUNDED PRECEDING. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
简写成 range unbounded preceding
o RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING. The short form of this is RANGE UNBOUNDED FOLLOWING. RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.
简写成：range unbounded following
Window boundaries other than these two can have only one sort key in the ORDER BY clause of the analytic function. This restriction does not apply to window boundaries specified by the ROW keyword.
若窗口范围由range关键字指定的分析函数中指定的不是这两个窗口范围，那末order by 子句中仅能使用一个排序键值。若分析函数的窗口范围由row关键字指定，order by子句中排序键值的使用没有这个限制。
ASC | DESC Specify the ordering sequence (ascending or descending). ASC is the default.
asc | desc 指定排序顺序(升序或降序)。asc是默认值。
NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
nulls first | nulls last 指定若返回行包含空值，该值应该出现在排序序列的开始还是末尾。
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
升序排序的默认值是nulls last，降序排序的默认值是nulls first。
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.
分析函数总是按order_by_clause对行排序。然而，分析函数中的order_by_clause
只对各个分组进行排序，而不能保证查询结果有序。要保证最后的查询结果有序，可以使用查询的order_by_clause。
See Also:
order_by_clause of SELECT for more information on this clause
请参阅： select中的order_by_clause获取该子句的更多信息。
1.6 windowing_clauseSome 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 (*).
有些分析函数允许使用windowing_clause。在此节末尾的分析函数列表中，带有星号(*) 的函数都允许使用windowing_clause。
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.
row | range 这些关键字为每一行定义一个窗口，该窗口用于计算函数结果(物理或逻辑的行的集合).然后对窗口中的每一行应用分析函数。窗口在查询结果集或分组中从上至下移动。
? ROWS specifies the window in physical units (rows).
rows 指定窗口以物理单位(行)构成。
? RANGE specifies the window as a logical offset.
range 指定窗口以逻辑偏移量构成。
You cannot specify this clause unless you have specified the order_by_clause. Some window boundaries defined by the RANGE clause let you specify only one expression in the order_by_clause. Please refer to "Restrictions on the ORDER BY Clause".
只有指定order_by_clause后才能指定windowing_clause。有些range子句定义的窗口范围只能在order_by_clause中指定一个排序表达式。请参阅Restrictions on 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. 一个带逻辑偏移量的分析函数的返回值总是确定的。然而，除非排序表达式能产生唯一的排序，否则带有物理偏移量的分析函数的返回值可能会产生不确定的结果。为了解决此问题，你可能不得不在order_by_clause中指定多个列以获得唯一的排序。
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.
between ... and between … and子句用来指定窗口的起点和终点。第一个表达式(位于and之前)定义起点，第二个表达式(位于and之后)定义终点。
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.
若不使用between而仅指定一个终点，那末oracle认为它是起点，终点默认为当前行。
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 preceding 指定unbounded preceding 指明窗口开始于分组的第一行。它只用于指定起点而不能用于指定终点。
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.
unbounded following 指定unbounded following 指明窗口结束于分组的最后一行。它只用于指定终点而不能用于指定起点。
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.
current row 用作起点，current row 指定窗口开始于当前行或当前值(依赖于是否分别指定row 或range)。在这种情况下终点不能为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.
用作终点，current row 指定窗口结束于当前行或当前值(依赖于是否分别指定row 或 range)。在这种情况下起点不能为value_expr following。
value_expr PRECEDING or value_expr FOLLOWING For RANGE or ROW:
range或row中的value_expr preceding 或 value_expr following：
? If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING.
若value_expr FOLLOWING是起点，那末终点必须是value_expr FOLLOWING。
? If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING.
若value_expr PRECEDING是终点，那末起点必须是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
请参阅：
NUMTOMINTERVAL和NUMTODSINTERVAL获取关于数次转换为时间间隔的信息。
If you specified ROWS:
若windowing_clause由rows指定：
? value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
value_expr是一个物理偏移量，它必须是一个常量或表达式，并且表达式的值必须为正数值。
? If value_expr is part of the start point, then it must evaluate to a row before the end point.
若value_expr是起点的一部分，那末它必须在终点之前对行求值。
If you specified RANGE:
若windowing_clause由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.
value_expr是一个逻辑偏移量。它必须是常量，或值为正数值的表达式，或时间间隔文字常量。请参阅Literals获取有关时间间隔文字常量的信息。
? You can specify only one expression in the order_by_clause
只能在order_by_clause中指定一个表达式。
? If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.
若value_expr求值为一个数字值，那末order by expr必须为数字或date 类型。
? If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.
若value_expr求值为一个间隔值，那末order by expr必须是一个date类型。
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
若完全忽略windowing_clause，那末默认值为 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.
分析函数通常用于数据仓库环境中。下面是分析函数列表，带有星号的函数可以包含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
请参阅：
Oracle Data Warehousing Guide获取关于这些函数及其方案使用说明的更多信息。
2. AVG 2.1 Syntax
AVG([ DISTINCT | ALL ] expr)
[ OVER(analytic_clause) ]
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions
2.2 Purpose
AVG returns average value of expr.
Avg函数返回expr的平均值。
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
函数参数可取任何数字类型或任何可以隐式转换为数字类型的非数字类型。函数返回类型与参数类型相同，都为数字类型。
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
Distinct关键字仅能在analytic_clause的query_partition_clause中使用。在 order_by_clause和windowing_clause中不允许使用distinct。
See Also:
"About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"
2.3 Aggregate Example
The following example calculates the average salary of all employees in the hr.employees table:
下面的例子计算hr.employees表中所有雇员的平均薪水：
SELECT AVG(salary) "Average" FROM employees;
AVERAGE
--------
6425
2.4 Analytic ExampleThe following example calculates, for each employee in the employees table, the average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee:
下面的例子计算，employees表中相同经理下的每一雇员和雇佣日期正好位于该雇员正前后的雇员的平均薪水：
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) over(PARTITION BY manager_id ORDER BY hire_date rows
BETWEEN 1 preceding AND 1 following) AS c_mavg
FROM employees;
MANAGER_ID LAST_NAME HIRE_DATE SALARY C_MAVG
---------- ------------------------- --------- ---------- ----------
100 Kochhar 21-SEP-89 17000 17000
100 De Haan 13-JAN-93 17000 15000
100 Raphaely 07-DEC-94 11000 11966.6667
100 Kaufling 01-MAY-95 7900 10633.3333
100 Hartstein 17-FEB-96 13000 9633.33333
100 Weiss 18-JUL-96 8000 11666.6667
100 Russell 01-OCT-96 14000 11833.3333 . . .
3. CORR 3.1 Syntax
CORR(expr1, expr2)
[ OVER (analytic_clause) ]
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions
3.2 Purpose
CORR returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.
Corr返回一组数值对的相关系数。它可以用作聚集或分析函数。
This function takes as arguments 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, implicitly converts the remaining arguments to that datatype, and returns that datatype.
函数参数可取任何数字类型或任何可以隐式转换为数字类型的非数字类型。Oracle根据最高数字优先级确定参数，隐式地将需要处理的参数转换为数字类型，并返回数字类型。
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence
Oracle Database applies the function to the set of (expr1, expr2) after eliminating the pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
Oracle数据库使用该函数前先排除(expr1,expr2)集中所有expr1或expr2为null的数值对。然后作如下的计算：
COVAR_POP(expr1, expr2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2))
The function returns a value of type NUMBER. If the function is applied to an empty set, then it returns null. 函数返回一个number类型的值。若函数应用在一个空集上，那末它将返回null。
Note:
The CORR function calculates the Pearson's correlation coefficient, which requires numeric expressions as arguments. Oracle also provides the CORR_S (Spearman's rho coefficient) and CORR_K
(Kendall's tau-b coefficient) to support nonparametric or rank correlation.
注意：
Corr函数计算Pearson关系系数时，需要用数字表达式作为参数。Oracle也提
供了corr_s(Spearman's rho系数 和) corr_k(Kendall's tau-b系数)来支 持非参数或排名相关性。
See Also:
"Aggregate Functions", "About SQL Expressions" for information on valid forms of expr, and CORR_* and CORR_S
3.3 Aggregate Example
The following example calculates the coefficient of correlation between the list prices and minimum prices of products by weight class in the sample table oe.product_information:
下面的列子，计算oe.product_information表中不同重量等级产品订价和最低价格之
间的相关系数：
SELECT weight_class, corr(list_price, min_price)
FROM product_information
GROUP BY weight_class;
WEIGHT_CLASS CORR(LIST_PRICE,MIN_PRICE)
------------ -------------------------- 1 .99914795 2 .999022941 3 .998484472 4 .999359909 5 .999536087
补充：这个查询与下面的查询等价：
SELECT weight_class,
covar_pop(list_price, min_price) /
(stddev_pop(list_price) * stddev_pop(min_price))
FROM product_information
WHERE list_price IS NOT NULL
AND min_price IS NOT NULL GROUP BY weight_class;
3.4 Analytic ExampleThe following example shows the correlation between duration at the company and salary by the employee's position. The result set shows the same correlation for each employee in a given job:
下面的例子显示了不同职务的雇员的工龄与薪水之间的相关性。结果表明职务相同的雇员有相同的相关性：
SELECT employee_id, job_id,
to_char((SYSDATE - hire_date) YEAR TO MONTH) "Yrs-Mns", salary,
corr(SYSDATE - hire_date, salary) over(PARTITION BY job_id) AS
"Correlation"
FROM employees
WHERE department_id IN (50, 80)
ORDER BY job_id, employee_id;
EMPLOYEE_ID JOB_ID Yrs-Mns SALARY Correlation ----------- ---------- ------- ---------- ----------- 145 SA_MAN +08-07 14000 .912385598 146 SA_MAN +08-04 13500 .912385598 147 SA_MAN +08-02 12000 .912385598 148 SA_MAN +05-07 11000 .912385598 149 SA_MAN +05-03 10500 .912385598 150 SA_REP +08-03 10000 .80436755 151 SA_REP +08-02 9500 .80436755
152 SA_REP +07-09 9000 .80436755
153 SA_REP +07-01 8000 .80436755
154 SA_REP +06-05 7500 .80436755
155 SA_REP +05-06 7000 .80436755 ...
4. COVAR_POP 4.1 Syntax
COVAR_POP(expr1, expr2)
[ OVER (analytic_clause) ]
See A "Analy restric
lso:
tic Functions" for information on syntax, semantics, and tions
4.2 Purpose
COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.
Covar_pop返回一组数值对的总体协方差。它可以用作聚集或分析函数。
This function takes as arguments any numeric datatype or any nonnumeric datatype that
highest numeric preced
can be implicitly converted to a numeric datatype. Oracle determines the argument with the ence, implicitly converts the remaining arguments to that datatype, and returns that datatype.
函数参数可取任何数字类型或任何可以隐式转换为数字类型的非数字类型。Oracle根据最高数字优先级确定参数，隐式地将需要处理的参数转换为数字类型，并返回数字类型。
See A
Table
implici numer
lso:
2-10, "Implicit Type Conversion Matrix" for more information on t conversion and "Numeric Precedence" for information on
ic precedence
Oracle Database applies the function to the set of (expr1, expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
Oracle数据库使用该函数前先排除(expr1,expr2)集中所有expr1或expr2为null的数值对。然后作如下的计算：
(SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n
where n is the number of (expr1, expr2) pairs where neither expr1 nor expr2 is null.
这里n是(expr1,expr2)数值对的个数，expr1和expr2都不能为null。
The function returns a value of type NUMBER. If the function is applied to an empty set, then it returns null.
函数返回一个number类型的值。若将此函数应用在一个空集上，那末它将返回null。
See Also:
"About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"
4.3 Aggregate Example
The following example calculates the population covariance and sample covariance for time employed (SYSDATE - hire_date) and salary using the sample table hr.employees:
下面的例子计算hr.employees表中不同职务雇员的雇佣时间和薪水的总体协方差和样本协方差：
SELECT job_id,
covar_pop(SYSDATE - hire_date, salary) AS covar_pop, c ar_samp(ovSYSDATE - hire_date, salary) AS covar_samp
FROM employees
WHERE department_id IN (50, 80)
GROUP BY job_id;
JOB_ID COVAR_POP COVAR_SAMP ---------- ----------- -----------
ST_MAN 436092.000 545115.000 SH_CLERK 782717.500 823913.158 SA_MAN 660700.000 825875.000 SA_REP 579988.466 600702.340
ST_CLERK 176577.250 185870.789
4.4 Analytic ExampleThe following example calculates cumulative sample covariance of the list price and minimum price of the products in the sample schema oe:
下面的例子计算oe模式中不同产品的订价和最低价格的累计样本协方差：
SELECT product_id, supplier_id,
covar_pop(list_price, min_price) over(ORDER BY product_id, supplier_id) AS cum_covp,
covar_samp(list_price, min_price) over(ORDER BY product_id, supplier_id) AS cum_covs FROM product_information p
WHERE category_id = 29
ORDER BY product_id, supplier_id;
PRODUCT_ID SUPPLIER_ID CUM_COVP CUM_COVS ---------- ----------- ---------- --------- 1774 103088 0
1775 103087 1473.25 2946.5 1794 103096 1702.77778 2554.16667 1825 103093 1926.25 2568.33333 2004 103086 1591.4 1989.25
2005 103086 1512.5 1815 2416 103088 1475.97959 1721.97619 . . .
5. COVAR_SAMP 5.1 Syntax
COVAR_SAMP(expr1, expr2)
[ OVER (analytic_clause) ]
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions
5.2 Purpose
COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.
Covar_samp返回一组数值对的样本协方差。它可用作聚集或分析函数。
This function takes as arguments 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, implicitly converts the remaining arguments to that datatype, and returns that datatype.
函数参数可取任何数字类型或任何可以隐式转换为数字类型的非数字类型。Oracle根据最高数字优先级确定参数，隐式地将需要处理的参数转换为数字类型，并返回数字类型。
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence
Oracle Database applies the function to the set of (expr1, expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Then Oracle makes the following computation:
Oracle数据库使用该函数前先排除(expr1,expr2)集中所有expr1或expr2为null的
数值对。然后作如下的计算：
(SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1)
where n is the number of (expr1, expr2) pairs where neither expr1 nor expr2 is null.
这里n是(expr1,expr2)数值对的个数，expr1和expr2都不能为null。
The function returns a value of type NUMBER. If the function is applied to an empty set, then it returns null.
函数返回一个number类型的值。若将此函数应用在一个空集上，那末它将返回null。
See A "Abou and "A
lso:
t SQL Expressions" for information on valid forms of expr
ggregate Functions"
5.3 Aggregate Example
Please refer to the aggregate example for COVAR_POP.
请参阅covar_pop聚集函数例子。
5.4 Analytic ExamplePlease refer to the analytic example for COVAR_POP.
请参阅cova_pop分析函数例子。
6. COUNT 6.1 Syntax
COUNT({ * | [ DISTINCT | ALL ] expr })
[ OVER (analytic_clause) ]
See A
lso:
"Analytic Functions" for information on syntax, semantics, and restrictions
6.2 Purpose
COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function.
Count返回查询结果集的行数。它可以用作聚集或分析函数。
If you specify DISTINCT, then you can specify only the query_partition_clause of the analytic_clause. The order_by_clause and windowing_clause are not allowed.
Distinct关键字仅能在analytic_clause的query_partition_clause中使用。在 order_by_clause和windowing_clause中不允许使用distinct。
If you specify expr, then COUNT returns the number of rows where expr is not null. You can count either all rows, or only distinct values of expr.
若expr作为函数参数，那末count不计算expr为null的行。函数要么计算所有行，要么仅计算expr的不同值。
If you specify the asterisk (*), then this function returns all rows, including duplicates and nulls. COUNT never returns null.
若星号(*)作为函数参数，那末函数返回包括数据重复的行和数据为null的行在内的所有行数。Count绝不返回null。
See Also:
"About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"
6.3 Aggregate Examples
The following examples use COUNT as an aggregate function:
下面是count用作聚集函数的若干例子：
SELECT COUNT(*) "Total" FROM employees;
Total ----------
107
SELECT COUNT(*) "Allstars" FROM employees WHERE commission_pct > 0;
Allstars ---------
35
SELECT COUNT(commission_pct) "Count" FROM employees;
Count ----------
35
SELECT COUNT(DISTINCT manager_id) "Managers" FROM employees;
Managers ----------
18
6.4 Analytic ExampleThe following example calculates, for each employee in the employees table, the moving count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary.
下面的例子计算employees表每个雇员与雇员自己薪水相差在50至150之间的雇员的个数。
SELECT last_name, salary,
COUNT(*) over(ORDER BY salary RANGE BETWEEN 50 preceding AND 150 following) AS mov_count
FROM employees;
LAST_NAME SALARY MOV_COUNT ------------------------- ---------- ---------- Olson 2100 3
Markle 2200 2
Philtanker 2200 2
Landry 2400 8
Gee 2400 8
Colmenares 2500 10
Patel 2500 10 . . .
7. CUME_DIST 7.1 Aggregate Syntax cume_dist_aggregate::=
CUME_DIST(expr[,expr ]...)
WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ] ]...
)
7.2 Analytic Syntax cume_dist_analytic::=
CUME_DIST( )
OVER ([ query_partition_clause ] order_by_clause)
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions
7.3 Purpose
CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.
Cume_dist计算一个值在一组值中的累计分布。Cume_dist返回值的范围为(0,1]。连接值总是对相同的累积值进行求值。
This function takes as arguments any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that datatype, makes the calculation, and returns NUMBER.
函数参数可取任何数字类型或任何可以隐式转换为数字类型的非数字类型。Oracle根据最高数字优先级确定参数，隐式地将需要处理的参数转换为数字类型，然后进行计算，并返回 number类型的值。
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence
? As an aggregate function, CUME_DIST calculates, for a hypothetical row r identified by the arguments of the function and a corresponding sort specification, the relative position of row r among the rows in the aggregation group. Oracle makes this calculation as if the hypothetical row r were inserted into the group of rows to be aggregated over. The arguments of the function identify a single hypothetical row within each aggregate group. Therefore, they must all evaluate to constant expressions within each aggregate group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.
CUME_DIST用作聚集函数时，对于一个被函数参数和相应排序规则确定的假定行r， cume_dist计算此假定行r在聚集分组行中的相对位置。Oracle对此进行计算时，就好像假定行r插入了被聚集的行组中一样。函数参数只确定聚集分组内的一个假定行。因此，它们必须对每个聚集分组中的常量表达式全部求值。常量参数表达式和聚集的order by子句中的表达式按位置进行匹配。因此，两者参数个数必须相同，类型必须兼容。
As an analytic function, CUME_DIST computes the relative position of a specified value in a group of values. For a row r, assuming ascending ordering, the CUME_DIST of r is the number of rows with values lower than or equal to the value of r, divided by the number of rows being evaluated (the entire query result set or a partition).
CUME_DIST用作分析函数时，用于计算一个值在一组值中的相对位置。假定按升序排序的一个结果集或分组中存在一行r，cume_dist()在r上结果如是求得：值小于等于行r上值的行的行数，除以整个查询结果集或分组的行数。
7.4 Aggregate ExampleThe following example calculates the cumulative distribution of a hypothetical employee with a salary of $15,500 and commission rate of 5% among the employees in the sample table oe.employees:
下面的例子计算oe.employees表中薪水达到$15500并且佣金率达到5%的假定雇员的累计分布值：
SELECT cume_dist(15500, .05) within
GROUP(
ORDER BY salary, commission_pct) "Cume-Dist of 15500"
FROM employees;
Cume-Dist of 15500
------------------ .972222222
7.5 Analytic ExampleThe following example calculates the salary percentile for each employee in the purchasing division. For example, 40% of clerks have salaries less than or equal to Himuro.
下面的例子计算每个采购科雇员的薪水百分点。例如，40％的职员的薪水少于或等于Himur。
SELECT job_id, last_name, salary, cume_dist() over(PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%';
JOB_ID LAST_NAME SALARY CUME_DIST
---------- ------------------------- ---------- ----------
PU_CLERK Colmenares 2500 .2 PU_CLERK Himuro 2600 .4
PU_CLERK Tobias 2800 .6
PU_CLERK Baida 2900 .8
PU_CLERK Khoo 3100 1
PU_MAN Raphaely 11000 1
8. DENSE_RANK 8.1 Aggregate Syntax dense_rank_aggregate::=
DENSE_RANK(expr [, expr ]...) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[,expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ] ]...
)
8.2 Analytic Syntax dense_rank_analytic::=
DENSE_RANK( )
OVER([ query_partition_clause ] order_by_clause)
See A "Analy restric
lso:
tic Functions" for information on syntax, semantics, and tions
8.3 Purpose
Rank values are not skipped
in the event of t
DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. ies. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.
Dense_rank计算有序组中行的排名，返回的排名是一个number数值。排名是从1开始的连续整数。排名的最大值是查询返回的唯一值的个数。排名一旦与行关联就不会产生跳跃的值。值相等的行排名相同。此函数对于计算top-N和bottom-N报表十分有用。
This function accepts as arguments any numeric datatype and returns NUMBER.
函数接受任何数字类型的参数并返回number类型。
? As an aggregate function, DENSE_RANK calculates the dense rank of a
hypothetical row identified by the arguments of the function with respect to a given
sort specification.
The arguments of the function must all evaluate to constant
expressions within each aggregate group, because they identify a single row within each group. The constant argument expressions and the expressions in the order_by_clause of the aggregate match by position. Therefore, the number of arguments must be the same and types must be compatible.
Dense_rank用作聚集函数时，它计算由一个带有排序规则的函数参数确定的假定行的密集排名。函数参数必须对每个聚集分组中的常量表达式全部求值。常量参数表达式和聚集的order by子句中的表达式按位置进行匹配。因此，参数个数必须相同，参数类型必须兼容。
? As an analytic function, DENSE_RANK computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the order_by_clause.
Dense_rank用作分析函数时，它计算按照order_by_clause中value_exprs 值排序返回的查询结果中，每一行相对于其他行的排名。
8.4 Aggregate ExampleThe following example computes the ranking of a hypothetical employee with the salary $15,500 and a commission of 5% in the sample table oe.employees:
下面的例子计算oe.employees表中薪水达到$15500并且佣金达到5％的假定雇员的排名：
SELECT dense_rank(15500, .05) within
GROUP(
ORDER BY salary DESC, commission_pct) "Dense Rank"
FROM employees;
Dense Rank ------------------- 3
8.5 Analytic ExampleThe following statement selects the department name, employee name, and salary of all employees who work in the human resources or purchasing department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for RANK.
下面的语句在在人力资源或采购部门中，选择部门名称，雇员名称，雇员薪水，然后对这两个部门中每个唯一的薪水值排名。薪水相等则排名相同。请将本例与rank示例比较。
SELECT d.department_name, e.last_name,
e.salary,
dense_rank() over(PARTITION BY e.department_id ORDER BY e.salary) AS drank
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN ('30', '40');
DEPARTMENT_NAME LAST_NAME SALARY DRANK ----------------------- ------------------ ---------- ----------
Purchasing Colmenares 2500 1
Purchasing Himuro 2600 2
Purchasing Tobias 2800 3 Purchasing Baida 2900 4 Purchasing Khoo 3100 5 Purchasing Raphaely 11000 6
Human Resources Marvis 6500
9. FIRST 9.1 Syntaxfirst::=
aggregate_function
KEEP
(DENSE_RANK FIRST ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ] ]...
)
[ OVER query_partition_clause ]
See A
lso:
"Analytic Functions" for information on syntax, semantics, and restrictions of the ORDER BY clause and OVER clause
9.2 Purpose
FIRST and LAST are very similar functions. Both are aggregate and analytic functions that operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. If only one row ranks as FIRST or LAST, the aggregate operates on the set with only one element.
First和last是非常类似的函数。它们都可用作聚集和分析函数，操作按排序规则排名后的行组中排名为first或last的值。若分组中只有排名为first或last的行，那末只对这个唯一元素进行聚集操作(意思是说，当分组中只有一行记录时，不论first或last 都取这一行――译者注)。
This function takes as an argument any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. The function returns the same datatype as the numeric datatype of the argument.
函数参数可取任何数字类型或者是任何可以隐式转换为数字类型的非数字类型。函数返回类型与参数类型相同，都为数字类型。
When you need a value from the first or last row of a sorted group, but the needed value is not the sort key, the FIRST and LAST functions eliminate the need for self-joins or views and enable better performance.
当已排序组中第一行或最后一行的值不是排序键值时，为了获得更好的性能，first和 last函数不会进行自连接或产生视图。
? The aggregate_function is any one of the MIN, MAX, SUM, AVG, COUNT, VARIANCE, or STDDEV functions. It operates on values from the rows that rank either FIRST or LAST. If only one row ranks as FIRST or LAST, the aggregate operates on a singleton (nonaggregate) set.
aggregate_function可以是min,max,sum,avg,count,variance,或
stddev函数中的任一个。它操作组中排名为first或last值。若分组中只有排名为first或last的行，那末只对这个唯一元素进行聚集操作。
? The KEEP keyword is for semantic clarity. It qualifies aggregate_function, indicating that only the FIRST or LAST values of aggregate_function will be returned. 使用Keep关键字是为了保持语义清晰。它限制aggregate_function，表示仅返回aggregate_function的first或last值。
? DENSE_RANK FIRST or DENSE_RANK LAST indicates that Oracle Database will aggregate over only those rows with the minimum (FIRST) or the maximum (LAST) dense rank (also called olympic rank).
Dense_rank_first或dense_rank_last表明Oracle数据库仅将排名为最小 (first)或最大(last)的行聚集在一起。
You can use the FIRST and LAST functions as analytic functions by specifying the OVER clause. The query_partitioning_clause is the only part of the OVER clause valid with these functions.
First和last函数中指定over子句可用作分析函数。在这两个分析函数的over子句中仅能使用query_partitioning_clause。
See Also:
Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and LAST
9.3 Aggregate Example
The following example returns, within each department of the sample table hr.employees, the minimum salary among the employees who make the lowest commission and the maximum salary among the employees who make the highest commission:
下面的例子返回hr.employees表中每个部门佣金最少雇员的最低薪水以及佣金最高雇员的最高薪水：
SELECT department_id,
MIN(salary) keep(dense_rank FIRST ORDER BY commission_pct) "Worst",
MAX(salary) keep(dense_rank LAST ORDER BY commission_pct)
"Best"
FROM employees
GROUP BY department_id;
DEPARTMENT_ID Worst Best ------------- ---------- ---------- 10 4400 4400 20 6000 13000
30 2500 11000
40 6500 6500
50 2100 8200
60 4200 9000
70 10000 10000
80 6100 14000
90 17000 24000
100 6900 12000
110 8300 12000
7000 7000
9.4 Analytic ExampleThe next example makes the same calculation as the previous example but returns the result for each employee within the department:
接下的例子对前例作相同的计算，但返回的是部门中每个雇员的薪水：
SELECT last_name, department_id, salary,
MIN(salary) keep(dense_rank FIRST ORDER BY commission_pct) over(PARTITION BY department_id) "Worst",
MAX(salary) keep(dense_rank LAST ORDER BY commission_pct)
over(PARTITION BY department_id) "Best"
FROM employees
ORDER BY department_id, salary;
LAST_NAME DEPARTMENT_ID SALARY Worst Best ------------------- ------------- ---------- ---------- ---------- Whalen 10 4400 4400 4400 Fay 20 6000 6000 13000 Hartstein 20 13000 6000 13000
. . .
Gietz 110 8300 8300 12000
Higgins 110 12000 8300 12000
Grant 7000 7000 7000
10. FIRST_VALUE 10.1 Syntax
FIRST_VALUE (expr [ IGNORE NULLS ])
OVER (analytic_clause)
See A "Analy restric
lso:
tic Functions" for information on syntax, semantics, and tions, including valid forms of expr
10.2 Purpose
FIRST_VALUE is an analytic function. It returns the first value in an ordered set of values.
If the first value in the set is null, then the function returns NULL unless you specify
r data dens
IGNORE NULLS. This setting is useful fo ification. If you specify IGNORE NULLS, then FIRST_VALUE returns the fist non-null value in the set, or NULL if all values are null. Please refer to "Using Partitioned Outer Joins: Examples" for an example of data
densification.
First_value只用作分析函数。它返回已排序集的第一个值。若集合中的第一个值为null，除非指定ignor nulls那末函数返回null。忽略空值的限定对稠化数据很有用处。若指定ignor nulls，那末first_value函数返回集合中第一个不为null的值，或若值全为null则返回null。请参阅Using Partioned Outer Joins:Examples中关于稠化数据的例子。
You cannot use FIRST_VALUE or any other analytic function for expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for expr. Please refer to "About SQL Expressions" for information on valid forms of expr.
不能在expr中使用first_value或其他任何分析函数。也就是说，此处分析函数不能嵌套，但可以在expr中使用内置函数表达式。请参阅About SQL Expressions获取合法 expr的相关信息。
10.3 ExamplesThe following example selects, for each employee in Department 90, the name of the employee with the lowest salary.
下面的例子，选出部门90中薪水最低的每一雇员的名字：
SELECT department_id, last_name, salary,
first_value(last_name) over(ORDER BY salary ASC rows unbounded preceding) AS lowest_sal FROM (SELECT *
FROM employees
WHERE department_id = 90
ORDER BY employee_id);
DEPARTMENT_ID LAST_NAME SALARY LOWEST_SAL
------------- ------------- ---------- -------------------------
90 Kochhar 17000 Kochhar
90 De Haan 17000 Kochhar
90 King 24000 Kochhar
The example illustrates the nondeterministic nature of the FIRST_VALUE function. Kochhar and DeHaan have the same salary, so are in adjacent rows. Kochhar appears first because the rows returned by the subquery are ordered by employee_id. However, if the rows returned by the subquery are ordered by employee_id in descending order, as in the next example, then the function returns a different value:
这个例子表明了first_name函数的不确定性。Kochhar和De Haan有相同的薪水，因此在行中位置相邻。Kochhar出现在第一行，因为行是通过按employee_id排序的子查询返回的。然而，若行是通过按employee_id降序排序的子查询返回的，正如下面的例子一样，那末函数返回的值不同：
SELECT department_id, last_name, salary,
first_value(last_name) over(ORDER BY salary ASC rows unbounded preceding) AS fv
FROM (SELECT *
FROM employees
WHERE department_id = 90
ORDER BY employee_id DESC);
DEPARTMENT_ID LAST_NAME SALARY FV
------------- ------------- ---------- -------------------------
90 De Haan 17000 De Haan
90 Kochhar 17000 De Haan
90 King 24000 De Haan
The following example shows how to make the FIRST_VALUE function deterministic by ordering on a unique key.
下面的例子说明怎样通过一个唯一键值排序使first_value函数具有确定性：
SELECT department_id, last_name, salary, hire_date,
first_value(last_name) over(ORDER BY salary ASC, hire_date rows unbounded preceding) AS fv FROM (SELECT *
FROM employees
WHERE department_id = 90
ORDER BY employee_id DESC);
DEPARTMENT_ID LAST_NAME SALARY HIRE_DATE FV
------------- ------------- ---------- --------- ---------------
90 Kochhar 17000 21-SEP-89 Kochhar
90 De Haan 17000 13-JAN-93 Kochhar
90 King 24000 17-JUN-87 Kochhar
11. LAG 11.1 Syntax
LAG(value_expr [, offset ] [, default ])
OVER ([ query_partition_clause ] order_by_clause)
See Also:
"Analytic Functions" for information on syntax, semantics, and restrictions, including valid forms of value_expr
11.2 Purpose
LAG is an analytic function. It provides access to more than one row of a table at the same time without a self join. Given a series of rows returned from a query and a position of the cursor, LAG provides access to a row at a given physical offset prior to that position.
Lag只能用作分析函数。它提供在不使用自连接的情况下访问表中多个行的途径。给定要查询的行组和一个位置指针，lag能根据给定的物理偏移量访问前面位置的行。
If you do not specify offset, then its default is 1. The optional default value is returned if the offset goes beyond the scope of the window. If you do not specify default, then its default is null.
若不指定offset，那末其默认为1。若偏移量超出窗口范围，则返回可选的default值。若没有指定default，那末其默认为null。
You cannot use LAG or any other analytic function for value_expr. That is, you cannot nest analytic functions, but you can use other built-in function expressions for value_expr.
不能在value_expr中使用lag或其他任何分析函数。也就是说，此处分析函数不能嵌套。
但是可以在value_expr中使用内置函数表达式。
See Also:
"About SQL Expressions" for information on valid forms of expr and LEAD
11.3 Examples
The