Oracle 10gR2分析函式彙總

(Translated By caizhuoyi 2008‐9‐19)

說明：

1、原文中底色為黃的部分翻譯存在商榷之處，請大家踴躍提意見；

2、原文中淡藍色字型的文字，不宜翻譯，保持原樣。

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.

分析函式通過將行分組後，再計算這些分組的值。它們與聚集函式不同之處在於能夠對每一分組返回多行值。分析函式根據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_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_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 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:

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 *

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

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

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。

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

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 Example

Please 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 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表每個僱員與僱員自己薪水相差在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 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::=

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

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

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函式的不確定性。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 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';

LAST_NAME HIRE_DATE SALARY PREV_SAL

------------------------- --------- ---------- ----------

Khoo 18-MAY-95 3100 0

Tobias 24-JUL-97 2800 3100

Baida 24-DEC-97 2900 2800

Himuro 15-NOV-98 2600 2900

Colmenares 10-AUG-99 2500 2600

12. LAST

12.1 Syntax

last::=

aggregate_function KEEP (DENSE_RANK LAST ORDER BY expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

[, expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ] ]...

)

[ OVER query_partition_clause ]

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions of the query_partitioning_clause

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

函式引數可取任何數字型別或者是任何可以隱式轉換為數字型別的非數字型別。函式返回型別與引數型別相同，都為數字型別。

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Please refer to FIRST for complete information on this function and for examples of its use.

函式使用的全部資訊和用例請參閱first函式。

13. LAST_VALUE

13.1 Syntax

LAST_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

13.2 Purpose

LAST_VALUE is an analytic function. It returns the last value in an ordered set of values. If the last value in the set is null, then the function returns NULL unless you specify IGNORE

densification

densificatio

NULLS. This setting is useful for data . If you specify IGNORE NULLS, then LAST_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 n.

Last_value只用作分析函式。它返回已排序集的最後一個值。若集合中最後一個值為空，除非指定為ignore nulls，否則返回null。忽略空值的限定對稠化資料很有用處。若指定ignor nulls，那末last_value返回集合中最後一個不為null的值，或若值全為null則返回null。請參閱Using Partioned Outer Joins:Examples中關於稠化資料的例子。

You cannot use LAST_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中使用last_value或其他任何分析函式。也就是說，此處分析函式不能巢狀，但可以在expr中使用內建函式表示式。請參閱About SQL Expressions獲取合法 expr的相關資訊。

13.3 Examples

The following example returns, for each row, the hire date of the employee earning the highest salary:

下面的例子返回僱傭期間薪水最高的僱員：

SELECT last_name, salary, hire_date,

last_value(hire_date) over(ORDER BY salary rows BETWEEN unbounded preceding AND unbounded following) AS lv

FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date);

LAST_NAME SALARY HIRE_DATE LV

------------------------- ---------- --------- ---------

Kochhar 17000 21-SEP-89 17-JUN-87

De Haan 17000 13-JAN-93 17-JUN-87

King 24000 17-JUN-87 17-JUN-87

This example illustrates the nondeterministic nature of the LAST_VALUE function. Kochhar and De Haan have the same salary, so they are in adjacent rows. Kochhar appears first because the rows in the subquery are ordered by hire_date. However, if the rows are ordered by hire_date in descending order, as in the next example, then the function returns a different value:

這個例子表明了last_name函式的不確定性。Kochhar和De Haan有相同的薪水，因此在行中位置相鄰。Kochhar出現在第一行，因為行是通過按hire_date排序的子查詢返回的。然而若行是通過按hire_date降序排序的子查詢返回的，正如下面的例子一樣，那末函式返回的值不同：

SELECT last_name, salary, hire_date,

last_value(hire_date) over(ORDER BY salary rows BETWEEN unbounded preceding AND unbounded following) AS lv

FROM (SELECT *

FROM employees

WHERE department_id = 90 ORDER BY hire_date DESC);

LAST_NAME SALARY HIRE_DATE LV

------------------------- ---------- --------- ---------

De Haan 17000 13-JAN-93 17-JUN-87

Kochhar 17000 21-SEP-89 17-JUN-87

King 24000 17-JUN-87 17-JUN-87

The following two examples show how to make the LAST_VALUE function deterministic by ordering on a unique key. By ordering within the function by both salary and hire_date, you can ensure the same result regardless of the ordering in the subquery.

下面的兩個例子說明怎樣通過一個唯一鍵值排序使last_value函式具有確定性。通過在函式中使用salary和hire_date排序，不論子查詢中排序如何，都能確保查詢結果一致。

SELECT last_name, salary, hire_date,

last_value(hire_date) over(ORDER BY salary, hire_date rows BETWEEN unbounded preceding AND unbounded following) AS lv FROM (SELECT * FROM employees WHERE department_id = 90 ORDER BY hire_date);

LAST_NAME SALARY HIRE_DATE LV

------------------------- ---------- --------- ---------

Kochhar 17000 21-SEP-89 17-JUN-87

De Haan 17000 13-JAN-93 17-JUN-87

King 24000 17-JUN-87 17-JUN-87

SELECT last_name, salary, hire_date,

last_value(hire_date) over(ORDER BY salary, hire_date rows

BETWEEN unbounded preceding AND unbounded following) AS lv

FROM (SELECT *

FROM employees

WHERE department_id = 90

ORDER BY hire_date DESC);

LAST_NAME SALARY HIRE_DATE LV

------------------------- ---------- --------- ---------

Kochhar 17000 21-SEP-89 17-JUN-87

De Haan 17000 13-JAN-93 17-JUN-87

King 24000 17-JUN-87 17-JUN-87

14. LEAD

14.1 Syntax

LEAD(value_expr [, offset ] [, default ])

OVER ([ query_partition_clause ] order_by_clause)

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions, including valid forms of value_expr

14.2 Purpose

series of rows returned from a query and a position 

LEAD 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

of the cursor, 

LEAD

 provides access to a row at a given physical offset beyond that

position.

Lead只能用作分析函式。它提供在不使用自連線的情況下訪問表中多個行的途徑。給定要查詢的行組和一個位置指標，lead能訪問距離給定物理偏移量的行。

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 table. If you do not specify default, then its default value is null.

若不指定offset，那末其預設為1。若偏移量超出表範圍，則返回可選的default值。若沒有指定default，那末其預設為null。

You cannot use LEAD 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中使用lead或其他任何分析函式。也就是說，此處不能分析函式不能巢狀。但是可以在value_expr中使用內建函式表示式。

See Also:

"About SQL Expressions" for information on valid forms of expr and LAG

14.3 Examples

The following example provides, for each employee in the employees table, the hire date of the employee hired just after:

下面的列子提供employees表中僱傭日期恰好在當前僱員後的僱員：

SELECT last_name, hire_date,

lead(hire_date, 1) over(ORDER BY hire_date) AS "NextHired"

FROM employees

WHERE department_id = 30;

LAST_NAME HIRE_DATE NextHired

------------------------- --------- ---------

Raphaely 07-DEC-94 18-MAY-95

Khoo 18-MAY-95 24-JUL-97

Tobias 24-JUL-97 24-DEC-97

Baida 24-DEC-97 15-NOV-98

Himuro 15-NOV-98 10-AUG-99

Colmenares 10-AUG-99

15. MAX

15.1 Syntax

MAX([ DISTINCT | ALL ] expr)

[ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

15.2 Purpose

MAX returns maximum value of expr. You can use it as an aggregate or analytic function.

Max返回expr的最大值。它可用作聚集或分析函式。

See A "Abou expr, compa

lso: 

t SQL Expressions"for information on valid forms of  "Floating-Point Numbers" for information on binary-float rison semantics, and "Aggregate Functions"

15.3 Aggregate Example

The following example determines the highest salary in the hr.employees table:

下面的例子確定hr.employees表中的最高薪水：

SELECT MAX(salary) "Maximum" FROM employees;

Maximum

----------

24000

15.4 Analytic Example

The following example calculates, for each employee, the highest salary of the employees reporting to the same manager as the employee.

下面的例子計算具有相同經理的僱員的最高薪水：

SELECT manager_id, last_name, salary,

MAX(salary) over(PARTITION BY manager_id) AS mgr_max

FROM employees;

MANAGER_ID LAST_NAME SALARY MGR_MAX

---------- ------------------------- ---------- ----------

100 Kochhar 17000 17000

100 De Haan 17000 17000

100 Raphaely 11000 17000

100 Kaufling 7900 17000

100 Fripp 8200 17000

100 Weiss 8000 17000

. . .

If you enclose this query in the parent query with a predicate, then you can determine the employee who makes the highest salary in each department:

若在父查詢中使用謂詞，那末可以確定各部門中哪個僱員薪水最高：

SELECT manager_id, last_name, salary FROM (SELECT manager_id, last_name, salary,

MAX(salary) over(PARTITION BY manager_id) AS rmax_sal

FROM employees)

WHERE salary = rmax_sal;

MANAGER_ID LAST_NAME SALARY

---------- ------------------------- ----------

100 Kochhar 17000

100 De Haan 17000

101 Greenberg 12000

101 Higgens 12000

102 Hunold 9000

103 Ernst 6000

108 Faviet 9000

114 Khoo 3100

120 Nayer 3200

120 Taylor 3200

121 Sarchand 4200

122 Chung 3800

123 Bell 4000

124 Rajs 3500

145 Tucker 10000

146 King 10000

147 Vishney 10500

148 Ozer 11500

149 Abel 11000

201 Goyal 6000

205 Gietz 8300

King 24000

16. MIN

16.1 Syntax

MIN([ DISTINCT | ALL ] expr)

[ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

16.2 Purpose

MIN returns minimum value of expr. You can use it as an aggregate or analytic function.

Min返回expr的最小值。它可以用作聚集或分析函式。

See Also:

"About SQL Expressions" for information on valid forms of expr, "Floating-Point Numbers" for information on binary-float comparison semantics, and "Aggregate Functions"

16.3 Aggregate Example

The following statement returns the earliest hire date in the hr.employees table:

下面的語句返回hr.employees表中僱員最早入職時間：

SELECT MIN(hire_date) "Earliest" FROM employees;

Earliest

---------

17-JUN-87

16.4 Analytic Example

The following example determines, for each employee, the employees who were hired on or before the same date as the employee. It then determines the subset of employees reporting to the same manager as the employee, and returns the lowest salary in that subset.

下面的例子確定，同一經理下的各僱員，僱傭日期不晚於自己的僱員的最低薪水：

SELECT manager_id, last_name, hire_date, salary,

MIN(salary) over(PARTITION BY manager_id ORDER BY hire_date

RANGE unbounded preceding) AS p_cmin

FROM employees;

MANAGER_ID LAST_NAME HIRE_DATE SALARY P_CMIN

---------- ------------------------- --------- ---------- ----------

100 Kochhar 21-SEP-89 17000 17000

100 De Haan 13-JAN-93 17000 17000

100 Raphaely 07-DEC-94 11000 11000

100 Kaufling 01-MAY-95 7900 7900

100 Hartstein 17-FEB-96 13000 7900

100 Weiss 18-JUL-96 8000 7900

100 Russell 01-OCT-96 14000 7900

100 Partners 05-JAN-97 13500 7900 100 Errazuriz 10-MAR-97 12000 7900 . . .

17. NTILE

17.1 Syntax

NTILE(expr)

OVER ([ query_partition_clause ] order_by_clause)

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions, including valid forms of expr

17.2 Purpose

NTILE is an analytic function. It divides an ordered data set into a number of buckets indicated by expr and assigns the appropriate bucket number to each row. The buckets are numbered 1 through expr. The expr value must resolve to a positive constant for each partition. Oracle Database expects an integer, and if expr is a noninteger constant, then Oracle truncates the value to an integer. The return value is NUMBER.

Ntile只能用作分析函式。它將一個有序數集分成expr數目的桶，並且對每一行賦值適當的桶數。桶的取值範圍為1…expr。對每一行的位置來說expr值必須是一個正常數。 Oracle資料庫期望它是一個整數，若expr不是一個整數常量，那末Oracle將expr值截斷為一個整數。函式返回值是number型別。

The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed one for each bucket, starting with bucket 1.

桶中的行數至多相差1.餘值(行數除以桶數得到的餘數)從1號桶開始，與桶號相同的分佈在每個桶中(直到等於餘值的桶為止)。

If expr is greater than the number of rows, then a number of buckets equal to the number of rows will be filled, and the remaining buckets will be empty.

若expr比行數要大，那末行數將用作桶數，並且剩餘的桶數將被置為空(結果集中也不會顯示)。

You cannot use NTILE 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.

不能在expr中使用ntile或其他任何分析函式。也就是說，此處分析函式不能巢狀，但是可以在expr中使用內建函式表示式。

See Also:

"About SQL Expressions" for information on valid forms of expr and Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

17.3 Examples

The following example divides into 4 buckets the values in the salary column of the oe.employees table from Department 100. The salary column has 6 values in this department, so the two extra values (the remainder of 6 / 4) are allocated to buckets 1 and 2, which therefore have one more value than buckets 3 or 4.

下面的例子，在oe.employees表中，部門100的薪水值被分成4個桶。此部門的Salary 列有6個值，因此兩個額外的值(6/4的餘數)被分配給桶1和桶2，因此它們比桶3和桶 4多一個值。

SELECT last_name, salary, ntile(4) over(ORDER BY salary DESC) AS quartile

FROM employees

WHERE department_id = 100;

LAST_NAME SALARY QUARTILE

------------------------- ---------- ----------

Greenberg 12000 1

Faviet 9000 1

Chen 8200 2

Urman 7800 2

Sciarra 7700 3

Popp 6900 4

18. PERCENT_RANK

18.1 Aggregate Syntax percent_rank_aggregate::=

PERCENT_RANK(expr [, expr ]...) WITHIN GROUP

(ORDER BY expr [ DESC | ASC ]

[NULLS { FIRST | LAST } ]

[, expr [ DESC | ASC ]

[NULLS { FIRST | LAST } ] ]...

)

18.2 Analytic Syntax percent_rank_analytic::=

PERCENT_RANK( )

OVER ([ query_partition_clause ] order_by_clause)

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

18.3 Purpose

PERCENT_RANK is similar to the CUME_DIST (cumulative distribution) function. The range of values returned by PERCENT_RANK is 0 to 1, inclusive. The first row in any set has a PERCENT_RANK of 0. The return value is NUMBER.

Percent_rank與cume_dist是類似的(累計分佈)函式。Percent_rank返回0到1 的數值。任意集合的第一行percent_rank值都為0。函式返回一個number值。

See A

Table implici

lso: 

2-10, "Implicit Type Conversion Matrix" for more information on t conversion

hetical row 

? As an aggregate function, PERCENT_RANK calculates, for a hypotr

identified by the arguments of the function and a corresponding sort specification, the rank of row r minus 1 divided by the number of rows in the aggregate group.

This calculation is made as if the hypothetical row 

r

 were inserted into the group

of rows over which Oracle Database is to aggregate. 

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.

當percent_rank用作聚集函式時，它計算，對一個由函式引數和相應排序規則確定的假定行r，用行r在聚集分組中的排名減去1，再除以行數。Oracle對此聚集組進行計算時，就像假定行r被插入計算的行組中一樣。函式引數確定了各個聚集分組中唯一的假定行。常量參數列達式與聚集分組的order by子句中的表示式根據位置匹配。因此，引數個數必須相同且型別必須相容。

? As an analytic function, for a row r, PERCENT_RANK calculates the rank of r minus 1, divided by 1 less than the number of rows being evaluated (the entire query result set or a partition).

當percent_rank用作分析函式時，對行r，percent_rank計算r的排名，再用排名減去1，若得到的結果小於(整個查詢結果集或一個分組)行數，再用1除以此結果。

18.4 Aggregate Example

The following example calculates the percent rank of a hypothetical employee in the sample table hr.employees with a salary of $15,500 and a commission of 5%:

下面的例子計算hr.exployees表中薪水為$15500並且佣金為5％的假想僱員百分比排名：

SELECT percent_rank(15000, .05) within

GROUP(

ORDER BY salary, commission_pct) "Percent-Rank"

FROM employees;

Percent-Rank

------------

.971962617

18.5 Analytic Example

The following example calculates, for each employee, the percent rank of the employee's salary within the department:

下面的例子計算各部門中僱員薪水的百分比排名：

SELECT department_id, last_name, salary,

percent_rank() over(PARTITION BY department_id ORDER BY salary

DESC) AS pr

FROM employees

ORDER BY pr, salary;

DEPARTMENT_ID LAST_NAME SALARY PR ------------- ------------------------- ---------- ----------

10 Whalen 4400 0

40 Marvis 6500 0

. . .

80 Vishney 10500 .176470588

50 Everett 3900 .181818182 30 Khoo 3100 .2

. . .

80 Johnson 6200 .941176471

50 Markle 2200 .954545455

50 Philtanker 2200 .954545455

50 Olson 2100 1 . . .

19. PERCENTILE_CONT

19.1 Syntax

PERCENTILE_CONT(expr) WITHIN GROUP

(ORDER BY expr [ DESC | ASC ])

[ OVER (query_partition_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions of the OVER clause

19.2 Purpose

PERCENTILE_CONT is an inverse distribution function that assumes a continuous distribution model. It takes a percentile value and a sort specification, and returns an interpolated value that would fall into that percentile value with respect to the sort specification. Nulls are ignored in the calculation.

Percentile_cont是一個採用連續分佈模型的反分佈函式。它輸入一個百分點值並根據相關的排序規則，計算後返回一個內插值，這個值由排序規則相關的百分點值確定。函式計算時忽略空值。

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

The first expr must evaluate to a numeric value between 0 and 1, because it is a percentile value. This expr must be constant within each aggregation group. The ORDER BY clause takes a single expression that must be a numeric or datetime value, as these are the types over which Oracle can perform interpolation.

第一個expr的值必須是0到1的數字，因為它是百分點值。在各聚集分組中expr必須為常數。Order by子句中只能使用一個數字或日期表示式，因為Oracle只支援這些資料型別的內插。

The result of PERCENTILE_CONT is computed by linear interpolation between values after ordering them. Using the percentile value (P) and the number of rows (N) in the aggregation group, we compute the row number we are interested in after ordering the rows with respect to the sort specification. This row number (RN) is computed according to the formula RN = (1+ (P*(N-1)). The final result of the aggregate function is computed by linear interpolation between the values from rows at row numbers CRN = CEILING(RN) and FRN = FLOOR(RN).

Percentile_cont結果是由值和排序後的值之間線性內插計算出來的。使用百分點值(P) 與聚集分組的行數(N)，計算按照排序規則排序後的行號。行號(RN)根據RN = (1+ (P*(N-1))計算而得。聚集函式最終的結果是通過行號的行值CRN = CEILING(RN)與 FRN = FLOOR(RN)之間線性內插計算而得。

The final result will be:

最終的結果將是：

If (CRN = FRN = RN) then the result is

(value of expression from row at RN)

Otherwise the result is

(CRN - RN) * (value of expression for row at FRN) +

(RN - FRN) * (value of expression for row at CRN)

You can use the PERCENTILE_CONT function as an analytic function. You can specify only the query_partitioning_clause in its OVER clause. It returns, for each row, the value that would fall into the specified percentile among a set of values within each partition.

Percentile_cont可用作分析函式。可以在over子句中僅指定 query_partitioning_clause。對每一行，它返回屬於每個分組中的一組值的指定百分點。

The MEDIAN function is a specific case of PERCENTILE_CONT where the percentile value defaults to 0.5. For more information, please refer to MEDIAN.

Median函式是percentile_count函式的特例，百分點值預設為0.5。更多的資訊請參閱median。

19.3 Aggregate Example

The following example computes the median salary in each department:

下面的例子計算每個部門的中值薪水：

SELECT department_id, percentile_cont(0.5) within

GROUP(

ORDER BY salary DESC) "Median cont", percentile_disc(0.5) within

GROUP(

ORDER BY salary DESC) "Median disc"

FROM employees

GROUP BY department_id;

DEPARTMENT_ID Median-cont Median-disc

------------- ----------- -----------

10 4400 4400

20 9500 13000

30 2850 2900

40 6500 6500

50 3100 3100

60 4800 4800

70 10000 10000

80 8800 8800

90 17000 17000

100 8000 8200

110 10150 12000

PERCENTILE_CONT and PERCENTILE_DISC may return different results.

PERCENTILE_CONT returns a computed result after doing linear interpolation. PERCENTILE_DISC simply returns a value from the set of values that are aggregated over. When the percentile value is 0.5, as in this example, PERCENTILE_CONT returns the average of the two middle values for groups with even number of elements, whereas PERCENTILE_DISC returns the value of the first one among the two middle values. For aggregate groups with an odd number of elements, both functions return the value of the middle element.

Percentile_count和percentile_disc可能返回不同的結果。Percentile_count

返回線性內插後的計算結果。Percentile_disc僅從聚集的一組值中返回一個值。當百分點值為0.5時，正如下面的例子，percentile_cont返回分組中偶數位元素兩個中值的平均值，然而percentile_disc返回這兩個中值的第一個值。對於奇數位元素的聚集分組，兩個函式都返回中值元素的值。

19.4 Analytic Example

In the following example, the median for Department 60 is 4800, which has a corresponding percentile (Percent_Rank) of 0.5. None of the salaries in Department 30 have a percentile of 0.5, so the median value must be interpolated between 2900 (percentile 0.4) and 2800 (percentile 0.6), which evaluates to 2850.

在下面的例子，部門60的中值為4800，與之相應的百分點值為0.5。在部門30中沒有薪水百分點為0.5的人，因此必須插入一個2900到2800的中值，這個值為2850。

SELECT last_name, salary, department_id, percentile_cont(0.5) within

GROUP(

ORDER BY salary DESC) over(PARTITION BY department_id)

"Percentile_Cont", percent_rank() over(PARTITION BY department_id

ORDER BY salary DESC) "Percent_Rank"

FROM employees

WHERE department_id IN (30, 60);

LAST_NAME SALARY DEPARTMENT_ID Percentile_Cont Percent_Rank

------------- ---------- ------------- --------------- ------------ Raphaely 11000 30 2850 0

Khoo 3100 30 2850 .2

Baida 2900 30 2850 .4

Tobias 2800 30 2850 .6

Himuro 2600 30 2850 .8

Colmenares 2500 30 2850 1

Hunold 9000 60 4800 0

Ernst 6000 60 4800 .25

Austin 4800 60 4800 .5

Pataballa 4800 60 4800 .5

Lorentz 4200 60 4800 1

20. PERCENTILE_DISC

20.1 Syntax

PERCENTILE_DISC(expr) WITHIN GROUP

(ORDER BY expr [ DESC | ASC ])

[ OVER (query_partition_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions of the OVER clause

20.2 Purpose

PERCENTILE_DISC is an inverse distribution function that assumes a discrete distribution model. It takes a percentile value and a sort specification and returns an element from the set. Nulls are ignored in the calculation.

Percentile_disc是一個採用連續分佈模型的反分佈函式。它輸入一個百分點值並根據相關的排序規則，計算後返回一個內插值，這個值由排序規則相關的百分點值確定。函式計算時忽略空值。

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

The first expr must evaluate to a numeric value between 0 and 1, because it is a percentile value. This expression must be constant within each aggregate group. The

ORDER BY clause takes a single expression that can be of any type that can be sorted.

第一個expr的值必須是0到1的數字，因為它是百分點值。在各聚集分組中expr必須為常數。Order by子句中只能使用一個表示式，該表示式值的型別可以是任何可排序型別。

For a given percentile value P, PERCENTILE_DISC sorts the values of the expression in the ORDER BY clause and returns the value with the smallest CUME_DIST value (with respect to the same sort specification) that is greater than or equal to P.

對於一個假定的百分點值P，percentile_dist根據order by子句中的表示式值排序，並返回一個大於等於P的最小的cume_dist值(與相同的排序規則相關)。

20.3 Aggregate Example

See aggregate example for PERCENTILE_CONT.

參見percentile_count聚集函式的例子。

20.4 Analytic Example

The following example calculates the median discrete percentile of the salary of each employee in the sample table hr.employees:

下面的例子計算hr.employees表中每個僱員薪水的中值離散百分點：

SELECT last_name, salary, department_id, percentile_disc(0.5) within

GROUP(

ORDER BY salary DESC) over(PARTITION BY department_id)

"Percentile_Disc", cume_dist() over(PARTITION BY department_id

ORDER BY salary DESC) "Cume_Dist"

FROM employees

WHERE department_id IN (30, 60);

LAST_NAME SALARY DEPARTMENT_ID Percentile_Disc Cume_Dist

------------- ---------- ------------- --------------- ----------

Raphaely 11000 30 2900 .166666667

Khoo 3100 30 2900 .333333333

Baida 2900 30 2900 .5

Tobias 2800 30 2900 .666666667

Himuro 2600 30 2900 .833333333

Colmenares 2500 30 2900 1

Hunold 9000 60 4800 .2

Ernst 6000 60 4800 .4

Austin 4800 60 4800 .8

Pataballa 4800 60 4800 .8 Lorentz 4200 60 4800 1

The median value for Department 30 is 2900, which is the value whose corresponding percentile (Cume_Dist) is the smallest value greater than or equal to 0.5. The median value for Department 60 is 4800, which is the value whose corresponding percentile is the smallest value greater than or equal to 0.5.

部門30的中值為2900，相應的百分點(Cume_dist)是大於等於0.5的最小值。部門60的中值為4800，相應的百分點是大於等於0.5的最小值。

21. RANK

21.1 Aggregate Syntax rank_aggregate::=

RANK(expr [, expr ]...) WITHIN GROUP

(ORDER BY expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ]

[, expr [ DESC | ASC ]

[ NULLS { FIRST | LAST } ] ]...

)

21.2 Analytic Syntax rank_analytic::=

RANK( )

OVER ([ query_partition_clause ] order_by_clause)

See A

lso:

"Analytic Functions" for information on syntax, semantics, and restrictions

21.3 Purpose

RANK calculates the rank of a value in a group of values. The return type is NUMBER.

Rank計算一組值的排名。它的返回型別為number。

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence

Rows with equal values for the ranking criteria receive the same rank. Oracle Database then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. This function is useful for top-N and bottom-N reporting.

相等值的行排名相同。Oracle資料庫計算當前排名佔據的行數加上當前排名得到下一個排名。因此，排名可能是不連續的數字。此函式對於求top-N和bottom-N報表很有用處。

? As an aggregate function, RANK calculates the 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 their types must be compatible.

Rank用作聚集函式時，它計算一個被帶有排序規則的函式引數確定的假定行的排名。函式引數必須對每個聚集組中的常量表示式全部求值。常量參數列達式和聚集中的order by子句中的表示式通過位置匹配。因此，引數個數必須相等且型別必須相容。

? As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

Rank用作分析函式時，它計算由order_by_clause中的value_expr值確定返回的查詢結果的每一行的排名。

21.4 Aggregate Example

The following example calculates the rank of a hypothetical employee in the sample table hr.employees with a salary of $15,500 and a commission of 5%:

下面的例子計算hr.employees表中薪水為$15500和佣金為5％的僱員的排名：

SELECT rank(15500, .05) within

GROUP(

ORDER BY salary, commission_pct) "Rank"

FROM employees;

Rank

----------

105

Similarly, the following query returns the rank for a $15,500 salary among the employee salaries:

相似地，下面的查詢返回薪水為$15500的僱員的排名：

SELECT rank(15500) within

GROUP(

ORDER BY salary DESC) "Rank of 15500"

FROM employees;

Rank of 15500

--------------

4

21.5 Analytic Example

The following statement ranks the employees in the sample hr schema in department 80 based on their salary and commission. Identical salary values receive the same rank and cause nonconsecutive ranks. Compare this example with the example for DENSE_RANK.

下面的語句對hr模式emplooyes表中部門80，以薪水和佣金排名。相同的薪水值有相同的排名，並且導致排名的不連續。請比較densce_rank例子。

SELECT department_id, last_name, salary, commission_pct, rank() over(PARTITION BY department_id ORDER BY salary DESC, commission_pct) "Rank" FROM employees

WHERE department_id = 80;

DEPARTMENT_ID LAST_NAME SALARY COMMISSION_PCT Rank

------------- ------------------------- ---------- -------------- ----------

80 Russell 14000 .4 1

80 Partners 13500 .3 2

80 Errazuriz 12000 .3 3

80 Ozer 11500 .25 4

80 Cambrault 11000 .3 5

80 Abel 11000 .3 5

80 Zlotkey 10500 .2 7

80 Vishney 10500 .25 8

80 Bloom 10000 .2 9

80 Tucker 10000 .3 10

80 King 10000 .35 11

80 Fox 9600 .2 12

80 Greene 9500 .15 13

80 Bernstein 9500 .25 14

80 Sully 9500 .35 15

80 Hall 9000 .25 16

80 McEwen 9000 .35 17

80 Hutton 8800 .25 18

80 Taylor 8600 .2 19

80 Livingston 8400 .2 20

80 Olsen 8000 .2 21

80 Smith 8000 .3 22

80 Cambrault 7500 .2 23

80 Doran 7500 .3 24

80 Smith 7400 .15 25

80 Bates 7300 .15 26

80 Marvins 7200 .1 27

80 Tuvault 7000 .15 28

80 Sewall 7000 .25 29

80 Lee 6800 .1 30

80 Ande 6400 .1 31

80 Banda 6200 .1 32

80 Johnson 6200 .1 32

80 Kumar 6100 .1 34

22. RATIO_TO_REPORT

22.1 Syntax

RATIO_TO_REPORT(expr)

OVER ([ query_partition_clause ])

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions, including valid forms of expr

22.2 Purpose

RATIO_TO_REPORT is an analytic function. It computes the ratio of a value to the sum of a set of values. If expr evaluates to null, then the ratio-to-report value also evaluates to null.

Ratio_to_report只能用作分析函式。它計算一個值在一組值總和中佔的比率。若expr 求值為null，那末ratio_to_report值也為null。

The set of values is determined by the query_partition_clause. If you omit that clause, then the ratio-to-report is computed over all rows returned by the query.

組值由query_partition_clause確定。若忽略該子句，那末ratio_to_report計算查詢返回的所有行。

You cannot use RATIO_TO_REPORT 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中不能使用ratio_to_report或其他任何分析函式。也就是說，此處分析函式不能巢狀，但是在expr中可以使用內建函式表示式。請參閱About SQL Expressions 獲取合法expr的更多資訊。

22.3 Examples

The following example calculates the ratio-to-report value of each purchasing clerk's salary to the total of all purchasing clerks' salaries:

下面的例子計算每個採購員薪水在整個採購員薪水總和中的ratio-to-report值：

SELECT last_name, salary, ratio_to_report(salary) over() AS rr

FROM employees

WHERE job_id = 'PU_CLERK';

LAST_NAME SALARY RR

------------------------- ---------- ----------

Khoo 3100 .223021583

Baida 2900 .208633094

Tobias 2800 .201438849

Himuro 2600 .18705036

Colmenares 2500 .179856115 23. REGR_ (LINEAR REGRESSION) FUNCTIONS

The linear regression functions are:

線性迴歸函式包括：

? REGR_SLOPE

? REGR_INTERCEPT

? REGR_COUNT

? REGR_R2

? REGR_AVGX

? REGR_AVGY

? REGR_SXX ? REGR_SYY

? REGR_SXY 23.1 Syntax linear_regr::=

{ REGR_SLOPE

| REGR_INTERCEPT

| REGR_COUNT

| REGR_R2

| REGR_AVGX

| REGR_AVGY

| REGR_SXX

| REGR_SYY

| REGR_SXY

}

(expr1 , expr2)

[ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

23.2 Purpose

The linear regression functions fit an ordinary-least-squares regression line to a set of number pairs. You can use them as both aggregate and analytic functions.

線性迴歸函式適用於一組數值對的最小二乘法迴歸線。它可用作聚集和分析函式。

See Also:

"Aggregate Functions" and "About SQL Expressions" for information on valid forms of expr

These functions take 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 applies the function to the set of (expr1, expr2) pairs after eliminating all pairs for which either expr1 or expr2 is null. Oracle computes all the regression functions simultaneously during a single pass through the data.

Oracle使用該函式前先排除(expr1,expr2)中expr1或expr2不為null數值對。 Oracle在資料單個傳遞期間計算所有的迴歸函式。

expr1 is interpreted as a value of the dependent variable (a y value), and expr2 is interpreted as a value of the independent variable (an x value).

Expr1是因變數(作為y值)，expr2是自變數(作為x值)。

? REGR_SLOPE returns the slope of the line. The return value is a numeric datatype and can be null. After the elimination of null (expr1, expr2) pairs, it makes the following computation:

REGR_SLOPE 返回行的斜率。返回值是一個數字型別並能為空。在排除(expr1， expr2) 中的null對後，它作如下的計算：

COVAR_POP(expr1, expr2) / VAR_POP(expr2)

? REGR_INTERCEPT returns the y-intercept of the regression line. The return value is a numeric datatype and can be null. After the elimination of null (expr1, expr2) pairs, it makes the following computation:

REGR_INTERCEPT 返回迴歸線的y 軸截距。返回值是一個數字型別並能為空。在排除 (expr1，expr2) 中的null對後，它作如下的計算：

AVG(expr1) - REGR_SLOPE(expr1, expr2) * AVG(expr2)

? REGR_COUNT returns an integer that is the number of non-null number pairs used to fit the regression line.

REGR_COUNT 返回用於匹配迴歸線的非空的數字對的個數。

? REGR_R2 returns the coefficient of determination (also called R-squared or goodness of fit) for the regression. The return value is a numeric datatype and can be null. VAR_POP(expr1) and VAR_POP(expr2) are evaluated after the elimination of null pairs. The return values are:

REGR_R2 返回迴歸確定係數（也稱為R_squared或完全匹配) 。返回值是一個數字型別並能為空。 VAR_POP(expr1) 和 VAR_POP(expr2)在null對排除後

進行求值。返回值是：

NULL if VAR_POP(expr2) = 0

若VAR_POP(expr2) = 0，值為null

1 if VAR_POP(expr1) = 0 and VAR_POP(expr2) != 0

若VAR_POP(expr1) = 0且VAR_POP(expr2) != 0，值為1

POWER(CORR(expr1,expr),2) if VAR_POP(expr1) > 0 and VAR_POP(expr2) != 0

若VAR_POP(expr1) > 0且VAR_POP(expr2) != 0，值為 POWER(CORR(expr1,expr),2)

All of the remaining regression functions return a numeric datatype and can be null:

所有餘下的迴歸函式返回數字型別並能為空：

? REGR_AVGX evaluates the average of the independent variable (expr2) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs:

REGR_AVGX 計算迴歸直線自變數 (expr2) 的平均值。它排除 (expr1，expr2) 中的null對後作如下計算：

AVG(expr2)

? REGR_AVGY evaluates the average of the dependent variable (expr1) of the regression line. It makes the following computation after the elimination of null (expr1, expr2) pairs:

REGR_AVGY計算迴歸直線因變數 (expr1) 的平均值。它排除(expr1，expr2) 中的null對後作如下計算：

AVG(expr1)

REGR_SXY, REGR_SXX, REGR_SYY are auxiliary functions that are used to compute various diagnostic statistics.

REGR_SXY，REGR_SXX，REGR_SYY是用來計算各種診斷統計的輔助函式。

? REGR_SXX makes the following computation after the elimination of null (expr1, expr2) pairs:

REGR_SXX 在排除(expr1，expr2)中的null對後作如下計算：

REGR_COUNT(expr1, expr2) * VAR_POP(expr2)

? REGR_SYY makes the following computation after the elimination of null (expr1, expr2) pairs:

REGR_SXY 在排除(expr1，expr2)中的null對後作如下計算：

REGR_COUNT(expr1, expr2) * VAR_POP(expr1)

? REGR_SXY makes the following computation after the elimination of null (expr1, expr2) pairs:

REGR_SXY 在排除(expr1，expr2)中的null對後作如下計算：

REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)

The following examples are based on the sample tables sh.sales and sh.products.

下面的例子基於示例表 sh.sales 和 sh.products。

23.3 General Linear Regression Example

The following example provides a comparison of the various linear regression functions used in their analytic form. The analytic form of these functions can be useful when you want to use regression statistics for calculations such as finding the salary predicted for each employee by the model. The sections that follow on the individual linear regression functions contain examples of the aggregate form of these functions.

下面的示例比較了具有各種不同分析形式的線性迴歸函式。對計算諸如使用模型預測僱員薪水增長之類的迴歸統計，這些分析函式十分有用。下面單獨的線性迴歸函式部分包含這些函式的聚合形式的示例。

SELECT job_id, employee_id id, salary,

regr_slope(SYSDATE - hire_date, salary) over(PARTITION BY job_id) slope,

regr_intercept(SYSDATE - hire_date, salary) over(PARTITION BY job_id) intcpt,

regr_r2(SYSDATE - hire_date, salary) over(PARTITION BY job_id) rsqr,

regr_count(SYSDATE - hire_date, salary) over(PARTITION BY job_id) COUNT,

regr_avgx(SYSDATE - hire_date, salary) over(PARTITION BY job_id) avgx,

regr_avgy(SYSDATE - hire_date, salary) over(PARTITION BY job_id) avgy

FROM employees

WHERE department_id IN (50, 80)

ORDER BY job_id, employee_id;

JOB_ID ID SALARY SLOPE INTCPT RSQR COUNT AVGX AVGY

---------- ----- ---------- ----- --------- ----- ------ ---------- ---------

SA_MAN 145 14000 .355 -1707.035 .832 5 12200.000 2626.589

SA_MAN 146 13500 .355 -1707.035 .832 5 12200.000 2626.589 SA_MAN 147 12000 .355 -1707.035 .832 5 12200.000 2626.589 SA_MAN 148 11000 .355 -1707.035 .832 5 12200.000 2626.589

SA_MAN 149 10500 .355 -1707.035 .832 5 12200.000 2626.589

SA_REP 150 10000 .257 404.763 .647 29 8396.552 2561.244

SA_REP 151 9500 .257 404.763 .647 29 8396.552 2561.244

SA_REP 152 9000 .257 404.763 .647 29 8396.552 2561.244

SA_REP 153 8000 .257 404.763 .647 29 8396.552 2561.244

SA_REP 154 7500 .257 404.763 .647 29 8396.552 2561.244

SA_REP 155 7000 .257 404.763 .647 29 8396.552 2561.244 SA_REP 156 10000 .257 404.763 .647 29 8396.552 2561.244 ...

23.4 REGR_SLOPE and REGR_INTERCEPT Examples

The following example calculates the slope and regression of the linear regression model for time employed (SYSDATE - hire_date) and salary using the sample table hr.employees. Results are grouped by job_id.

下面的例子計算表 hr.employees中僱傭時間 (SYSDATE-hire_date) 和薪水的斜率和線性迴歸模型的迴歸值。結果按job_id分組。

SELECT job_id,

regr_slope(SYSDATE - hire_date, salary) slope, regr_intercept(SYSDATE - hire_date, salary) intercept

FROM employees

WHERE department_id IN (50, 80)

GROUP BY job_id

ORDER BY job_id;

JOB_ID SLOPE INTERCEPT

---------- ----- ------------

SA_MAN .355 -1707.030762

SA_REP .257 404.767151

SH_CLERK .745 159.015293

ST_CLERK .904 134.409050

ST_MAN .479 -570.077291

23.5 REGR_COUNT Examples

The following example calculates the count of by job_id for time employed (SYSDATE - hire_date) and salary using the sample table hr.employees. Results are grouped by job_id.

下面的例子計算，hr.employees表中，僱傭時間 (SYSDATE-hire_date)和薪水按 job_id計數。結果按job_id分組。

SELECT job_id, regr_count(SYSDATE - hire_date, salary) COUNT

FROM employees

WHERE department_id IN (30, 50)

GROUP BY job_id;

JOB_ID COUNT

---------- ------

ST_MAN 5

PU_MAN 1

SH_CLERK 20

PU_CLERK 5

ST_CLERK 20

23.6 REGR_R2 Examples

The following example calculates the coefficient of determination the linear regression of time employed (SYSDATE - hire_date) and salary using the sample table hr.employees:

下面的例子計算，hr.employees表中，僱傭時間 (SYSDATE-hire_date)和薪水線性

迴歸的確定係數：

SELECT job_id, regr_r2(SYSDATE - hire_date, salary) regr_r2

FROM employees

WHERE department_id IN (80, 50)

GROUP BY job_id;

JOB_ID REGR_R2

---------- -----------

ST_MAN .694185080

SH_CLERK .879799698

SA_MAN .832447480

SA_REP .647007156

ST_CLERK .742808493

23.7 REGR_AVGY and REGR_AVGX Examples

The following example calculates the average values for time employed (SYSDATE - hire_date) and salary using the sample table hr.employees. Results are grouped by job_id:

下面的例子計算，hr.employees表中，僱傭時間 (SYSDATE-hire_date)和薪水的平均值。結果按job_id分組：

SELECT job_id,

regr_avgy(SYSDATE - hire_date, salary) avgy, regr_avgx(SYSDATE - hire_date, salary) avgx

FROM employees

WHERE department_id IN (30, 50)

GROUP BY job_id;

JOB_ID AVGY AVGX

---------- --------------- ------

ST_MAN 2899.055555556 7280

PU_MAN 3785.455555556 11000

SH_CLERK 2531.955555556 4925

PU_CLERK 2709.255555556 2780

ST_CLERK 2631.605555556 2785

23.8 REGR_SXY, REGR_SXX, and REGR_SYY Examples

The following example calculates three types of diagnostic statistics for the linear regression of time employed (SYSDATE - hire_date) and salary using the sample table hr.employees:

下面的列子計算，hr.employees表中，僱傭時間 (SYSDATE-hire_date)和薪水的三

種線性迴歸型別的診斷統計：

SELECT job_id,

regr_sxy(SYSDATE - hire_date, salary) regr_sxy, regr_sxx(SYSDATE - hire_date, salary) regr_sxx, regr_syy(SYSDATE - hire_date, salary) regr_syy

FROM employees

WHERE department_id IN (80, 50)

GROUP BY job_id

ORDER BY job_id;

JOB_ID REGR_SXY REGR_SXX REGR_SYY ---------- ---------- ----------- ---------SA_MAN 3303500 9300000.0 1409642 SA_REP 16819665.5 65489655.2 6676562.55 SH_CLERK 4248650 5705500.0 3596039 ST_CLERK 3531545 3905500.0 4299084.55

ST_MAN 2180460 4548000.0 1505915.2

24. ROW_NUMBER

24.1 Syntax

ROW_NUMBER( )

OVER ([ query_partition_clause ] order_by_clause)

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

24.2 Purpose

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

Row_number只可用作分析函式。它作用於按order_by_claus排序的已排序行組中，從1開始為每一行(或者為一個分組中的各行，或者為查詢返回的各行)分配一個唯一的數字。

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.

在查詢內部巢狀包含row_number的子查詢，並在外部查詢中限定row_number值的範圍，可以得到內部查詢的精確結果。此函式可用於實現top-N、 bottom-N 和inner-N 報表輸出。為了使結果一致，查詢必須確保排序後結果唯一。

You cannot use ROW_NUMBER 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中不能使用row_number 或其他任何分析函式。也就是此處分析函式不能巢狀。但是可以在expr中使用內建函式表示式。請參閱About SQL Expressions獲取合法 expr的更多資訊。

24.3 Examples

For each department in the sample table oe.employees, the following example assigns numbers to each row in order of employee's hire date: 下面的例子在oe.employees表中，各部門按僱員僱傭日期排序後賦予每行一個數：

SELECT department_id, last_name, employee_id,

row_number() over(PARTITION BY department_id ORDER BY employee_id) AS emp_id

FROM employees;

DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID

------------- ------------------------- ----------- ----------

10 Whalen 200 1

20 Hartstein 201 1

20 Fay 202 2

30 Raphaely 114 1

30 Khoo 115 2

30 Baida 116 3

30 Tobias 117 4

30 Himuro 118 5

30 Colmenares 119 6

40 Mavris 203 1

. . .

100 Popp 113 6

110 Higgins 205 1

110 Gietz 206 2

ROW_NUMBER is a nondeterministic function. However, employee_id is a unique key, so the results of this application of the function are deterministic.

Row_number是非確定性函式。然而，這裡exployee_id是唯一鍵值，因此應用函式的結果是確定的。

See Also:

FIRST_VALUE and LAST_VALUE for examples of nondeterministic behavior

The following inner-N query selects all rows from the employees table but returns only the fifty-first through one-hundredth row:

下面的inner-N查詢，選擇employees表中所有行，但是結果集只返回51到100行：

SELECT last_name

FROM (SELECT last_name, row_number() over(ORDER BY last_name) r

FROM employees)

WHERE r BETWEEN 51 AND 100;

25. STDDEV

25.1 Syntax

STDDEV([ DISTINCT | ALL ] expr)

[ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

25.2 Purpose

STDDEV returns the sample standard deviation of expr, a set of numbers. You can use it as both an aggregate and analytic function. It differs from STDDEV_SAMP in that STDDEV returns zero when it has only 1 row of input data, whereas STDDEV_SAMP returns null.

Stddev返回expr的樣本標準偏差。它可用作聚集和分析函式。它與stddev_samp的不同之處在於，當計算的輸入資料只有一行時，stddev返回0，而stddev_samp返回null。

Oracle Database calculates the standard deviation as the square root of the variance defined for the VARIANCE aggregate function.

Oracle資料庫中，標準偏差計算結果與variance用作集聚函式計算結果的平方根相等。

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: 

? "Aggregate Functions", VARIANCE, and STDDEV_SAMP 

? "About SQL Expressions" for information on valid forms of expr 

25.3 Aggregate Examples

The following example returns the standard deviation of the salaries in the sample hr.employees table:

下面的例子返回hr.employees表中薪水的標準偏差：

SELECT STDDEV(salary) "Deviation" FROM employees;

Deviation

----------

3909.36575

25.4 Analytic Examples

The query in the following example returns the cumulative standard deviation of the salaries in Department 30 in the sample table hr.employees, ordered by hire_date:

下面的查詢示例返回hr.employees表中部門30按照hire-date排序後薪水的累積標準偏差：

SELECT last_name, salary, STDDEV(salary) over(ORDER BY hire_date)

"StdDev"

FROM employees

WHERE department_id = 30;

LAST_NAME SALARY StdDev

------------------------- ---------- ----------

Raphaely 11000 0

Khoo 3100 5586.14357

Tobias 2800 4650.0896

Baida 2900 4035.26125

Himuro 2600 3649.2465

Colmenares 2500 3362.58829

26. STDDEV_POP

26.1 Syntax

STDDEV_POP(expr)

[ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

26.2 Purpose

STDDEV_POP computes the population standard deviation and returns the square root of the population variance. You can use it as both an aggregate and analytic function.

Stddev_pop計算總體標準偏差並返回總體方差的平方根。它可用作聚集和分析函式。

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

This function is the same as the square root of the VAR_POP function. When VAR_POP returns null, this function returns null.

該函式與var_pop函式的平方根等價。當var_pop返回null時，該函式也返回null。

See Also: 

? "Aggregate Functions" and VAR_POP 

? "About SQL Expressions" for information on valid forms of expr 

26.3 Aggregate Example

The following example returns the population and sample standard deviations of the amount of sales in the sample table sh.sales:

下面的例子返回sh.sales表中銷售總量的總體和樣本標準偏差：

SELECT stddev_pop(amount_sold) "Pop", stddev_samp(amount_sold)

"Samp"

FROM sales;

Pop Samp

---------- ----------

896.355151 896.355592

26.4 Analytic Example

The following example returns the population standard deviations of salaries in the sample hr.employees table by department:

下面的例子返回hr.employees表中部門薪水的總體標準偏差：

SELECT department_id, last_name, salary,

stddev_pop(salary) over(PARTITION BY department_id) AS pop_std FROM employees;

DEPARTMENT_ID LAST_NAME SALARY POP_STD

------------- ------------------------- ---------- ----------

10 Whalen 4400 0 20 Hartstein 13000 3500 20 Goyal 6000 3500

. . .

100 Sciarra 7700 1644.18166

100 Urman 7800 1644.18166

100 Popp 6900 1644.18166

110 Higgens 12000 1850

110 Gietz 8300 1850

27. STDDEV_SAMP

27.1 Syntax

STDDEV_SAMP(expr)

[ OVER (analytic_clause) ]

See A

"Analy

lso: 

tic Functions" for information on syntax, semantics, and

restrictions

27.2 Purpose

STDDEV_SAMP computes the cumulative sample standard deviation and returns the square root of the sample variance. You can use it as both an aggregate and analytic function.

Stddev_samp計算累積樣本標準偏差並返回樣本方差的平方根。它可以用作聚集和分析函式。

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

This function is same as the square root of the VAR_SAMP function. When VAR_SAMP returns null, this function returns null.

該函式與var_samp函式的平方根等價。當var_samp返回null時，此函式也返回null。

See Also: 

? "Aggregate Functions" and VAR_SAMP 

? "About SQL Expressions" for information on valid forms of expr 

27.3 Aggregate Example

Please refer to the aggregate example for STDDEV_POP.

請參閱stddev_pop聚集函式的例子。

27.4 Analytic Example

The following example returns the sample standard deviation of salaries in the employees table by department:

下面的例子返回employees表中部門薪水的樣本標準偏差：

SELECT department_id, last_name, hire_date, salary,

stddev_samp(salary) over(PARTITION BY department_id ORDER BY hire_date rows BETWEEN unbounded preceding AND CURRENT ROW) AS cum_sdev FROM employees;

DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV

------------- --------------- --------- ---------- ----------

10 Whalen 17-SEP-87 4400

20 Hartstein 17-FEB-96 13000

20 Goyal 17-AUG-97 6000 4949.74747 30 Raphaely 07-DEC-94 11000

30 Khoo 18-MAY-95 3100 5586.14357

30 Tobias 24-JUL-97 2800 4650.0896

30 Baida 24-DEC-97 2900 4035.26125

. . .

100 Chen 28-SEP-97 8200 2003.33056

100 Sciarra 30-SEP-97 7700 1925.91969

100 Urman 07-MAR-98 7800 1785.49713

100 Popp 07-DEC-99 6900 1801.11077 110 Higgens 07-JUN-94 12000

110 Gietz 07-JUN-94 8300 2616.29509

28. SUM

28.1 Syntax

SUM([ DISTINCT | ALL ] expr)

[ OVER (analytic_clause) ]

See Also:

"Analytic Functions" for information on syntax, semantics, and restrictions

28.2 Purpose

SUM returns the sum of values of expr. You can use it as an aggregate or analytic function.

Sum返回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"

28.3 Aggregate Example

The following example calculates the sum of all salaries in the sample hr.employees table:

下面的例子計算hr.employees表中僱員薪水之和：

SELECT SUM(salary) "Total" FROM employees;

Total

----------

691400

28.4 Analytic Example

The following example calculates, for each manager in the sample table hr.employees, a cumulative total of salaries of employees who answer to that manager that are equal to or less than the current salary. You can see that Raphaely and Cambrault have the same cumulative total. This is because Raphaely and Cambrault have the identical salaries, so Oracle Database adds together their salary values and applies the same cumulative total to both rows.

下面的例子計算在hr.employees表中具有同一經理的僱員中薪水小於等於當前僱員的薪水的僱員薪水的累積總值。可以看到Rahaely和Cambrault有相同的累積總值，這是因為Raphaely和Cambrault有相同的薪水，故Oracle資料庫將它們的薪水值同時累加得到這兩行的累積總值。

SELECT manager_id, last_name, salary,

SUM(salary) over(PARTITION BY manager_id ORDER BY salary RANGE unbounded preceding) l_csum

FROM employees;

MANAGER_ID LAST_NAME SALARY L_CSUM

---------- --------------- ---------- ----------

100 Mourgos 5800 5800

100 Vollman 6500 12300

100 Kaufling 7900 20200

100 Weiss 8000 28200

100 Fripp 8200 36400

100 Zlotkey 10500 46900

100 Raphaely 11000 68900

100 Cambrault 11000 68900

100 Errazuriz 12000 80900

. . .

149 Taylor 8600 30200

149 Hutton 8800 39000

149 Abel 11000 50000

201 Fay 6000 6000

205 Gietz 8300 8300

King 24000 24000

29. VAR_POP

29.1 Syntax

VAR_POP(expr) [ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

29.2 Purpose

VAR_POP returns the population variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.

Var_pop返回非空數集的總體方差。它可用作聚集和分析函式。

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 the function is applied to an empty set, then it returns null. The function makes the following calculation:

若函式作用於一個空集，那末它返回null。函式按照下面的公式進行計算：

(SUM(expr²) - SUM(expr)² / COUNT(expr)) / COUNT(expr)

See Also:

"About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"

29.3 Aggregate Example

The following example returns the population variance of the salaries in the employees table:

下面的例子返回employees表中薪水的總體方差：

SELECT VAR_POP(salary) FROM employees;

VAR_POP(SALARY)

---------------

15140307.5

29.4 Analytic Example

The following example calculates the cumulative population and sample variances in the sh.sales table of the monthly sales in 1998:

下面的例子計算sh.sales表中1998年月度銷售量的累計總體和樣本方差：

SELECT t.calendar_month_desc,

var_pop(SUM(s.amount_sold)) over(ORDER BY t.calendar_month_desc) "Var_Pop", var_samp(SUM(s.amount_sold)) over(ORDER BY

t.calendar_month_desc) "Var_Samp"

FROM sales s, times t

WHERE s.time_id = t.time_id

AND t.calendar_year = 1998

GROUP BY t.calendar_month_desc;

CALENDAR Var_Pop Var_Samp

-------- ---------- ---------- 1998-01 0

1998-02 6.1321E+11 1.2264E+12

1998-03 4.7058E+11 7.0587E+11

1998-04 4.6929E+11 6.2572E+11

1998-05 1.5524E+12 1.9405E+12

1998-06 2.3711E+12 2.8453E+12

1998-07 3.7464E+12 4.3708E+12

1998-08 3.7852E+12 4.3260E+12

1998-09 3.5753E+12 4.0222E+12

1998-10 3.4343E+12 3.8159E+12

1998-11 3.4245E+12 3.7669E+12

1998-12 4.8937E+12 5.3386E+12

30. VAR_SAMP

30.1 Syntax

VAR_SAMP(expr) [ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

30.2 Purpose

VAR_SAMP returns the sample variance of a set of numbers after discarding the nulls in this set. You can use it as both an aggregate and analytic function.

Var_samp返回非空數集的樣本方差。它可用作聚集和分析函式。

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 the function is applied to an empty set, then it returns null. The function makes the following calculation:

若函式作用於一個空集，那末它返回null。函式按照下面的公式進行計算：

(SUM(expr²) - SUM(expr)² / COUNT(expr)) / (COUNT(expr) - 1)

This function is similar to VARIANCE, except that given an input set of one element, VARIANCE returns 0 and VAR_SAMP returns null.

該函式與variance類似，不同的是，若輸入集合只有一個元素，variance返回0，而 var_samp返回null。

See Also:

"About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"

30.3 Aggregate Example

The following example returns the sample variance of the salaries in the sample employees table.

下面的例子返回employees表中薪水值的樣本方差：

SELECT var_samp(salary) FROM employees;

VAR_SAMP(SALARY)

----------------

15283140.5

30.4 Analytic Example

Please refer to the analytic example for VAR_POP.

請參閱var_pop分析函式的例子。

31. VARIANCE

31.1 Syntax

VARIANCE([ DISTINCT | ALL ] expr)

[ OVER (analytic_clause) ]

See A "Analy restric

lso: 

tic Functions" for information on syntax, semantics, and tions

31.2 Purpose

VARIANCE returns the variance of expr. You can use it as an aggregate or analytic function.

Variance返回expr的方差。它可用作聚集或分析函式。

Oracle Database calculates the variance of expr as follows:

Oracle資料庫按照下面原則計算expr的方差：

? 0 if the number of rows in expr = 1

若符合expr的行數為1，則返回0

? VAR_SAMP if the number of rows in expr > 1

若符合expr的行數大於1，則返回var_samp。

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。

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, "About SQL Expressions" for information on valid forms of expr and "Aggregate Functions"

31.3 Aggregate Example

The following example calculates the variance of all salaries in the sample employees table:

下面的例子計算employees表中所有薪水的方差：

SELECT VARIANCE(salary) "Variance" FROM employees;

Variance

----------

15283140.5

31.4 Analytic Example

The following example returns the cumulative variance of salary values in Department 30

ordered by hire date.

下面的例子返回部門30按僱傭日期排序的薪水值的累計方差：

SELECT last_name, salary,

VARIANCE(salary) over(ORDER BY hire_date) "Variance"

FROM employees

WHERE department_id = 30;

LAST_NAME SALARY Variance

--------------- ---------- ----------

Raphaely 11000 0

Khoo 3100 31205000

Tobias 2800 21623333.3

Baida 2900 16283333.3

Himuro 2600 13317000

Colmenares 2500 11307000

About Me

...............................................................................................................................

● 本文整理自網路

● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文部落格園地址：http://www.cnblogs.com/lhrbest

● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群：230161599 微信群：私聊

● 聯絡我請加QQ好友(646634621)，註明新增緣由

● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

...............................................................................................................................

拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2142309/，如需轉載，請註明出處，否則將追究法律責任。

Oracle 10gR2分析函式彙總

1. ANALYTIC FUNCTIONS

1.1 analytic_function

1.2 Arguments

1.3 analytic_clause

1.4 query_partition_clause

1.5 order_by_clause

1.6 windowing_clause

2. AVG

2.1 Syntax

2.2 Purpose

2.3 Aggregate Example

2.4 Analytic Example

3. CORR

3.1 Syntax

3.2 Purpose

3.3 Aggregate Example

3.4 Analytic Example

4. COVAR_POP

4.1 Syntax

4.2 Purpose

4.3 Aggregate Example

4.4 Analytic Example

5. COVAR_SAMP

5.1 Syntax

5.2 Purpose

5.3 Aggregate Example

5.4 Analytic Example

6. COUNT

6.1 Syntax

6.2 Purpose

6.3 Aggregate Examples

6.4 Analytic Example

7. CUME_DIST

7.1 Aggregate Syntax cume_dist_aggregate::=

7.2 Analytic Syntax cume_dist_analytic::=

7.3 Purpose

7.4 Aggregate Example

7.5 Analytic Example

8. DENSE_RANK

8.1 Aggregate Syntax dense_rank_aggregate::=

8.2 Analytic Syntax dense_rank_analytic::=

8.3 Purpose

8.4 Aggregate Example

8.5 Analytic Example

9. FIRST

9.1 Syntax

first::=

9.2 Purpose

9.3 Aggregate Example

9.4 Analytic Example

10. FIRST_VALUE

10.1 Syntax

10.2 Purpose

10.3 Examples

11. LAG

11.1 Syntax

11.2 Purpose

11.3 Examples

12. LAST

12.1 Syntax

last::=

12.2 Purpose

13. LAST_VALUE

13.1 Syntax

13.2 Purpose

13.3 Examples

14. LEAD

14.1 Syntax

14.2 Purpose

14.3 Examples

15. MAX

15.1 Syntax

15.2 Purpose

15.3 Aggregate Example

15.4 Analytic Example

16. MIN

16.1 Syntax

16.2 Purpose

16.3 Aggregate Example