Calculating the Moving Average

Calculating the moving average is useful to get an estimate about the trends in a data set. The moving average is the average of any subset of numbers over a period of time. For example, if you have retail data that spans over ten years, you could calculate a three year moving average, a four year moving average, and so on. This example calculates a 40-second moving average of bids for one stock. This examples uses the ticks table schema.

Query

=> SELECT ts, bid, AVG(bid)
   OVER(ORDER BY ts
       RANGE BETWEEN INTERVAL '40 seconds' 
       PRECEDING AND CURRENT ROW)
FROM ticks 
WHERE stock = 'abc' 
GROUP BY bid, ts 
ORDER BY ts;
         ts          |  bid  |     ?column?
---------------------+-------+------------------
 2011-07-12 10:23:54 | 10.12 |            10.12
 2011-07-12 10:23:58 | 10.34 |            10.23
 2011-07-12 10:23:59 | 10.75 | 10.4033333333333
 2011-07-12 10:25:15 | 11.98 |            11.98
 2011-07-12 10:25:16 |       |            11.98
(5 rows)

DROP TABLE Ticks CASCADE;


CREATE TABLE Ticks (ts TIMESTAMP, Stock varchar(10), Bid float);
INSERT INTO Ticks VALUES('2011-07-12 10:23:54', 'abc', 10.12);
INSERT INTO Ticks VALUES('2011-07-12 10:23:58', 'abc', 10.34);
INSERT INTO Ticks VALUES('2011-07-12 10:23:59', 'abc', 10.75); 
INSERT INTO Ticks VALUES('2011-07-12 10:25:15', 'abc', 11.98); 
INSERT INTO Ticks VALUES('2011-07-12 10:25:16', 'abc'); 
INSERT INTO Ticks VALUES('2011-07-12 10:25:22', 'xyz', 45.16); 
INSERT INTO Ticks VALUES('2011-07-12 10:25:27', 'xyz', 49.33); 
INSERT INTO Ticks VALUES('2011-07-12 10:31:12', 'xyz', 65.25); 
INSERT INTO Ticks VALUES('2011-07-12 10:31:15', 'xyz'); 

COMMIT;