SQL 累计求和

SQL 累计求和,你想针对某一列进行累计求和。

SQL 累计求和 问题描述

你想针对某一列进行累计求和。

SQL 累计求和 解决方案

作为示例,下面的解决方案将介绍如何计算全体员工工资的累计额。为了便于理解,查询结果按照 SAL 列排序,这样很容易观察累计和的增长。
DB2 和 Oracle
使用 SUM 函数的窗口函数版本进行累计求和。

1 select ename, sal,
2        sum(sal) over (order by sal,empno) as running_total
3   from emp
4   order by 2
 
ENAME             SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH             800           800
JAMES             950          1750
ADAMS            1100          2850
WARD             1250          4100
MARTIN           1250          5350
MILLER           1300          6650
TURNER           1500          8150
ALLEN            1600          9750
CLARK            2450         12200
BLAKE            2850         15050
JONES            2975         18025
SCOTT            3000         21025
FORD             3000         24025
KING             5000         29025

MySQLPostgreSQLSQL Server
使用标量子查询来进行累计求和(而不是使用如 SUM OVER 这样的窗口函数,因为不能像 DB2 和 Oracle 的解决方案那样方便地按照 SAL 列对结果集排序)。最终的累计和是正确的(最后一行的合计值与前面的 DB2 和 Oracle 解决方案的结果一致),但是中间结果因为缺少了排序操作而不尽相同。

1 select e.ename, e.sal,
2        (select sum(d.sal) from emp d
3          where d.empno <= e.empno) as running_total
4   from emp e
5  order by 3
 
ENAME             SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH             800           800
ALLEN            1600          2400
WARD             1250          3650
JONES            2975          6625
MARTIN           1250          7875
BLAKE            2850         10725
CLARK            2450         13175
SCOTT            3000         16175
KING             5000         21175
TURNER           1500         22675
ADAMS            1100         23775
JAMES             950         24725
FORD             3000         27725
MILLER           1300         29025

SQL 累计求和 扩展知识

使用新的 ANSI 窗口函数很容易进行累计求和。对于尚未支持窗口函数的数据库管理系统而言,必须使用标量子查询(通过一个具有唯一值的字段做连接查询)。
DB2 和Oracle
使用窗口函数 SUM OVER 很容易进行累计求和。上述解决方案使用的 ORDER BY 子句的后面不仅有 SAL 列,还有 EMPNO 列(该列是主键),这是为了防止累计求和过程中出现重复值。以下示例中的 RUNNING_TOTAL2 列展示了重复值会导致什么样的问题。

select empno, sal,
       sum(sal)over(order by sal,empno) as running_total1,
       sum(sal)over(order by sal) as running_total2
  from emp
 order by 2
 
ENAME             SAL RUNNING_TOTAL1 RUNNING_TOTAL2
---------- ---------- -------------- --------------
SMITH             800            800            800
JAMES             950           1750           1750
ADAMS            1100           2850           2850
WARD             1250           4100           5350
MARTIN           1250           5350           5350
MILLER           1300           6650           6650
TURNER           1500           8150           8150
ALLEN            1600           9750           9750
CLARK            2450          12200          12200
BLAKE            2850          15050          15050
JONES            2975          18025          18025
SCOTT            3000          21025          24025
FORD             3000          24025          24025
KING             5000          29025          29025

员工 WARD、MARTIN、SCOTT 和 FORD 对应的 RUNNING_TOTAL2 是不正确的。这是因为他们的工资在 EMP 表里出现了不止一次,而这些重复值也被计算到累计和中。这就是为什么必须把 EMPNO 列(它是唯一的)加入排序项,才能得到正确的计算结果,即 RUNNING_TOTAL1。考虑这样一种情况:对于 ADAMS,RUNNING_TOTAL1RUNNING_TOTAL2 两列都是 2850。2850 加上 WARD 的工资 1250,应该得到 4100,然而 RUNNING_TOTAL2 返回的却是 5350。这是为什么呢?因为 WARD 和 MARTIN 的工资相同,这两个 1250 相加得到 2500,再加上 2850 就是 WARD 和 MARTIN 这两行对应的 5350。通过把若干列组合起来作为排序列,能够避免出现重复值(例如,SALEMPNO 的组合是唯一的),这样就能保证得到正确的累计和。
MySQLPostgreSQL 和 SQL Server
MySQL、PostgreSQL 和 SQL Server 因为这些数据库管理系统尚未完全支持窗口函数,所以我们只能通过标量子查询来计算累计和。必须通过一个有唯一值的列做连接查询,否则,如果两个员工的工资相同,那么得到的累计和就是错误的。对于本实例而言,关键在于把 D.EMPNOE.EMPNO 连接起来,并针对每个小于或者等于 E.EMPNOD.EMPNO 计算出对应的 D.SAL。如果把标量子查询改写成一个 EMP 表全体数据与部分数据之间的连接查询,会更加容易理解这一点。

select e.ename as ename1, e.empno as empno1, e.sal as sal1,
       d.ename as ename2, d.empno as empno2, d.sal as sal2
  from emp e, emp d
 where d.empno <= e.empno
   and e.empno = 7566
 
ENAME          EMPNO1       SAL1 ENAME          EMPNO2       SAL2
---------- ---------- ---------- ---------- ---------- ----------
JONES            7566       2975 SMITH            7369        800
JONES            7566       2975 ALLEN            7499       1600
JONES            7566       2975 WARD             7521       1250
JONES            7566       2975 JONES            7566       2975

EMPNO2 列中的每一个值都会和 EMPNO1 列中对应的值进行比较。如果 EMPNO2 列中的值小于或者等于 EMPNO1 列中对应的值,则其对应的 SAL2 列的值会被计入总和。对于上述查询而言,员工 SMITH、ALLEN、WARD 和 JONES 的 EMPNO 值会和 JONES 的 EMPNO 值相比较。由于四个员工的 EMPNO 值都满足小于或者等于 JONES 的 EMPNO 值这一条件,因此他们的工资都会被计入总和。反之,(在上述查询中)任何 EMPNO 值大于 JONES 的 EMPNO 值的员工,其工资都不会被计入总和。对于完整的查询而言,其工作方式也是同样的,如果 EMPNO 值小于或者等于全表的最大值 7934(MILLER 的 EMPNO 值),则其对应的工资就会被计入总和。

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程

SQL 实例