mysql - 在新列中引入计算值

Question

我想引入一个新列，其中包含接下来三个周期的计算总和。

这是示例数据

Company Value   Period
------- -----   ------
MGT     9       1401
MGT     2       1402
MGT     3       1403
MGT     4       1404
MGT     5       1405
MGT     6       1406
MGT     7       1407
MGT     8       1408
MGT     9       1409
MGT     10      1410
MGT     11      1411
MGT     12      1412
MGT     3       1501

这是预期的数据

Company Value   Period  New_Column
------- -----   ------  ----------
MGT     9       1401    9
MGT     2       1402    12
MGT     3       1403    15
MGT     4       1404    18
MGT     5       1405    21
MGT     6       1406    24
MGT     7       1407    27
MGT     8       1408    30
MGT     9       1409    33
MGT     10      1410    26
MGT     11      1411    15
MGT     12      1412    3
MGT     3       1501    0

New_Column 包含 future 三个月的新增值。例如，考虑列 Period 1403 (March'14)，其中值为 3，New_Column 现在应该添加 future 三个月的值即 [1404(2014 年 4 月)+ 1404(14 年 5 月)+ 1405(14 年 6 月)] 的值 [4 + 5 + 6] = 15

Answer 1

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (company CHAR(3) NOT NULL
 ,dt DATE NOT NULL
 ,value INT NOT NULL
 ,PRIMARY KEY(company,dt)
 );

 INSERT INTO my_table VALUES
 ('MGT',20140101,9),
 ('MGT',20140201,2),
 ('MGT',20140301,3),
 ('MGT',20140401,4),
 ('MGT',20140501,5),
 ('MGT',20140601,6),
 ('MGT',20140701,7),
 ('MGT',20140801,8),
 ('MGT',20140901,9),
 ('MGT',20141001,10),
 ('MGT',20141101,11),
 ('MGT',20141201,12),
 ('MGT',20150101,3);

 mysql> SELECT * FROM my_table;
 +---------+------------+-------+
 | company | dt         | value |
 +---------+------------+-------+
 | MGT     | 2014-01-01 |     9 |
 | MGT     | 2014-02-01 |     2 |
 | MGT     | 2014-03-01 |     3 |
 | MGT     | 2014-04-01 |     4 |
 | MGT     | 2014-05-01 |     5 |
 | MGT     | 2014-06-01 |     6 |
 | MGT     | 2014-07-01 |     7 |
 | MGT     | 2014-08-01 |     8 |
 | MGT     | 2014-09-01 |     9 |
 | MGT     | 2014-10-01 |    10 |
 | MGT     | 2014-11-01 |    11 |
 | MGT     | 2014-12-01 |    12 |
 | MGT     | 2015-01-01 |     3 |
 +---------+------------+-------+

 SELECT x.*
      , COALESCE(SUM(y.value),0) new_val 
   FROM my_table x 
   LEFT
   JOIN my_table y 
     ON y.company = x.company 
    AND y.dt BETWEEN x.dt + INTERVAL 1 MONTH AND x.dt + INTERVAL 3 MONTH 
  GROUP 
     BY x.company
      , x.dt;
 +---------+------------+-------+---------+
 | company | dt         | value | new_val |
 +---------+------------+-------+---------+
 | MGT     | 2014-01-01 |     9 |       9 |
 | MGT     | 2014-02-01 |     2 |      12 |
 | MGT     | 2014-03-01 |     3 |      15 |
 | MGT     | 2014-04-01 |     4 |      18 |
 | MGT     | 2014-05-01 |     5 |      21 |
 | MGT     | 2014-06-01 |     6 |      24 |
 | MGT     | 2014-07-01 |     7 |      27 |
 | MGT     | 2014-08-01 |     8 |      30 |
 | MGT     | 2014-09-01 |     9 |      33 |
 | MGT     | 2014-10-01 |    10 |      26 |
 | MGT     | 2014-11-01 |    11 |      15 |
 | MGT     | 2014-12-01 |    12 |       3 |
 | MGT     | 2015-01-01 |     3 |       0 |
 +---------+------------+-------+---------+