oracle从8.1.6开始提供分析函数,用于计算基于组的某种聚合值。它和聚合函数的不同之处在于每个组返回多行,聚合函数每个组只返回一行。
开窗函数:指定了分析函数工作的数据窗口大小,这个数据大小会随数据行数变化而变化,示例如下:
over(order by salary) 按照salary排序进行累计,order by是个默认的开窗函数
over(partition by deptno)按照部门分区
over(order by salary range between 50 preceding and 150 following)每行对应的数据窗口是之前行幅度值不超过50,之后行幅度值不超过150
over(order by salary rows between 50 preceding and 150 following)每行对应的数据窗口是之前50行,之后150行
over(order by salary rows between unbounded preceding and unbounded following)每行对应的数据窗口是从第一行到最后一行,等效:over(order by salary range between unbounded preceding and unbounded following)
AVG功能描述,用于计算一个组和数据窗口内表达式的平均值。
sample:select avg(salary) over(partition by manager_id order by hire_date rows between 1 preceding and 1 following) as avg_salary from employee.
PERCENTILE_RANK,返回一个与输入的分布百分比值相对应的数据值,分布百分比的计算方法见函数PERCENT_RANK,如果没有正好对应的数据值,就通过下面算法来得到值:
RN = 1+ (P*(N-1)) 其中P是输入的分布百分比值,N是组内的行数
CRN = CEIL(RN) FRN = FLOOR(RN)
if (CRN = FRN = RN) then
(value of expression from row at RN)
else
(CRN - RN) * (value of expression for row at FRN) +
(RN - FRN) * (value of expression for row at CRN)
注意:本函数与PERCENTILE_DISC的区别在找不到对应的分布值时返回的替代值的计算方法不同。