over partition by order by rows|range（1）

oralce提供了分析函数，用于一些常见的数据分析。

语法格式如下：

function_name(arg1,arg2...argn)

over (

partition by arga,argb...

order by argA,argB

[range|rows]  between

 [unbounded preceding |current row| n preceding|n following]

and

[unbounded following|current row|n preceding|n follwing]

)

对于function_name，可以是聚合函数，例如sum，count等

也可以是分析函数，如下：

row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)
 

partition by

用于数据分区，相同参数的一组数据为同一个分区，所有的聚合函数和分析函数均计算同一个分区的数据。

例如：

parition by year,week

那么将计算同一个年-周里面的数据。

order by

用于实现分区内部数据的排序，排序以后，每条数据将在分区内部有新的顺序。相应的聚合函数和分析函数，也将按这个顺序计算。

rows|range between 

unbounded preceding|current row|n preceding|n following

and 

unbounded following|current row|n preceding|n following

是开窗语句，用于细化数据区间。

具体示例，请参照下一节