SQL 动态区间聚合运算|极客教程

SQL 动态区间聚合运算，你想执行动态的聚合运算，例如计算 EMP 表工资的动态合计。你希望把入职最早的员工的 HIREDATE 作为起始点，每隔 90 天计算一次工资合计值。你想调查一下在最早入职的员工和最近入职的员工之间每隔 90 天工资的波动状况。

SQL 动态区间聚合运算问题描述

你想执行动态的聚合运算，例如计算 EMP 表工资的动态合计。你希望把入职最早的员工的 HIREDATE 作为起始点，每隔 90 天计算一次工资合计值。你想调查一下在最早入职的员工和最近入职的员工之间每隔 90 天工资的波动状况。你希望得到如下所示的结果集。

HIREDATE        SAL SPENDING_PATTERN
----------- ------- ----------------
17-DEC-1980     800              800
20-FEB-1981    1600             2400
22-FEB-1981    1250             3650
02-APR-1981    2975             5825
01-MAY-1981    2850             8675
09-JUN-1981    2450             8275
08-SEP-1981    1500             1500
28-SEP-1981    1250             2750
17-NOV-1981    5000             7750
03-DEC-1981     950            11700
03-DEC-1981    3000            11700
23-JAN-1982    1300            10250
09-DEC-1982    3000             3000
12-JAN-1983    1100             4100

SQL 动态区间聚合运算解决方案

部分数据库支持在窗口函数的帧或窗口子句中指定动态窗口，这样一来本问题的解决就变得容易多了。关键之处在于调用窗口函数时要按照 HIREDATE 排序，并指定一个为期 90 天的日期窗口，该日期窗口的起始点是第一个员工的入职日期。工资合计值的计算针对的是一组动态变化的员工，包括当前员工，以及入职时间早于当前员工但相差不超过 90 天的所有人。对于不支持这一类窗口函数的数据库，可以使用标量子查询，做法自然稍显繁琐。
DB2 和 Oracle
对于 DB2 和 Oracle，使用窗口函数 SUM OVER，并按照 HIREDATE 排序。在窗口或“帧”子句里指定时间范围为 90 天，这样就能针对每个员工以及入职时间比他早 90 天以内的所有人的工资执行合计计算。因为 DB2 的窗口函数不支持在 ORDER BY 子句中指定 HIREDATE（下面第 3 行代码），我们只好改用 ORDERY BY DAYS(HIREDATE)。

1  select hiredat,
2         sal,
3         sum(sal)over(order by days(hiredate)
4                         range between 90 preceding
5                           and current row) spending_pattern
6    from emp e

相较于 DB2 解决方案，Oracle 解决方案更加简洁明了，因为 Oracle 的窗口函数支持 DATE 类型排序。

1  select hiredate,
2         sal,
3         sum(sal)over(order by hiredate
4                         range between 90 preceding
5                           and current row) spending_pattern
6    from emp e

MySQL、PostgreSQL 和 SQL Server
MySQL、PostgreSQL 和 SQL Server 使用标量子查询计算工资合计值，合计值的计算范围包括当前员工，以及入职时间早于当前员工但相差不超过 90 天的所有人。

1 select e.hiredate,
2        e.sal,
3        (select sum(sal) from emp d
4          where d.hiredate between e.hiredate-90
5                               and e.hiredate) as spending_pattern
6   from emp e
7  order by 1

SQL 动态区间聚合运算扩展知识

DB2 和 Oracle
DB2 和 Oracle 的解决方案大致相同。两者差别极小，唯一的不同之处在于窗口函数的 ORDER BY 子句如何操作 HIREDATE。在写作本书时，如果想通过一个数值来指定窗口的范围，DB2 是不支持在 ORDER BY 子句中使用 DATE 类型的。（例如，RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 支持日期类型的排序，但 RANGE BETWEEN 90 PRECEDING AND CURRENT ROW 却不支持这么做。）
掌握本解决方案的关键是要了解查询语句中窗口子句的工作原理。首先，我们定义好的窗口会按照 HIREDATE 对所有员工的工资进行排序。然后，调用聚合函数计算工资总额。但是，这里计算出的并不是所有工资的总和。具体的处理过程如下所示。
(1) 评估最早入职的员工的工资。第一个员工入职之前不会有其他人已经入职，因而此时的工资总额就是第一个员工的工资。
(2) （按照 HIREDATE 的先后顺序）评估下一位员工的工资。该员工的工资会被计入动态工资合计值，其他入职时间比他早、但相差 90 天以内的员工的工资也会被计入合计值。
第一个员工的 HIREDATE 是 1980 年 12 月 17 日，紧接着入职的下一位员工的 HIREDATE 是 1981 年 2 月 20 日。第二个员工的入职日期和第一个员工相差不足 90 天，因此第二个员工对应的动态工资合计值等于 2400（1600 + 800）。为了更深入地理解 SPENDING_PATTERN 列的计算方法，不妨仔细观察下面的查询语句及其结果集。

select distinct
       dense_rank()over(order by e.hiredate) window,
       e.hiredate current_hiredate,
       d.hiredate hiredate_within_90_days,
       d.sal sals_used_for_sum
  from emp e,
       emp d
 where d.hiredate between e.hiredate-90 and e.hiredate
　
WINDOW CURRENT_HIREDATE HIREDATE_WITHIN_90_DAYS SALS_USED_FOR_SUM
------ ---------------- ----------------------- -----------------
     1 17-DEC-1980      17-DEC-1980                           800
     2 20-FEB-1981      17-DEC-1980                           800
     2 20-FEB-1981      20-FEB-1981                          1600
     3 22-FEB-1981      17-DEC-1980                           800
     3 22-FEB-1981      20-FEB-1981                          1600
     3 22-FEB-1981      22-FEB-1981                          1250
     4 02-APR-1981      20-FEB-1981                          1600
     4 02-APR-1981      22-FEB-1981                          1250
     4 02-APR-1981      02-APR-1981                          2975
     5 01-MAY-1981      20-FEB-1981                          1600
     5 01-MAY-1981      22-FEB-1981                          1250
     5 01-MAY-1981      02-APR-1981                          2975
     5 01-MAY-1981      01-MAY-1981                          2850
     6 09-JUN-1981      02-APR-1981                          2975
     6 09-JUN-1981      01-MAY-1981                          2850
     6 09-JUN-1981      09-JUN-1981                          2450
     7 08-SEP-1981      08-SEP-1981                          1500
     8 28-SEP-1981      08-SEP-1981                          1500
     8 28-SEP-1981      28-SEP-1981                          1250
     9 17-NOV-1981      08-SEP-1981                          1500
     9 17-NOV-1981      28-SEP-1981                          1250
     9 17-NOV-1981      17-NOV-1981                          5000
    10 03-DEC-1981      08-SEP-1981                          1500
    10 03-DEC-1981      28-SEP-1981                          1250
    10 03-DEC-1981      17-NOV-1981                          5000
    10 03-DEC-1981      03-DEC-1981                           950
    10 03-DEC-1981      03-DEC-1981                          3000
    11 23-JAN-1982      17-NOV-1981                          5000
    11 23-JAN-1982      03-DEC-1981                           950
    11 23-JAN-1982      03-DEC-1981                          3000
    11 23-JAN-1982      23-JAN-1982                          1300
    12 09-DEC-1982      09-DEC-1982                          3000
    13 12-JAN-1983      09-DEC-1982                          3000
    13 12-JAN-1983      12-JAN-1983                          1100

仔细看 WINDOW 列的话，我们会发现具有相同 WINDOW 值的行会被计入动态工资合计值。以 WINDOW 值等于 3 的那一组为例，该窗口中被计入合计的工资分别是 800、1600 和 1250，合计为 3650。回头再看一下“问题”部分给出的最终结果集，我们看到 1981 年 2 月 22 日 (WINDOW 3) 那一行的 SPENDING_PATTERN 值确实是 3650。如果想证明自连接查询为每个窗口都找到了正确的工资，我们只要按照 CURRENT_DATE 分组，并把 SALS_USED_FOR_SUM 值相加求和即可。查询结果应该会和“问题”部分给出的结果集相一致（1981 年 12 月 3 日对应的结果本应有两行，但这里把重复行去掉了）。

select current_hiredate,
       sum(sals_used_for_sum) spending_pattern
  from (
select distinct
       dense_rank()over(order by e.hiredate) window,
       e.hiredate current_hiredate,
       d.hiredate hiredate_within_90_days,
       d.sal sals_used_for_sum
  from emp e,
       emp d
 where d.hiredate between e.hiredate-90 and e.hiredate
       ) x
 group by current_hiredate
　
CURRENT_HIREDATE SPENDING_PATTERN
---------------- ----------------
17-DEC-1980                   800
20-FEB-1981                  2400
22-FEB-1981                  3650
02-APR-1981                  5825
01-MAY-1981                  8675
09-JUN-1981                  8275
08-SEP-1981                  1500
28-SEP-1981                  2750
17-NOV-1981                  7750
03-DEC-1981                 11700
23-JAN-1982                 10250
09-DEC-1982                  3000
12-JAN-1983                  4100

MySQL、PostgreSQL 和 SQL Server
基于 HIREDATE 排序，并使用聚合函数 SUM 以计算出每 90 天的工资合计值。除此之外，本解决方案的关键之处还包括标量子查询的运用（自连接查询也能达到同样目的）。为了方便你理解，不妨把本解决方案先转换成自连接查询，并仔细观察哪些行会被包含进求和计算。考虑如下所示的结果集，它的查询结果和前面给出的解决方案相同。

select e.hiredate,
       e.sal,
       sum(d.sal) as spending_pattern
  from emp e, emp d
 where d.hiredate
       between e.hiredate-90 and e.hiredate
 group by e.hiredate,e.sal
 order by 1
　
HIREDATE      SAL SPENDING_PATTERN
----------- ----- ----------------
17-DEC-1980   800              800
20-FEB-1981  1600             2400
22-FEB-1981  1250             3650
02-APR-1981  2975             5825
01-MAY-1981  2850             8675
09-JUN-1981  2450             8275
08-SEP-1981  1500             1500
28-SEP-1981  1250             2750
17-NOV-1981  5000             7750
03-DEC-1981   950            11700
03-DEC-1981  3000            11700
23-JAN-1982  1300            10250
09-DEC-1982  3000             3000
12-JAN-1983  1100             4100

如果上述输出结果依然不够清楚明白，不妨删除分组和聚合运算部分，先打印出笛卡儿积的结果。首先使用 EMP 表产生一个笛卡儿积以便每个 HIREDATE 都可以和其他 HIREDATE 进行比较。（下面只打印出了部分结果集，因为 EMP 表的笛卡儿积会返回 196 行（14×14）数据。）

select e.hiredate,
       e.sal,
       d.sal,
       d.hiredate
  from emp e, emp d
　
HIREDATE      SAL   SAL HIREDATE
----------- ----- ----- -----------
17-DEC-1980   800   800 17-DEC-1980
17-DEC-1980   800  1600 20-FEB-1981
17-DEC-1980   800  1250 22-FEB-1981
17-DEC-1980   800  2975 02-APR-1981
17-DEC-1980   800  1250 28-SEP-1981
17-DEC-1980   800  2850 01-MAY-1981
17-DEC-1980   800  2450 09-JUN-1981
17-DEC-1980   800  3000 09-DEC-1982
17-DEC-1980   800  5000 17-NOV-1981
17-DEC-1980   800  1500 08-SEP-1981
17-DEC-1980   800  1100 12-JAN-1983
17-DEC-1980   800   950 03-DEC-1981
17-DEC-1980   800  3000 03-DEC-1981
17-DEC-1980   800  1300 23-JAN-1982
20-FEB-1981  1600   800 17-DEC-1980
20-FEB-1981  1600  1600 20-FEB-1981
20-FEB-1981  1600  1250 22-FEB-1981
20-FEB-1981  1600  2975 02-APR-1981
20-FEB-1981  1600  1250 28-SEP-1981
20-FEB-1981  1600  2850 01-MAY-1981
20-FEB-1981  1600  2450 09-JUN-1981
20-FEB-1981  1600  3000 09-DEC-1982
20-FEB-1981  1600  5000 17-NOV-1981
20-FEB-1981  1600  1500 08-SEP-1981
20-FEB-1981  1600  1100 12-JAN-1983
20-FEB-1981  1600   950 03-DEC-1981
20-FEB-1981  1600  3000 03-DEC-1981
20-FEB-1981  1600  1300 23-JAN-1982

仔细查看上述结果集可以发现，除了其自身，不存在比 1980 年 12 月 17 日更早的 HIREDATE。因此，那一行对应的合计值应该是 800。然后是下一个 HIREDATE，1981 年 2 月 20 日，我们注意到只有一个 HIREDATE 落在了 90 天的窗口范围内（比该日期早 90 天），那就是 1980 年 12 月 17 日。为 1980 年 12 月 17 日对应的 SAL 值和 1981 年 2 月 20 对应的 SAL 值求和，结果是 2400（因为我们寻找的是当前 HIREDATE 以及 90 天之内的日期），这恰好就是最终结果集该 HIREDATE 对应的工资合计值。
现在我们已经理解具体的做法了，接着在 WHERE 子句里添加一个条件以筛选出每一个 HIREDATE 以及早于该 HIREDATE 90 天以内的日期。

select e.hiredate,
       e.sal,
       d.sal sal_to_sum,
       d.hiredate within_90_days
  from emp e, emp d
 where d.hiredate
       between e.hiredate-90 and e.hiredate
 order by 1
　
HIREDATE      SAL SAL_TO_SUM WITHIN_90_DAYS
----------- ----- ---------- --------------
17-DEC-1980   800        800    17-DEC-1980
20-FEB-1981  1600        800    17-DEC-1980
20-FEB-1981  1600       1600    20-FEB-1981
22-FEB-1981  1250        800    17-DEC-1980
22-FEB-1981  1250       1600    20-FEB-1981
22-FEB-1981  1250       1250    22-FEB-1981
02-APR-1981  2975       1600    20-FEB-1981
02-APR-1981  2975       1250    22-FEB-1981
02-APR-1981  2975       2975    02-APR-1981
01-MAY-1981  2850       1600    20-FEB-1981
01-MAY-1981  2850       1250    22-FEB-1981
01-MAY-1981  2850       2975    02-APR-1981
01-MAY-1981  2850       2850    01-MAY-1981
09-JUN-1981  2450       2975    02-APR-1981
09-JUN-1981  2450       2850    01-MAY-1981
09-JUN-1981  2450       2450    09-JUN-1981
08-SEP-1981  1500       1500    08-SEP-1981
28-SEP-1981  1250       1500    08-SEP-1981
28-SEP-1981  1250       1250    28-SEP-1981
17-NOV-1981  5000       1500    08-SEP-1981
17-NOV-1981  5000       1250    28-SEP-1981
17-NOV-1981  5000       5000    17-NOV-1981
03-DEC-1981   950       1500    08-SEP-1981
03-DEC-1981   950       1250    28-SEP-1981
03-DEC-1981   950       5000    17-NOV-1981
03-DEC-1981   950        950    03-DEC-1981
03-DEC-1981   950       3000    03-DEC-1981
03-DEC-1981  3000       1500    08-SEP-1981
03-DEC-1981  3000       1250    28-SEP-1981
03-DEC-1981  3000       5000    17-NOV-1981
03-DEC-1981  3000        950    03-DEC-1981
03-DEC-1981  3000       3000    03-DEC-1981
23-JAN-1982  1300       5000    17-NOV-1981
23-JAN-1982  1300        950    03-DEC-1981
23-JAN-1982  1300       3000    03-DEC-1981
23-JAN-1982  1300       1300    23-JAN-1982
09-DEC-1982  3000       3000    09-DEC-1982
12-JAN-1983  1100       3000    09-DEC-1982
12-JAN-1983  1100       1100    12-JAN-1983

现在我们能清楚地看到哪些 SAL 值会被计入合计值了，接下来只要调用聚合函数 SUM 生成最终结果集即可。

select e.hiredate,
       e.sal,
       sum(d.sal) as spending_pattern
  from emp e, emp d
 where d.hiredate
       between e.hiredate-90 and e.hiredate
 group by e.hiredate,e.sal
 order by 1

比较上述查询和下面的查询（本例给出的标量子查询解决方案）的结果集，我们会发现它们其实是相同的。

select e.hiredate,
       e.sal,
       (select sum(sal) from emp d
         where d.hiredate between e.hiredate-90
                              and e.hiredate) as spending_pattern
  from emp e
 order by 1
　
HIREDATE      SAL SPENDING_PATTERN
----------- ----- ----------------
17-DEC-1980   800              800
20-FEB-1981  1600             2400
22-FEB-1981  1250             3650
02-APR-1981  2975             5825
01-MAY-1981  2850             8675
09-JUN-1981  2450             8275
08-SEP-1981  1500             1500
28-SEP-1981  1250             2750
17-NOV-1981  5000             7750
03-DEC-1981   950            11700
03-DEC-1981  3000            11700
23-JAN-1982  1300            10250
09-DEC-1982  3000             3000
12-JAN-1983  1100             4100