rolling functions, rolling aggregates, sliding window, moving average

[jangorecki]

To gather requirements in single place and refresh ~4 years old discussions creating this issue to cover rolling functions feature (also known as rolling aggregates, sliding window or moving average/moving aggregates).

rolling functions

• rollmean
• rollsum
• rollmin (new rolling functions: min and prod #7299)
• rollmax (left aligned, adaptive, rolling max #7264)
• rollmedian (rolling median #7315)
• rollprod (new rolling functions: min and prod #7299)
• rollsd (frollvar and frollsd #7361)
• rollvar (frollvar and frollsd #7361)
• rollapply (user provided FUN) (rewritten for more features in frollapply rewritten, by.column=F, parallel, any type #7272)

features

• multiple columns at once
• multiple windows at once
• multiple columns and multiple windows at once
• atomic vectors input and single window returns atomic vectors
• various length list of vectors
• align: left/center/right
• handling NAs
• handling Inf (left aligned, adaptive, rolling max #7264)
• fill constant
• long vector support
• partial window support (frollmean partial argument #4968, PR left aligned, adaptive, rolling max #7264)
• adaptive window support
• use openmp to parallelize calculation of multiple columns/windows
• rounding error correction
• timing in verbose mode from parallel region (blocked by testing output using patterns disabled when parentheses present #3422, print uint64 using inttypes.h PRIu64 instead of llu #3423)
• partial & adaptive support (left aligned, adaptive, rolling max #7264)
• give.names argument, same as shift has (left aligned, adaptive, rolling max #7264)
• frollapply by.column=FALSE (issue add by.column=F argument in frollapply #4887, PR frollapply rewritten, by.column=F, parallel, any type #7272)
• unevenly spaced time series (rolling funs / shift could support logical window #3241, PR froll support for unevenly spaced time series #7291)

[jangorecki]

Proposed rollmean implementation, simplified.

x = data.table(v1=1:5, v2=1:5)
k = c(2, 3)

i - single column
j - single window
m - int referring to single row
w - current row's sum of rolling window
r - answer for each i, j

for i in x
  for j in k
  r = NA_real_
  w = 0
    for m in 1:length(i)
      w = w + i[m]
      w = w - i[m-j]
      r[m] = w / j

[MichaelChirico]

yes, and many more rolled functions follow the same basic idea (including rolling standard deviation/any expectation-based moment, and any function like rollproduct that uses invertible * instead of + to aggregate within the window

[st-pasha]

I always envisioned rolling window functionality as grouping the dataset into multiple overlapping groups (windows). Then the API would look something like this:

DT[i, j,
   by = roll(width=5, align="center")]

Then if j contains, say, mean(A), we can internally replace it with rollmean(A) -- exactly like we are doing with gmean() right now. Or j can contain an arbitrarily complicated functionality (say, run a regression for each window), in which case we'd supply .SD data.table to it -- exactly like we do with groups right now.

This way there's no need to introduce 10+ new functions, just one. And it feels data.table-y in spirit too.

[MichaelChirico]

yes, agree

…

On Sat, Apr 21, 2018, 3:38 PM Pasha Stetsenko ***@***.***> wrote: I always envisioned rolling window functionality as *grouping* the dataset into multiple overlapping groups (windows). Then the API would look something like this: DT[i, j, by = roll(width=5, align="center")] Then if j contains, say, mean(A), we can internally replace it with rollmean(A) -- exactly like we are doing with gmean() right now. Or j can contain an arbitrarily complicated functionality (say, run a regression for each window), in which case we'd supply .SD data.table to it -- exactly like we do with groups right now. This way there's no need to introduce 10+ new functions, just one. And it feels data.table-y in spirit too. — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#2778 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AHQQdbADiE4aAI1qPxPnFXUM5gR-0w2Tks5tquH8gaJpZM4TeTQf> .

[jangorecki]

@st-pasha interesting idea, looks like data.table-y spirit, but it will impose many limitations, and isn't really appropriate for this category of functions.

---

• how to perform rollmean by group

DT[, rollmean(V1, 3), by=V2]

• how to calculate different window sizes for different columns

DT[, .(rollmean(V1, 3), rollmean(V2, 100))]

• how to calculate rollmean outside of [.data.table as we now allow for shift

rollmean(rnorm(10), 3)

• how to support queries like

DT[, .(rollmean(list(V1, V2), c(5, 20)), rollmean(list(V2, V3), c(10, 30)))]

• how to call mean and rollmean in same j call

DT[, .(rollmean(V1, 3), mean(V1)), by=V2]

---

Usually when using by we aggregate data to smaller number of rows, while rolling functions always returns vector of same length as input. This types of functions in SQL have API in the following style:

SELECT AVG(value) OVER (ROWS BETWEEN 99 PRECEDING AND CURRENT ROW)
FROM tablename;

You can still combine it with GROUP BY as follows:

SELECT AVG(value) OVER (ROWS BETWEEN 99 PRECEDING AND CURRENT ROW)
FROM tablename
GROUP BY group_columns;

So in SQL those functions stays in SELECT which refers to j in DT.
In DT we could achieve the same with:

DT[, rollmean(value, 100)]
DT[, rollmean(value, 100), group_columns]

Rolling functions fits into same category of functions as shift which also returns same number of rows as it got on input.
Shift in SQL looks like:

SELECT LAG(value, 1) OVER ()
FROM tablename;

---

mean and rollmean are not just different functions, they are different categories of functions. One meant to aggregate according to group, another not aggregate at all. This is easily visible in SQL where we don't use GROUP BY for rolling functions type but we do need to use GROUP BY for aggregates like mean (eventually getting grant total when grouping clause is not present).
I don't see strong reasoning to try to apply same optimizations rules as we do for mean, especially when it doesn't really fit to use case, and all that just for the sake of data.table-y spirit. Current proposal is data.table-y spirit too, it can easily combined with :=, same as shift. It just adds set of new functions, currently not available in data.table.

[st-pasha]

@jangorecki Thanks, these are all valid considerations. Of course different people have different experiences, and different views as to what should be considered "natural".

It is possible to perform rollmean by group: this is just a 2-level grouping: DT[, mean(V1), by=.(V2, roll(3))]. However I don't see how to make different window sizes on different columns with my syntax...

I must admit I never seen SQL syntax for rolling joins before. It's interesting that they use standard aggregator such as AVG yet apply the windowing specification to it. Looking at the Transact-SQL documentation there are some interesting ideas there, for example the distinction between logical/physical row selection. They do allow different "OVER" operators on different columns, however in all examples they give, it is the same OVER clause repeated multiple times. So it suggests that this use-case is much more common, and hence using a single roll() group would result in less repetition.

Also, this SO question provides an interesting insight why the OVER syntax was introduced in SQL at all:

You can use GROUP BY SalesOrderID. The difference is, with GROUP BY you can only have the aggregated values for the columns that are not included in GROUP BY. In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values. That is, although you are not doing that in your example query, you could retrieve both individual OrderQty values and their sums, counts, averages etc. over groups of same SalesOrderIDs.

So it appears that the syntax is designed to circumvent the limitation of standard SQL where group-by results could not be combined with unaggregated values (i.e. selecting both A and mean(A) in the same expression). However data.table does not have such a limitation, so it has more freedom in its choice of syntax.

---

Now, if we want to really get ahead of the curve, we need to think in a broader perspective: what are the "rolling" functions, what are they used for, how they can be extended, etc. Here's my take this, coming from a statistician's point-of-view:

"Rolling mean" function is used to smooth some noisy input. Say, if you have observations over time and you want to have some notion of "average quantity", which would nevertheless vary over time although very slowly. In this case "rolling mean over last 100 observations" or "rolling mean over all previous observations" can be considered. Similarly, if you observe certain quantity over a range of inputs, you may smooth it out by applying "rolling mean over ±50 observations".

• So, the first extension is to look at "smooth windows": imagine a mean over past observations where the further an observation in the past, the less its contribution is. Or an average of nearby observations over a Gaussian kernel.
• Second are adaptive windows. For example, if you have a noisy input defined over an interval [0, 1], then smoothing it using a ±N window produces a biased result near the edges. An unbiased estimator would adapt the shape of the window based on the distance from the edge.
• Resample smoothing: the restriction to produce exactly as many observations as in the source data is too limiting. If you think of your data as a noisy observations of some underlying function, then it is perfectly reasonable to ask to compute the smoothed values of that function on a mesh that is coarser / finer than the original input. Or perhaps the original data is spaced irregularly and you want to resample it onto a regular grid.
• Jackknife: for each observation you want to compute average/regression over all observations except the current.
• K-fold split: view data as multiple groups where each group excludes only a small portion of the observations.

All of these can be implemented as extended grouping operators, with rolling windows being just one of the elements on this list. That being said, I don't why we can't have it both ways.

[jangorecki]

I must admit I never seen SQL syntax for rolling joins before.

I assume you mean rolling functions, issue has nothing to do with rolling joins.

They do allow different "OVER" operators on different columns, however in all examples they give, it is the same OVER clause repeated multiple times. So it suggests that this use-case is much more common, and hence using a single roll() group would result in less repetition.

It is just a matter of use case, if you are calling same OVER() many time you may find it more performant to use GROUP BY, build lookup table and re-use in other queries. Whatever examples are there, repeating OVER() is required to retain the feature of locality for each measure provided. My uses cases from Data Warehouses where not as simple as those from Microsoft docs.

In contrast, using windowed aggregate functions instead of GROUP BY, you can retrieve both aggregated and non-aggregated values.

In data.table we do := and by in one query to achieve it.

So it appears that the syntax is designed to circumvent the limitation of standard SQL where group-by results could not be combined with unaggregated values (i.e. selecting both A and mean(A) in the same expression). However data.table does not have such a limitation, so it has more freedom in its choice of syntax.

It isn't much limitation of SQL but just design of GROUP BY, that it will aggregate, the same way that our by aggregates. New API was required to cover new window functionalities. Grouping for SQL window function can be provided for each function call using FUN() OVER (PARTITION BY ...) where partition by is like local grouping for single measure. So to achieve flexibility of SQL we would need to use j = mean(V1, roll=5) or j = over(mean(V1), roll=5) keeping that API in j. Still this approach will not allow to support all use cases mentioned above.

you may smooth it out by applying "rolling mean over ±50 observations".

This is what align argument is used for.

So, the first extension is to look at "smooth windows": imagine a mean over past observations where the further an observation in the past, the less its contribution is. Or an average of nearby observations over a Gaussian kernel.

There are many variants (virtually unlimited number) of moving averages, the most common smoothing window function (other than rollmean/SMA) is exponential moving average (EMA). Which should be included, and which not, is not trivial to decide, and actually best to make that decision according to feature requests that will come from users, so far none like this was requested.

All of these can be implemented as extended grouping operators, with rolling windows being just one of the elements on this list.

Surely they can, but if you will look at SO, and issues created in our repo, you will see that those few rolling functions here are responsible for 95+% of requests from users. I am happy to work on EMA and other MAs (although I am not sure if data.table is best place for those), but as a separate issue. Some users, me included, are waiting for just simple moving average in data.table for 4 years already.

Here's my take this, coming from a statistician's point-of-view

My point-of-view comes from Data Warehousing (where I used window function, at least once a week) and price trend analysis (where I used tens of different moving averages).