Analytics SQL Functions by Category

eXtremeSQL provides a library of analytics SQL functions that operate on sequences. The examples in the SQL Analytics Examples page demonstrate several of these functions. The examples use data described in the Example Data section to demonstrate the different categories of functions. The links in the Functions by Category table explain and demonstrate the functions using one of two test tables described below.

Example Data

Some of the examples perform queries on a simple database with sequence fields of various types. The table definition for these examples is as follows:

 
    create table SimpleSequence(
        testNumber unsigned(4) primary key,
        bVal1 sequence(unsigned(1)),
        bVal2 sequence(unsigned(1)),
        bVal3 sequence(unsigned(1)),
        iVal1 sequence(int(4)),
        iVal2 sequence(int(4)),
        iVal3 sequence(int(4)),
        fVal1 sequence(float),
        fVal2 sequence(float),
        fVal3 sequence(float),
        dVal1 sequence(double),
        dVal2 sequence(double),
        dVal3 sequence(double),
        ts1 sequence(unsigned(4)),
        ts2 sequence(unsigned(4)),
        ui8 sequence(unsigned(8)) 
    );
     

Other examples use data taken from historical stock quote values for IBM from 2013. The table definition for these examples is as follows:

     
    create table Quote(
        symbol char(21) primary key, 
        day sequence(unsigned(4) asc),
        low sequence(float), 
        high sequence(float), 
        open sequence(float), 
        close sequence(float), 
        volume sequence(unsigned(4))
    );
     

(The script to create this table and load the stock quote data is IBM-q1-2013.sql in the directory samples/xsql/scripts/financial.)

Functions by Category

Please use the links below to view descriptions and examples of these methods by category:

Comparison_Operators	Functions that take an input sequence and produce a result sequence of Boolean values for the comparison operators: ==, !=, >, >=, <, <=
Logical_Operators	Functions that take an input sequence and produce a result sequence of Boolean values from the logical operators: not, and, or, xor
Conversion_Operators	Functions that convert the input sequence elements to the desired type
Unary_Operators	Functions that apply the specified operation to produce a result sequence of the same type: abs, neg
Binary_Functions	Functions that take an input sequence and produce a result sequence of the same type: add, sub, mul, div, mod, max, min
Collapse_Functions	Functions that collapse two sequences to scalar values such as: weighted sum, weighted average, covariance, correlation
Conditional_Functions	Functions that perform operations on one or two input sequences based on a condition such as: if, iif, filter, filter_pos
Manipulator_Functions	Functions that perform various sequence manipulations: repeat reverse, concat, subseq, diff, unique, norm, trend,ceil, floor, stretch, stretch0, asof_join, cross, extrema, map
Iterator_Functions	Functions that extract elements from sequence iterators such as: search
Top_Functions	Functions that return the top n elements (or their positions) of the input sequence: top_max, top_min, top_pos_max, top_pos_min
Grand Aggregate	Functions that produce a scalar aggregate value in the result sequence: count, max, min, sum, prd, avg, approxdc, approxdc_hash
Group_Aggregate	Functions that split the sequence into groups based on the values of a group_by argument: count, max, min, first, last, sum, avg, var_var_samp, dev, dev_samp, approxdc
Grid_Aggregate	Functions that split the sequence into intervals specified by the interval argument which determines the maximum number of elements in the group: max, min, sum, avg, var_var_samp, dev, dev_samp
Window_Aggregate	Like the “Grid” functions illustrated above, however the interval argument indicates the next N elements from the input sequence: max, min, sum, avg, var_var_samp, dev, dev_samp, ema
Hash_Aggregate	Group the resulting sequence by grouping sequence that is not ordered: count, max, min, sum, avg, approxdc
Cumulative_Aggregate	Accumulate the average, sum, product, etc. of all preceding elements in the result sequence: max, min, sum, prd, avg, var_var_samp, dev, dev_samp
Miscellaneous	Provide sort, order_by and histogram functionalities