0

I am working on a financial software where we have huge table of ticks with more than 800M rows.

A single row: symbol_id, broker_id, date, time, bid, ask

The problem is the table has many ticks with identical first four columns and different bid and ask.

What is the best way to update the table and combine such rows into one by taking an average of bid and ask columns?

Example:

|1|2|2012-01-01|12:00:00|1.3456|1.3678|

|1|2|2012-01-01|12:00:00|1.3463|1.3612|

Result row:

|1|2|2012-01-01|12:00:00|1.3459|1.3645|

Improve this question
5
  • I think the quickest way would be to do a create table as select ... using a group by, the drop the old table and rename the new one
    – user1822
    Commented Feb 6, 2014 at 8:25
  • a_horse_with_no_name is correct. @phil_g where is the primary key? Also you must put a trigger on the original table which updates the secondary average table, whenever a new insert is complete in the primary table. This is typically what they call upsert because a) it must put a new entry when the first four columns are encountered for the first time and update when the first four columns were encountered in the primary table.
    – Alexandros
    Commented Feb 6, 2014 at 9:34
  • @Alexandros thanks for the replies. I've tried the method on a slice of the table and it really works. It is my first database-involving project larger than 'creating a blog', so I've read many different articles and somehow decided that I do not need a PK for that table (it has indices though). All need is to keep countless number of ticks, requesting data for various periods.
    – phil_g
    Commented Feb 6, 2014 at 10:36
  • @Alexandros A trigger like that will be murder on performance - it'll serialize all inserts on the base table, at least for any given set of keys, on the UPDATE lock held on a given aggregate record. Periodic re-aggregation will be much more efficient. You've also got to watch accumulated rounding errors when you repeatedly recalculate an average; better to store the sum and number of values and increment them, then return sum/n on the fly.
    – Craig Ringer
    Commented Feb 6, 2014 at 11:48
  • As the goal of merging is to make sure we have maximum one tick in one second, I think I came up with the easiest solution: creating a composite PK from symbol_id, broker_id, date, time and just ignore records with the same PK on insert.
    – phil_g
    Commented Feb 6, 2014 at 12:23

1 Answer 1

Reset to default
-3 
|1|2|2012-01-01|12:00:00|1.3459|1.3645|
SELECT col1, col2, col3, col4, AVG(col5) col5, AVG(col6) col6
FROM table_name
GROUP BY col1, col2, col3, col4;
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.