title: How to calculate a moving average in MYSQL
body: <p>I have an application that stores stock quotes into my MySQL database.</p>
  
  <p>I have a table called <code>stock_history</code>:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>These are all the values in this table:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>
  
  <p>I am guaranteed that there will be 0 or 1 record for each date.</p>
  
  <p>Can I write a single query that will insert the three-day moving average (ie: the average closing prices of that day and the two previous trading days before it) into the <code>dmal_3</code> field? How?</p>
  
  <p>When the query is done, I want the table to look like this:</p>
  
  <pre><code>mysql&gt; 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)
  </code></pre>