oracle 用到的函数累积

select deptno,ename,sal,
sum(sal) over(order by ename) 连续求和,
sum(sal) over() 总和,
100*round(sal/sum(sal) over(),4) "份额(%)"
from emp

select round(123.456) from dual;             回傳 123

select round(123.456, 0) from dual;          回傳 123

select round(123.456, 1) from dual;          回傳 123.5

select round(123.456, 2) from dual;          回傳 123.46

select round(-123.456, 2) from dual;         回傳 -123.46


select deptno,sum(sal) over (partition by deptno order by ename) 按部门连续求总和 from emp;


select deptno,sum(sal) over (partition by deptno ) 不按部门连续求总和 from emp;
上面所谓的连续求和(这个术语)在数学上有个累加的动作,如1,2,3连续求和值为:(1)1,(1+2)3,(1+2+3)6
而不连续求和为:6,6,6

连续求和综合:

select deptno,
       ename,
       sal,
       sum(sal) over(partition by deptno order by ename) 部门连续求和, --各部门的薪水"连续"求和
       sum(sal) over(partition by deptno) 部门总和, -- 部门统计的总和，同一部门总和不变
       100 * round(sal / sum(sal) over(partition by deptno), 4) "部门份额(%)",
       sum(sal) over(order by deptno, ename) 连续求和, --所有部门的薪水"连续"求和
       sum(sal) over() 总和, -- 此处sum(sal) over () 等同于sum(sal)，所有员工的薪水总和
       100 * round(sal / sum(sal) over(), 4) "总份额(%)"
  from emp;

 

求各个部门的最高工资所得者是谁,其工资是多少,在哪外部门

select *
  from (select deptno,
               ename,
               sal,
               row_number() over(partition by deptno order by sal desc) topn
          from emp)
 where topn = 1;
其中使用了分析函数来为记录排名：

Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。

①ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。(有重复数据时其值仍加1) 

②DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。(有重复数据时其值保持不变)  

③RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名 。(有重复数据时其值不变,但在下一值时直接将重复数据的项进行累加)  

下图是分别用这三种分析函数的效果图:

REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
---------- ----------- ---------- ---------- ---------- ----------
            
         8          18                1253840         11         11         11
         5           2                 1224992         12         12         12
         9          23                1224992         12         12         13
         9          24                1224992         12         12         14
        10          30               1216858         15           13            15
   

比较上面3种不同的策略，我们在选择的时候就要根据客户的需求来定夺了：

①假如客户就只需要指定数目的记录，那么采用row_number是最简单的，但有漏掉的记录的危险

②假如客户需要所有达到排名水平的记录，那么采用rank或dense_rank是不错的选择。至于选择哪一种则看客户的需要，选择dense_rank或得到最大的记录


 以下用聚合函数求出和部门的最高工资
 select max(sal) from emp group by deptNO

 

 

说明: 1) PLSQL开发笔记和小结收集自http://www.blogjava.net/cheneyfree/

2)分析函数简述收集自http://space.itpub.net/7607759/

3)剩下的99%收集自: http://www.blogjava.net/pengpenglin/(偶补充了一点点);

http://www.blogjava.net/AstroQi/archive/2009/01/20/252022.html

 

昆明小虫http://ynlxc.cnblogs.com/ 收集,并补充了一点点