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 <https://www.onetick.com/cloud-services>`_.



Common Aggregations
####################

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


.. code-block:: sql
   :caption: 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'


.. csv-table:: Common Aggregations for Vodafone retrieval results
   :header: 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``


.. code-block:: sql
   :caption: 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


.. csv-table:: Common Aggregations for Vodafone grouped by Trade Currency retrieval results
   :header: 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``.

.. code-block:: sql
   :caption: 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


.. csv-table:: Common Aggregations for Vodafone grouped by 5 Minute time bucket retrieval results
   :header: 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)``.

.. code-block:: sql
   :caption: 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


.. csv-table:: Common Aggregations for Vodafone grouped by 1000 Records retrieval results
   :header: 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``


.. code-block:: sql
   :caption: 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

.. csv-table:: 5 Minute Bars for Vodafone retrieval results
   :header: 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






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.


.. code-block:: sql
   :caption: 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


.. csv-table:: Ranking by Trade Size descending retrieval results
   :header: 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"



.. code-block:: sql
   :caption: 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


.. csv-table:: Row Number by Trade Size descending retrieval results
   :header: 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.

.. code-block:: sql
   :caption: 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

.. csv-table:: Top 3 Trades by Trade Size desc, across each Trade Venue results
   :header: 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