Oracle分析函数是通过将行分组后,再计算这些分组值。它们与聚焦函数不同之处在于能够对每一分组返回多行值。它的设计目的是为了解决诸如“累计计算”,“找出分组内百分比”,“前N条查询”等问题。其实大部分的问题都是可以用PL/SQL来解决,但是它的性能并不能达到我们所期望的效果。分析函数是SQL语言的一种扩充,它并不是仅仅为了使代码变得更简单,而且它的速度比纯粹的SQL或者PL/SQL更快。
一、常用的分析函数分类
1. 等级(Ranking)函数:用于寻找前N条查询,如:RANK、DENSE_RANK、ROW_NUMBER等,例如:
select product_type, item, total_qoh,
row_number()over (partition by product_typeorder by item asc) ord, --分组排序,可进行每组前N条查询
'' "End"from tab_product;
2. 制表(Reporting)函数:用于计算不同的累计,如:SUM、AVG、COUNT、MAX、MIN等,例如:
select product_type, item, total_qoh,
sum(total_qoh) over(partition by product_type) total_by_prodtype, --分组求和
'' "End"
from tab_product;
3. 开窗(Windowing)函数:与制表函数同名,与制表函数不同之处:制表函数作用于一个分区或一组上的所有列,开窗函数则把一个分区或一组分为不同的窗口(比如:每日结余),例如:
select product_type, item, total_qoh,
sum(total_qoh) over(partition by product_typeorder by item) total_by_prodtype, --默认情况窗口范围是当前行和前面所有行,这个在Order_By_Clause子句中介绍
'' "End"
from tab_product;