Oracle 10g 分析函数最终版


Oraccle100gR2 分分析函函数  (Trannslated BBy caizhuuoyi 2008‐9‐19)  说明明:  1、 2、 1. A Ana agg calle wind calc of ro 分析 分组 一个 小可 Ana ORD befo in th 分析 有wh 数只 Ana repo 分析 ana ana 原文中底色 原文中淡蓝色 ANALYTIC  alytic function regate funct ed a window dow of rows culations for ows or a log 析函数通过将 组返回多行值 个窗口。每一 可以用多个物 alytic functio DER BY claus ore the analy he select list 析函数是查询 here,gro 只能用于选择 alytic functio orting aggre 析函数通常用 alytic_fun alytic_fun 为黄的部分翻 色字体的文字 FUNCTIO ns compute tions in that w and is def s is defined. the current ical interval 将行分组后,再 值。分析函数 一行都对应有一 物理行进行度 ns are the la se. All joins ytic function t or ORDER B 询中除需要在 up by,和h 择列或order ns are comm gates. 用于计算数据 nction::= nction([ 翻译存在商榷 字,不宜翻译 NS  an aggrega they return fined by the The window row. Window such as tim 再计算这些分 根据analyt 一个在行上滑 度量,也可以 ast set of op and all WHER s are proces BY clause. 在最终处理的o having子句 r by子句中。 monly used 据累积值,数 = arguments 榷之处,请大大家踊跃提意意见;  译,保持原样样。  ate value bas multiple row analytic_ w determines w sizes can me. sed on a gro ws for each g _clause. F s the range be based o oup of rows. group. The g For each row of rows use on either a p They differ group of row w, a sliding d to perform hysical num from ws is m the mber 分组的值。它 tic_claue 滑动的窗口。 使用逻辑区间 它们与聚集函 es(分析子句 。该窗口确定 间进行度量, 函数不同之处 句)将行分组 定当前行的计 ,比如时间。 处在于能够对 组,一个分组 计算范围。窗 对每一 称为 口大 perations per RE, GROUP B ssed. There rformed in a BY, and HAV efore, analyti a query exce VING clause ic functions ept for the fin es are compl can appear nal leted only order by子 句都要在处理 。 子句之外最后 理分析函数之 后执行的操作 之前进行计算 作。所有连接 。因此,分析 接和所 析函 to compute 据移动值、数 s ]) cumulative, 数据中间值, moving, ceentered, and ,和输出集合合报表。 OOVER (anaalytic_claause) ana [ q [ o que PAR { | } ord ORD { e [ A [ N [, ]. win alytic_cla query_part order_by_c ery_partit RTITION BY value_ex ( value_ der_by_cla ause::= DER [ SIBL expr | pos ASC | DESC NULLS FIRS , { expr [ ASC | [ NULLS ... ndowing_cl tition_cl clause [ tion_clau Y xpr[, valu _expr[, va ause::= LINGS ] B sition | C ] ST | NULL | positio DESC ] FIRST | lause ::= lause ] windowing use::= ue_expr ] alue_expr BY c_alias } LS LAST ] on | c_al NULLS LAS = g_clause ... r ]... ) lias } ST ] ] ] { R { B { | | } AN { | | } | { | | } } The 以下 1.1 Spe disc Ana 1.2 Ana any dete rem othe ROWS | RAN BETWEEN UNBOUNDE CURRENT value_ex ND UNBOUNDE CURRENT value_ex UNBOUNDE CURRENT value_ex e semantics 下各节将讨论 analytic_fu ecify the nam cussion of se alytic_fun Arguments alytic functio nonnumeric ermines the aining argum erwise noted NGE } ED PRECEDI ROW xpr { PREC ED FOLLOWI ROW xpr { PREC ED PRECED ROW xpr PRECED of this synta 论分析函数语 unction  me of an ana emantics). nction指定 s  ns take 0 to c datatype th argument w ments to tha d for an indiv ING CEDING | ING CEDING | DING DING ax are discus 语法的语义。 alytic functio 定分析函数的 3 argument hat can be im with the high at datatype. vidual functi FOLLOWING FOLLOWING ssed in the s on (see the li 的名称。(请参 ts. The argu mplicitly con est numeric The return t on. G } G } sections tha isting of ana 参阅以下语义 uments can b nverted to a c precedence type is also at follow. alytic functio 义论述中的分 be any num numeric dat e and implic that datatyp ns following 分析函数列表 eric datatyp tatype. Orac citly converts pe, unless g this 表) pe or cle s the 分析函数可取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中)中使用该子句,应该使用语法图中下分支中的语法(带有圆括号 )。 analytic functions in the same query, each with the same or 同一查询中可以使用多个分析函数,它们可以有相同或不同的partition by键值。 the objects being queried have the parallel attribute, and if you specify an analytic 被查询的对象具有并行特性,并且分析函数中包含query_partition_clause,那末 lid values of value_expr are constants, columns, nonanalytic functions, function alue_expr的有效值包括常量,表列,非分析函数,函数表达式,或者前面这些元素的任 1.5 order_by_clause Use the order_by_clause to specify how data is ordered within a partition. For all ake You can specify multiple different PARTITION BY keys. 在 If function with the query_partition_clause, then the function computations are parallelized as well. 若 函数的计算也是并行的。 Va expressions, or expressions involving any of these. v 意组合表达式。 analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which t 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和 用多个键 ithin each function, you can specify multiple ordering expressions. Doing so is ession 个函数内可以指定多个排序表达式。当使用函数给值排名时,尤其显得意义非凡,因为第 henever the order_by_clause results in identical values for multiple rows, the PERCENTILE_DISC之外(它们只能取唯一的键值)外的分析函数,分组中可以使 值对值进行排序,每个键值在value_expr中定义,并且被排序序列限定。 W especially useful when using functions that rank values, because the second expr can resolve ties between identical values for the first expression. 每 二个表达式能够解决按照第一个表达式排序后仍然存在相同排名的问题。 W 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后,仍存在值相同的行,则每一行都会返回相同的结果。相关 estrictions on the ORDER BY Clause The following restrictions apply to the rder by子句的限制:下面是使用order by子句的一些限制: • When used in an analytic function, the order_by_clause must take an in er 分析函数中的order_by_clause必须是一个表达式(expr)。Sibling关键字在 • An analytic function that uses the RANGE keyword can use multiple sort keys in its 当分析函数使用range关键字限定窗口时,若使用的窗口是下列两个窗口之一,那 o RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. 行为的例子请参阅考sum分析函数的例子。 R ORDER BY clause: O expression (expr). The SIBLINGS keyword is not valid (it is relevant only hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to ord the overall query or subquery. 此处是非法的(它仅仅与层次查询有关)。位置(position)和列别名(c_alias) 也是非法的。除此之外,order_by_clause的用法与整个查询或子查询中的相同。 ORDER BY clause if it specifies either of these two windows: 末可以在分析函数的order by子句中使用多个排序键值。 short form of this is RANGE UNBOUNDED PRECEDING. 简写成 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 指明窗口结束于分组的最后一行 它只用 。 于指定终点而不能用于指定起点。 URRENT ROW As a start point, CURRENT ROW specifies that the window begins at urrent row 用作起点,current row 指定窗口开始于当前行或当前值(依赖于是否分 an end point, CURRENT ROW specifies that the window ends at the current row or 作终点,current row 指定窗口结束于当前行或当前值(依赖于是否分别指定row 或 C 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. c 别指定row 或range)。在这种情况下终点不能为value_expr preceding。 As value (depending on whether you have specified ROW or RANGE, respectively). In this case the start point cannot be value_expr FOLLOWING. 用 range)。在这种情况下起点不能为value_expr following。 value_expr PRECEDING or value_expr FOLLOWING For RANGE or ROW: ange或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。 • If value_expr PRECEDING is the end point, then the start point must be 若value_expr PRECEDING是终点,那末起点必须是value_expr PRECEDING。 r value_expr FOLLOWING. value_expr PRECEDING. If you are defining a logical window defined by an interval of time in numeric format, then 要定义一个数字格式的时间间隔的逻辑窗口,那末可能需要用到转换函数。 e Also:  you may need to use conversion functions. 若 Se NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals 请参阅: TERVAL和NUMTODSINTERVAL获取关于数次转换为时间间隔的信息。 you specified ROWS: windowing_clause由rows指定: • value_expr is a physical offset. It must be a constant or expression and must value_expr是一个物理偏移量,它必须是一个常量或表达式,并且表达式的值必 • If value_expr is part of the start point, then it must evaluate to a row before the 若 value_expr 是起点的一部分,那末它必须在终点之前对行求值。 you specified RANGE: NUMTOMIN If 若 evaluate to a positive numeric value. 须为正数值。 end point. If 若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是一个逻辑偏移量。它必须是常量,或值为正数值的表达式,或时间 • 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 若 value_expr 求值为一个数字值,那末 order by expr 必须为数字或 date • If value_expr evaluates to an interval value, then the ORDER BY expr must be a 若value_expr求值为一个间隔值,那末order by expr必须是一个date类型。 you omit the windowing_clause entirely, then the default is RANGE BETWEEN 完全忽略windowing_clause,那末默认值为 range between unbounded Analytic functions are commonly used in data warehousing environments. In the list of 分析函数通常用于数据仓库环境中。下面是分析函数列表,带有星号的函数可以包含 G 间隔文字常量。请参阅Literals获取有关时间间隔文字常量的信息。 numeric or DATE datatype. 类型。 DATE datatype. If UNBOUNDED PRECEDING AND CURRENT ROW. 若 preceding and current row 。 analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause. windowing_clause。 AV * CORR * POPCOVAR_ * COVAR_SAMP * COUNT * CUME_DIST DEN FIRS FIRS LAG LAS LAS LEA MAX MIN NTI PER PER PER RAN RAT REG ROW STD STD STD SUM VAR VAR VAR NSE_RANK ST ST_VALUE See  Ora scen 请参 Ora 2. A 2.1 GAV * G ST ST_VALUE ** AD X * N * LE RCENT_RA RCENTILE_ RCENTILE_ NK TIO_ NK _CONT _DISC TO_RE GR_ (Linear W_NUMBER DDEV * DDEV_P EPORT r Regressio OP DDEV_SAM M * R_PO n R P n) Functions P * R_SAMP * RIANCE * Also:  ta Wcle Da a narios illustr 参阅: acle Data AVG   Syntax  ([ DISTINCT * * arehousing G rating their u Warehous T | ALL ] e s * Guide for mo use ing Guide expr) ore informat e获取关于这 tion on thesee functions a 这些函数及其其方案使用说 and for 明的更多信息。 息 [ 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 Distinct关键字仅能在analytic_clause的query_partition_clause中使用。在 the analytic_clause. The order_by_clause and windowing_clause are not allowed. 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表中所有雇员的平均薪水: hr.employees table: SELLECT AVG( FROM AVE ---- 2.4 The ave rang 下面 后的 SEL BET FR MANA ---- . . 3. C 3.1 s ERAGE ----- 6425 Analytic E e following ex rage salary ge just befor 面的例子计算 的雇员的平均 LECT manag last_n hire_d salary AVG(sa TWEEN 1 pr ROM emplo AGER_ID LAS ------- --- 100 Koc 100 De 100 Rap 100 Kau 100 Har 100 Wei 100 Rus . CORR   Syntax  salary) "  Example xample calc of the emplo re through ju 算,employe 均薪水: ger_id, name, date, y, alary) ove receding oyees; ST_NAME ----------- chhar Haan phaely ufling rtstein iss ssell Average" culates, for e oyees repor ust after the ees表中相同 er(PARTITI AND 1 fol ----------- employees; each employ rting to the s employee: 同经理下的每 ION BY man llowing) HIRE_DAT -- -------- 21-SEP-8 13-JAN-9 07-DEC-9 01-MAY-9 17-FEB-9 18-JUL-9 01-OCT-9 yee in the em same manag mployees ttable, the ger who werre hired in thhe 每一雇员和雇雇佣日期正好位于该雇员正正前 nager_id O AS c_mavg ORDER BY h TE SALA -- -------- 89 170 93 170 94 110 95 79 96 130 96 80 96 140 i g ARY C_M --- ------- 000 17 000 15 000 11966.6 900 10633.3 000 9633.33 000 11666.6 000 11833.3 ire_date rrows MAVG ---- 7000 5000 6667 3333 3333 6667 3333 CORR(expr1, expr2) [ OVER (analytic_clause) ] See Also: "Analytic Functions" for information on syntax, semantics, and restrictions CORR returns the coefficient of correlation of set of number pairs. You can use it as an aggrega 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 3.2 Purpose a te or analytic 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 computation: Oracle数 ll的 数值对。 COVAR_POP(expr1, expr2) / (STDDEV_POP(e 1) * STDDEV_POP(expr2)) 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 据库使用该函数前先排除(expr1,expr2)集中所有expr1或expr2为nu 然后作如下的计算: xpr 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 The follo le calculates the coefficient of correlation between the list prices and minimum oe.prod 下面的列子,计算oe.product_information表中不同重量等级产品订价和最低价格之 间的相关系数: SELECT weight_class, corr(list_price, min_price) WEIGHT_CLASS CORR(LIST_PRICE,MIN_PRICE) .99914795 2 .999022941 359909 .999536087 ice) / Aggregate Example wing examp prices of products by weight class in the sample table uct_information: FROM product_information GROUP BY weight_class; ------------ -------------------------- 1 3 .998484472 4 .999 5 补充: 这个查询与下面的查询等价: SELECT weight_class, covar_pop(list_price, min_pr (stddev_pop(list_price) * stddev_pop(min_price)) AND min_price IS NOT NULL BY weight_class; any and salary t shows the same correlation for each employee 的工龄与薪水之间的相关性。结果表明职务相同的雇员有 相同的相关性: SELECT employee_id, , corr(SYSDATE - hire_date, salary) over(PARTITION BY job_id) AS es department_id IN (50, 80) loyee_id; ----- ------- ---------- ----------- +08-04 13500 .912385598 +08-02 12000 .912385598 0 .912385598 500 .912385598 150 SA_REP +08-03 10000 .80436755 FROM product_information WHERE list_price IS NOT NULL GROUP 3.4 Analytic Example The following example shows the correlation between duration at the comp by the employee's position. The result se in a given job: 下面的例子显示了不同职务的雇员 job_id, to_char((SYSDATE - hire_date) YEAR TO MONTH) "Yrs-Mns", salary "Correlation" FROM employe WHERE ORDER BY job_id, emp EMPLOYEE_ID JOB_ID Yrs-Mns SALARY Correlation ----------- ----- 145 SA_MAN +08-07 14000 .912385598 146 SA_MAN 147 SA_MAN 148 SA_MAN +05-07 1100 149 SA_MAN +05-03 10 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. CCOVAR_POOP   4.1 COV Syntax  A 4.2 CO V an a Cov This can the data 函数 高数 AR_POP(expr [ OVER (ana See A "Analy restrict Purpose VAR_POP ret aggregate o var_pop返回 s function ta be implicitly highest num atype, and re 数参数可取任 数字优先级确 See A Table implicit numer r1, expr2) alytic_clau lso: ytic Functio tions turns the po r analytic fu 回一组数值对 kes as argu y converted meric preced eturns that d 任何数字类型 确定参数,隐 lso: 2-10, "Impli t conversio ric preceden use) ] ons" for in pulation cov nction. 对的总体协方 ments any n to a numeri dence, implic datatype. 型或任何可以 隐式地将需要 cit Type Co on and "Nu nce nformation variance of a 方差。它可以 numeric data c datatype. citly convert 隐式转换为数 处理的参数转 onversion M umeric Prec on syntax, a set of num 以用作聚集或 atype or any Oracle dete s the remain 数字类型的非 转换为数字类 atrix" for mo cedence" fo semantics mber pairs. Y 或分析函数。 y nonnumer ermines the a ning argume 非数字类型。 类型,并返回 ore informa or informat s, and You can use ic datatype t argument w ents to that Oracle根 回数字类型。 tion on ion on it as that ith 根据最 Oracle D nction 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: ll nor expr2 is null. n是(expr1,expr2)数值对的个数,expr1和expr2都不能为null。 atabase applies the fu Oracle数据库使用该函数前先排除(expr1,expr2)集中所有expr1或expr2为nu 的 数值对。然后作如下的计算: (SUM(expr1 * expr2) - SUM(expr2) * SUM(expr1) / n) / n where n is the number of (expr1, expr2) pairs where neither expr1 这里 The function returns a value of type NUMBER. If the function is applied to an empty set, then it returns null. 函数返回一个number类型的值。若将此函数应用在一个空集上,那末它将返回null。 See Also: "About SQL Expressions" for information on valid forms of expr and "Aggregate Functions" 4.3 Agg The following example calculates the population covariance and sample covariance for ) and salary using the sample table hr.employees: ar_ hire_date, salary) AS covar_pop, c ar_samp(SYSDATE - hire_date, salary) AS covar_samp 545115.000 regate Example time employed (SYSDATE - hire_date 下面的例子计算hr.employees表中不同职务雇员的雇佣时间和薪水的总体协方差和样本 协方差: SELECT job_id, cov pop(SYSDATE - ov FROM employees WHERE department_id IN (50, 80) GROUP BY job_id; JOB_ID COVAR_POP COVAR_SAMP ----------- ---------- ----------- T_MAN 436092.000S SH_CLERK 782717.500 823913.158 SA_MMAN 6660700.000 825875.000 600702.340SA_RREP 5779988.466 185870.789ST_CCLERK 1776577.250 4.4 Analytic E Example Thee following exxample calcculates cumuulative sampple covariance of the listt price and min 下面 SEL sup sup FR WH OR PROD ---- . . 5. C 5.1 COV imum price 面的例子计算 LECT produ suppli covar_ pplier_id) covar_ pplier_id) ROM produ HERE categ RDER BY pr DUCT_ID SUP ------- --- 1774 1775 1794 1825 2004 2005 2416 . COVAR_SA Syntax  VAR_SAMP(ex [ OVER (ana of the produ 算oe模式中不 uct_id, ier_id, _pop(list_ ) AS cum_ _samp(list ) AS cum_ uct_inform gory_id = roduct_id, PPLIER_ID --------- - 103088 103087 103096 1 103093 103086 103086 103088 1 AMP   xpr1, expr2 alytic_clau ucts in the sa 不同产品的订 _price, m _covp, t_price, _covs mation p li 29 , supp e CUM_COVP ---------- 0 1473.25 702.77778 1926.25 1591.4 1512.5 475.97959 2) use) ] ample schema oe: m 订价和最低价价格的累计样本本协方差: ORRDER BY pmin_price)) over( rroduct_idd, OORDER BY min_pricee) over( pproduct_iid, er_id; CUM_COVS 68.33333 ---------- 2946.5 2554.16667 25 1989.25 1815 1721.97619 See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 5.2 Pur COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as analytic function. This function takes as arguments any numeric datatype or any nonnumeric datatype that rgument with pose an aggregate or Covar_samp返回一组数值对的样本协方差。它可用作聚集或分析函数。 can be implicitly converted to a numeric datatype. Oracle determines the a 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 atabase applies the fuOracle D nction 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: ll的 如下的计算: 1 nor expr2 is null. 这里n是(expr1,expr2)数值对的个数,expr1和expr2都不能为null。 Oracle数据库使用该函数前先排除(expr1,expr2)集中所有expr1或expr2为nu 数值对。然后作 (SUM(expr1 * expr2) - SUM(expr1) * SUM(expr2) / n) / (n-1) where n is the number of (expr1, expr2) pairs where neither expr The then 函数 5.3 Plea 请参 5.4 Plea 请参 6. C 6.1 OUC N e function ret n it returns n 数返回一个nu See A "About and "A Aggregate ase refer to 参阅covar_p Analytic E ase refer to 参阅cova_po COUNT   Syntax  NT({ * | [ [ OVER (ana See A turns a valuee of type NUUMBER. If thee function is applied to aan empty sett null. umber类型 返回null。 , 的的值。若将此此函数应用在在一个空集上上,那末它将将 lso: t SQL Exp Aggregate Fu e Example the aggrega pop聚集函数  Example the analytic op分析函数例 DISTINCT | alytic_clau lso: ressions" fo unctions" e ate example 数例子。 example fo 例子。 ALL ] exp use) ] or informati for COVAR r COVAR_P r }) ion on vali R_POP. POP. d forms off expr "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 Count返回查询结果集的行数。它可以用作聚集或分析函数。 If you specify DISTINCT, then you can specify only the query_partition_clause of Distinct关键字仅能在analytic_clause的query_partition_clause中使用。在 If you specify expr, then COUNT returns the number of rows where expr is not null. You 若expr作为函数参数,那末count不计算expr为null的行。函数要么计算所有行,要么 If you specify the asterisk (*), then this function returns all rows, including duplicates and 若星号(*)作为函数参数,那末函数返回包括数据重复的行和数据为null的行在内的所有 or analytic function. the analytic_clause. The order_by_clause and windowing_clause are not allowed. order_by_clause和windowing_clause中不允许使用distinct。 can count either all rows, or only distinct values of expr. 仅计算expr的不同值。 nulls. COUNT never returns 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用作聚集函数的若干例子: LECT COUNT(*) "Total" FROM employees; SE Total LECT COUNT(*) "Allstars" FROM employees WHERE commission_pct > 0; llstars LECT COUNT(commission_pct) "Count" FROM employees; Count LECT COUNT(DISTINCT manager_id) "Managers" FROM employees; Managers 6.4 Analytic Example The following example calculates, for each employee in the employees table, the moving 下面的例子计算employees表每个雇员与雇员自己薪水相差在50至150之间的雇员的个数。 T last_name, salary, (ORDER BY salary RANGE BETWEEN 50 preceding AND 150 mov_count SALARY MOV_COUNT ----------------------- ---------- ---------- ---------- 107 SE A --------- 35 SE ---------- 35 SE ---------- 18 count of employees earning salaries in the range 50 less than through 150 greater than the employee's salary. SELEC COUNT(*) over following) AS FROM employees; LAST_NAME -- Olson 2100 3 Markle 2200 2 Philtanker 2200 2 Landry 2400 8 Gee 2400 8 Colmenares 2500 10 Pateel 2500 10 . . . 7. CCUME_DISST   7.1 Aggregatee Syntax  cumme_dist_agggregate::= CUME W ) 7.2 cum UMC E O E_DIST(expr WITHIN GROU (ORDER BY e [ ] ) Analytic S me_dist_ana E_DIST( ) OVER ([ que r[,expr ].. UP expr [ DESC [ NULL [, expr [ D [ N ]...  Syntax alytic::= ery_partiti .) C | ASC ] LS { FIRST DESC | ASC NULLS { FIR ion_clause | LAST } ] ] ST | LAST } ] order_by_ } ] clause) _ See Also: "Analytic Functions" for information on syntax, semantics, and restrictions 7.3 Purpose the cumulative distribution of a value in a group of values. The returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value. eric datatype. Oracle Database determines the argument with the highest numeric precedence, implicitly converts the remaining e根据最 行计算,并返回 number类型的值。 CUME_DIST calculates range of values 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 num arguments to that datatype, makes the calculation, and returns NUMBER. 函数参数可取任何数字类型或任何可以隐式转换为数字类型的非数字类型。Oracl 高数字优先级确定参数,隐式地将需要处理的参数转换为数字类型,然后进 See Also: Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion and "Numeric Precedence" for information on numeric precedence • A on, CUME_DIST calculates, for a hypothetical row r s an aggregate functi 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 constant expressions within each aggregate group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate m by position. Therefore, the number of arguments must be the same and their types must be compatible. CUME_DIST 用作聚集函数时,对于一个被函数参数和相应排序规则确定的假定行 r, cume_dist计算此假 to atch 定行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 result 析函数时,用于计算一个值在一组值中的相对位置。假定按升 序排序的一个结果集或分组中存在一行 r,cume_dist()在 r 上结果如是求得:值 7.4 Ag 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 set or a partition). CUME_DIST 用作分 小于等于行 r 上值的行的行数,除以整个查询结果集或分组的行数。 gregate 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 loyees表中薪水达到$15500并且佣金率达到5%的假定雇员的累 计分布值: e_dist(15500, .05) within GROUP( me-Dist of 15500" ployees; ---------------- le lculates the salary percentile for each employee in the purchasing division. For example, 40% of clerks have salaries less than or equal to 子计算每个采购科雇员的薪水百分点。例如,40%的职员的薪水少于或等于 Himur。 t_name, salary, table oe.employees: 下面的例子计算oe.emp SELECT cum ORDER BY salary, commission_pct) "Cu FROM em Cume-Dist of 15500 -- .972222222 7.5 Analytic Examp The following example ca Himuro. 下面的例 SELECT job_id, las cum FR WH JOB_ ---- PU_C PU_C PU_C PU_C PU_C PU_M 8. D 8.1 den DENS (O de ) 8.2 n cume_d me_dist ROM emplo HERE job_i _ID LAS ------- --- CLERK Col CLERK Him CLERK Tob CLERK Bai CLERK Kho MAN Rap DENSE_RA Aggregate nse_rank_ag SE_RANK(exp ORDER BY ex [, ]. Analytic S nse_rank_an dist() oveer(PARTITTION BY ORDDER BY ary) AS joob_id sall oyees id LIKE 'PPU%'; ST_NAME SALAARY CUME_DDIST ---------------------- -- ----------- ----------- .2 lmenares 25500 .4 muro 26600 .6 bias 28800 .8 ida 29900 1 oo 31100 1 phaely 110000 ANK   e Syntax  ggregate::== ]...) WIT HHpr [, expr xpr [ DESC [ NULLS expr [ DES [ NUL ..  Syntax  nalytic::= | ASC ] S { FIRST | SC | ASC ] LLS { FIRST IN GROUP LAST } ] | LAST } ]] DENS O 8.3 DEN rank valu in th This Den 的连 的值 This 函数 SE_RANK( ) OVER([ quer See A "Analy restrict Purpose NSE_RANK c k as a NUMB ue is the num he event of t s function is nse_rank 计 连续整数。排 值。值相等的 s function ac 数接受任何数 • As an a hypothe sort spe express within e order_ argume Dense_ 行的密集 表达式和 相同,参 ry_partitio lso: ytic Functio tions omputes the ER. The ran mber of uniq ties. Rows w useful for to 计算有序组中 名的最大值是 的行排名相同 ccepts as ar 数字类型的参 ggregate fu etical row ide ecification. T sions within ach group. T _by_clause nts must be _rank 用作聚 集排名。函数 和聚集的 ord 参数类型必须 on_clause ] ons" for in e rank of a r nks are cons que values re with equal va op-N and bo 中行的排名, 是查询返回的 同。此函数对 rguments an 参数并返回 nu nction, DENS entified by th The argumen each aggreg The constan e of the agg e the same a 聚集函数时, 数参数必须对 der by 子句 须兼容。 order_by_c nformation row in an ord secutive inte eturned by t alues for the ottom-N repo 返回的排名 的唯一值的个 于计算 top- ny numeric d umber 类型 SE_RANK ca he argument nts of the fun gate group, nt argument gregate matc and types m 它计算由一 对每个聚集分 句中的表达式 clause) on syntax, dered group gers beginn the query. R e ranking crit orting. 是一个 num 个数。排名一 -N 和 botto datatype and 。 alculates the ts of the fun nction must because the expressions ch by positio ust be comp 一个带有排序 分组中的常量 式按位置进行 semantics s, and p of rows andd returns thee ning with 1. T Rank values teria receive mber 数值。 一旦与行关联 om-N 报表十 d returns NUM e dense rank ction with re all evaluate ey identify a s and the ex on. Therefore patible. 序规则的函数 量表达式全部 行匹配。因此 The largest rank are not skippped e the same rrank. 排名是从 1 联就不会产生 十分有用。 开始 生跳跃 MBER. k of a espect to a ggiven e to constantt a single row xpressions in e, the numb 数参数确定的 求值。常量参 此,参数个数 n the ber of 假定 参数 数必须 • Dense_rank 用作分析函数时,它计算按照 order_by_clause 中 value_exprs The fol e $15,500 and a commission of 5% in the sample table oe.employees: ees 表中薪水达到$15500 并且佣金达到 5%的假定雇员的排 名: GROUP( M employees; --------- .5 Analytic Example 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 e rank. Compare this example with the example for RANK 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. 值排序返回的查询结果中,每一行相对于其他行的排名。 8.4 Aggregate Example  lowing example computes the ranking of a hypothetical employe with the salary 下面的例子计算 oe.employ SELECT dense_rank(15500, .05) within ORDER BY salary DESC, commission_pct) "Dense Rank" FRO Dense Rank ---------- 3 8 The following statement are equal receive the sam . 。 e.last_name, e.salary) AS drank ents d id = d.department_id nt_id IN ('30', '40'); AST_NAME SALARY DRANK 下面的语句在在人力资源或采购部门中,选择部门名称,雇员名称,雇员薪水,然后对这两 个部门中每个唯一的薪水值排名。薪水相等则排名相同。请将本例与 rank 示例比较 SELECT d.department_name, e.salary, dense_rank() over(PARTITION BY e.department_id ORDER BY FROM employees e, departm WHERE e.department_ AND d.departme DEPARTMENT_NAME L ------------------------- ------------------ ----------- ----------- 1 2 3 Colmenares Purcchasing 25 Purc Purc Purc Purc Purc Huma 9. F 9.1 first aggr    K ) chasing chasing chasing chasing chasing an Resource FIRST   Syntax  t::= regate_func KEEP  (DENSE_RANK expr [ DES [ NUL [, expr [ [ ]... ) [ OVER quer See A es ction K FIRST ORD SC | ASC ] LLS { FIRST DESC | ASC NULLS { FI ry_partitio lso: Himuro Tobias Baida Khoo Raphaely Marvis DER BY T | LAST } C ] IRST | LAST on_clause ] ] } ] 00 2600 2800 4 2900 5 3100 6 11000 6500 "Analytic Functions" for information on syntax, semantics, and restrictions of the ORDER BY clause and OVER clause 9.2 Pur FIRST and LAST are very similar functions. Both are aggregate and analytic functions alues from a set of rows that rank as the FIRST or LAST with pose that operate on a set of v 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 都取这一行――译者注)。 type of the argument. 类 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 data 函数参数可取任何数字类型或者是任何可以隐式转换为数字类型的非数字类型。函数返回 型与参数类型相同,都为数字类型。 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, row ranks as FIRST or LAST, the aggregate operates on a singleton (nonaggregate) set. 排 元素进行聚集操作。 • , VARIANCE, or STDDEV functions. It operates on values from the rows that rank either FIRST or LAST. If only one 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 ) You can use the FIRST and LAST functions as analytic functions by specifying the OVER clause. with the 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)的行聚集在一起。 The query_partitioning_clause is the only part of the OVER clause valid se 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 Agg The following example returns, within each department of the sample table , the minimum salary among the employees who make the lowest m salary among the employees who make the highest commission: 雇员 SELECT department_id, MAX(salary) keep(dense_rank LAST ORDER BY commission_pct) T_ID Worst Best - ---------- 4400 regate Example hr.employees commission and the maximu 下面的例子返回 hr.employees 表中每个部门佣金最少雇员的最低薪水以及佣金最高 的最高薪水: MIN(salary) keep(dense_rank FIRST ORDER BY commission_pct) "Worst", "Best" FROM employees GROUP BY department_id; DEPARTMEN ------------- --------- 10 4400 20 6000 13000 as the previous example but returns the result for each employee within the department: 接下的例子对前例作相同的计算,但返回的是部门中每个雇员的薪水: department_id, MIN(salary) keep(dense_rank FIRST ORDER BY commission_pct) epartment_id) "Worst", (dense_rank LAST ORDER BY commission_pct) BY department_id) "Best" Worst Best ---------- ---------- ---------- ---------- 4400 4400 4400 y 20 6000 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 SELECT last_name, salary, over(PARTITION BY d MAX(salary) keep over(PARTITION FROM employees ORDER BY department_id, salary; LAST_NAME DEPARTMENT_ID SALARY ------------------- --- Whalen 10 Fa Hartstein 20 13000 6000 13000 . . . Gietz 110 8300 8300 12000 Higgins 110 12000 8300 12000 Grant 7000 7000 7000 10. FIRST_VAALUE   1 Syntax 10.   FIRS O 10. FIR If th IGN then Plea den Fir 除非 定 i 为 n 化数 You can exp 不能 套, exp ST_VALUE (e OVER (analy See A "Analy restrict 2 Purpose RST_VALUE e first value NORE NULLS n FIRST_VA ase refer to sification. rst_value 非指定 ignor ignor null null 则返回 数据的例子。 u cannot use not nest ana pr. Please re 能在 expr 中 但可以在 e pr 的相关信息 expr [ IGNO ytic_clause lso: ytic Functio tions, includ e is an analyt in the set is . This settin ALUE returns "Using Parti 只用作分析 r nulls 那 ls,那末 fi 回 null。请参 e FIRST_VA alytic functio efer to "Abo 中使用 first xpr 中使用 息。 ORE NULLS ] e) ons" for in ding valid for tic function. s null, then t g is useful fo s the fist non itioned Oute 函数。它返回 末函数返回 irst_value 参阅 Using ALUE or any ons, but you ut SQL Exp t_value 或 内置函数表达 ) nformation rms of expr It returns the he function or data dens n-null value er Joins: Exa 回已排序集的 null。忽略 e 函数返回集 Partione other analyt can use oth ressions" fo 其他任何分析 达式。请参阅 on syntax, r e first value returns NUL sification. If y in the set, o amples" for a 的第一个值。若 略空值的限定 集合中第一个 d Outer J tic function f her built-in fu r information 析函数。也就 阅 About SQ semantics s, and in an ordere LL unless yo you specify or NULL if all an example ed set of val ou specify IGNORE NUL l values are of data lues. LLS, null. 若集合中的第 定对稠化数据 个不为 null Joins:Exam 第一个值为 n 据很有用处。 的值,或若 mples 中关 null 若指 值全 于稠 , for expr. Th unction expr n on valid fo hat is, you ressions for orms of exprr. 就是说,此处 QL Express 处分析函数不 sions 获取 能嵌 合法 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); DEPAARTMENT_ID LAST_NAME SALARY FV ---------- ---- -------------------- ---- ------ --------------------- 90 1De Haan 7000 De Haaan 90 1Kochhar 7000 De Haaan 90 2King 4000 De Haaan The orde 下面 SEL unb FR DEPA ---- 11. 11. e following ex ering on a u 面的例子说明 LECT depar last_n salary hire_d first_ bounded pr ROM (SELE FROM WHER ORDE ARTMENT_ID ---------- 90 90 90 LAG   1 Syntax  xample show nique key. 明怎样通过一 rtment_id name, y, date, _value(las receding) ECT * M employe RE departm ER BY empl LAST_NAME ---------- Kochhar De Haan King   ws how to mmake the FIRST_VALUEE function deeterministic by 排序使 firsst_value 函一个唯一键值 函数具有确定定性: d, st_name) o AS fv over(ORDEER BY salarry ASC, hiire_date rrows ees ment_id = loyee_id = 90 DESC); LARY HIRE_D SA DATE FV ---- ---------- ---------- ---------------- 17000 21-SEPP-89 Kochhaar 17000 13-JANN-93 Kochhaar 24000 17-JUNN-87 Kochhaar 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: 下面 SEL FR WH LAST ---- Khoo Tobi Baid Himu Colm 12. 12. last aggr 面的例子提供供 employeees 表中每个雇 LECT last_ hire_d salary lag(sa ROM emplo HERE job_i T_NAME ----------- o ias da uro menares LAST   1 Syntax  t::= regate_func (DENSE_RANK expr [ DES [ NUL [, expr [ [ ]... _name, date, y, alary, 1, oyees id = 'PU_C -----------   ction KEEP K LAST ORDE SC | ASC ] LLS { FIRST DESC | ASC NULLS { FI 0) over( _CLERK'; HIRE_DATE -- -------- 18-MAY-9 24-JUL-9 24-DEC-9 15-NOV-9 10-AUG-9 ER BY T | LAST } C ] IRST | LAST 雇员正前一个个被雇佣的雇雇员薪水: (ORDER BY hire_datte) AS preev_sal E SALARRY PREV_SSAL - ----------- ----------- 0 5 31000 7 28000 31100 7 29000 28800 8 26000 29900 9 25000 26600 ] } ] ) [ 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_VALLUE   1 Syntax 13.   LAST O 13. LAS the NUL LAS refe Las 除非 指定 为 n 化数 You nest Plea 不能 套, exp T_VALUE(exp OVER (analy See A "Analy restrict 2 Purpose ST_VALUE is last value in LLS. This se ST_VALUE re er to "Using P st_value 只 非指定为 ign 定 ignor nu null 则返回 数据的例子。 u cannot use t analytic fun ase refer to 能在 expr 中 但可以在 e pr 的相关信息 pr [ IGNORE ytic_clause lso: ytic Functio tions, includ e s an analytic n the set is n tting is usef eturns the fis Partitioned O 只用作分析函 nore nulls ulls,那末 回 null。请参 e LAST_VAL nctions, but "About SQL 中使用 last_ xpr 中使用 息。 E NULLS ]) e) ons" for in ding valid for c function. It null, then the ul for data d st non-null v Outer Joins: 函数。它返回 s,否则返回 last_valu 参阅 Using LUE or any o you can use L Expression _value 或其 内置函数表达 nformation rms of expr returns the e function re densification value in the s Examples" 已排序集的最 回 null。忽 ue 返回集合 Partione other analytic e other built ns" for inform 其他任何分析 达式。请参阅 on syntax, r last value in turns NULL . If you spec set, or NULL for an exam 最后一个值。 略空值的限定 合中最后一个 d Outer J c function fo -in function mation on va 析函数。也就 阅 About SQ semantics s, and n an ordered unless you cify IGNORE if all values mple of data d set of value specify IGN NULLS, the s are null. Ple densificatio es. If NORE en ease on. 若集合中最 定对稠化数据 个不为 null 的 Joins:Exam 最后一个值为 据很有用处。 的值,或若值 mples 中关 空, 若 值全 于稠 or expr. Tha expressions alid forms of at is, you ca s for expr. f expr. nnot 就是说,此处 QL Express 分析函数不能 sions 获取 能嵌 合法 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   1 Syntax 14.   LEAD O 14. LEA sam of th pos Lea 查询 If yo if the defa 若不 若没 D(value_exp OVER ([ que See A "Analy restrict 2 Purpose AD is an ana me time witho he cursor, LE ition. ad 只能用作分 询的行组和一 ou do not sp e offset goe ault value is 不指定 offse 没有指定 def pr [, offse ery_partiti lso: ytic Functio tions, includ e lytic function out a self joi EAD provide 分析函数。它 一个位置指针 ecify offse es beyond th null. et,那末其默 fault,那末 et ] [, def ion_clause ons" for in ding valid for n. It provides in. Given a s es access to 它提供在不使 针,lead 能访 et, then its d he scope of t 默认为 1。若 末其默认为 n ault ]) ] order_by_ nformation rms of valu s access to series of row o a row at a g 使用自连接的 访问距离给定 default is 1. T the table. If 若偏移量超出 null。 _clause) on syntax, ue_expr semanticss, and more than o ws returned given physic one row of a from a quer cal offset be a table at the ry and a pos yond that e sition 的情况下访问 定物理偏移量 问表中多个行 量的行。 行的途径。给给定要 The optiona you do not s l default v specify defa value is retu ault, then rned its 出表范围,则则返回可选的的 default 值值。 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   1 Syntax 15.   MAX 15. MAX Max 15. The 下面 SEL ([ DISTINCT [ OVER (ana See A "Analy restrict 2 Purpose X returns ma x 返回 expr See A "About expr, compa 3 Aggrega e following ex 面的例子确定 LECT MAX(s T | ALL ] e alytic_clau lso: ytic Functio tions e aximum valu 的最大值。 lso: t SQL Ex "Floating- arison sema ate Exampl xample dete 定 hr.emplo salary) " expr) use) ] ons" for in e of expr. Y 它可用作聚集 xpressions" Point Num ntics, and "A le  ermines the oyees 表中的 Maximum" nformation You can use 集或分析函数 for inform mbers" for Aggregate F highest sala 的最高薪水: FROM emp on syntax, e it as an ag 数。 mation on information Functions" ary in the hr loyees; semantics s, and gregate or aanalytic funcction. valid form n on bina ms of ary-float r.employeees table: 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 Kocchhar 170000 Haan 100 De 170000 101 Greeenberg 120000 101 Higggens 120000 102 Hunnold 90000 103 Ernnst 60000 108 Favviet 90000 114 Khooo 31100 120 Nayyer 32200 120 Tayylor 32200 121 Sarrchand 42200 122 Chuung 38800 123 Belll 40000 124 Rajjs 35500 145 Tuccker 100000 146 Kinng 100000 147 Visshney 105500 148 Ozeer 115500 149 Abeel 110000 201 Goyyal 60000 205 Gieetz 83300 Kinng 240000 16. MIN   1 Syntax 16.   MIN ([ DISTINCT [ OVER (ana See A "Analy restrict T | ALL ] e alytic_clau lso: ytic Functio tions expr) use) ] ons" for in nformation on syntax, semanticss, and 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; MANAAGER_ID LASST_NAME HIRE_DATTE SALAARY P_CCMIN ----------- --------------------------- ---------- ----------- ----------- 100 Kocchhar 21-SEP-889 170000 177000 Haan 100 De 13-JAN-993 170000 177000 100 Rapphaely 07-DEC-994 110000 111000 100 Kauufling 01-MAY-995 79900 77900 100 Harrtstein 17-FEB-996 130000 77900 100 Weiiss 18-JUL-996 80000 77900 100 Russsell 01-OCT-996 140000 77900 100 Parrtners 05-JAN-997 135500 77900 100 Errrazuriz 10-MAR-997 120000 77900 . . . 17. NTILE   1 Syntax 17.   NTIL O 17. NTI indic are eac then LE(expr) OVER ([ que See A "Analy restrict 2 Purpose ILE is an an cated by ex numbered 1 h partition. O n Oracle trun ery_partiti lso: ytic Functio tions, includ e alytic functio xpr and assi 1 through ex Oracle Data ncates the v ion_clause ons" for in ding valid for on. It divides igns the app xpr. The ex base expec value to an in ] order_by_ nformation rms of expr s an ordered propriate buc xpr value m ts an intege nteger. The _clause) on syntax, r d data set in cket number ust resolve r, and if exp return value semantics nto a numbe r to each row to a positive pr is a nonin e is NUMBER s, and r of buckets w. The buck e constant fo nteger const R. kets or tant, 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 FR WH ROM emplo HERE depar oyees rtment_id = 100; SALALASTT_NAME RY QUARTIILE ---------------------------- ---------- ----------- 120 1 Greeenberg 00 90 1 00 Faviiet 82 2 00 Chenn 78 2 00 Urmaan 77 3 00 Sciaarra 69 4 00 Poppp 18. PERCENT__RANK   1 Aggrega18. ate Syntax   perccent_rank__aggregate::= PERC ) 18. perc CENT_RANK(e (ORDER BY expr [ DES [NULL [, expr [ [N ]... ) 2 Analytic cent_rank_ expr [, exppr ]...) WITHIN GROUP SC | ASC ] LS { FIRST | LAST } ] DESC | ASCC ] NULLS { FIRRST | LAST } ] c Syntax  _analytic::= PERC O 18. PER of va PER Per 的数 CENT_RANK( OVER ([ que See A "Analy restrict 3 Purpose RCENT_RANK alues return RCENT_RANK rcent_rank 数值。任意集 See A Table implicit • As an a identifie the rank This cal of rows function they mu constan the agg same an 当 perc 确定的假 ) ery_partiti lso: ytic Functio tions e  K is similar t ned by PERC K of 0. The r k 与 cume_d 集合的第一行 lso: 2-10, "Impli t conversion ggregate fu ed by the arg k of row r m lculation is m over which n identify a s ust all evalua nt argument regate matc nd their type cent_rank 假定行 r,用 ion_clause ons" for in to the CUME_ CENT_RANK return value dist 是类似 行 percent_ cit Type Co n nction, PERC guments of t minus 1 divid made as if th Oracle Data single hypoth ate to consta expressions ch by positio es must be c 用作聚集函 用行 r 在聚集 ] order_by_ nformation _DIST (cum is 0 to 1, inc is NUMBER. 似的(累计分布 _rank 值都为 onversion M CENT_RANK the function ed by the nu he hypotheti abase is to a hetical row w ant expressi s and the ex on. Therefore compatible. 函数时,它计 集分组中的排 _clause) on syntax, mulative distr clusive. The 布)函数。Pe 为 0。函数返 atrix" for mo K calculates, and a corre umber of row cal row r w aggregate. T within each a ions within e xpressions in e the numbe 算,对一个 排名减去 1, semantics s, and ribution) func first row in ction. The ra any set has ange a ercent_ra 返回一个 num ank 返回 0 到 mber 值。 到 1 ore information on for a hypoth esponding so ws in the ag ere inserted The argume aggregate g each aggreg n the ORDER er of argume 由函数参数和 再除以行数。 hetical row r ort specifica gregate gro d into the gro nts of the roup. There gate group. T R BY clause ents must be 和相应排序规 。Oracle 对 r tion, up. oup fore, The of e the 规则 对此 聚集组进行计算时,就像假定行 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; DEPAARTMENT_ID LAST_NAME SSALARY PR ------------------------ ---------------- ----------- ----------- 10 0 Whalen 4400 40 0 Marvis 6500 . . . 80 Vishney 10500 .1766470588 50 Everett 3900 .1811818182 30 Khoo .2 3100 . . . 80 Johnson 6200 .9411176471 50 Markle 2200 .9544545455 50 Philtankerr 2200 .9544545455 50 1 Olson 2100 . . . 19. PERCENTILE_CONTT   1 Syntax 19.   PERC CENTILE_CON (ORDER BY e [ OVER (que See A "Analy restrict NT(expr) WI expr [ DESC ery_partiti lso: ytic Functio tions of the O ITHIN GROUP C | ASC ]) ion_clause) ons" for in OVER claus P ] nformation se on syntax, semanticss, and 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 WHHERE deparrtment_id IN (30, 60); SALARYLASTT_NAME Y DEPARTMENTT_ID Percenntile_Cont Percent_Raank -------------- ----------- ------------- ---------------- ------------- Raph Khoo Baid Tobi Himu Colm Huno Erns Aust Pata Lore 20. 20. PERC haely o da ias uro menares old st tin aballa entz PERCENT 1 Syntax  CENTILE_DIS (ORDER BY e [ OVER (que See A "Analy restrict 11000 3100 2900 2800 2600 2500 9000 6000 4800 4800 4200 ILE_DISC    SC(expr) WI expr [ DESC ery_partiti lso: ytic Functio tions of the O 0 0 0 0 0 0 0 0 0 0 0   ITHIN GROUP C | ASC ]) ion_clause) ons" for in OVER claus 30 2850 0 30 2850 .2 30 2850 .4 30 2850 .6 30 2850 .8 30 2850 1 60 4800 0 60 4800 .25 60 4800 .5 60 4800 .5 60 4800 1 P ] nformation se on syntax, semanticss, and 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   1 Aggrega21. ate Syntax   rankk_aggregatte::= RANK ) 21. rank RANK O K(expr [, e (ORDER BY expr [ DES [ NUL [, expr [ [ ]... ) 2 Analytic k_analytic: K( ) OVER ([ que See A expr ]...) SC | ASC ] LLS { FIRST DESC | ASC NULLS { FI c Syntax := ery_partiti lso: WITHIN GRO T | LAST } C ] IRST | LAST ion_clause UP ] } ] ] order_by_ _clause) "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_TOO_REPORTT   1 Syntax 22.   RATI O 22. RAT set o null. Rat 求值 The clau 组值 算查 You can exp Exp 能嵌 获取 IO_TO_REPOR OVER ([ que See A "Analy restrict 2 Purpose TIO_TO_REP of values. If . tio_to_rep 值为 null,那 e set of value use, then the 值由 query_ 查询返回的所 u cannot use not nest ana pr. Please re pr 中不能使用 嵌套,但是在 取合法 expr RT(expr) ery_partiti lso: ytic Functio tions, includ e PORT is an a expr evalu port 只能用 那末 ratio_ es is determ e ratio-to-rep _partition 所有行。 e RATIO_TO alytic functio efer to "Abo 用 ratio_t 在 expr 中可 的更多信息 ion_clause ons" for in ding valid for analytic func uates to null, 用作分析函数 _to_repor mined by the port is comp n_clause 确 O_REPORT o ons, but you ut SQL Exp to_report 以使用内置函 。 ]) nformation rms of expr ction. It comp , then the ra 数。它计算一个 rt 值也为 nu query_par puted over a 确定。若忽略 or any other can use oth ressions" fo 或其他任何 函数表达式。 on syntax, r putes the rat atio-to-report 个值在一组值 ull。 rtition_c ll rows retur 略该子句,那 analytic func her built-in fu r information 分析函数。也 。请参阅 Ab semantics tio of a value t value also 值总和中占的 clause. If yo rned by the q 那末 ratio_ ction for exp unction expr n on valid fo 也就是说,此 bout SQL E s, and e to the sum evaluates to 的比率。若 e ou omit that query. _to_report pr. That is, ressions for orms of expr 此处分析函数 Expressio m of a o expr t 计 you r. 数不 ns 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::= { RE | RE | RE | RE | RE | RE | RE | RE | RE } (exp [ OV 23. The num 线性 EGR_SLOPE EGR_INTERCE EGR_COUNT EGR_R2 EGR_AVGX EGR_AVGY EGR_SXX EGR_SYY EGR_SXY pr1 , expr2 VER (analyt See A "Analy restrict 2 Purpose e linear regre mber pairs. Y 性回归函数适 EPT 2) tic_clause) lso: ytic Functio tions e ession funct You can use 适用于一组数 ] ons" for in ions fit an or e them as bo 数值对的最小 nformation rdinary-leas oth aggregat 二乘法回归线 on syntax, t-squares re te and analy 线。它可用作 semantics s, and egression lin ytic functions ne to a set o s. f 作聚集和分析析函数。 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 AVVGY AVGX ----------- ----------------- ------ ST_MMAN 28899.0555555556 7280 PU_MMAN 37785.4555555556 11000 SH_CCLERK 25531.9555555556 4925 PU_CCLERK 27709.2555555556 2780 ST_CCLERK 26631.6055555556 2785 8 REGR_SX23. XY, REGR_SSXX, and RREGR_SYY E Examples The regr hr. e following ex ression of tim employees xample calc me employe s: culates three ed (SYSDATE e types of dia E - hire_da agnostic sta ate) and sa atistics for th lary using th he linear he sample taable 下面 种线 SEL FR WH GR OR JOB_ ---- SA_M SA_R SH_C ST_C ST_M 24. 24. 面的列子计算 线性回归类型 算,hr.empl 型的诊断统计 loyees 表中 计: LECT job_i regr_s regr_s regr_s ROM emplo HERE depar ROUP BY jo RDER BY jo _ID R ------- --- MAN REP 168 CLERK CLERK MAN ROW_NU 1 Syntax  id, sxy(SYSDAT sxx(SYSDAT syy(SYSDAT oyees rtment_id ob_id ob_id; REGR_SXY -------- -- 3303500 819665.5 6 4248650 3531545 2180460 UMBER     ATE - hire ATE - hire ATE - hire IN (80, REGR_SXX ---------- 9300000.0 65489655.2 5705500.0 3905500.0 4548000.0 中,雇佣时间间 (SYSDATEE-hire_daate)和薪水的的三 e_date, sa e_date, sa alary) re e_date, sa alary) re egr_sxy, alary) re egr_sxx, egr_syy 50) REGR_SYY ---------- 1409642 6676562.55 3596039 4299084.55 1505915.2 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 FR ROM (SELE WH 25. 25. STDD 25. STD as b retu Std 同之 Ora defi Ora This that data FROM ECT last_n HERE r BET STDDEV   1 Syntax  DEV([ DISTI [ OVER (ana See A "Analy restrict 2 Purpose DDEV returns both an aggr rns zero wh ddev 返回 ex 之处在于,当计 cle Databas ned for the V acle 数据库 s function ta can be imp atype as the M employe TWEEN 51 A   INCT | ALL alytic_clau lso: ytic Functio tions e s the sample regate and a hen it has on xpr 的样本标 计算的输入数 se calculates VARIANCE a 中,标准偏差 kes as an a plicitly conve e numeric da name, row ees) AND 100; ] expr) use) ] ons" for in e standard d analytic func nly 1 row of i 标准偏差。它 数据只有一行 s the standa aggregate fu 差计算结果与 rgument any erted to a nu atatype of th w_number()) over(ORRDER BY laast_name) r nformation deviation of e ction. It diffe input data, w 它可用作聚集 行时,stddev ard deviation unction. 与 variance y numeric d meric dataty e argument on syntax, semanticss, and expr, a set rs from STD whereas STD of numbers DDEV_SAMP DDEV_SAMP . You can us in that STDD P returns nul se it DEV l. 集和分析函数 v 返回 0,而 s 数。它 与 std stddev_sa ddev_samp amp 返回 nul 的不 ll。 n as the squ e 用作集聚函 atatype or a ype. The fun . are root of the variance 函数计算结果果的平方根相等。 any nonnume nction return eric datatyp ns the same e 该函数参数可取任何数字类型或是任何能隐式转换成数字类型的非数字类型。函数返回类型 与函数参数类型相同,都为数字类型。 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 排序后薪水的累积标 准偏差: SEL "St FR LECT last_ tdDev" WH LAST ---- Raph Khoo Tobi Baid Himu Colm 26. 26. STDD 26. STD the Std ROM emplo HERE depar T_NAME ----------- haely o ias da uro menares STDDEV_ 1 Syntax  DEV_POP(exp [ OVER (ana See A "Analy restrict 2 Purpose DDEV_POP c population v ddev_pop 计 _name, saalary, STDDDEV(salary) over((ORDER BY hire_datte) oyees rtment_id = 30; SALARY StdDDev ------------- ---------- ----------- 110 0 00 31 00 5586.143357 28 00 4650.08896 29 00 4035.261125 26 00 3649.24465 25 00 3362.588829 POP     pr) alytic_clauuse) ] lso: ytic Functio tions e omputes the variance. Yo 计算总体标准 ons" for in e population ou can use it 准偏差并返回 nformation n standard d t as both an 回总体方差的 on syntax, semanticss, and eviation and aggregate d returns the and analytic e square roo c function. ot of 的平方根。它可用作聚集和和分析函数。。 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.44 Analyticc Example The sam e following ex mple hr.emp xample retu ployees tab rns the pop ble by depa ulation stand rtment: dard deviations of salarries in the 下面 SEL FR DEPA ---- . . 27. 27. STDD 面的例子返回回 hr.emplooyees 表中部 LECT depar last_n salary stddev ROM emplo ARTMENT_ID ---------- 10 20 20 . 100 100 100 110 110 STDDEV_ 1 Syntax  DEV_SAMP(ex [ OVER (ana See A "Analy rtment_id name, y, v_pop(sala oyees; LAST_NAME ---------- Whalen Hartstein Goyal Sciarra Urman Popp Higgens Gietz SAMP     xpr) alytic_clau lso: ytic Functio d, ary) over ----------- use) ] ons" for in 部门薪水的总总体标准偏差差: (PARTITIOON BY depaartment_idd) AS pop__std SSALARY PPOP_STD ----- ----------- ----------- 0 4400 3500 13000 3500 6000 7700 16444.18166 7800 16444.18166 6900 16444.18166 1850 12000 1850 8300 nformation on syntax, semantics s, 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.44 Analyticc Example The emp e following ex ployees tab xample retu ble by depar rns the sam rtment: mple standard deviation oof salaries inn the 下面 SEL hir FR DEPA ---- . . 28. 28. 面的例子返回回 employeees 表中部门薪薪水的样本标标准偏差: LECT depar last_n rtment_idd, hire_d name, salary date, stddev re_date ro y, ROM emplo ARTMENT_ID ---------- 10 20 20 30 30 30 30 . 100 100 100 100 110 110 SUM   1 Syntax  v_samp(sal ows BETWEE oyees; LAST_NAME ---------- Whalen Hartstein Goyal Raphaely Khoo Tobias Baida Chen Sciarra Urman Popp Higgens Gietz   lary) ove N unbounde er(PARTITI ed precedi ION BY de ing AND CU epartment_ URRENT ROW HIRE_ ------ ---- 17-SE 17-FE 17-AU 07-DE 18-MA 24-JU 24-DE 28-SE 30-SE 07-MA 07-DE 07-JU 07-JU _DATE S ----- ----- EP-87 EB-96 UG-97 EC-94 AY-95 UL-97 EC-97 EP-97 EP-97 AR-98 EC-99 UN-94 UN-94 SALARY CU ------ ---- 4400 13000 6000 4949 11000 3100 5586 2800 465 2900 4035 8200 2003 7700 1925 7800 1785 6900 1801 12000 8300 2616 UM_SDEV ------- 9.74747 6.14357 50.0896 5.26125 3.33056 5.91969 5.49713 1.11077 6.29509 _id ORDER W) AS cum_s R BY sdev 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 Errrazuriz 12000 800900 . . . 8 149 Tayylor 600 300200 8 149 Huttton 800 399000 11 149 Abeel 000 500000 6 201 Fayy 000 66000 8 205 Gieetz 300 88300 24 Kinng 000 244000 29. VAR_POPP   1 Syntax 29.   VAR_ 29. VAR this Var This that data 该函 与函 _POP(expr) See A "Analy restrict 2 Purpose R_POP return set. You ca r_pop 返回非 s function ta can be imp atype as the 函数参数可取 函数参数类型 [ OVER (an lso: ytic Functio tions e ns the popul an use it as b 非空数集的总 kes as an a plicitly conve e numeric da 取任何数字类 型相同,都为 nalytic_cla ons" for in lation varian both an aggr 总体方差。它 rgument any erted to a nu atatype of th 类型或是任意 数字类型。 ause) ] nformation nce of a set o regate and a 它可用作聚集 y numeric d meric dataty e argument 能隐式转换成 on syntax, semanticss, and of numbers analytic func after discard ction. ding the nulls in 集和分析函数数。 atatype or a ype. The fun . any nonnume nction return eric datatyp ns the same e 成数字类型的的非数字类型型。函数返回类型 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(expr2) - SUM(expr)2 / 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.c FR calendar_m WH A GR CALE ---- 1998 1998 1998 1998 1998 1998 1998 1998 1998 1998 1998 1998 30. 30. VAR_ ROM sales month_des HERE s.tim AND t.cal ROUP BY t. ENDAR Va ----- ----- 8-01 8-02 6.132 8-03 4.705 8-04 4.692 8-05 1.552 8-06 2.371 8-07 3.746 8-08 3.785 8-09 3.575 8-10 3.434 8-11 3.424 8-12 4.893 VAR_SAM 1 Syntax  _SAMP(expr) See A "Analy restrict s s, times c) "Var_SSamp" me_id = t. s t lendar_yea .time_id .calendar_ ar = 1998 _month_de esc; ar_Pop Vaar_Samp ------ ----------- 0 21E+11 1.22264E+12 58E+11 7.05587E+11 29E+11 6.25572E+11 24E+12 1.94405E+12 11E+12 2.84453E+12 64E+12 4.37708E+12 52E+12 4.32260E+12 53E+12 4.02222E+12 59E+12 43E+12 3.81 45E+12 3.76669E+12 37E+12 5.33386E+12 MP     ) [ OVER (a lso: ytic Functio tions analytic_cl ons" for in ause) ] nformation on syntax, semantics s, and 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(expr2) - SUM(expr)2 / 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" 3 Aggrega30. ate Examplle The emp e following ex ployees tab xample retu ble. rns the sammple variancee of the salaaries in the ssample 下面 SEL VAR_ ---- 30.4 Plea 请参 31. 31. VARI 面的例子返回回 employeees 表中薪水值值的样本方差差: LECT var_s _SAMP(SALAR ----------- 15283140 4 Analytic ase refer to 参阅var_pop VARIANC 1 Syntax  IANCE([ DIS [ OVER See A "Analy restrict samp(salaary) FROM employees; RY) --- 0.5 c Example the analytic p分析函数的 E     STINCT | AL R (analytic lso: ytic Functio tions example for VAR_POPP. 的例子。 LL ] expr) c_clause) ] ons" for in nformation on syntax, semanticss, and 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
还剩92页未读

继续阅读

下载pdf到电脑,查找使用更方便

pdf的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享pdf获得金币 ] 2 人已下载

下载pdf

pdf贡献者

airmancn

贡献于2013-01-20

下载需要 8 金币 [金币充值 ]
亲,您也可以通过 分享原创pdf 来获得金币奖励!
下载pdf