SQL之累计和及累计差详解

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

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

SUM

SUM([ 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        800
ALLEN          7499       2400
WARD           7521       3650
JONES          7566       6625
MARTIN         7654       7875
BLAKE          7698      10725
CLARK          7782      13175
SCOTT          7788      16175
KING           7839      21175
TURNER         7844      22675
ADAMS          7876      23775
JAMES          7900      24725
FORD           7902      27725
MILLER         7934      29025

14 rows selected

SQL> 

这样就可以看到具体的累积工资之和了。但是这么看还不清晰，我们使用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                                                                              SMITH
ALLEN          7499       2400 800+1600                                                                         SMITH+ALLEN
WARD           7521       3650 800+1600+1250                                                                    SMITH+ALLEN+WARD
JONES          7566       6625 800+1600+1250+2975                                                               SMITH+ALLEN+WARD+JONES
MARTIN         7654       7875 800+1600+1250+2975+1250                                                          SMITH+ALLEN+WARD+JONES+MARTIN
BLAKE          7698      10725 800+1600+1250+2975+1250+2850                                                     SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE
CLARK          7782      13175 800+1600+1250+2975+1250+2850+2450                                                SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK
SCOTT          7788      16175 800+1600+1250+2975+1250+2850+2450+3000                                           SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT
KING           7839      21175 800+1600+1250+2975+1250+2850+2450+3000+5000                                      SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING
TURNER         7844      22675 800+1600+1250+2975+1250+2850+2450+3000+5000+1500                                 SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER
ADAMS          7876      23775 800+1600+1250+2975+1250+2850+2450+3000+5000+1500+1100                            SMITH+ALLEN+WARD+JONES+MARTIN+BLAKE+CLARK+SCOTT+KING+TURNER+ADAMS
JAMES          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+JAMES
FORD           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+FORD
MILLER         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+MIL

14 rows selected

SQL> 

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

计算累计差

SQL> create or replace view v_detail
  2  as
  3  select rownum seq,a.*
  4  from (select 编号,项目,金额 from detail order by 编号) a;

View created

SQL> select * from v_detail;

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

SQL> 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      -2300

SQL> 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      18250

SQL> 

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