Oracle分析函数总结

                 Oracle分析函数总结

在日常的生产环境中，我们接触得比较多的是OLTP系统(即Online Transaction Process)，这些系统的特点是具备实时要求，或者至少说对响应的时间多长有一定的要求；其次这些系统的业务逻辑一般比较复杂，可能需要经过多次的运算。比如我们经常接触到的电子商城。
在这些系统之外，还有一种称之为OLAP的系统(即Online Aanalyse Process)，这些系统一般用于系统决策使用。通常和数据仓库、数据分析、数据挖掘等概念联系在一起。这些系统的特点是数据量大，对实时响应的要求不高或者根本不关注这方面的要求，以查询、统计操作为主。
分析函数允许你对一个数据集进排序和筛选，适于复杂的sql查询，并且高效，日后在工作中遇到复杂的sql查询时，除了罗列sql外，也可以借鉴一下分析函数换个思路去解决问题。
既然叫做函数，依照对函数的理解，它应有一些参数，另外分析函数与我们常用聚集函数同名（如：sum()），所以应有一些关键字，以下是分析函数的语法：
例:sum(sal) over (partition by deptno order by ename) new_alias
sum就是函数名
(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)
over 是一个关键字,用于标识分析函数,否则查询分析器不能区别sum()聚集函数和sum()分析函数。
partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区。
order by ename 是可选的order by 子句,有些函数需要它,有些则不需要.依靠已排序数据的那些函数,如:用于访问结果集中前一行和后一行的LAG和LEAD,必须使用,其它函数,如AVG,则不需要.在使用了任何排序的开窗函数时,该子句是强制性的,它指定了在计算分析函数时一组内的数据是如何排序的。
注意:分析函数允许你对一个数据集进排序和筛选,这是SQL从来不能实现的.除了最后的Order by子句之外,分析函数是在查询中执行的最后的操作集,这样的话,就不能直接在谓词中使用分析函数,即不能在上面使用where或having子句!!!
oracle分析函数实际上操作对象是查询出的数据集，也就是说不需二次查询数据库，实际上就是oracle实现了一些我们自身需要编码实现的统计功能，对于简化开发工作量有很大的帮助，特别在开发第三方报表软件时是非常有帮助的。
分析函数例子：
Count()、Avg()
SELECT area, AVG(score) OVER(PARTITION BY area ORDER BY area) avg_score FROM students;
SELECT area, count(0) over(partition by area order by area) nums FROM students;
Sum()
2001年度每个客户订单的总额及客户所对应区域的订单总额
–2001年度每个客户订单的总额及客户所对应区域的订单总额
select t.cust_nbr customer,
t.region_id region,
sum(t.tot_sales) cust_sales, –个人订单总额
sum(sum(t.tot_sales)) over(partition by t.region_id) region_sales –区域订单总额
from orders_tmp t
where t.year = 2001
group by t.region_id, t.cust_nbr;
个人订单总额占到区域订单总额10%以上的大客户
select *
from (select t.cust_nbr customer,
t.region_id region,
sum(t.tot_sales) cust_sales, –个人订单总额
sum(sum(t.tot_sales)) over(partition by t.region_id) region_sales –区域订单总额
from orders_tmp t
where t.year = 2001
group by t.region_id, t.cust_nbr) all_sales
where all_sales.cust_sales > all_sales.region_sales * 0.1;