Windowing#
A series of time windowing examples are provided showing how to aggregate across time windows, and retrieve time and record relative values, using OneTick Cloud sample databases.
These use the SQL windowing, by including the OVER clause, sorting by time, using order by TIMESTAMP asc.
e.g. OVER (order by TIMESTAMP asc)
Rolling time windows are defined by defining a range interval, within the OVER clause, where the units can be MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK.
e.g. OVER (order by TIMESTAMP asc range interval '1' minute preceding)
Rolling record windows can be defined by specifyiing a row count within the OVER clause.
e.g. OVER (order by TIMESTAMP asc rows 50 preceding)
Accumulative Sum#
Accumulative sums are returned through use of both the SUM aggregate, plus the OVER clause, ordering by TIMESTAMP asc.
select PRICE, SIZE,
SUM(SIZE) OVER(order by TIMESTAMP asc) as ACCUM_SIZE
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
LIMIT 10
Symbol |
Timestamp |
PRICE |
SIZE |
ACCUM_SIZE |
|---|---|---|---|---|
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.232 |
70 |
184,613 |
184,613 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.233 |
70.01 |
140 |
184,753 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.287 |
70.01 |
500 |
185,253 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:08.113 |
70.104 |
2,800 |
188,053 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.380 |
70.137 |
49 |
188,102 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.943 |
70.0981 |
66,908 |
255,010 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.944 |
70.1097 |
214 |
255,224 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.119 |
70.146 |
840 |
256,064 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.933 |
70.132 |
344 |
256,408 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:20.863 |
70.19 |
4,012 |
260,420 |
Rolling Sum#
Rolling sums are returned with a syntax similar to the Accumulative Sum. However in this case the OVER clause includes a range interval.
e.g. over(order by TIMESTAMP asc range interval '60' second preceding)
select PRICE, SIZE,
SUM(SIZE) over(order by TIMESTAMP asc range interval '60' second preceding) as ROLLING_SIZE_1_MIN
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
LIMIT 1000
Symbol |
Timestamp |
PRICE |
SIZE |
ROLLING_SIZE_1_MIN |
|---|---|---|---|---|
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.232 |
70 |
184,613 |
184,613 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.233 |
70.01 |
140 |
184,753 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.287 |
70.01 |
500 |
185,253 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:08.113 |
70.104 |
2,800 |
188,053 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.380 |
70.137 |
49 |
188,102 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.943 |
70.0981 |
66,908 |
255,010 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.944 |
70.1097 |
214 |
255,224 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.119 |
70.146 |
840 |
256,064 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.933 |
70.132 |
344 |
256,408 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:20.863 |
70.19 |
4,012 |
260,420 |
Moving Average in Time#
Simple moving averages are returned with the OVER clause, including the range interval.
select PRICE,
AVG(PRICE) over (order by TIMESTAMP asc range interval '1' minute preceding) as sma1_price,
AVG(PRICE) over (order by TIMESTAMP asc range interval '5' minute preceding) as sma5_price
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
LIMIT 10
Symbol |
Timestamp |
PRICE |
SMA5_PRICE |
SMA1_PRICE |
|---|---|---|---|---|
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.232 |
70 |
70 |
70 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.233 |
70.01 |
70.005 |
70.005 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.287 |
70.01 |
70.0066666667 |
70.0066666667 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:08.113 |
70.104 |
70.031 |
70.031 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.380 |
70.137 |
70.0522 |
70.0522 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.943 |
70.0981 |
70.05985 |
70.05985 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.944 |
70.1097 |
70.0669714286 |
70.0669714286 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.119 |
70.146 |
70.07685 |
70.07685 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.933 |
70.132 |
70.0829777778 |
70.0829777778 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:20.863 |
70.19 |
70.09368 |
70.09368 |
Moving Average in Records#
Moving averages can be calculated based on a number of rows, rather than a range interval.
select PRICE, AVG(PRICE) over (order by TIMESTAMP asc rows 5 preceding) as mavg_price
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
LIMIT 10
Symbol |
Timestamp |
PRICE |
MAVG_PRICE |
|---|---|---|---|
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.232 |
70 |
70 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.233 |
70.01 |
70.005 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.287 |
70.01 |
70.0066666667 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:08.113 |
70.104 |
70.031 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.380 |
70.137 |
70.0522 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.943 |
70.0981 |
70.05985 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.944 |
70.1097 |
70.0781333333 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.119 |
70.146 |
70.1008 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.933 |
70.132 |
70.1211333333 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:20.863 |
70.19 |
70.1354666667 |
Exponential Moving Average#
Exponential moving averages are calculated using specialized window functions that apply exponential decay to historical data points.
Two types of exponential moving averages are available: record-based exponential weighting (EXP_W_AVERAGE) and time-based exponential weighting (EXP_TW_AVERAGE).
EXP_W_AVERAGE- Applies exponential weighting based on record decay factorEXP_TW_AVERAGE- Applies exponential weighting based on time decay factor
Both aggregates take a DECAY value, which is the numeric value determining the exponential curve, and an optional DECAY_VALUE_TYPE, which determines how that DECAY value is interpreted.
EXP_W_AVERAGE supports three decay value types:
LAMBDA- Default. Weights of data points in a bucket decrease exponentially in the direction from the most recent tick to the most aged one, being equal toexp(-Lambda * N)for a fixed weight decay value Lambda.HALF_LIFE_INDEX-DECAYspecifies the necessary number of consecutive ticks, the first one of which would have twice less the weight of the last one.NUM_LOOKBACK_PERIODS-DECAYspecifies the EMA period N, matching the conventional smoothing-factor formulaalpha = 2/(N+1)used by common EMA implementations, for example pandasewm(span=N, adjust=False).
NUM_LOOKBACK_PERIODS is the decay value type to use when an N-period EMA is required, such as the 12, 26 and 9 period EMAs used to build MACD in the Technical Analysis section.
-- Returns price with three types of Exponential Moving Averages
select PRICE,
EXP_W_AVERAGE(PRICE,DECAY=0.01) OVER(order by TIMESTAMP asc) as ewa_price, -- Using the default decay value type of LAMBDA
EXP_W_AVERAGE(PRICE,DECAY=0.01,DECAY_VALUE_TYPE='LAMBDA') OVER(order by TIMESTAMP asc) as ewa_lambda_price,
EXP_W_AVERAGE(PRICE,DECAY=2,DECAY_VALUE_TYPE='HALF_LIFE_INDEX') OVER(order by TIMESTAMP asc) as ewa_halflife_price,
EXP_W_AVERAGE(PRICE,DECAY=10,DECAY_VALUE_TYPE='NUM_LOOKBACK_PERIODS') OVER(order by TIMESTAMP asc) as ewa_lookback_periods_price
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
Exponential Time Weighted Moving Average#
EXP_TW_AVERAGE weights each data point by elapsed time rather than by tick position, so gaps between ticks affect the result.
It supports two decay value types:
HALF_LIFE_INDEX- Default.DECAYspecifies the half life in seconds, the time interval after which a data point has half the weight of the later one.LAMBDA- Weights of data points in a bucket decrease exponentially in the direction from the most recent tick to the most aged one, being equal toexp(-Lambda * N)for a fixed weight decay value Lambda.
-- Returns price with Time Weighted Moving Average
select PRICE,
EXP_TW_AVERAGE(PRICE,DECAY=300) OVER(order by TIMESTAMP asc) as etwa_price, -- Using the default decay value type of HALF_LIFE_INDEX
EXP_TW_AVERAGE(PRICE,DECAY=0.01,DECAY_VALUE_TYPE='LAMBDA') OVER(order by TIMESTAMP asc) as etwa_lambda_price,
EXP_TW_AVERAGE(PRICE,DECAY=300,DECAY_VALUE_TYPE='HALF_LIFE_INDEX') OVER(order by TIMESTAMP asc) as etwa_halflife_price
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
Time Shifts#
Values can be retrieved relative to the current row by a specified time period in milliseconds using the time_shift function. , which requires the field enclosed in quotes, plus the time shift.
Negative time shifts retrieve the prevailing value at the specified number of milliseconds before the row timestamp.
Positive time shifts retrieve the prevailing value at the specified number of milliseconds after the row timestamp.
select TIMESTAMP, PRICE, SIZE, TRADE_ID, TRADE_VENUE,
time_shift('PRICE',-1000) as PRICE_BACK_1S,
time_shift('PRICE',-10000) as PRICE_BACK_10S,
time_shift('PRICE',-60000) as PRICE_BACK_60S,
time_shift('PRICE',1000) as PRICE_FWD_1S,
time_shift('PRICE',10000) as PRICE_FWD_10S,
time_shift('PRICE',60000) as PRICE_FWD_60S
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
LIMIT 10
Symbol |
Timestamp |
PRICE |
SIZE |
TRADE_ID |
TRADE_VENUE |
PRICE_BACK_1S |
PRICE_BACK_10S |
PRICE_BACK_60S |
PRICE_FWD_1S |
PRICE_FWD_10S |
PRICE_FWD_60S |
|---|---|---|---|---|---|---|---|---|---|---|---|
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.232 |
70 |
184,613 |
911727684223506 |
XLON |
70.104 |
70.19 |
70.45 |
|||
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.233 |
70.01 |
140 |
911727684223588 |
XLON |
70.104 |
70.19 |
70.45 |
|||
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.287 |
70.01 |
500 |
911727684223589 |
XLON |
70.104 |
70.19 |
70.45 |
|||
LSE_SAMPLE::VOD |
2024-01-03 08:00:08.113 |
70.104 |
2,800 |
25706436899852400 |
XLON |
70.01 |
70.137 |
70.19 |
70.08 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.380 |
70.137 |
49 |
892755055723892848 |
XLON |
70.104 |
70.132 |
70.19 |
70.08 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.943 |
70.0981 |
66,908 |
50599517591654512 |
XLON |
70.104 |
70.19 |
70.19 |
70.08 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.944 |
70.1097 |
214 |
77621115355877488 |
XLON |
70.104 |
70.19 |
70.19 |
70.08 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.119 |
70.146 |
840 |
390726911293870192 |
XLON |
70.104 |
70.19 |
70.19 |
70.08 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.933 |
70.132 |
344 |
953676864715182192 |
XLON |
70.137 |
70.19 |
70.23 |
70.08 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:20.863 |
70.19 |
4,012 |
911727684224102 |
XLON |
70.132 |
70.146 |
70.265 |
70.3 |
70.44 |
Record Shifts / Lag & Lead#
- Values can be retrieved relative to the current row by a specified number of records using the
LAGandLEADfunctions. They require the field, and assume a 1 record offset, or additionally the number of records to shift is also included.
LAG(PRICE) OVER(ORDER BY TIMESTAMP)- Return thePRICEshifted back 1 recordLAG(PRICE,5) OVER(ORDER BY TIMESTAMP)- Return thePRICEshifted back 5 records
select TIMESTAMP, PRICE, SIZE, TRADE_ID, TRADE_VENUE,
LAG(PRICE) OVER(ORDER BY TIMESTAMP) as PRICE_1_BACK,
LAG(TRADE_ID) OVER(ORDER BY TIMESTAMP) as TRADE_ID_1_BACK,
LAG(PRICE,5) OVER(ORDER BY TIMESTAMP) as PRICE_5_BACK,
LAG(TRADE_ID,5) OVER(ORDER BY TIMESTAMP) as TRADE_ID_5_BACK,
LEAD(PRICE) OVER(ORDER BY TIMESTAMP) as PRICE_1_FWD,
LEAD(TIMESTAMP) OVER(ORDER BY TIMESTAMP) as NEXT_TIMESTAMP,
LEAD(TRADE_ID) OVER(ORDER BY TIMESTAMP) as TRADE_ID_1_FWD,
LEAD(PRICE,5) OVER(ORDER BY TIMESTAMP) as PRICE_5_FWD,
LEAD(TRADE_ID,5) OVER(ORDER BY TIMESTAMP) as TRADE_ID_5_FWD
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
limit 1000
Symbol |
Timestamp |
PRICE |
SIZE |
TRADE_ID |
TRADE_VENUE |
PRICE_1_BACK |
TRADE_ID_1_BACK |
PRICE_5_BACK |
TRADE_ID_5_BACK |
PRICE_1_FWD |
TRADE_ID_1_FWD |
PRICE_5_FWD |
TRADE_ID_5_FWD |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.232 |
70 |
184,613 |
911727684223506 |
XLON |
70.01 |
911727684223588 |
70.0981 |
50599517591654512 |
||||
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.233 |
70.01 |
140 |
911727684223588 |
XLON |
70 |
911727684223506 |
70.01 |
911727684223589 |
70.1097 |
77621115355877488 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:06.287 |
70.01 |
500 |
911727684223589 |
XLON |
70.01 |
911727684223588 |
70.104 |
25706436899852400 |
70.146 |
390726911293870192 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:08.113 |
70.104 |
2,800 |
25706436899852400 |
XLON |
70.01 |
911727684223589 |
70.137 |
892755055723892848 |
70.132 |
953676864715182192 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.380 |
70.137 |
49 |
892755055723892848 |
XLON |
70.104 |
25706436899852400 |
70.0981 |
50599517591654512 |
70.19 |
911727684224102 |
||
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.943 |
70.0981 |
66,908 |
50599517591654512 |
XLON |
70.137 |
892755055723892848 |
70 |
911727684223506 |
70.1097 |
77621115355877488 |
70.24 |
911727684224103 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:09.944 |
70.1097 |
214 |
77621115355877488 |
XLON |
70.0981 |
50599517591654512 |
70.01 |
911727684223588 |
70.146 |
390726911293870192 |
70.26 |
911727684224104 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.119 |
70.146 |
840 |
390726911293870192 |
XLON |
70.1097 |
77621115355877488 |
70.01 |
911727684223589 |
70.132 |
953676864715182192 |
70.26 |
911727684224105 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:10.933 |
70.132 |
344 |
953676864715182192 |
XLON |
70.146 |
390726911293870192 |
70.104 |
25706436899852400 |
70.19 |
911727684224102 |
70.23 |
911727684224190 |
LSE_SAMPLE::VOD |
2024-01-03 08:00:20.863 |
70.19 |
4,012 |
911727684224102 |
XLON |
70.132 |
953676864715182192 |
70.137 |
892755055723892848 |
70.24 |
911727684224103 |
70.23 |
911727684224191 |
Time-Weighted Average Price (TWAP)#
Time-Weighted Average Price calculates the average price weighted by the time intervals between trades. Useful for trade execution analysis and as a benchmark for algorithm performance evaluation.
The TW_AVG aggregate produces the time-weighted average. Selecting AVG alongside it shows the
difference: AVG treats every tick equally, while TW_AVG weights each value by how long it stood.
select
AVG(PRICE) as avg_price,
TW_AVG(PRICE) as twap_price
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
group by time_bucket(INTERVAL '5' MINUTE)
limit 1000
Time-Weighted Average Spread (TWAS)#
Time-Weighted Average Spread calculates the average bid-ask spread weighted by how long each spread stood, rather than by how many quotes were published. Unlike a simple arithmetic average, TWAS accounts for how long each spread persists in the market, so a wide spread that lasted a second counts for more than one that was corrected immediately.
The spread is derived from the quote table as ASK_PRICE - BID_PRICE, and TW_AVG applies the time
weighting. The difference is substantial in practice: over a single VOD session, AVG returns 0.016 while
TW_AVG returns 1.105 on the same quotes.
Calculates TWAS for the entire specified time range.
select
TW_AVG(ASK_PRICE - BID_PRICE) as SPREAD
from LSE_SAMPLE.QTE
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
limit 1000
Aggregates TWAS grouped by exchange venue to analyze spread differences across markets.
select EXCHANGE,
TW_AVG(ASK_PRICE - BID_PRICE) as SPREAD
from US_COMP_SAMPLE.QTE
where symbol_name = 'CSCO'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
group by EXCHANGE
Divides the time range into equal intervals with time_bucket and calculates TWAS for each bucket, to show
how the spread moves over the session.
select
TW_AVG(ASK_PRICE - BID_PRICE) as SPREAD
from LSE_SAMPLE.QTE
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
group by time_bucket(INTERVAL '5' MINUTE)
limit 1000
Calculates TWAS over a rolling time window, so each row carries the time-weighted spread for the preceding minute. The spread is computed in a sub-query first, as the window function aggregates an existing column.
select TW_AVG(SPREAD) over(order by TIMESTAMP asc range interval '60' second preceding) as ROLLING_SPREAD_1_MIN
from (
select
ASK_PRICE-BID_PRICE as SPREAD
from LSE_SAMPLE.QTE
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
)
Dynamic Bar Creation with Fill Forward#
Returns OHLC bars with the last price filled forward into subsequent time buckets even when no trades occur. Uses TIME_SERIES_TYPE='STATE_TS' on the LAST aggregate function to extend the previous bar’s closing price forward. Useful for creating complete time series where every bucket has a value, even during low-volume periods.
-- Returns OHLC prices with the Last Price Filled Forward into 5-minute buckets
SELECT
FIRST(PRICE) as FIRST_PRICE,
MAX(PRICE) as HIGH_PRICE,
MIN(PRICE) as LOW_PRICE,
LAST(PRICE,TIME_SERIES_TYPE='STATE_TS') as LAST_PRICE,
VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE) as VWAP_PRICE,
SUM(SIZE) as VOLUME,
COUNT(SIZE) as TRADE_COUNT
FROM US_COMP_SAMPLE.TRD
WHERE SYMBOL_NAME = 'HD'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
and SIZE > 1000
group by time_bucket(INTERVAL '5' MINUTE)