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
是开窗语句,用于细化数据区间。
具体示例,请参照下一节