MySQL 对移动范围内的值进行聚集

深藏阁楼爱情的钟 2022-11-01 00:48 112阅读 0赞

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:  
[scott建表及录入数据sql脚本][scott_sql]

# 一.需求 #

要计算移动聚集。

例如，求表EMP中工资的移动和，要从第一个员工的hiredate开始，计算任何90天内的总和，以观察从雇佣第一个员工至雇佣最后一个员工期间90天开销的波动情况。

应返回以下结果集:

# 二.解决方案 #

窗口函数的子句中指定可移动窗口，这个问题就很容易解决。

关键是需要在窗口函数中按hiredate排序，然后指定一个从雇佣第一个员工开始的90天窗口。

如果无法使用这样的窗口函数，则可以使用标量子查询，使起解决方案可能更复杂。

-- 标量子查询写法
    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;

测试记录:

mysql> 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 |
    +------------+---------+------------------+
    | 1980-12-17 |  800.00 |           800.00 |
    | 1981-02-20 | 1600.00 |          1600.00 |
    | 1981-02-22 | 1250.00 |          2850.00 |
    | 1981-04-02 | 2975.00 |          2975.00 |
    | 1981-05-01 | 2850.00 |          2850.00 |
    | 1981-06-09 | 2450.00 |          2450.00 |
    | 1981-09-08 | 1500.00 |          1500.00 |
    | 1981-09-28 | 1250.00 |          2750.00 |
    | 1981-11-17 | 5000.00 |          5000.00 |
    | 1981-12-03 |  950.00 |          8950.00 |
    | 1981-12-03 | 3000.00 |          8950.00 |
    | 1982-01-23 | 1300.00 |          1300.00 |
    | 1987-06-13 | 3000.00 |          4100.00 |
    | 1987-06-13 | 1100.00 |          4100.00 |
    +------------+---------+------------------+
    14 rows in set (0.00 sec)

[scott_sql]: https://www.jianshu.com/p/532fe68924cb