在业务需要中,尤其是在日常的结算中,会进行累积和和累计差的求值,那么遇到该类业务该如何处理呢?
首先看一下累积和的问题。
在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>
以上就是累计和和差的业务解析。