roll

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

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.

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'.

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.

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:

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.

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

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.

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

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.

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