Support more options for rollingWindow leading/trailing values
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
At the moment, `rollingWindow` `leading` and `trailing` parameters supports values in the following format: `(-?\d+) (minute|hour|day|week|month|year)` which can be used to define the window size to the right and left of the `offset`.
While this works OK in many scenarios, there are circumstances in which the correct value for `leading`/`trailing` parameter cannot be determined at schema creation.
For example, let's say you want to calculate the total rolling revenue since the beginning of the last month to today. To achieve this, you will need to know the query's date range (which is only available at query creation) so that you can precisely calculate the number of days to set to the `trailing` parameter.
```
{
offset: 'start',
trailing: '? day' // how many days to the start of the previous month?
}
```
To overcome such limitations, additional options can be added to enable specifying beginning/ending of a period, e.g start/end of month, year, week, n months ago etc.
One way would be to add additional options such as:
```
{
offset: 'start',
trailing: 'start of 1 month ago'
}
```
Or something similar could be attained by introducing a modifier after a `pipe` symbol, to specify the start/end of a period as follow
```
{
offset: 'start',
trailing: '1 month | start of month'
}
```
The period part can optionally be omitted:
```
{
offset: 'start',
leading: '| end of year' // calculate running total until the period's year-end
}
```
My suggestions might not be the best way to implement this feature. But I do hope they serve well to explain how this improvement can be implemented.
I am open to your ideas and suggestions.
Thanks
Contributor guide
Assessment
This issue has not been assessed yet.