ORACLE 统计分析函数使用实例

统计分析函数也称为窗口函数。分析函数以一定的方法在一个与当前行相关的结果集子集中进行计算。这个子集可以称为窗口。

分析函数具有3个基本组成部分：分区子句，排序子句以及开窗子句。分析函数的基本语法是：
function(argument1,argument2,...argumentN) over ([partition-by-clause][order-by-clause][windowing-clause])

function：是所调用的接收0个或多个参数的分析函数。分析函数包括Lag、Lead、First_value、Last_value、Rank、Dense_rank、Row_number、Percentile_cont、Ntile、Listagg等。
分区子句(partition-by-clause)：按照分区列的值对数据行进行分组。所有分区列的值相同的数据行被组合为一个数据分区。
排序子句(order-by-clause)：通过一列或一个表达式的值来对数据分区中的行进行排序。在分区子句中按照分区列的值来进行排序，在数据分区内的数据行按照排序列的值进行排序。使用NULLS FIRST或NULLS LAST子句可以把空值放到数据分区的最上面或最下面。
开窗子句(partition-by):用来指定不同的分区边界，而order-by子句可以改变分区内的排序顺序。

开窗子句(windowing-clause)指定了分析函数进行运算的数据子集，可以使用窗口说明子句来指定上下边界条件。语法如下：
[ROWS | RANGE] BETWEEN AND
whereas
is [UNBOUNDED PRECEDING | CURRENT ROW | n PRECEDING | n FOLLOWING]
is [UNBOUNDED FOLLOWING | CURRENT ROW | n PRECEDING | n FOLLOWING ]

1）UNBOUNDED PRECEDING | CURRENT ROW的使用：


2）n PRECEDING | n FOLLOWING的使用：


Lag和Lead函数提供了跨行引用的能力。Lag提供了访问结果集中前面的行的能力，Lead函数允许访问结果集中后面的行。通常可用于同比数据或者环比百分比增减等。
Lag和Lead仅仅支持partition-by子句和order by子句，不支持开窗子句。
语法形式为:lag(expression,offset,default) over (partition-clause order-by-clause)

Lag函数使用位移量n来访问第n行的数据，比如LAG(sale,10,sale),访问分区中向前推10行的数据。
Lead函数与Lag函数类似，它访问排序后的结果集中当前值后面的数据行。

first_value和last_value函数在计算排序过的结果集中的最大值和最小值的时候是很有用的。first_value函数从一个数据窗口中的第一行获取列植，而last_value函数从该窗口中最后一行获取列值。
first_value和last_value函数支持分区子句和开窗子句。可用于例如生成某个产品在一定市场领域的最高销售额、定义窗口子句计算到目前为止的最大销售额或者过去的几周哪家的销售额最大等等。
除了获取最大列值以外，还可以通过最上面那一行中获取其他列。
空值通过[respect nulls | ignore nulls]子句来进行处理。respect nulls子句是默认值。
如果指定了ignore nulls子句，则first_value函数将会返回在窗口中第一个列植不为空的行的值。


nth_value函数：获取排序后的结果集中的任意一行，first_value函数可以被写为nth_value(column_name,1)。
nth_value的语法为：
NTH_VALUE(measure,n)[FROM FIRST|FROM LAST][RESPECT NULLS|IGNORE NULLS] OVER (partitioning-clause order-by-clause windowing-cluase)

RANK函数以数值形式返回一个数据行在排序后的结果集中的位置。用于查询最上面或最下面的N行，比如销售量在前10位的周。
开窗子句在RANK函数中不适用，rank函数是应用于数据分区中的所有行上的。
dense_rank函数是rank函数的变体。两者的区别在于当存在并列的时候dense_rank函数不会跳过排名值。


row_number函数为有序结果集中的每一行分配唯一的行编号。如果声明了分区子句，则为每一行分配一个基于其在该有序分区中位置的唯一编号。如果一个数据分区中的两行具有同样的值，row_number函数的值是不确定的。

ratio_to_report函数计算数据分区中某个值与和值的比率。比如计算每家门店的销售额占总销售额的百分比。


percent_rank函数以0到1之间的分数形式返回某个值在数据分区中的排名。对应计算某个值在结果集中按百分比所处的相对位置是很有用的。


percentile_cont函数可以用来计算中位值，比如一个城市或者地区中等收入家庭的收入值。其中中位值的percent_rank为0.5，使用percentile_cont(0.5)子句会返回中位值。
percentile_cont函数的语法是：
percentile_cont(expr) within group (sort-clause) over (partition-clause order-by-clause)



percentile_disc函数与percentile_cont函数类似但不同的是使用了离散分布模型。percentile_cont(0.5)会计算两个离得最近的值的平均值。


ntile函数对一个数据分区中的有序结果集进行划分，将其分组为各个桶，并为每个小组分配唯一一个组编号。这个函数可以用于移除异常值，将异常值分组到顶部或底部的桶中，然后在统计分析的时候将这些值排除。


listagg函数提供了将来自多个行中的列值转化为列表格式的能力。比如吧一个部门中所有员工的名字连起来。
语法格式如下：listagg(string,separator) within group (order-by-clause) over (partition-by-clause)

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

转载于:http://blog.itpub.net/26770925/viewspace-1369724/