Oracle10gR2分析函数

Orac c le100gR2分析函函数(Translat t ing By ca a izhuoyi)说明明：1、 2、 1 A Ana agg callewind calc of ro 分析分组一行物理Ana ORD befo in th 分析有wh 数只Ana repo 分析anaana原文中底色原文中淡蓝色nalytic Fu 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 只能用于sel alytic functio orting aggre 析函数通常用alytic_fun alytic_fun 为黄的部分翻色字体的文字unctionsns 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 ect 列或ord ns are comm gates.用于计算数据nction::=nction([ 翻译存在商榷字，不宜翻译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 子句der 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 ooup 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 fromws 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, ce e ntered, and ，和输出集合合报表。

O VER (ana a lytic_cla a use)ana[ q[ oquePAR { | }ordORD { e [ A [ N [, ].winalytic_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 Yxpr[, valu _expr[, vaause::= LINGS ] B sition | C ]ST | NULL | positio DESC ]FIRST | lause ::=lause ] windowing use::=ue_expr ]alue_expr BYc_alias }LS LAST ]on | c_al NULLS LAS=g_clause ...r ]... )lias }ST ] ] ]{ R { B { | | } AN { | | }| { | | }}The以下1.1 Spe disc Ana1.2 Ana any deterem ROWS | RAN BETWEENUNBOUNDE CURRENT value_exNDUNBOUNDE CURRENT value_ex UNBOUNDECURRENT value_ex e semantics 下各节将讨论analytic_fu ecify the nam cussion of se alytic_fun Arguments alytic functio nonnumeric ermines the aining argum NGE }ED PRECEDI ROWxpr { PREC ED FOLLOWI ROWxpr { PREC ED PRECED ROWxpr PRECED of this synta 论分析函数语unctionme of an ana emantics).nction 指定sns take 0 to c datatype th argument w ments to tha INGCEDING | INGCEDING | DING DINGax are discus 语法的语义。

alytic functio 定分析函数的3 argument hat can be im with the high at datatype. FOLLOWING FOLLOWING ssed in the s on (see the li 的名称。

(请参ts. The argu mplicitly con est numeric The return t 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, unlessg this 表) pe or cle s theotherwise noted for an individual function.分析函数可取0-3个参数。

参数可以是任何数字类型或是可以隐式转换为数字类型的非数字类型。

Oracle根据最高数字优先级别确定函数参数，并且隐式地将需要处理的参数转换为适合的数据类型。

函数的返回类型也为数字类型，除非此函数另有说明。

See Also:"Numeric Precedence" for information on numeric precedence and Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion请参阅："Numeric Precedence"可获取数字优先级的相关信息，参阅表2-10—隐式类型转换矩阵，可获取隐式转换的更多信息。

1.3analytic_clauseUse OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM, WHERE, GROUP BY, and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.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 analyticfunction in a subquery and compute another analytic function over it.Analytic_clause的任何部分都不能指定任何分析函数。