Miscellaneous Analytics SQL Functions

The following function take a variety of input sequence arguments and produce result sequences or scalar types as described in the table below:

seq_histogram( ... )	Build a histogram for the input sequence. Minimal (inclusive) and maximal (exclusive) values for input sequence should be specified as well as the number of intervals (histogram columns). (The number of intervals should not be greater than tile size)
seq_sort( ... )	Sort the sequence elements previously extracted using get() and construct a permutation array (of positions) that can be used to access the elements of other sequences (also extracted to arrays)
seq_order_by( ... )	Sort the sequence elements using the permutation produced by sort(). If the data parameter is "None", this function extracts all sequence elements to an array and then creates an iterator though this array according to the given permutation. The created array should be removed by the reset() method. If the data parameter is not NULL, then the input iterator is ignored and data is take from this array.

Example

As explained in Analytics Functions Library Examples page, the data in the following example is taken from historical values from 2013 for IBM. The data is loaded into xSQL by running various scripts in directory samples/xsql/scripts/financial. To reproduce the example below run the g.bat (or g.sh on Linux systems) specifying the example number 11. For instance, with scripts/financial as the current working directory, simply type:

     
    g 11
     

Following is an example code snippet demonstrating some of these functions for the first quarter of 2013.

 
    -- sort, order_by
            
    SELECT symbol, seq_search(day, 20130101, 20130331) as Q1_13,
        seq_sort(volume@Q1_13, 'asc') as by_volume,
        seq_order_by(close, by_volume) as "close order_by_vloume"
    FROM Quote WHERE symbol='SYM0';
     
    symbol 
     Q1_13{} 
      by_volume {}
          close order_by_vloume{}
    --------------------------------------------------------------------
    SYM0    
    {20130101, 20130104, 20130106, 20130110, 20130123, ...<2 element(s)>..., 
      20130213, 20130214, 20130216, 20130311, 20130326}      
    {45, 48, 69, 335, 367, ...<2 element(s)>..., 420, 450, 732, 805, 808}
      {74.7300033569336, 50.3699989318848, 57.7299995422363, 62.8300018310547, 80.9199981689453, ...<2 element(s)>...,  
      37.2799987792969, 29.9400005340576, 67.870002746582, 80.3199996948242, 45.7900009155273}