【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            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;

效果图：