Timeline for PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 27, 2016 at 3:43 | answer | added | Tjatoer | timeline score: -3 | |
| Feb 6, 2014 at 12:23 | comment | added | phil_g |
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.
|
|
| Feb 6, 2014 at 11:48 | comment | added | Craig Ringer |
@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.
|
|
| Feb 6, 2014 at 10:36 | comment | added | phil_g | @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. | |
| Feb 6, 2014 at 9:34 | comment | added | Alexandros | 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. | |
| Feb 6, 2014 at 8:37 | review | First posts | |||
| Feb 6, 2014 at 8:46 | |||||
| Feb 6, 2014 at 8:25 | comment | added | user1822 |
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
|
|
| Feb 6, 2014 at 8:19 | history | asked | phil_g | CC BY-SA 3.0 |