"Oracle分析函数"
Oracle分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。
认识分析函数:
分析函数和聚合函数的不同之处在于,普通的聚合函数用groupby分组,每个分组返回一个统计值,而分析函数采用partitionby分组,并且每组每行都可以返回一个统计值。
分析函数的形式:
分析函数带有一个开窗函数over(),包含三个分析子句:分组(PartitionBy)、排序(OrderBy)、窗口(Rows)。他们的使用形式如下:
over(PartitionBy xxx OrderBy yyy rows between zzz)
分析函数的例子:
示例目的:显示各部门员工的工资,并附带显示该部分的最高工资。
last_value(sal)over(…)是一个分析函数,整体作为一个select选择列,列别名为max_sal
理解over()函数:
over()函数是分析函数的核心,用于定义分析函数的窗口。over()函数可以分为三部分:partition by、order by和rows。
partition by:用于定义分析函数的分组依据,例如partition by department将数据分组到不同的部门中。
order by:用于定义分析函数的排序依据,例如order by salary将数据按薪资从高到低排序。
rows:用于定义分析函数的窗口,例如rows between 2 preceding and 2 following将窗口定义为当前行和前两个行后两个行。
常见分析函数:
1. first_value()和last_value():求最值对应的其他属性
示例:last_value(sal)over(…)是一个分析函数,整体作为一个select选择列,列别名为max_sal
2. rank(),dense_rank()和row_number():求排序
示例:rank() over (order by salary) as ranking将员工按照薪资从高到低排序,并返回当前员工的排名
3. lag()和lead():求之前或之后的第N行
示例:lag(sal, 2) over (order by salary) as previous_sal将当前员工的薪资与前两个员工的薪资比较
4. rollup()和cube():排列组合分组
示例:rollup(department, salary)将数据分组到不同的部门中,并计算每个部门的薪资总和
5. max(),min(),sum()和avg():求移动的最值总和与平均值
示例:avg(sal) over (partition by department) as avg_sal将每个部门的薪资平均值计算出来
6. ratio_to_report():求百分比
示例:ratio_to_report(sal) over (partition by department) as ratio将每个部门的薪资占总薪资的百分比计算出来