Oracle 分析函数的学习笔记一 语句的成份

分析函数，最早是从ORACLE8.1.6开始出现的，它的设计目的通常是为了解决诸如“累计计算”，“找出分组内百分比”，“前-N条查询”，“移动平均数计算”"等问题。其实大部分的问题都可以用PL/SQL解决，但是它的性能并不能达到你所期望的效果。分析函数是SQL言语的一种扩充，它并不是仅仅试代码变得更简单而已，它的速度比纯粹的SQL或者PL/SQL更快。现在这些扩展已经被纳入了美国国家标准化组织SQL委员会的SQL规范说明书中。

 分析函数用于计算基于组的某种聚合值，特征就在于over()。如果从语法格式上区分的话，没加over()的即是集合函数，加了over()即是分析函数, over不能单独使用。行的分组被称窗口，并通过分析语句定义。对于每行而言，定义了一个“滑动”窗口。这个窗口定义了“当前行”参见运算的行的范围。窗口大小可以基于物理行数，也可以基于逻辑间隔如时间。

使用 OVER 分析子句来指示函数操作在一个查询结果集上。这就是说，它在FROM, WHERE, GROUP BY, and HAVING 子句之后进行计算。你可以在select 列表或者order by子句中指定分析函数。要基于分析函数过滤查询的结果集，可在父查询里嵌套这些函数，然后过滤嵌套子查询的结果集。除了ORDER BY（按…排序）语句外，分析函数是一条查询被执行的操作。所有合并、WHERE、GROUP BY、HAVING语句都是分析函数处理之前完成的。因此，分析函数只出现在选择列表或ORDER BY（按…排序）语句中。

 Over()里面三类参数：

query_partition_clause子句：Partition by exp1[ ,exp2]...；主要用于分组，可以理解成select中的group by；不过它跟select语句后跟的group by子句并不冲突；指定该子句之后，前面的函数起效范围就是该分组内，若不指定，函数将查询结果集中的所有行当作一个单独的组。你可以在同一个子查询中指定多个分析函数，每个具有相同或不同的PARTITION BY keys。

Order by_子句：Order by exp1[asc|desc] [ ,exp2 [asc|desc]]... [nulls first|last]；指定数据在一个分区中如何进行排序。其参数基本与select中的order by相同，对所有分析函数而言（除 PERCENTILE_CONT 、PERCENTILE_DISC (只需要一个单独的键)外）, 你可以在一个分区中指定多个键值，每个键值由变量表达式确定，并确定了一指定的序列，这么做尤其有用，在使用函数获得排名时，因为第二个表达式能解析唯一值与第一个表达式之间的关系。Nulls first|last是用来限定nulls在分组序列中的所在位置的，我们知道oracle中对于null的定义是未知，所以默认order by的时候nulls总会被排在最前面。如果想控制值为null的行显示位置，nulls first|last参数就能派上用场了。

限制：在分析函数里面，ORDER BY 子句必须使用一个表达式。 SIBLINGS 关键字是非法的（它仅在一个父子层级关系查询中才有意义）。Position (position) and column aliases (c_alias) 也是非法的。

开窗子句

Window子句：该子句的语法比较复杂，具体可以见下图；

一些分析函数允许使用开窗子句。在本节最后将列出分析函数的列表，其中带有(*)星号的，允许跟随开窗子句。

ROWS | RANGE

这些关键字为每个行定义一个“窗口”（一个物理或者逻辑的行集），用于计算函数的结果。这个函数然后将被应用到窗口中的所有的行。窗口从头到尾随着查询结果或者分区进行“滑动”.

• ROWS 在物理单元（行）中指定窗口.
• RANGE 指定窗口为一个逻辑偏移。

除非你已经指定了order_by_clause，否则你不能指定该子句。

BETWEEN ... AND

 使用BETWEEN ... AND 子句来指定窗口的开始和结果点.

如果你省略BETWEEN只指定一个结束点， Oracle 认为从起始点开始，结束点缺省为当前行。

UNBOUNDED PRECEDING

指定UNBOUNDED PRECEDING ，来指定窗口在分区内开始的首行. 这是用来指定起始点的，不能用于指定结束点。

UNBOUNDED FOLLOWING

指定 UNBOUNDED FOLLOWING，来指定窗口在分区中结束的最后一尾行。这是用于指定结束点的，不能用于指定开始点。

CURRENT ROW

作为一个起始点， CURRENT ROW 指定窗口在当前行或者值（分别取决于是否指定了ROW or RANGE）开始. 在这种情况下，结束点不能是value_expr PRECEDING.

作为一个结束点，CURRENT ROW 指定窗口在当前行或者值（分别取决于是否指定了ROW or RANGE）结束。在这种情况下，开始点不能是value_expr FOLLOWING.

value_expr PRECEDING or value_expr FOLLOWING

For RANGE or ROW:

• 如果 value_expr FOLLOWING为起始点,那么结束点必须为value_expr FOLLOWING.
• 如果value_expr PRECEDING 为结束点,那么起始点必须为value_expr PRECEDING.

如果你正定义一个数字格式的时间间隔逻辑窗口, 那么你可能会需要使用转换函数.

如果你指定了ROWS:

• value_expr是一个物理位移.它必须是一常量或者表达式,必须被评估为一个正的数值.
• 如果value_expr 是起始点的一部分,那么它必须评估为结束点之前的某一行.

如果你指定了RANGE:

• value_expr 是一个逻辑的位移. 它必须是一常量或者表达式,以评估为一个正的数值或者一个间隔常量.

• 在order_by_clause中,你只可以指定一个表达式.
• 如果将 value_expr 评估为一个数值,那么ORDER BY expr必须为一个 NUMBER or DATE 数据类型的.
• 如果将 value_expr 评估为一个间隔值,那么ORDER BY expr 必须为DATE类型.

如果你省略了整个windowing_clause , 那么缺省为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

分析函数通常被用于数据仓库环境.函数后面跟有星号的(*) 允许使用完整的语法,包括windowing_clause.

AVG *

CORR *

COVAR_POP *

COVAR_SAMP *

COUNT *

CUME_DIST

DENSE_RANK

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *

LEAD

MAX *

MIN *

NTILE

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

RANK

RATIO_TO_REPORT

REGR_ (Linear Regression) Functions *

ROW_NUMBER

STDDEV *

STDDEV_POP *

STDDEV_SAMP *

SUM *

VAR_POP *

VAR_SAMP *

VARIANCE *