# Analytics Functions Library Examples

> The table below lists a number of examples using the Vector-based Analytics Functions library. The first example demonstrates how a C API "pipeline" of function calls can be used to compute closing prices adjusting for splits; then compares this to the SQL `select` statement which performs the same computation. As the SQL API is more compact, it is used for the remaining examples.

> ## SQL syntax extension: the @ operator

> The examples 3 through 14 give the flavor of `select` statements using `sequences` or what are typically referred to as “time series”. Some of these examples use an extension to standard SQL syntax to express the “pipeline” of function calls more neatly. eXtremeSQL adds the `sequence` operator `@` which is a polymorphic operator used to map, project or sort a `sequence` based on the result of another `sequence` computation.

> To illustrate this usage, consider the following three sequence queries:

>         select seq_sort(volume, 'asc') as by_volume,
>             seq_order_by(open, by_volume),
>             seq_order_by(close, by_volume)
>         from Instrument where name='ABB';

>         select seq_top_pos_max(close, 3) as top_price,
>             seq_map(open, top_price),
>             seq_map(close, top_price),
>             seq_map(high, top_price),
>             seq_map(low, top_price)
>         from Instrument;

>         select max_price
>         from ( select seq_search(hdate, 20100101, 20100331) as quarter,
>                 seq_max(seq_project(close, quarter)) as max_price
>             from Instrument where name='ABB');

> These statements can be written using the `@` operator as follows:

>         select seq_sort(volume, 'asc') as by_volume, open@by_volume, close@by_volume
>         from Instrument where name='ABB';

>         select seq_top_pos_max(close, 3) as top_price, open@top_price, close@top_price, high@top_price, low@top_price
>         from Instrument;

>         select max_price
>         from ( select seq_search(hdate, 20100101, 20100331) as quarter,
>                 seq_max(close@quarter) as max_price
>             from Instrument where name='ABB');

> Note that if the right part of the `@` operation refers to the result of `seq_sort()`, the `@` invokes `seq_order_by()`; if the right part refers to the result of `seq_search()`, the `@` invokes `seq_project()`; and if the right part refers to the result of some operator that returns the positions of elements in the sequence, eg. `seq_top_pos_max()` the `@` invokes ``seq_map()``.

> ## Keyword "flattened"

> The key word `flattened` is an eXtremeSQL extension that transforms `sequence` fields into a table so that the result set appears as individual rows for each set of corresponding sequence elements with the class’s scalar fields’ values repeated on each row. (See Example 3 below.)

> ## Example data and scripts

> For the examples 3 through 14 we perform queries on a simple database class:

>         class Quote {
>             char<MAX_SYMBOL_LEN> symbol;
>             sequence<date asc> day;
>             sequence<float> low;
>             sequence<float> high;
>             sequence<float> open;
>             sequence<float> close;
>             sequence<uint4> volume;
>             unique tree<symbol> by_sym;
>         };

> The data in the following examples is taken from historical values from 2013 for IBM. This sample data and a group of script and shell command files that perform the select statements in the following examples are included in the eXtremeDB for HPC samples directory `samples/xsql/scripts/financial`. To reproduce any of the examples run the `x.bat` (or `x.sh` on Linux systems) specifying the example number. For instance, to run example 7, with `scripts/financial` as the current working directory, simply type:

>         x 7

> The command file invokes xSQL with the appropriate SQL scripts. (Please note that the output from xSQL will be formatted differently in most cases from that printed below as we have reformatted the text here and highlighted specific elements to aid in describing the operations being performed.)

> ## Example links

> Please use the links below to view details of each example:

> Example 1 | Historical Closing Prices Adjusting for Splits
> Example 2 | Volume Weighted Average Buy and Sell Prices
> Example 3 | Sequence Interval
> Example 4 | Arithmetic on Sequences
> Example 5 | Filter Sequence Elements
> Example 6 | Correlation
> Example 7 | Grand Aggregate Maximum
> Example 8 | Window Aggregate
> Example 9 | Grid Aggregate
> Example 10 | Group Aggregate Sum
> Example 11 | Hash Aggregate Average
> Example 12 | Sort Sequence Elements
> Example 13 | Top Values
> Example 14 | Value Weighted Average