开窗函数

分析函数Over用法

数据库 2007-08-25 23:01:18 阅读24 评论0   字号：大中小 订阅

这段分析函数说明不算很详细，不过也可以帮我们解决很多问题了，记在这里备用吧！

 

格式
analytic_function OVER
(
 [PARTITION BY value_expr]
 [    ORDER BY expr [ASC/DESC] [NULLS FIRST/NULLS LAST]
            [ROWS/RANGE BETWEEN UNBOUNDED PRECEDING/
                                CURRENT ROW/
                                value_expr PRECEDING/FOLLOWING
                            AND UNBOUNDED FOLLOWING/
                                CURRENT ROW/
                                value_expr PRECEDING/FOLLOWING
            ]
 ]
)
太复杂了,是吧?!

讲简单一点,分析函数是对查询结果的操作(多数为汇总类的函数)
其中:PARTITION BY子句用于分组(类似GROUP BY), 即在各个分组之内应用分析函数
     ORDER BY子句用于在分组中排序
     后面的一大串(即ROWS/RANGE BETWEEN...AND...)是对于每一行数据的分析范围

 

 

举几个例子:

1.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename) x
from scott.emp; 
--注意PARTITION BY, ORDER BY

2.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between unbounded preceding and current row) x
from scott.emp; 
--注意ROWS BETWEEN unbounded preceding AND current row 
--是指第一行至当前行的汇总

3.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between current row and unbounded following) x
from scott.emp; 
--注意ROWS BETWEEN current row AND unbounded following 
--是指当前行到最后一行的汇总

4.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between 1 preceding and current row) x
from scott.emp; 
--注意ROWS BETWEEN 1 preceding AND current row
--是指当前行的上一行(rownum-1)到当前行的汇总

5.
select empno, ename, deptno, sal,
       sum(sal) over (partition by deptno order by ename
                      rows between 1 preceding and 2 following) x
from scott.emp; 
--注意ROWS BETWEEN 1 preceding AND 1 following
--是指当前行的上一行(rownum-1)到当前行的下辆行(rownum+2)的汇总

转自：http://blog.163.com/hanwei_xg/blog/static/34591698200772511118289/

with temp as(
select 1 aa from dual
union all
select 2 aa from dual
union all
select 2 aa from dual
union all
select 2 aa from dual
union all
select 3 aa from dual
union all
select 4 aa from dual
union all
select 5 aa from dual
union all
select 6 aa from dual
union all
select 7 aa from dual
union all
select 8 aa from dual
union all
select 9 aa from dual
)
select aa, sum(aa) over(order by aa  range between 2 preceding and 2 following) bb from temp

解释：

  sum(aa)求和：求的是aa不同值的，针对当前行的上两行和下两行的合计，其中如果上面行数，不足两行，则按剩余行计算，

下面的行也是如此。

with temp as(
select 1 aa from dual
union all
select 2 aa from dual
union all
select 2 aa from dual
union all
select 2 aa from dual
union all
select 3 aa from dual
union all
select 4 aa from dual
union all
select 5 aa from dual
union all
select 6 aa from dual
union all
select 7 aa from dual
union all
select 8 aa from dual
union all
select 9 aa from dual
)
select aa, sum(aa) over (order by aa rows between 2 preceding and 2 following) bb from temp

解释：和rang不同之处是这里不区分aa值，是否相同，严格按照上两行和下两行加当前行合计，只有上下两行不足时，也只取剩下的行数，

进行计算。

with t_test as 
(
select 1 id, 12 a, 19 b, 0 c from dual union all 
select 2, 15 , 17 , 0 from dual union all 
select 5, 11 , 32 , 0 from dual union all 
select 6, 18 , 79 , 0 from dual union all 
select 3, 23 , 342, 0 from dual union all 
select 4, 134, 545, 0 from dual 

)
select id,a,b,c,nvl(sum((a*b)) over( order by id rows between unbounded preceding and 1 preceding),0)
from t_test