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