极客教程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