Oracle 10gR2分析函数

2019年11月06日 阅读数:176
这篇文章主要向大家介绍Oracle 10gR2分析函数,主要内容包括基础应用、实用技巧、原理机制等方面,希望对大家有所帮助。

Oracle 10gR2分析函数汇总html

 




(Translated By caizhuoyi 2008‐9‐19)


说明: ios


一、 wps27E6.tmp原文中底色为黄的部分翻译存在商榷之处,请你们踊跃提意见;  面试

二、 原文中淡蓝色字体的文字,不宜翻译,保持原样。  数据库

1. ANALYTIC FUNCTIONS

Analytic 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. express

分析函数经过将行分组后,再计算这些分组的值。它们与汇集函数不一样之处在于可以对每一分组返回多行值。分析函数根据analytic_claues(分析子句)将行分组,一个分组称为一个窗口。每一行都对应有一个在行上滑动的窗口。该窗口肯定当前行的计算范围。窗口大小能够用多个物理行进行度量,也可使用逻辑区间进行度量,好比时间。  windows

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子句以外最后执行的操做。全部链接和全部wheregroup by,和having子句都要在处理分析函数以前进行计算。所以,分析函数只能用于选择列或order by子句中。  网络

cumulative, moving, centered, and oracle

Analytic functions are commonly used to compute app

reporting aggregates.

分析函数一般用于计算数据累积值,数据移动值、数据中间值,和输出集合报表。 

 

analytic_function::= 

wps27E7.tmp 

 

analytic_function([ arguments ])

   OVER (analytic_clause)

 

analytic_clause::= 

wps27E8.tmp 

 

[ query_partition_clause ]

[ order_by_clause [ windowing_clause ] ]

 

query_partition_clause::=

wps27F9.tmp 

 

PARTITION BY

  { value_expr[, value_expr ]...

  | ( value_expr[, value_expr ]... )

  }   

order_by_clause::=

wps27FA.tmp 

 

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 ::=

wps27FB.tmp 

 

{ 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_function

Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).

Analytic_function指定分析函数的名称。(请参阅如下语义论述中的分析函数列表)

1.2 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

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_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.

 

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_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.

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_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. 

 

Order_by_clause用以指定分组中数据的排序形式。除PERCENTILE_CONTPERCENTILE_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 behavior.

 

只要使用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_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 (*).

 

有些分析函数容许使用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:

 

rangerow中的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 

 

请参阅: 

NUMTOMINTERVALNUMTODSINTERVAL获取关于数次转换为时间间隔的信息。 

 

If you specified ROWS:

 

windowing_clauserows指定: 

? 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_clauserange指定: 

? 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 

wps280C.tmpDENSE_RANK 

FIRST 

FIRST_VALUE *

LAG 

LAST 

wps280D.tmpLAST_VALUE *

LEAD 

wps280E.tmpMAX * MIN *

NTILE 

PERCENT_RANK 

wps281E.tmpPERCENTILE_CONT 

PERCENTILE_DISC 

RANK 

RATIO_TO_REPORT 

wps281F.tmpREGR_ (Linear Regression) Functions *

ROW_NUMBER 

STDDEV *

STDDEV_POP *

STDDEV_SAMP *

SUM *

wps2820.tmpVAR_POP *

VAR_SAMP *

wps2831.tmpVARIANCE

 

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

wps2832.tmp 

 

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_clausequery_partition_clause中使用。在 order_by_clausewindowing_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 Example 

The 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

wps2833.tmp 

 

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)集中全部expr1expr2null的数值对。而后做以下的计算: 

 

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 Example 

The 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

wps2844.tmp 

 

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)集中全部expr1expr2null的数值对。而后做以下的计算: 

 

(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)数值对的个数,expr1expr2都不能为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 Example 

The 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

wps2845.tmp

 

 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)集中全部expr1expr2null

数值对。而后做以下的计算: 

 

(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)数值对的个数,expr1expr2都不能为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 Example 

Please refer to the analytic example for COVAR_POP.

请参阅cova_pop分析函数例子。 

6. COUNT 

6.1 Syntax

wps2846.tmp 

 

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_clausequery_partition_clause中使用。在 order_by_clausewindowing_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不计算exprnull的行。函数要么计算全部行,要么仅计算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 Example 

The 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表每一个雇员与雇员本身薪水相差在50150之间的雇员的个数。 

 

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::=

wps2856.tmp 

 

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::=

wps2857.tmp

 

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 wps2858.tmpidentified 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用做汇集函数时,对于一个被函数参数和相应排序规则肯定的假定行rcume_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用做分析函数时,用于计算一个值在一组值中的相对位置。假定按升序排序的一个结果集或分组中存在一行rcume_dist()r上结果如是求得:值小于等于行r上值的行的行数,除以整个查询结果集或分组的行数。 

7.4 Aggregate Example 

The 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 Example 

The 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::=

wps2869.tmp 

 

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::=

wps286A.tmp 

 

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-Nbottom-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_clausevalue_exprs 值排序返回的查询结果中,每一行相对于其余行的排名。 

8.4 Aggregate Example

The 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 Example 

The 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 Syntax

first::=

wps286B.tmp 

 

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.

Firstlast是很是相似的函数。它们均可用做汇集和分析函数,操做按排序规则排名后的行组中排名为firstlast的值。若分组中只有排名为firstlast的行,那末只对这个惟一元素进行汇集操做(意思是说,当分组中只有一行记录时,不论firstlast 都取这一行――译者注) 

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.

当已排序组中第一行或最后一行的值不是排序键值时,为了得到更好的性能,firstlast函数不会进行自链接或产生视图。 

? 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函数中的任一个。它操做组中排名为firstlast值。若分组中只有排名为firstlast的行,那末只对这个惟一元素进行汇集操做。 

? 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_functionfirstlast值。 

? 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_firstdense_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.

Firstlast函数中指定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 Example 

The 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

wps287B.tmp 

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 Examples 

The 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函数的不肯定性。KochharDe 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

wps287C.tmp 

 

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 following example provides, for each salesperson in the employees table, the salary of the employee hired just before: 下面的例子提供employees表中每一个雇员正前一个被雇佣的雇员薪水: 

SELECT last_name,        hire_date,        salary,

       lag(salary, 1, 0) over(ORDER BY hire_date) AS prev_sal

  FROM employees

 WHERE job_id = 'PU_CLERK';