【转】分析函数语法解析

下面文章对分析函数的语法总结很好，特借鉴过来看一下，以备后用。

以下文章转自网络：

分析函数语法解析

2009年01月14日 星期三 10:52

分析函数语法解析 1 分析函数整体语法 分析函数的语法如下： analytic_function([ arguments ]) OVER (analytic_clause) 这里： Analytic_function是分析函数的名称； arguments是分析函数的参数； over用来标识函数是一个分析函数； analytic_clause用来确定分析函数的操作规则。包括query_partition_clause、order_by_clause和windowing_clause三个子句。 2 Analytic_function 分析函数的名称。Oracle10gR2带的内置分析函数有39个；对于用户自定义的分析函数，分析函数名称需要满足标识符规则。 3 Arguments 分析函数所带参数，内置分析函数一般带0-3个参数。参数可以是任何数字类型或是可以隐式转换为数字类型的数据类型。Oracle根据最高数字优先级别确定函数参数，并且隐式地将需要处理的参数转换为数字类型。 用户自定义分析函数的参数，可以根据实际情况使用。 4 Over 用以标识函数是一个分析函数，对于既可作为聚集函数又可作为分析函数的函数，Oracle无法识别，必须用over来标识此函数为分析函数。也不是说只可用作分析函数的函数就可以省略该关键字，over关键字是必须的，是分析函数就必须使用。 Over后面的()中是analytic_clause，即使analytic_clause的三个部分全不使用，()也必不可省，否则Oracle因不能识别函数为分析函数而报错。 5 Analytic_clause Analytic_clause的语法如下： [ query_partition_clause ] [ order_by_clause [ windowing_clause ] ] 这里： query_partition_clause是查询分组子句； order_by_clause是分组排序子句； windowing_clause是窗口范围子句。 分析函数在查询结果集确定之后才开始进行计算，Analytic_clause就是用来定义函数怎样对查询结果集进行分组计算的。 根据Oracle对查询和分析函数的处理方法可知，在select和order by子句中都可以使用分析函数。 query_partition_by、order_by_clause和windowing_clause三个子句是可选的，将三个子句分别简记为p，o，w。 合法的组合方式有如下6种： 1).        Pow (query_partition_clause order_by_clause windowing_clause) 分组，排序，定义窗口范围 2).        Po (query_partition_clause order_by_clause) 分组，排序，窗口默认为range between unbounded preceding and current row 3).        P (query_partition_clause) 分组，不排序，没有窗口 4).        Ow (order_by_clause windowing_clause) 分组为整个查询结果集，排序，定义窗口范围 5).        O (order_by_clause) 分组为整个查询结果集，排序，窗口默认为range between unbounded preceding and current row 6).        Null () 分组为整个查询结果集，不排序，没有窗口 因为只有存在order_by_clause，才能有windowing_clause，故不存在如下两种形式的组合： pw(query_partition_clause windowing_clause) w(windowing_clause) 总结： 1).        对于是否存在order_by_clause，分析函数可以分为两类，含有order_by_clause的一般称为windowing function，不含的称为reporting function。 2).        Windowing function，对查询结果集进行分组，排序，根据窗口范围计算分组中每一行的函数结果。 3).        Reporting function，对查询结果集进行分组，不排序，窗口范围为整个分组，在每一个分组内，计算整个分组的函数值，再将函数值分别赋给分组内的每一行。 注意事项： 1)．Analytic_clause内不能包含任何分析函数。 2). 用户自定义分析函数和内置函数分析函数都可以使用over (analytic_clause)。 5.1 Query_partition_clause query_partition_clause的语法如下： PARTITION BY value_expr[, value_expr ]... 这里： partition by是关键字，表示要将查询结果集分组； value_expr是分组表达式。 Partition by子句根据一个或多个value_expr将查询结果集分成若干组。分组的原理与group by分组的原理类似，将value_expr相同的行集中在一起。 若不使用该子句，那末函数将整个查询结果集作为一个分组。 value_expr可以为常量、表列、非分析函数、函数表达式，或者前面这些元素的任意组合表达式。 5.2 Order_by_clause Order_by_clause的语法如下： ORDER BY expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, expr [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] ]... 这里： order by是关键字，表示要将分组排序； expr是排序表达式； asc|desc和nulls first|null last是排序的附加规则。 Order_by_clause根据一个或多个expr对分组进行排序。 Expr只能为表达式，不能为位置编号或列别名。 注意事项： 1).        当窗口范围为 Range between unbounded preceding and current row 或 Range between current row and unbounded following 时， 可以在order_by_clause中使用多个expr对分组进行排序。其他range窗口范围只能使用一个expr。使用rows定义窗口范围无此限制。 2). asc|desc 指定排序是按expr升序还是降序排序，若不指定则默认按asc进行升序排序。 3). nulls first|nulls last 指定若返回行包含空值，该值应该出现在排序序列的开始还是末尾。升序排序的默认值是nulls last，降序排序的默认值是nulls first。 5.3 Windowing_clause Windowing_clause的语法如下： { ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND    { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING } } 这里， rows 是关键字，指定窗口由物理行构成； range 是关键字，指定窗口由逻辑偏移量构成； between…and是关键字，用来指定窗口的起始点和终结点； Unbounded preceding指明窗口开始于分组的第一行； Current row，作为起始点，指明窗口开始于当前行或当前值；作为终结点，指明窗口结束于当前行或当前值； Unbounded following指明窗口结束于分组的最后一行； Value_expr为物理或逻辑偏移量表达式。 Windowing_clause用来指定分组中当前行的计算范围。 不论rows还是range窗口，窗口总是在分组中从上至下滑动的。 窗口范围可以由between…and限定，也可以不用between…and，不用的都表示窗口到当前行结束。 ROWS窗口，是由分组排序后分组中若干连续的行构成的窗口。 以下是全部合法的ROWS窗口范围: 1).        Rows between unbounded preceding and unbounded following 窗口开始于分组第一行，结束于分组最后一行。 2).        Rows [between] unbounded preceding [and current row] 窗口开始于分组第一行，结束于当前行。 3).        Rows between unbounded preceding and value_expr preceding 窗口开始于分组第一行，结束于当前行前value_expr行。 4).        Rows between unbounded preceding and value_expr following 窗口开始于分组第一行，结束于当前行后value_expr行。 5).        Rows between current row and unbounded following 窗口开始于当前行，结束于分组最后一行。 6).        Rows [between current row and] current row 窗口开始于当前行，结束于当前行。 7).        Rows between current row and value_expr following 窗口开始于当前行，结束于当前行后value_expr行。 8).        Rows between value_expr preceding and unbounded following 窗口开始于当前行前value_expr行，结束于分组最后一行。 9).        Rows [between value_expr] preceding [and current row] 窗口开始于当前行前value_expr行，结束于当前行。 10).        Rows between value_expr1 preceding and value_expr2 preceding 窗口开始于当前行前value_expr1行，结束于当前行前value_expr2行。这里一定要满足value_expr1>=value_expr2。 11).        Rows between value_expr1 preceding and value_expr2 following 窗口开始于当前行前value_expr1行，结束于当前行后value_expr2行。 12).        Rows between value_expr following and unbounded following 窗口开始于当前行后value_expr行，结束于分组最后一行。 13).        Rows between value_expr1 following and value_expr2 following 窗口开始于当前行后value_expr1行，结束于当前行后value_expr2行。这里一定要满足value_expr1<=value_expr2 14).        Rows unbounded preceding 与2等价。 15).        Rows current row 与6等价。 16).        Rows value_expr preceding 与9等价。 RANGE窗口，相当于给order_by_clause中的expr加一个where限定条件，分组中满足条件 (当order by expr asc时，where expr between a and b 当order by expr desc时 where expr between b and a)的所有行构成一个逻辑窗口。 其中a由分组中第ra行的值计算而来，b由分组中第rb行的值计算而来，且ra<=rb。 根据range中是否包含unbounded，可以分为两类，含unbounded range窗口，和不含unbounded range窗口。 对于前者，有ra<=rb。当order by expr asc时，a<=b，须使用where expr between a and b；当order by expr desc时，a>b，须使用where expr between b and a。 对于后者，有ra=rb。总有a<=b，使用where expr between a and b。 对于在order_by_clause中可以使用多个expr的窗口： Range between unbounded preceding and current row Range between current row and unbounded following 当它们使用多个expr排序时(注意不是一个)分别等价于： Rows between unbounded preceding and current row Rows between current row and unbounded following 假设分组第一行的值为first_value，最后一行的值为last_value。当前行的值为current_value。 1).        Range between unbounded preceding and unbounded following Where expr between first_value and last_value –-when asc or expr between last_value and first_value     --when desc 2).        Range [between] unbounded preceding [and current row] Where expr between first_value and current_value or expr between current_value and first_value 3).        Range between unbounded preceding and value_expr preceding Where expr between first_value and current_value-value_expr or expr between current_value-value_expr and first_value 4).        Range between unbounded preceding and value_expr following Where expr between first_value and current_value+value_expr or expr between current_value+value_expr and first_value 5).        Range between current row and unbounded following Where expr between current_value and last_value or expr between last_value and current_value 6).        Range [between current row and] current row Where expr = current_value 7).        Range between current row and value_expr following Where expr between current_value and current_value+value_expr 8).        Range between value_expr preceding and unbounded following Where expr between current_value-value_expr and last_value 9).        Range [between value_expr] preceding [and current row] Where expr between current_value-value_expr and current_value 10).        Range between value_expr1 preceding and value_expr2 preceding 这里一定要满足value_expr1>=value_expr2。 Where expr between current_value-value_expr1 and current_value-value_expr2 11).        Range between value_expr1 preceding and value_expr2 following Where expr between current_value-value_expr1 and current_value+value_expr2 12).        Range between value_expr following and unbounded following Where expr between current_value+value_expr and last_value or expr between last_value and current_value+value_expr 13).        Range between value_expr1 following and value_expr2 following 这里一定要满足value_expr1<=value_expr2。 Where expr between current_value+value_expr1 and current_value+value_expr2 14).        Range unbounded preceding 与2等价。 15).        Range current row 与6等价。 16).        Range value_expr preceding 与9等价。 注意事项： 1).若windowing_clause由rows指定，则： (1)value_expr是物理偏移量，它必须是常量或值为非负数的表达式。 (2)若value_expr是起点的一部分，那末它必须在终点之前对行求值。 2).若windowing_clause由range指定，则: (1)value_expr是逻辑偏移量。它必须是常量或值为非负数的表达式或时间间隔文字常量。 (2)若value_expr值为一个数字，那末order_by_clause中 expr必须为数字或date类型。 (3)若value_expr为一个间隔值，那末order_by_clause中expr必须是一个date类型。 3).若完全忽略windowing_clause，那末默认的窗口范围为 range between unbounded preceding and current row 。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12932950/viewspace-669765/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12932950/viewspace-669765/