Getting Price Differential for Two Stocks
The following subquery selects out two stocks of interest. The outer query uses the LAST_VALUE() and OVER() components of analytics, with IGNORE NULLS.
Schema
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;
ticks Table
=> SELECT * FROM ticks; ts | stock | bid ---------------------+-------+------- 2011-07-12 10:23:59 | abc | 10.75 2011-07-12 10:25:22 | xyz | 45.16 2011-07-12 10:23:58 | abc | 10.34 2011-07-12 10:25:27 | xyz | 49.33 2011-07-12 10:23:54 | abc | 10.12 2011-07-12 10:31:15 | xyz | 2011-07-12 10:25:15 | abc | 11.98 2011-07-12 10:25:16 | abc | 2011-07-12 10:31:12 | xyz | 65.25 (9 rows)
Query
=> SELECT ts, stock, bid, last_value(price1 IGNORE NULLS) OVER(ORDER BY ts) - last_value(price2 IGNORE NULLS) OVER(ORDER BY ts) as price_diff FROM (SELECT ts, stock, bid, CASE WHEN stock = 'abc' THEN bid ELSE NULL END AS price1, CASE WHEN stock = 'xyz' then bid ELSE NULL END AS price2 FROM ticks WHERE stock IN ('abc','xyz') ) v1 ORDER BY ts; ts | stock | bid | price_diff ---------------------+-------+-------+------------ 2011-07-12 10:23:54 | abc | 10.12 | 2011-07-12 10:23:58 | abc | 10.34 | 2011-07-12 10:23:59 | abc | 10.75 | 2011-07-12 10:25:15 | abc | 11.98 | 2011-07-12 10:25:16 | abc | | 2011-07-12 10:25:22 | xyz | 45.16 | -33.18 2011-07-12 10:25:27 | xyz | 49.33 | -37.35 2011-07-12 10:31:12 | xyz | 65.25 | -53.27 2011-07-12 10:31:15 | xyz | | -53.27 (9 rows)