Getting Latest Bid and Ask Results
The following query fills in missing NULL values to create a full book order showing latest bid and ask price and size, by vendor id. Original rows have values for (typically) one price and one size, so use last_value with "ignore nulls" to find the most recent non-null value for the other pair each time there is an entry for the ID. Sequenceno provides a unique total ordering.
Schema:
=> CREATE TABLE bookorders( vendorid VARCHAR(100), date TIMESTAMP, sequenceno INT, askprice FLOAT, asksize INT, bidprice FLOAT, bidsize INT); => INSERT INTO bookorders VALUES('3325XPK','2011-07-12 10:23:54', 1, 10.12, 55, 10.23, 59); => INSERT INTO bookorders VALUES('3345XPZ','2011-07-12 10:23:55', 2, 10.55, 58, 10.75, 57); => INSERT INTO bookorders VALUES('445XPKF','2011-07-12 10:23:56', 3, 10.22, 43, 54); => INSERT INTO bookorders VALUES('445XPKF','2011-07-12 10:23:57', 3, 10.22, 59, 10.25, 61); => INSERT INTO bookorders VALUES('3425XPY','2011-07-12 10:23:58', 4, 11.87, 66, 11.90, 66); => INSERT INTO bookorders VALUES('3727XVK','2011-07-12 10:23:59', 5, 11.66, 51, 11.67, 62); => INSERT INTO bookorders VALUES('5325XYZ','2011-07-12 10:24:01', 6, 15.05, 44, 15.10, 59); => INSERT INTO bookorders VALUES('3675XVS','2011-07-12 10:24:05', 7, 15.43, 47, 58); => INSERT INTO bookorders VALUES('8972VUG','2011-07-12 10:25:15', 8, 14.95, 52, 15.11, 57); COMMIT;
Query:
=> SELECT sequenceno Seq, date "Time", vendorid ID, LAST_VALUE (bidprice IGNORE NULLS) OVER (PARTITION BY vendorid ORDER BY sequenceno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Bid Price", LAST_VALUE (bidsize IGNORE NULLS) OVER (PARTITION BY vendorid ORDER BY sequenceno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Bid Size", LAST_VALUE (askprice IGNORE NULLS) OVER (PARTITION BY vendorid ORDER BY sequenceno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS "Ask Price", LAST_VALUE (asksize IGNORE NULLS) OVER (PARTITION BY vendorid order by sequenceno ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Ask Size" FROM bookorders ORDER BY sequenceno; Seq | Time | ID | Bid Price | Bid Size | Ask Price | Ask Size -----+---------------------+---------+-----------+----------+-----------+---------- 1 | 2011-07-12 10:23:54 | 3325XPK | 10.23 | 59 | 10.12 | 55 2 | 2011-07-12 10:23:55 | 3345XPZ | 10.75 | 57 | 10.55 | 58 3 | 2011-07-12 10:23:57 | 445XPKF | 10.25 | 61 | 10.22 | 59 3 | 2011-07-12 10:23:56 | 445XPKF | 54 | | 10.22 | 43 4 | 2011-07-12 10:23:58 | 3425XPY | 11.9 | 66 | 11.87 | 66 5 | 2011-07-12 10:23:59 | 3727XVK | 11.67 | 62 | 11.66 | 51 6 | 2011-07-12 10:24:01 | 5325XYZ | 15.1 | 59 | 15.05 | 44 7 | 2011-07-12 10:24:05 | 3675XVS | 58 | | 15.43 | 47 8 | 2011-07-12 10:25:15 | 8972VUG | 15.11 | 57 | 14.95 | 52 (9 rows)