3

I'd like to sum up moving averages for a number of different categories when storing log records. Imagine a service that saves web server logs one entry at a time. Let's further imagine, we don't have access to the logged records. So we see them once but don't have access to them later on.

For different pages, I'd like to know

  • the total number of hits (easy)
  • a "recent" average (like one month or so)
  • a "long term" average (over a year)

Is there any clever algorithm/data model that allows to save such moving averages without having to recalculate them by summing up huge quantities of data?

I don't need an exact average (exactly 30 days or so) but just trend indicators. So some fuzziness is not a problem at all. It should just make sure that newer entries are weighted higher than older ones.

One solution probably would be to auto-create statistics records for each month. However, I don't even need past month statistics, so this seems like overkill. And it wouldn't give me a moving average but rather swap to new values from month to month.

Ortwin Gentz
  • 52,648
  • 24
  • 135
  • 213

3 Answers3

6

An easy solution would be to keep an exponentially decaying total.

It can be calculated using the following formula:

newX = oldX * (p ^ (newT - oldT)) + delta

where oldX is the old value of your total (at time oldT), newX is the new value of your total (at time newT); delta is the contribution of new events to the total (for example the number of hits today); p is less or equal to 1 and is the decay factor. If we take p = 1, then we have the total number of hits. By decreasing p, we effectively decrease the interval our total describes.

Rotsor
  • 13,655
  • 6
  • 43
  • 57
  • Thanks. Would it make sense to use UNIX timestamps for `newT` and `oldT`, set `delta` to 1 (in order to evaluate the formula freshly for each new logged record)? – Ortwin Gentz Nov 21 '11 at 13:55
  • 1
    Seems to work great. Looks like `p=0.9` gives me a 10 time units average and `p=0.99` a 100 time units average. – Ortwin Gentz Nov 22 '11 at 10:48
  • 1
    Great! By the way, don't forget to apply the formula with `delta = 0` when displaying the totals to the user. Otherwise, the user will be able to see stale values. – Rotsor Nov 22 '11 at 11:39
  • Thanks, implemented the formula in the getter method. To log a new record I just have to add 1 to the value from the getter method. – Ortwin Gentz Nov 22 '11 at 15:29
1

If all you really want is a smoothed value with a given time constant then the easiest thing is to use a single pole recursive IIR filter (aka AR or auto-regressive filter in time series analysis). This takes the form:

Xnew = k * X_old + (1 - k) * x

where X_old is the previous smoothed value, X_new is the new smoothed value, x is the current data point and k is a factor which determines the time constant (usually a small value, < 0.1). You may need to determine the two k values (one value for "recent" and a smaller value for "long term") empirically, based on your sample rate, which ideally should be reasonably constant, e.g. one update per day.

Paul R
  • 208,748
  • 37
  • 389
  • 560
  • The constant sample rate is not given in my case since I want to avoid saving intermediary values during a timeframe (e.g. sum of records per day). So I'd like to evaluate the new values right when receiving a new log record. – Ortwin Gentz Nov 21 '11 at 13:59
0

It may be solution for you.

You can aggregate data to intermediate storage grouped by hour or day. Than grouping function will work very fast, because you will need to group small amount of records and inserts will be fast as well. Precision decisions up to you.

It can be better than auto-correlated exponential algorithms because you can understand what you calculate easier and it doesn't require math each step.

For last term data you can use capped collections with limited amount of records. They supported natively by some DBs for example MongoDB.

varela
  • 1,281
  • 1
  • 10
  • 16