SQL之累计和及累计差详解

在业务需要中，尤其是在日常的结算中，会进行累积和和累计差的求值，那么遇到该类业务该如何处理呢？

首先看一下累积和的问题。
在Oracle中，有表emp表，记录了员工的详细信息，其中有sal一列，那么
需求来了，
需求，
我们要累计各个部门员工总工资的姓名(ename)和员工编号(empno)
在Oracle中，对于此类问题oracle提供了分析函数。
累计求和的当然需要使用到sum函数。首先来看以下官方文档中的sum

SUMSUM([ DISTINCT | ALL ] expr)   [ OVER (analytic_clause) ]

可以看到distinct |all|expr都是可选参数 over也是可选参数，其中的这个over就是oracle提供的分析函数。现在来做这个需求。

SQL> select ename 员工姓名,empno 员工编号,sum(sal) over(order by empno) 累积工资  2  from emp  3  order by empno;员工姓名       员工编号       累积工资---------- -------- ----------SMITH          7369        800ALLEN          7499       2400WARD           7521       3650JONES          7566       6625MARTIN         7654       7875BLAKE          7698      10725CLARK          7782      13175SCOTT          7788      16175KING           7839      21175TURNER         7844      22675ADAMS          7876      23775JAMES          7900      24725FORD           7902      27725MILLER         7934      2902514 rows selectedSQL> 

这样就可以看到具体的累积工资之和了。但是这么看还不清晰，我们使用Oracle提供的listagg函数可以处理一下。

SQL> select ename 员工姓名,empno 员工编号,sum(sal) over(order by empno) 累积工资，  2  (select listagg(sal,'+') within group(order by empno) from emp b where b.empno<=a.empno) 计算公式,  3  (select listagg(ename,'+') within group(order by empno) from emp c where c.empno<=a.empno) 员工累计信息  4  from emp a  5  order by empno;员工姓名       员工编号       累积工资 计算公式                                                                         员工累计信息---------- -------- ---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------SMITH          7369        800 800                                                                              SMITHALLEN          7499       2400 800+1600                                                                         SMITH+ALLENWARD           7521       3650 800+1600+1250                                                                    SMITH+ALLEN+WARDJONES          7566       6625 800+1600+1250+2975                                                               SMITH+ALLEN+WARD+JONESMARTIN         7654       7875 800+1600+1250+2975+1250                                                          SMITH+ALLEN+WARD+JONES+MARTINBLAKE          7698      10725 800+1600+1250+2975+1250+2850                                                     SMITH+ALLEN+WARD+JONES+MARTIN+BLAKECLARK          7782      13175 800+1600+1250+2975+1250+2850+2450                                                SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARKSCOTT          7788      16175 800+1600+1250+2975+1250+2850+2450+3000                                           SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTTKING           7839      21175 800+1600+1250+2975+1250+2850+2450+3000+5000                                      SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KINGTURNER         7844      22675 800+1600+1250+2975+1250+2850+2450+3000+5000+1500                                 SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNERADAMS          7876      23775 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100                            SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMSJAMES          7900      24725 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950                        SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS+JAMESFORD           7902      27725 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950+3000                   SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS+JAMES+FORDMILLER         7934      29025 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100+950+3000+1300              SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS+JAMES+FORD+MIL14 rows selectedSQL> 

这样看起来就比较明白了。
除了分析函数，还可以使用开窗函数和标量来写，不过代价会比分析函数高，因为需要访问两次表，这里就不做了。

计算累计差

SQL> create or replace view v_detail  2  as  3  select rownum seq,a.*  4  from (select 编号,项目,金额 from detail order by 编号) a;View createdSQL> select * from v_detail;       SEQ         编号 项目                 金额---------- ---------- ------------ ----------         1       1000 预交费用          30000         2       7782 支出1              3450         3       7839 支出2              6000         4       7934 支出3              2300SQL> select 编号,项目,金额,  2  (case when seq=1 then 金额 else -金额 end ) 转换后的值 from v_detail;        编号 项目                 金额      转换后的值---------- ------------ ---------- ----------      1000 预交费用          30000      30000      7782 支出1              3450      -3450      7839 支出2              6000      -6000      7934 支出3              2300      -2300SQL> with x as  2  (select rownum seq ,a.*  3  from (select 编号,项目,金额 from detail order by 编号) a)  4  select 编号,项目,金额,  5  (case when seq=1 then 金额 else -金额 end ) 转换后的值 from x;        编号 项目                 金额      转换后的值---------- ------------ ---------- ----------      1000 预交费用          30000      30000      7782 支出1              3450      -3450      7839 支出2              6000      -6000      7934 支出3              2300      -2300

现在开始计算累计差。
在这里说一下with x的用法，如果使用with x的写法，那就完全没有必要建立视图v_detail了，因为with x中的x就是一个视图，只是这个视图是临时的，如果查询结束，这个视图也就跟着消失了。

SQL> with x as  2  (select rownum seq,a.*  3  from (select 编号,项目,金额 from detail order by 编号) a)  4  select 编号,项目,金额,  5  sum(case when seq = 1 then 金额 else -金额 end ) over(order by seq) 余额  6  from x;        编号 项目                 金额         余额---------- ------------ ---------- ----------      1000 预交费用          30000      30000      7782 支出1              3450      26550      7839 支出2              6000      20550      7934 支出3              2300      18250SQL> 

以上就是累计和和差的业务解析。