Rolling Window
Use the ROWS BETWEEN keyword to specify a rolling window over which running total functions (for example RUNAVG) are computed.
Running total functions such as RUNSUM, RUNAVG, RUNMIN and RUNMAX can be specified with rolling window or a shifted boundary. An example could be a moving average used with time series data to smooth out short-term fluctuations and highlight longer-term trends or cycles.
ROWS BETWEEN does not currently support the COUNT function.
Syntax
SELECT (RUNNING TOTAL)(...) ROWS BETWEEN (...) AND (...)
SELECT RUNAVG (...) ROWS BETWEEN (…) AND (…)
Examples
In context of Date:
Define a moving average:
SELECT RUNAVG (Revenue) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
Return Year-to-Date (YTD) for Revenue:
SELECT RUNSUM (Revenue) WITHIN Year (Opp. Close) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Define five days preceding and the current date:
SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING
SELECT RUNAVG({metric}) ROWS BETWEEN 5 PRECEDING AND 1 FOLLOWING
Display year to date - the same result as without specifying ROWS BETWEEN statement:
SELECT RUNSUM({metric}) WITHIN {Year} ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
Define year to yesterday:
SELECT RUNSUM({metric}) WITHIN {Year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ROW
In context of Month:
Define last twelve months:
SELECT RUNSUM({metric}) ROWS BETWEEN 13 PRECEDING AND 1 PRECEDING
Define everything before current month:
SELECT RUNAVG({metric}) WITHIN {year} ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
For more information see, Running Total Functions.