mysql - MYSQL如何计算移动平均线

Question

我有一个将股票报价存储到我的 MySQL 数据库中的应用程序。

我有一个名为 stock_history 的表:

mysql> desc stock_history;
+-------------------+---------------+------+-----+---------+----------------+
| Field             | Type          | Null | Key | Default | Extra          |
+-------------------+---------------+------+-----+---------+----------------+
| id                | int(11)       | NO   | PRI | NULL    | auto_increment |
| date              | date          | NO   | MUL | NULL    |                |
| close             | decimal(12,5) | NO   | MUL | NULL    |                |
| dmal_3            | decimal(12,5) | YES  | MUL | NULL    |                |
+-------------------+---------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

这些是此表中的所有值:

mysql> select date, close, dmal_3 from stock_history order by date asc;
+------------+----------+----------+
| date       | close    |   dmal_3 | 
+------------+----------+----------+-
| 2000-01-03 |  2.00000 |     NULL | 
| 2000-01-04 |  4.00000 |     NULL | 
| 2000-01-05 |  6.00000 |     NULL | 
| 2000-01-06 |  8.00000 |     NULL | 
| 2000-01-07 | 10.00000 |     NULL | 
| 2000-01-10 | 12.00000 |     NULL | 
| 2000-01-11 | 14.00000 |     NULL | 
| 2000-01-12 | 16.00000 |     NULL | 
| 2000-01-13 | 18.00000 |     NULL | 
| 2000-01-14 | 20.00000 |     NULL | 
+------------+----------+----------+-
10 rows in set (0.01 sec)

我保证每个日期会有 0 或 1 条记录。

我能否编写一个查询，将三天移动平均线(即当天和前两个交易日的平均收盘价)插入 dmal_3 字段？怎么办？

查询完成后，我希望表看起来像这样:

mysql> select date, close, dmal_3 from stock_history order by date asc;
+------------+----------+----------+
| date       | close    |   dmal_3 | 
+------------+----------+----------+
| 2000-01-03 |  2.00000 |     NULL | 
| 2000-01-04 |  4.00000 |     NULL | 
| 2000-01-05 |  6.00000 |  4.00000 | 
| 2000-01-06 |  8.00000 |  6.00000 | 
| 2000-01-07 | 10.00000 |  8.00000 | 
| 2000-01-10 | 12.00000 | 10.00000 | 
| 2000-01-11 | 14.00000 | 12.00000 | 
| 2000-01-12 | 16.00000 | 14.00000 | 
| 2000-01-13 | 18.00000 | 16.00000 | 
| 2000-01-14 | 20.00000 | 18.00000 | 
+------------+----------+----------+
10 rows in set (0.01 sec)

Answer 1

这就是我所说的良好挑战。我的解决方案首先为值创建一个计数器并将其用作表。我从中选择所有内容，并加入与子查询相同的查询，检查两者上计数器的位置。一旦查询工作，它只需要与实际表进行内部连接来进行更新。这是我的解决方案:

update stock_history tb1
   inner join 
(
select a.id, 
    case when a.step < 3 then null
    else
    (select avg(b.close) 
       from (
            select hh.*, 
                   @stp:=@stp+1 stp
              from stock_history hh,
                   (select @sum:=0, @stp:=0) x
             order by hh.dt
             limit 17823232
             ) b
      where b.stp >= a.step-2 and b.stp <= a.step
     ) 
     end dmal_3
  from (select h1.*, 
               @step:=@step+1 step
          from stock_history h1,
               (select @sum:=0, @step:=0) x
         order by h1.dt
         limit 17823232
        ) a
) x on tb1.id = x.id
set tb1.dmal_3 = x.dmal_3;

为了便于测试，我更改了一些列名称。这是工作的 SQLFiddle:http://sqlfiddle.com/#!9/e7dc00/1

如果您有任何疑问，请告诉我，以便我澄清!

编辑

子查询中添加了 limit 17823232 子句，因为我不知道您使用的是哪个版本的 MySql。根据它(>= 5.7，不确定)，数据库优化器将忽略内部 order by 子句，使其无法正常工作。我只是选择了一个随机的大数字，通常您可以使用允许的最大值。

你的表和我的表之间唯一具有不同列名的列是 date 我将其命名为 dt 因为 date 是保留字并且您应该使用反引号 (`) 来使用此类列，因此我将在上面的查询中将其保留为 dt。