# roll {#roll}

**Parent topic:**[SQL Reference](../../Programming/SQLStatements/SQLStatement.md)

## Syntax {#syntax}

`roll(X, step, [stepType='time'], [fill='none'], [closed='left'], [origin='start_day'], [exclude], [label])`

## Details {#topic_vpz_3xp_mgc}

`roll` is a dummy function to define a user-defined sliding window in SQL queries. In standard `CONTEXT BY` or `GROUP BY` with analytic functions, the *step* is fixed at 1 row. With a dummy function `interval`, `GROUP BY` supports arbitrary *steps* but requires equal window sizes. While `roll` removes these limits, allowing user-defined window size and *step*.

`roll` should appear as the first expression in the SELECT clause and be used together with CONTEXT BY.

**Return value:** A vector with a length equal to the number of windows. Each element of the vector represents the last value of column *X* in the corresponding window, except when *stepType* = 'window', in which case it represents the *label*.

**Windowing Logic**

To use the `roll` function with the SELECT clause, the *step* and the window size are determined by different windowing logics:

-   If *stepType*='window': window size is determined by the *window* parameter.
-   If *stepType* ≠ 'window': `roll` only defines the *step*. The window size is determined by the function type in the SELECT clause:
    -   Aggregate functions \(built-in or user-defined\): window size = *step*.
    -   m-functions:
        -   If *stepType* = 'time': when *window* is of DURATION type, window size =*window*; when *window* is an integer, window size =*window* \* *step*.
        -   If *stepType* = 'row': *window* must be an integer, window size =*window*.
        -   If *stepType* = 'volume' / 'cumvol': *window* must be an integer, window size =*window* \* *step*.
    -   tm functions \(only support *stepType* = 'time'\): window size =*window*.
    -   Columns in the table: window size = *step*, with results equivalent to applying function `last` on the column.

## Arguments {#arguments}

**X** A column representing time or trading volume.

-   If *stepType* is 'time', *X* must be of DATETIME/TIMESTAMP/NANOTIME/NANOTIMESTAMP type, and sorted in ascending order.
-   If *stepType* is 'volume', *X* must be a numeric column \(ascending order\) representing the trading volume.
-   If *stepType* is 'cumvol', *X* must be a numeric column \(ascending order\) representing cumulative trading volume.

**step** Step of the sliding window, which can be:

-   A DURATION scalar, representing time interval. Supported time units: M, w, d, H, m, s, ms, us, and ns.
-   A tuple of length 2, if *stepType* = ‘window’, used to specify a user-defined window. The first element specifies the start time, and the second specifies the end time \(must be greater than the start time\), both of which must be of the same type as the *X* parameter and in ascending order. By default, the window is left-closed and right-open, which can be adjusted using the *closed* parameter.

**stepType** \(optional\) A STRING scalar specifying the step type, which can be:

-   'time' \(default\): *X* is a temporal column, the calculation slide by the temporal column.
-   'row': slide by row count.
-   'volume': *X* is the trading volume \(non-cumulative\). The system automatically converts it into cumulative volume for sliding.
-   'cumvol': *X* is the cumulative trading volume, calculation slide by cumulative trading volume.
-   'window': *step* is a series of user-defined windows.

**fill** \(optional\) A STRING scalar specifying how to handle empty windows, which can be:

-   'none' \(default\): no output.
-   'null': output null.
-   'prev': fill with the previous non-null value.

**closed** \(optional\) A STRING scalar specifying the closure of a user-defined window, which can be:

-   'left' \(default\): left-closed, right-open.
-   'right': left-open, right-closed.

**origin** \(optional\) A STRING scalar specifying the start point for time-based sliding, effective only when *stepType*='time', which can be:

-   'start\_day' \(default\): the start is 00:00:00 of the date corresponding to the first value in the time series.
-   'start': the start is the first value in the time series.
-   'epoch': the start is 1970-01-01.

**exclude** \(optional\) A tuple specifying time periods to exclude, effective only when *stepType*='time'. Each element is a pair of TIME, NANOTIME, MINUTE and SECOND type, e.g., the lunch break for A-shares: \(11:30:00:13:00:00, 15:00:00:16:00:00\). If data exists within the specified time period, it must be filtered via the WHERE clause.

**label** \(optional\) A STRING vector indicating the name of the user-defined window, with a length equal to the number of user-defined windows. Specified only when *stepType*='window'.

## Examples {#examples}

Example 1. Group by symbol and use a sliding window with *step=60s* to compute the average price over the past 180 and 300 seconds.

``` {#codeblock_xmh_2l1_4gc}
timestamp = 2025.08.10T00:00:00 + 30 * 0..19
symbol = take(`A`B`C, 20)
price = [20.81,20.34,20.42,20.99,20.01,20.60,20.45,20.70,20.05,20.25,20.52,20.32,20.15,20.67,20.28,20.70,20.98,20.48,20.01,20.42]
vol = [1002,1091,1035,1041,1016,1038,1007,1007,1062,1030,1064,1012,1007,1007,1035,1038,1015,1041,1029,1048]
t = table(timestamp as timestamp, symbol as symbol, vol as vol, price as price)

select * from t order by symbol
// compute 180s and 300s moving averages via roll and mavg.
select
    roll(X=timestamp, step=60s, stepType='time') as period,
    mavg(price, 180s) as avg_60s,
    mavg(price, 300s) as avg_300s,
    symbol,
    price,
    vol
from t
context by symbol
```

Part of the result:

|period|avg\_60s|avg\_300s|symbol|price|vol|
|------|--------|---------|------|-----|---|
|2025.08.10 00:01:00|20.81|20.81|A|20.81|1,002|
|2025.08.10 00:02:00|20.9|20.9|A|20.99|1,041|
|2025.08.10 00:04:00|20.72|20.75|A|20.45|1,007|
|2025.08.10 00:05:00|20.35|20.625|A|20.25|1,030|
|2025.08.10 00:07:00|20.2|20.28333333333333|A|20.15|1,007|
|2025.08.10 00:08:00|20.424999999999997|20.3875|A|20.7|1,038|
|2025.08.10 00:10:00|20.355|20.286666666666665|A|20.01|1,029|

Example 2. Group by symbol with *step=2* to compute the average volume over the past 2 rows and the sum of volume over the past 3 rows.

``` {#codeblock_fnh_2l1_4gc}
select roll(X=vol, step=2, stepType='row') as window, avg(vol), msum(vol, 3), symbol, price, vol from t context by symbol
```

|window|avg\_vol|msum\_vol|symbol|price|vol|
|------|--------|---------|------|-----|---|
|1,041|1,021.5|2,043|A|20.99|1,041|
|1,030|1,018.5|3,078|A|20.25|1,030|
|1,038|1,022.5|3,075|A|20.7|1,038|
|1,016|1,053.5|2,107|B|20.01|1,016|
|1,064|1,035.5|3,087|B|20.52|1,064|
|1,015|1,011|3,086|B|20.98|1,015|
|1,038|1,036.5|2,073|C|20.6|1,038|
|1,012|1,037|3,112|C|20.32|1,012|
|1,041|1,038|3,088|C|20.48|1,041|

Example 3. Group by symbol, after excluding the 11:30–13:00 lunch break, set *step=60s* to calculate the average price over the past 180 and 300 seconds.

``` {#codeblock_nnh_2l1_4gc}
timestamp = (2025.08.10T11:20:00 + 30 * 0..8) join 2025.08.10T11:40:00.000 join 2025.08.10T11:40:30.000 join (2025.08.10T13:00:00 + 30 * 0..8)
symbol = take(`A`B`C, 20)
price = [20.81,20.34,20.42,20.99,20.01,20.60,20.45,20.70,20.05,20.25,20.52,20.32,20.15,20.67,20.28,20.70,20.98,20.48,20.01,20.42]
vol = [1002,1091,1035,1041,1016,1038,1007,1007,1062,1030,1064,1012,1007,1007,1035,1038,1015,1041,1029,1048]
t = table(timestamp as timestamp, symbol as symbol, vol as vol, price as price)

select
    roll(X=timestamp, step=60s, stepType='time', exclude=(11:30:00 : 13:00:00)) as period,
    mavg(price, 180s) as avg_60s,
    mavg(price, 300s) as avg_300s,
    symbol,
    price,
    vol
from t where second(timestamp) notBetween 11:30:00 and 13:00:00
context by symbol
```

|period|avg\_60s|avg\_300s|symbol|price|vol|
|------|--------|---------|------|-----|---|
|2025.08.10 11:21:00|20.8100|20.8100|A|20.8100|1,002|
|2025.08.10 11:22:00|20.9000|20.9000|A|20.9900|1,041|
|2025.08.10 11:24:00|20.7200|20.7500|A|20.4500|1,007|
|2025.08.10 13:01:00|20.1500|20.1500|A|20.1500|1,007|
|2025.08.10 13:03:00|20.4250|20.4250|A|20.7000|1,038|
|2025.08.10 13:04:00|20.3550|20.2867|A|20.0100|1,029|
|2025.08.10 11:21:00|20.3400|20.3400|B|20.3400|1,091|
|2025.08.10 11:23:00|20.1750|20.1750|B|20.0100|1,016|
|2025.08.10 11:24:00|20.3550|20.3500|B|20.7000|1,007|
|2025.08.10 13:02:00|20.6700|20.6700|B|20.6700|1,007|
|2025.08.10 13:03:00|20.8250|20.8250|B|20.9800|1,015|
|2025.08.10 13:05:00|20.7000|20.6900|B|20.4200|1,048|
|2025.08.10 11:22:00|20.4200|20.4200|C|20.4200|1,035|
|2025.08.10 11:23:00|20.5100|20.5100|C|20.6000|1,038|
|2025.08.10 11:25:00|20.3250|20.3567|C|20.0500|1,062|
|2025.08.10 13:02:00|20.2800|20.2800|C|20.2800|1,035|
|2025.08.10 13:04:00|20.3800|20.3800|C|20.4800|1,041|

Example 4. Define 3 user-defined time windows via *stepType='window'* to compute each stock's volume and weighted average price over the past 1, 3, and 7 days. The `roll` function uses incremental computation, significantly improving efficiency.

``` {#codeblock_vnh_2l1_4gc}
date = stretch(2025.08.01+ 0..8, 24)
time = take([09:30:00, 10:00:00, 10:30:00],24)
symbol = take(`A`B, 24)   
price = [22.40, 24.00, 23.50, 29.30, 24.10, 27.60, 20.10, 21.00, 23.60, 22.40,
               24.60, 23.30, 21.20, 20.60, 27.30, 29.30, 26.90, 25.20, 20.20, 20.10,
               28.30, 22.50, 28.20, 23.50]

vol = [1088, 1074, 1081, 1018, 1045, 1007, 1023, 1009, 1018, 1019,
           1013, 1017, 1039, 1057, 1045, 1062, 1046, 1033, 1022, 1007,
           1087, 1012, 1091, 1074]         
timestamp=concatDateTime(date,time)
t = table(timestamp as timestamp, symbol as symbol, price as price, vol as vol)
t = select * from t order by symbol, timestamp

startDates = [datetime(2025.08.01), datetime(2025.08.01), datetime(2025.08.01)]
endDates = [datetime(2025.08.02), datetime(2025.08.04), datetime(2025.08.07)]
labels = ["1d", "3d", "7d"]


select 
    roll(X=datetime(timestamp), step=(startDates, endDates), stepType='window', label=labels, fill='none') as period,
    timestamp,
    symbol,
    vol,
    price,
    sum(vol) as vol_sum,
    wavg(price, vol) as vwap
from t context by symbol
```

|period|timestamp|symbol|vol|price|vol\_sum|vwap|
|------|---------|------|---|-----|--------|----|
|1d|2025.08.01 10:30:00|A|1,081|23.5000|2,169|22.9482|
|3d|2025.08.03 10:30:00|A|1,018|23.6000|5,255|22.7491|
|7d|2025.08.06 10:00:00|A|1,046|26.9000|9,398|23.7454|
|1d|2025.08.01 10:00:00|B|1,074|24.0000|1,074|24.0000|
|3d|2025.08.03 10:00:00|B|1,009|21.0000|4,108|25.4590|
|7d|2025.08.06 10:30:00|B|1,033|25.2000|9,296|24.7450|

