Aggregation#

The following aggregations are supported:

SUM(), AVG(), TW_AVG(), MIN(), MAX(), COUNT(), FIRST(), LAST(), VAR(), VARP(), STDDEV(), STDDEVP(), FIRST_TIME(), LAST_TIME(), HIGH_TIME(), LOW_TIME(), VWAP(), MEDIAN(), CORRELATION(), EXP_TW_AVERAGE(), EXP_W_AVERAGE(), STANDARDIZED_MOMENT(), RANKING().

Results can be grouped using GROUP BY with either:

A field from the table. e.g. EXCHANGE
A time bucket using the time_bucket function
A row bucket using the ticks_bucket function

A series of simple examples are provided below showing how to retrieve aggregated market data from OneTick Cloud.

Common Aggregations#

Common aggregation methods include SUM(), AVG(), MIN(), MAX(), COUNT(), VAR(), VARP(), STDDEV(), STDDEVP().

Common Aggregations for Vodafone retrieval#

select
AVG(PRICE) as MEAN_PRICE,
STDDEV(PRICE) as STDDEV_PRICE,
MAX(PRICE) as MAX_PRICE,
MIN(PRICE) as MIN_PRICE,
COUNT(PRICE) as COUNT_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'

Common Aggregations for Vodafone retrieval results#
Symbol	Timestamp	MEAN_PRICE	STDDEV_PRICE	MAX_PRICE	MIN_PRICE	COUNT_PRICE
LSE_SAMPLE::VOD	2024-01-04 16:00:00	69.7709393124	4.87753568151	71.0166	0.812	11,925

Group by Field#

Data can be grouped by field such as TRADE_CURRENCY

Common Aggregations for Vodafone grouped by Trade Currency retrieval#

select
TRADE_CURRENCY,
AVG(PRICE) as MEAN_PRICE,
STDDEV(PRICE) as STDDEV_PRICE,
MAX(PRICE) as MAX_PRICE,
MIN(PRICE) as MIN_PRICE,
COUNT(PRICE) as COUNT_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 TRADE_CURRENCY

Common Aggregations for Vodafone grouped by Trade Currency retrieval results#
Symbol	Timestamp	TRADE_CURRENCY	MEAN_PRICE	STDDEV_PRICE	MAX_PRICE	MIN_PRICE	COUNT_PRICE
LSE_SAMPLE::VOD	2024-01-04 16:00:00	EUR	0.817847457627	0.0025159049878	0.825	0.812	59
LSE_SAMPLE::VOD	2024-01-04 16:00:00	GBX	70.1137871482	0.385602260811	71.0166	68.05	11,866

Group by Time Bucket#

Data can be grouped by time bucket, with the time_bucket function, which specifies an interval. e.g. time_bucket(INTERVAL '5' MINUTE). Intervals can be: MILLISECOND, SECOND, MINUTE, HOUR, DAY or WEEK.

Common Aggregations for Vodafone grouped by 5 Minute bar retrieval#

select
AVG(PRICE) as MEAN_PRICE,
STDDEV(PRICE) as STDDEV_PRICE,
MAX(PRICE) as MAX_PRICE,
MIN(PRICE) as MIN_PRICE,
COUNT(PRICE) as COUNT_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 10

Common Aggregations for Vodafone grouped by 5 Minute time bucket retrieval results#
Symbol	Timestamp	MEAN_PRICE	STDDEV_PRICE	MAX_PRICE	MIN_PRICE	COUNT_PRICE
LSE_SAMPLE::VOD	2024-01-03 08:05:00	69.5115052632	7.70537091338	70.57	0.8175	323
LSE_SAMPLE::VOD	2024-01-03 08:10:00	69.1482768519	9.42987377888	70.54	0.8185	108
LSE_SAMPLE::VOD	2024-01-03 08:15:00	68.4568388889	11.5137614925	70.54	0.817	72
LSE_SAMPLE::VOD	2024-01-03 08:20:00	69.4476636364	7.40070551216	70.3798	0.816	88
LSE_SAMPLE::VOD	2024-01-03 08:25:00	69.4555013333	8.03271759341	70.45	0.818	75
LSE_SAMPLE::VOD	2024-01-03 08:30:00	68.0768596774	12.3804045254	70.48	0.817	62
LSE_SAMPLE::VOD	2024-01-03 08:35:00	65.174885	18.5591156419	70.49	0.816	40
LSE_SAMPLE::VOD	2024-01-03 08:40:00	70.4912977778	0.0197907059097	70.54	70.4598	45
LSE_SAMPLE::VOD	2024-01-03 08:45:00	69.6322601449	8.37524346447	70.72	0.8195	138
LSE_SAMPLE::VOD	2024-01-03 08:50:00	70.6536	0.0184115275799	70.7	70.62	47

Group by Record Bucket#

Data can be grouped by count of records, with the ticks_bucket function, which specifies the number of records to group on ticks_bucket(1000).

Common Aggregations for Vodafone grouped by 1000 records#

select
AVG(PRICE) as MEAN_PRICE,
STDDEV(PRICE) as STDDEV_PRICE,
MAX(PRICE) as MAX_PRICE,
MIN(PRICE) as MIN_PRICE,
COUNT(PRICE) as COUNT_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 ticks_bucket(1000)
LIMIT 10

Common Aggregations for Vodafone grouped by 1000 Records retrieval results#
Symbol	Timestamp	MEAN_PRICE	STDDEV_PRICE	MAX_PRICE	MIN_PRICE	COUNT_PRICE
LSE_SAMPLE::VOD	2024-01-03 08:50:40.916	69.2407897	9.00384446298	70.72	0.816	1,000
LSE_SAMPLE::VOD	2024-01-03 09:56:27.519	70.1026542	5.82059741845	71.0166	0.818	1,000
LSE_SAMPLE::VOD	2024-01-03 11:30:39.417	70.100826	3.80441888802	70.54	0.8165	1,000
LSE_SAMPLE::VOD	2024-01-03 13:35:26.643	70.1238924	2.19545060204	70.46	0.8175	1,000
LSE_SAMPLE::VOD	2024-01-03 14:42:02.384	69.8510987	3.79117371178	70.33	0.814	1,000
LSE_SAMPLE::VOD	2024-01-03 15:48:14.860	69.8859224	2.18726682647	70.11	0.8165	1,000
LSE_SAMPLE::VOD	2024-01-04 08:00:34.083	69.5407809	2.18618336609	70.74	0.8135	1,000
LSE_SAMPLE::VOD	2024-01-04 09:31:44.801	69.0322738	7.52460074146	70.74	0.812	1,000
LSE_SAMPLE::VOD	2024-01-04 12:12:21.302	69.439274	4.86809167691	69.97	0.813	1,000
LSE_SAMPLE::VOD	2024-01-04 14:30:00.962	69.7273201	5.3607976549	70.55	0.816	1,000

Generating Bars#

Intraday bars can be generated by querying with both

time_bucket as the group by

Additional aggregations for FIRST, LAST, FIRST_TIME, HIGH_TIME, LOW_TIME, LAST_TIME

5 Minute Bars for Vodafone retrieval#

select
FIRST(PRICE) as first_price,
MAX(PRICE) as high_price,
MIN(PRICE) as low_price,
LAST(PRICE) as last_price,
FIRST_TIME(PRICE) as first_price_time,
HIGH_TIME(PRICE) as high_price_time,
LOW_TIME(PRICE) as low_price_time,
LAST_TIME(PRICE) as last_price_time,
SUM(SIZE) as sum_size,
COUNT(*) as trade_count
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
group by time_bucket(INTERVAL '5' MINUTE)
LIMIT 10

5 Minute Bars for Vodafone retrieval results#
Symbol	Timestamp	LAST_PRICE	FIRST_PRICE	HIGH_PRICE	LOW_PRICE	SUM_SIZE	TRADE_COUNT	FIRST_PRICE_TIME	HIGH_PRICE_TIME	LOW_PRICE_TIME	LAST_PRICE_TIME
LSE_SAMPLE::VOD	2024-01-03 08:05:00	70.543	70	70.57	0.8175	711,209	323	2024-01-03 08:00:06.232	2024-01-03 08:01:55.576	2024-01-03 08:04:14.972	2024-01-03 08:04:54.729
LSE_SAMPLE::VOD	2024-01-03 08:10:00	70.5	70.43	70.54	0.8185	181,979	108	2024-01-03 08:05:00.144	2024-01-03 08:09:05.774	2024-01-03 08:05:23.956	2024-01-03 08:09:54.846
LSE_SAMPLE::VOD	2024-01-03 08:15:00	70.22	70.5	70.54	0.817	193,178	72	2024-01-03 08:10:02.391	2024-01-03 08:10:03.089	2024-01-03 08:14:36.021	2024-01-03 08:14:55.376
LSE_SAMPLE::VOD	2024-01-03 08:20:00	70.3498	70.21	70.3798	0.816	308,094	88	2024-01-03 08:15:09.168	2024-01-03 08:19:23.245	2024-01-03 08:16:31.755	2024-01-03 08:19:58.251
LSE_SAMPLE::VOD	2024-01-03 08:25:00	70.45	70.33	70.45	0.818	59,217	75	2024-01-03 08:20:02.567	2024-01-03 08:24:57.229	2024-01-03 08:22:50.847	2024-01-03 08:24:57.229
LSE_SAMPLE::VOD	2024-01-03 08:30:00	70.19	70.43	70.48	0.817	170,563	62	2024-01-03 08:25:09.474	2024-01-03 08:25:31.087	2024-01-03 08:29:13.359	2024-01-03 08:29:40.994
LSE_SAMPLE::VOD	2024-01-03 08:35:00	70.49	70.2631	70.49	0.816	111,955	40	2024-01-03 08:30:10.383	2024-01-03 08:34:21.010	2024-01-03 08:30:46.534	2024-01-03 08:34:42.362
LSE_SAMPLE::VOD	2024-01-03 08:40:00	70.51	70.4798	70.54	70.4598	152,794	45	2024-01-03 08:35:01.445	2024-01-03 08:39:25.268	2024-01-03 08:38:29.489	2024-01-03 08:39:31.358
LSE_SAMPLE::VOD	2024-01-03 08:45:00	70.65	70.55	70.72	0.8195	926,490	138	2024-01-03 08:40:03.746	2024-01-03 08:43:02.677	2024-01-03 08:41:00.810	2024-01-03 08:44:26.284
LSE_SAMPLE::VOD	2024-01-03 08:50:00	70.63	70.67	70.7	70.62	130,943	47	2024-01-03 08:45:20.921	2024-01-03 08:46:04.406	2024-01-03 08:47:56.618	2024-01-03 08:49:28.304

VWAP (Volume Weighted Average Price)#

VWAP Bars can be calculated in two approaches:

Product divide by sum. e.g. SUM(PRICE*SIZE)/SUM(SIZE)
VWAP aggregate. e.g. VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE)

5 Minute VWAP Bars for Vodafone retrieval#

select
AVG(PRICE) as avg_price,
SUM(PRICE*SIZE)/SUM(SIZE) as vwap_simple,
VWAP(PRICE_FIELD_NAME=PRICE, SIZE_FIELD_NAME=SIZE) as vwap_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 10

5 Minute VWAP Bars for Vodafone retrieval results#
Symbol	Timestamp	AVG_PRICE	VWAP_PRICE	VWAP_SIMPLE
LSE_SAMPLE::VOD	2024-01-03 08:05:00	69.5115052632	70.2893943425	70.2893943425
LSE_SAMPLE::VOD	2024-01-03 08:10:00	69.1482768519	70.4218094626	70.4218094626
LSE_SAMPLE::VOD	2024-01-03 08:15:00	68.4568388889	70.3616344439	70.3616344439
LSE_SAMPLE::VOD	2024-01-03 08:20:00	69.4476636364	70.2229479996	70.2229479996
LSE_SAMPLE::VOD	2024-01-03 08:25:00	69.4555013333	70.144132749	70.144132749
LSE_SAMPLE::VOD	2024-01-03 08:30:00	68.0768596774	70.1942409614	70.1942409614
LSE_SAMPLE::VOD	2024-01-03 08:35:00	65.174885	70.2436858791	70.2436858791
LSE_SAMPLE::VOD	2024-01-03 08:40:00	70.4912977778	70.4957613519	70.4957613519
LSE_SAMPLE::VOD	2024-01-03 08:45:00	69.6322601449	70.6428644372	70.6428644372
LSE_SAMPLE::VOD	2024-01-03 08:50:00	70.6536	70.6577548613	70.6577548613

TWAP (Time Weghted Average Price)#

TWAP Bars are calculated ussing the TW_AVG aggregate.

5 Minute TWAP Bars for Vodafone retrieval#

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 10

5 Minute TWAP Bars for Vodafone retrieval results#
Symbol	Timestamp	AVG_PRICE	TWAP_PRICE
LSE_SAMPLE::VOD	2024-01-03 08:05:00	69.5115052632	69.3265707313
LSE_SAMPLE::VOD	2024-01-03 08:10:00	69.1482768519	70.318941692
LSE_SAMPLE::VOD	2024-01-03 08:15:00	68.4568388889	69.2694035883
LSE_SAMPLE::VOD	2024-01-03 08:20:00	69.4476636364	66.1942473627
LSE_SAMPLE::VOD	2024-01-03 08:25:00	69.4555013333	67.045867102
LSE_SAMPLE::VOD	2024-01-03 08:30:00	68.0768596774	68.6835245473
LSE_SAMPLE::VOD	2024-01-03 08:35:00	65.174885	64.9311066497
LSE_SAMPLE::VOD	2024-01-03 08:40:00	70.4912977778	70.4922488127
LSE_SAMPLE::VOD	2024-01-03 08:45:00	69.6322601449	68.2290445957
LSE_SAMPLE::VOD	2024-01-03 08:50:00	70.6536	70.660147269

Exponential Moving Averages#

Two exponential moving average aggregates are supported:

EXP_W_AVERAGE - Exponential Weighted Average
EXP_TW_AVERAGE - Exponential Time Weighted Average

Both expect a field to aggregate upon, and a DECAY value. In case of EXP_W_AVERAGE the decay value type is lambda. In the case of EXP_TW_AVERAGE the decay value type is the half life in seconds.

Exponential Moving Averages for Vodafone retrieval#

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'
LIMIT 10

Exponential Moving Averages for Vodafone retrieval results#
Symbol	Timestamp	PRICE	EWA_PRICE	ETWA_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.0050249998	70
LSE_SAMPLE::VOD	2024-01-03 08:00:06.287	70.01	70.0066999439	70.0098181932
LSE_SAMPLE::VOD	2024-01-03 08:00:08.113	70.104	70.0313910402	70.0099946952
LSE_SAMPLE::VOD	2024-01-03 08:00:09.380	70.137	70.052937366	70.0479119756
LSE_SAMPLE::VOD	2024-01-03 08:00:09.943	70.0981	70.0606538959	70.0614768218
LSE_SAMPLE::VOD	2024-01-03 08:00:09.944	70.1097	70.0678724207	70.0614867303
LSE_SAMPLE::VOD	2024-01-03 08:00:10.119	70.146	70.0779835739	70.0636667027
LSE_SAMPLE::VOD	2024-01-03 08:00:10.933	70.132	70.0842282564	70.077987189
LSE_SAMPLE::VOD	2024-01-03 08:00:20.863	70.19	70.0952877129	70.1148441425

Ranking & Row Numbering#

Both the RANK and ROW_NUMBER aggregates can be used to rank results. They both use the over clause to define the field and sort order to rank over.

Ranking by Trade Size descending retrieval#

SELECT PRICE,SIZE, RANK() over (order by SIZE desc) as rank_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

Ranking by Trade Size descending retrieval results#
Symbol	Timestamp	PRICE	SIZE	RANK_SIZE
LSE_SAMPLE::VOD	2024-01-03 08:00:06.232	70	184,613	22
LSE_SAMPLE::VOD	2024-01-03 08:00:06.233	70.01	140	9,310
LSE_SAMPLE::VOD	2024-01-03 08:00:06.287	70.01	500	8,145
LSE_SAMPLE::VOD	2024-01-03 08:00:08.113	70.104	2,800	3,992
LSE_SAMPLE::VOD	2024-01-03 08:00:09.380	70.137	49	9,974
LSE_SAMPLE::VOD	2024-01-03 08:00:09.943	70.0981	66,908	60
LSE_SAMPLE::VOD	2024-01-03 08:00:09.944	70.1097	214	9,106
LSE_SAMPLE::VOD	2024-01-03 08:00:10.119	70.146	840	7,072
LSE_SAMPLE::VOD	2024-01-03 08:00:10.933	70.132	344	8,376
LSE_SAMPLE::VOD	2024-01-03 08:00:20.863	70.19	4,012	3,250

Row Number by Trade Size descending retrieval#

select PRICE,SIZE, ROW_NUMBER() over (order by SIZE desc) as rowid
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

Row Number by Trade Size descending retrieval results#
Symbol	Timestamp	PRICE	SIZE	RANK_SIZE
Symbol	Timestamp	PRICE	SIZE	ROWID
LSE_SAMPLE::VOD	2024-01-03 10:45:39.254	70	18,757,000	1
LSE_SAMPLE::VOD	2024-01-03 10:46:42.278	70	18,757,000	2
LSE_SAMPLE::VOD	2024-01-03 16:35:07.353	69.51	15,788,154	3
LSE_SAMPLE::VOD	2024-01-03 10:38:17.754	70.5	4,400,000	4
LSE_SAMPLE::VOD	2024-01-03 10:49:25.635	70.5	4,400,000	5
LSE_SAMPLE::VOD	2024-01-03 09:52:50.608	70.5	1,320,000	6
LSE_SAMPLE::VOD	2024-01-04 08:00:02.764	70.33	922,507	7
LSE_SAMPLE::VOD	2024-01-03 17:52:28.171	69.502	644,286	8
LSE_SAMPLE::VOD	2024-01-03 17:46:40.536	70.283	425,511	9
LSE_SAMPLE::VOD	2024-01-03 17:11:16.706	70.08	419,767	10

Ranking can also be used in a nested query to filter the returned results to the top n per group.

Top 3 Trades by Trade Size desc, across each Trade Venue#

select * from (
select PRICE,SIZE, TRADE_VENUE, RANK() over (partition by TRADE_VENUE order by SIZE desc) as rank_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')
where rank_size <= 3
LIMIT 10

Top 3 Trades by Trade Size desc, across each Trade Venue results#
Symbol	Timestamp	PRICE	SIZE	TRADE_VENUE	RANK_SIZE
LSE_SAMPLE::VOD	2024-01-03 08:52:45.440	70.6	331,071	XLON	3
LSE_SAMPLE::VOD	2024-01-03 10:38:17.754	70.5	4,400,000	XOFF	3
LSE_SAMPLE::VOD	2024-01-03 10:45:39.254	70	18,757,000	XOFF	1
LSE_SAMPLE::VOD	2024-01-03 10:46:42.278	70	18,757,000	XOFF	1
LSE_SAMPLE::VOD	2024-01-03 10:49:25.635	70.5	4,400,000	XOFF	3
LSE_SAMPLE::VOD	2024-01-03 13:28:29.451	70.39	167,204	SINT	2
LSE_SAMPLE::VOD	2024-01-03 13:55:13.851	70.29	100,000	SINT	3
LSE_SAMPLE::VOD	2024-01-03 16:35:07.353	69.51	15,788,154	XLON	1
LSE_SAMPLE::VOD	2024-01-04 08:00:02.764	70.33	922,507	XLON	2
LSE_SAMPLE::VOD	2024-01-04 14:26:11.598	70.5	174,573	SINT	1