seq_skip_nan

The seq_skip_nan() sequence function may be used to omit NaN (Not-a-Number) values in SQL sequence operations. The following steps illustrate use of this function:

1. Create a sample table and populate it with data, e.g.:

 
    CREATE TABLE QuoteFlat(symbol string, bid float, ask float);
    INSERT INTO QuoteFlat VALUES('AAA', 1.01, 1.02);
    INSERT INTO QuoteFlat VALUES('AAA', 'nan', 'nan');
    INSERT INTO QuoteFlat VALUES('AAA', 1.03, 'nan');
    INSERT INTO QuoteFlat VALUES('AAA', 'nan', 1.05);
    INSERT INTO QuoteFlat VALUES('AAA', 1.05, 1.06);
    CREATE TABLE Quote(symbol string PRIMARY KEY, bid sequence(float), ask sequence(float));
    INSERT OR UPDATE INTO Quote(symbol, bid, ask) SELECT symbol, bid, ask FROM QuoteFlat;
     

2. Now it is possible to select only valid values, e.g.:

 
    SELECT symbol, seq_skip_nan(bid) FROM Quote;
     

3. Presence of NaN values in sequences causes arithmetic operations and aggregate functions to produce NaN as a result:

 
    SELECT seq_avg(bid) FROM Quote WHERE symbol='AAA';
    #1
    ------------------------------------------------------------------------------
    nan
     

However, the seq_skip_nan() API can be used to ignore NaN values:

 
    SELECT seq_avg(seq_skip_nan(bid)) FROM Quote WHERE symbol='AAA';
    #1
    ------------------------------------------------------------------------------
    1.02999997138977
     

4. Since arithmetic operations with NaN values yield NaN, it is possible to perform operations on all elements of sequences and drop invalid results afterwards. Consider a sample where an average of bid and ask is computed:

 
    SELECT (bid+ask)/2 FROM Quote WHERE symbol='AAA';
    #1
    ------------------------------------------------------------------------------
    {1.01499998569489, nan, nan, nan, 1.05499994754791}
     

The seq_skip_nan() function may be used to omit the invalid results:

 
    SELECT seq_skip_nan((bid+ask)/2) FROM Quote WHERE symbol='AAA';
    #1
    ------------------------------------------------------------------------------
    {1.01499998569489, 1.05499994754791}
     

A note on NaN values in Microsoft Windows

In the Microsoft Windows environment NULLs are denoted as 0.0/0.0 instead of the"nan" string. This is the value passed into the SQL statement

Note that if 0.0/0.0 is passed as a parameter in a C/C++ application, Visual Studio 2013 does not have the "nan" or "nanf" symbols defined. Therefore the following workaround can be used in the application code:

 
    #ifdef _WIN32
        #if _MSC_VER < 1900 /* Visual Studio 2013 and earlier */
            #include <float.h>
            #define isnan   _isnan
            const unsigned long nan[2]={0xffffffff, 0x7fffffff};
            #define nan(s)      (*( double* )nan)
            #define nanf(s)     (*( float* )nan)
        #endif
    #endif
     

Staring from the Visual Studio 2015 the "nan" and "nanf" symbols are present.