hi,
i am trying to add a column for my database below. it is a time series data with hourly electrical consumption over a year. i want a column which has the ratio of current consumption to that of the whole day's total consumption (sum of 24 hour) . this i can group by hour and join it with original data and do the ratio by simple division. But at the same time it is required to update (average) this ratio every week as new data comes along. for example at 10 AM on a given week and day of week (1-sunday thru 7-saturday), i want the average of the above mentioned ratio from the weeks before my current week for every hour =>
average = { [ (week-1) + (week-2) + .... + (week1) ]/number of weeks that are before }
how can i keep updating my hourly cum weekly average of this ratio as new data comes along?
Also i have attached my database as a .table file.
Thank you,
Date time satmp | day of week | Hour | elec consumption
| 2014-03-11T00:00:00 | 3 | 0 | 2.0 |
| 2014-03-11T01:00:00 | 3 | 1 | 2.0 |
| 2014-03-11T02:00:00 | 3 | 2 | 2.0 |
| 2014-03-11T03:00:00 | 3 | 3 | 2.0 |
| 2014-03-11T04:00:00 | 3 | 4 | 2.0 |
| 2014-03-11T05:00:00 | 3 | 5 | 2.0 |
| 2014-03-11T06:00:00 | 3 | 6 | 2.0 |
| 2014-03-11T07:00:00 | 3 | 7 | 324.8 |
| 2014-03-11T08:00:00 | 3 | 8 | 862.8000000000002 |
| 2014-03-11T09:00:00 | 3 | 9 | 755.1999999999998 |
| 2014-03-11T10:00:00 | 3 | 10 | 2.0 |
| 2014-03-11T11:00:00 | 3 | 11 | 55.8 |
| 2014-03-11T12:00:00 | 3 | 12 | 701.4000000000004 |
| 2014-03-11T13:00:00 | 3 | 13 | 2.0 |
| 2014-03-11T14:00:00 | 3 | 14 | 2.0 |
| 2014-03-11T15:00:00 | 3 | 15 | 2.0 |
| 2014-03-11T16:00:00 | 3 | 16 | 2.0 |
| 2014-03-11T17:00:00 | 3 | 17 | 2.0 |
| 2014-03-11T18:00:00 | 3 | 18 | 55.8 |
| 2014-03-11T19:00:00 | 3 | 19 | 2.0 |
| 2014-03-11T20:00:00 | 3 | 20 | 2.0 |
| 2014-03-11T21:00:00 | 3 | 21 | 647.6 |
| 2014-03-11T22:00:00 | 3 | 22 | 2.0 |
| 2014-03-11T23:00:00 | 3 | 23 | 2.0 |
| 2014-03-12T00:00:00 | 4 | 0 | 486.20000000000005 |
| 2014-03-12T01:00:00 | 4 | 1 | 1562.2 |
| 2014-03-12T02:00:00 | 4 | 2 | 378.59999999999985 |
| 2014-03-12T03:00:00 | 4 | 3 | 324.7999999999999 |
| 2014-03-12T04:00:00 | 4 | 4 | 271.0 |
| 2014-03-12T05:00:00 | 4 | 5 | 163.39999999999995 |
| 2014-03-12T06:00:00 | 4 | 6 | 109.60000000000001 |
| 2014-03-12T07:00:00 | 4 | 7 | 2.0 |
| 2014-03-12T08:00:00 | 4 | 8 | 2.0 |
| 2014-03-12T09:00:00 | 4 | 9 | 2.0 |