Oracle 窗口函数over()与窗口子语句

达人科技 2017-01-04 07:39

drop table t purge;
create  table t(id number,deptno number,name varchar2(20),sal number);
insert into t values(1,1,'1aa',120);
insert into t values(2,1,'2aa',300);
insert into t values(3,1,'3aa','100');
insert into t values(4,1,'4aa',99,);
insert into t values(5,1,'5aa',90);
insert into t values(6,2,'6aa',87);
insert into t values(7,2,'7aa',500);
insert into t values(8,2,'8aa',200);
insert into t values(9,2,'9aa',20);
insert into t values(10,2,'10aa',30);

deptno 为部门号，sal为薪水

--获取每个部门薪水的总数

select t.*,sum(sal)over(partition by deptno order by sal desc 
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as last_value 
from t t;
--last_value,sum为分析函数，over为开窗函数(窗口函数:分析函数分析时要处理的数据范围)，
--形式如:over(partition by xxx order by yyy rows between zzz)
--窗口子句:UNBOUNDED PRECEDING表示数据范围的第一行
--current row表示数据范围的当前行
--UNBOUNDED FOLLOWING表示数据范围的最后一行

查询出的值为:

看另一实例:

select t.*,sum(sal)over(partition by deptno order by sal asc 
       ROWS BETWEEN UNBOUNDED PRECEDING AND current row) as last_value 
from t t;

查询出的值为:

行转列：pivot(max(..) ... for(分组的字段)in(分组的值))

--行转列
select * from (select sal,deptno from t) 
pivot(max(sal) as max_sal,sum(sal)as sum_sal,avg(sal) as avg_sal for(deptno) in(1,2))

本文为头条号作者发布，不代表今日头条立场。