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. .. code-block:: sql :caption: Accumulative Sum of Trade Size across the time window 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 .. csv-table:: Accumulative Sum of SIZE across the time window results :header: 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)`` .. code-block:: sql :caption: Rolling Sum of Trade Size across a 60 second rolling period 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 .. csv-table:: Rolling Sum of Trade Size across a 60 second rolling period results :header: 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``. .. code-block:: sql :caption: Trade Price plus 1 and 5 Minute Moving averages retrieval. 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 .. csv-table:: Trade Price plus 1 and 5 Minute Moving averages retrieval results. :header: 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``. .. code-block:: sql :caption: 5 Row Moving average retrieval 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 .. csv-table:: 5 Row Moving average retrieval results. :header: 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 factor * ``EXP_TW_AVERAGE`` - Applies exponential weighting based on time decay factor (in milliseconds) .. code-block:: sql :caption: Trade Price with Exponential Moving Averages (Record and Time-based Decay) select PRICE, EXP_W_AVERAGE(PRICE,DECAY=0.01) OVER(order by TIMESTAMP asc) as ewa_price, EXP_TW_AVERAGE(PRICE,DECAY=300) OVER(order by TIMESTAMP asc) as etwa_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. .. code-block:: sql :caption: Trades with Time shifts 1, 10, and 60 seconds before and after each trade retrieval 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 .. csv-table:: Trades with Time shifts 1, 10, and 60 seconds before and after each trade retrieval results :header: 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 ``LAG`` and ``LEAD`` functions. They require the field, and assume a 1 record offset, or additionally the number of records to shift is also included. * ``LAG(MID_PRICE)`` - Return the ``MID_PRICE`` shifted back 1 record * ``LAG(MID_PRICE,5)`` - Return the ``MID_PRICE`` shifted back 5 records .. code-block:: sql :caption: Trades with shifts 1 and 5 records before and after each trade retrieval select TIMESTAMP, PRICE, SIZE, TRADE_ID, TRADE_VENUE, LAG(PRICE) as PRICE_1_BACK,LAG(TRADE_ID) as TRADE_ID_1_BACK, LAG(PRICE,5) as PRICE_5_BACK,LAG(TRADE_ID,5) as TRADE_ID_5_BACK, LEAD(PRICE) as PRICE_1_FWD,LEAD(TRADE_ID) as TRADE_ID_1_FWD, LEAD(PRICE,5) as PRICE_5_FWD,LEAD(TRADE_ID,5) 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 10 .. csv-table:: Trades with shifts 1 and 5 records before and after each trade retrieval results :header: 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. .. code-block:: sql :caption: TWAP Bar Creation select * from US_COMP_SAMPLE.TRD where symbol_name = 'CSCO' and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York' and TIMESTAMP < '2024-01-03 16:00:00 America/New_York' LIMIT 1000 Time-Weighted Average Size (TWAS) ################################## Time-Weighted Average Size calculates the average trade size weighted by the time intervals between trades across a specified period. Unlike a simple arithmetic average of trade sizes, TWAS accounts for how long each trade size persists in the market. A trade size that exists for longer periods has greater influence on the final TWAS value. The calculation weights each trade size by the time interval until the next trade, providing insight into typical execution sizes over different time windows. Calculates TWAS for the entire specified time range. .. code-block:: sql :caption: TWAS Across Time Range select avg(SIZE) as TWAS from US_COMP_SAMPLE.TRD where symbol_name = 'CSCO' and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York' and TIMESTAMP < '2024-01-03 16:00:00 America/New_York' Aggregates TWAS grouped by exchange venue to analyze size distribution across different markets. .. code-block:: sql :caption: TWAS by Exchange select EXCHANGE, avg(SIZE) as TWAS from US_COMP_SAMPLE.TRD where symbol_name = 'CSCO' and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York' and TIMESTAMP < '2024-01-03 16:00:00 America/New_York' group by EXCHANGE Divides the time range into equal intervals and calculates TWAS for each bucket to show size trends over time. .. code-block:: sql :caption: TWAS in Time-Based Buckets select floor(TIMESTAMP, 1 minute) as BUCKET_TIME, avg(SIZE) as TWAS from US_COMP_SAMPLE.TRD where symbol_name = 'CSCO' and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York' and TIMESTAMP < '2024-01-03 16:00:00 America/New_York' group by BUCKET_TIME Calculates TWAS using a rolling time window to show how average size changes as time advances. .. code-block:: sql :caption: TWAS in Rolling Window select TIMESTAMP, avg(SIZE) over(order by TIMESTAMP asc range interval '1' minute preceding) as ROLLING_TWAS from US_COMP_SAMPLE.TRD where symbol_name = 'CSCO' and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York' and TIMESTAMP < '2024-01-03 16:00:00 America/New_York' LIMIT 1000 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. .. code-block:: sql :caption: Dynamic Bar Creation with Fill 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)