Hello,
I'm a postgres newbie and am wondering what's the best way to do this.
I am gathering some data and will be inserting to a table once daily.
The table is quite simple but I want the updates to be as efficient as
possible since
this db is part of a big data project.
Say I have a table with these columns:
| Date | Hostname | DayVal | WeekAvg | MonthAvg |
When I insert a new row I have the values for Date, Hostname, DayVal.
Is it possible to define the table is such a way that the WeekAvg and
MonthAvg
are automatically updated as follows?
WeekAvg = current rows DayVal plus the sum of DayVal for the
previous 6 rows.
MonthAvg = current row's DayVal plus the sum of DayVal for the
previous 29 rows.
Should I place the logic in a Trigger or in a Function?
Does someone have an example or a link showing how I could set this
up?
Regards,
Alan