【Oracle篇】分析函数的使用

为什么需要分析函数？ 有些很难直接的SQL中做到的SQL做的查询但实际上是很普通的操作，他们包括： 1、计算运行行的总数——逐行的显示一个部门的累计工资。每行包括前面各行工资的总和。 2、查找一组内的百分数——显示在某些部门中付给个人的总工资的百分数

为什么需要分析函数？

有些很难直接的SQL中做到的SQL做的查询但实际上是很普通的操作，他们包括：

1、计算运行行的总数——逐行的显示一个部门的累计工资。每行包括前面各行工资的总和。

2、查找一组内的百分数——显示在某些部门中付给个人的总工资的百分数。将他们的工资与该部门的工资总和相除。

3、前N个查询

4、计算正在流动平均值

5、执行带等级的查询

--首先建表
drop table program;

create table program(
pno number,
pdate varchar(10),
pname varchar(10),
psal number
);

insert into program values(1,'2014-4-5','上期结余',12000);
insert into program values(2,'2014-4-6','购买货物1',-5000);
insert into program values(3,'2014-4-7','购买货物2',10000);
insert into program values(4,'2014-4-8','购买货物3',-10000);
insert into program values(5,'2014-4-9','购买货物4',20000);
insert into program values(6,'2014-4-10','发工资',-15000);

select * from program;


select pp.pno,pp.pdate,pp.pname,pp.pin,pp.pout,(pp.pout+pp.pin) psheng from
(select pno,pdate,pname,
case
when psal<0 then psal
else 0
end pout,
case
when psal>0 then psal
else 0
end pin
from program ) pp,program p
where pp.pno=p.pno;

效果图：

-- 统计比当前员工工资高500以外的人员信息

--提示：工资比我高的人数-工资比我高出500以内的人数

select e.*,(e.rk2-e.rk1) rank_all from(
select e.*,count(*) over(order by sal desc range 500 preceding) rk1,count(*) over(order by sal desc range 5000 preceding) rk2
from emp e) e;


--获取 BLAKE 所领导团队的平均工资值

select avg(blake.b_sal) b_salavg from(
select empno,lpad(' ',level*2)||ename,e.sal b_sal
from emp e
start with empno=(select empno from emp where ename='BLAKE') connect by prior empno=mgr) blake;


--给，除了BLAKE所领导的团队，工资加10%

--1
select other.empno,other.other_group,(other.o_sal*1.1) all_sal from(
select empno,lpad(' ',level*2)||ename other_group,e.sal o_sal
from emp e
start with empno!=(select empno from emp where ename='BLAKE') connect by prior empno=mgr) other;

--2
select other.empno,other.other_group,(other.o_sal*1.1) all_sal from(
select empno,lpad(' ',level*2)||ename other_group,e.sal o_sal
from emp e
start with ename='KING' and e.ename!='BLAKE' connect by prior empno=mgr) other;

--作业5： 定义一个物理表

-- 建表
drop table student;

create table student(
sscore number,
sbook varchar(10),
sname varchar(10)
);

insert into student values(90,'语文','小明');
insert into student values(63,'语文','小丽');
insert into student values(72,'语文','小王');
insert into student values(55,'语文','小孙');
insert into student values(80,'语文','小周');

insert into student values(0,'数学','小明');
insert into student values(61,'数学','小丽');
insert into student values(42,'数学','小王');
insert into student values(75,'数学','小孙');
insert into student values(81,'数学','小周');

insert into student values(52,'英语','小明');
insert into student values(78,'英语','小丽');
insert into student values(65,'英语','小王');
insert into student values(38,'英语','小孙');
insert into student values(95,'英语','小周');

select * from student;

select s.sname,
sum(decode(s.sbook,'语文',s.sscore,0)) "语文",
sum(decode(s.sbook,'数学',s.sscore,0)) "数学",
sum(decode(s.sbook,'英语',s.sscore,0)) "英语"
from student s group by s.sname;

效果图：