Calculating the moving average in Vertica for all stocks

I have a stocks table for example:

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;

I want to calculate moving average for all the stocks with a single query in Vertica.

This is the way to do so for only one stock (according to Vertica website):

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;

Output:

         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;

If I try to select all the stock:

SELECT 
    stock, ts, bid, 
    AVG(bid) OVER (ORDER BY ts
                   RANGE BETWEEN INTERVAL '40 seconds' PRECEDING AND CURRENT ROW)
FROM 
    ticks 
GROUP BY 
    stock, bid, ts 
ORDER BY 
    stock, ts;

I am getting a wrong result.

How can I fix that?

Answer

Add a PARTITION BY clause to your call to AVG():

SELECT ts, bid,
       AVG(bid) OVER (PARTITION BY stock ORDER BY ts
                      RANGE BETWEEN INTERVAL '40 seconds' PRECEDING AND
                      CURRENT ROW)
FROM ticks
ORDER BY ts;

Advertisement

Answer