Rolling window on DolphinDB time-series data

Question

In pandas, a rolling window can be calculated on a datetime-like column with an offset-like window.

The official documentation gives an example:

>>> df = pd.DataFrame({'B': [0, 1, 2, np.nan, 4]},
...                   index = [pd.Timestamp('20130101 09:00:00'),
...                            pd.Timestamp('20130101 09:00:02'),
...                            pd.Timestamp('20130101 09:00:03'),
...                            pd.Timestamp('20130101 09:00:05'),
...                            pd.Timestamp('20130101 09:00:06')])

>>> df.rolling('2s').sum()
                       B
2013-01-01 09:00:00  0.0
2013-01-01 09:00:02  1.0
2013-01-01 09:00:03  3.0
2013-01-01 09:00:05  NaN
2013-01-01 09:00:06  4.0

In DolphinDB, the moving functions like mavg, msum, etc. can only be calculated based on the row number of columns rather than time periods. So when I run the following code:

t = table(09:00:00 09:00:02 09:00:03 09:00:05 09:00:06 as time, 0 1 2 NULL 4 as B)
select msum(B, 2) from t

what I got is:

msum_B
------
NULL      
1     
3     
2     
4

And the result remains the same whatever the time column is.

I would like to know whether rolling windows can be calculated based on a time column in DolphinDB.

Davis Zhou Davis Zhou · Accepted Answer · 2019-10-31T12:52:21

window join of DolphinDB database satisfies your need.

select  tleft.time, sum from wj(t as tleft, t, -1:0, <sum(B) as sum>, `time)

Rolling window on DolphinDB time-series data

1 Answers