对数字进行统计操作-累计和、累计差

       最近，做了相关统计分析项目的后台开发工作，让我对oracle数据库中的数字操作，主要是聚集函数等有了进一步的理解和体会，故写下此文，一方面巩固这段时间对于所用sql语法或者说是sql优化的一些运用，再者也希望正在苦于sql优化方面的童鞋可以看到此文，能提供给他们一些帮助。

       常用的聚集函数都有哪些？AVG():求平均值；MIN():求最小值；MAX():求最大值；SUM():求和；COUNT(*):求条目数，聚集函数需要注意一点：聚集函数会忽略空值，这对SUM()等来说没什么影响，但是对于AVG()、COUNT(*)来说可能就会出现预料之外的结果，所以要根据需求决定是否把空值转为零。可以使用nvl(coloumName,0)或者coalesce(coloumName,0)，推荐使用后者，因为后者更好用。

      下面介绍常用的一些场景功能。

      一.生成累计和

      公司为了查看用人成本，需要对员工的工作进行累加，以便查看员工人数与工资支出之间的对应关系。

select empno as 编号,
       ename as 姓名,
       sal as 工资,
       sum(sal) over (order by empno) as 累计收入
  from emp order by empno

累计工资求和结果集

编号	姓名	工资	累计收入
7499	ALLEN	1600	1600
7521	WARD	1250	2850
7654	MARTIN	1250	4100
7698	BLAKE	2850	6950
7844	TURNER	1500	8450

       通过上表结果可以看到，函数“sum(sal) over (order by empno)”的结果是排序“over (order by empno)”后第一行到当前行的所有工资之和。

       二.计算累计差
       先上一个数据集，如下图所示： 

消费流水账模拟数据

编号	项目	金额
1000	预交费用	30000
7782	支出1	3450
7839	支出2	6000
7934	支出3	2300

       

with x as
(select rownum as seq,a.* from(select 编号,项目,金额 from detail order by 编号) a)
select 编号,
       项目,
       金额,
       sum(case when seq=1 then 金额 else -金额 end) over(order by seq) as 余额 from x;

      得到下图结果：

编号	项目	金额	余额
1000	预交费用	30000	30000
7782	支出1	3450	26550
7839	支出2	6000	20550
7934	支出3	2300	18250

 

        可以看到使用了with as 用法 ，其语法为with tmp as (select * from tb_name)，将括号里的作为一个临时表tmp。

        对数据排序，一般流水账的编号都是按顺序生成的，我们根据编号排序并生成序号：

select rownum as seq,a.* 
   from (select 编号,项目,金额 from detail order by 编号) a;

       可以看到，seq=1是收入，后面的是支出。使用case when根据seq是否为1进行累计值正负的判断。然后是用上面用的求累计和的方法进行运算即可。