The Interp function example

The Interp function is an example of a server function that uses the time series API. This function interpolates between values of a regular time series to fill in null elements.

This function does not handle individual null columns. It assumes that all columns are of type FLOAT.

Interp might be used as follows:

select Interp(stock_data) from daily_stocks where stock_name = 'HCLTECH';

This example, along with many others, is supplied in the $ONEDB_HOME/extend/TimeSeries.version directory.

To use the Interp function, create a server function:

create function Interp(TimeSeries) returns TimeSeries
external name '/tmp/Interpolate.bld(ts_interp)'
language c not variant;

You can now use the Interp function in a DB-Access statement. For example, consider the difference in output between the following two queries (the output has been reformatted; the actual output you would see would not be in tabular format):

select stock_data from daily_stocks where stock_name = 'HCLTECH';


2011-01-03 00:00:00    1   1   1   1
2011-01-04 00:00:00    2   2   2   2
NULL
2011-01-06 00:00:00    3   3   3   3


select Interp(stock_data) from daily_stocks where stock_name = 'HCLTECH';

2011-01-03 00:00:00    1   1   1   1
2011-01-04 00:00:00    2   2   2   2
2011-01-05 00:00:00    2.5 2.5 2.5 2.5
2011-01-06 00:00:00    3   3   3   3

/* 
 * SETUP:
 * create function Interp(TimeSeries) returns TimeSeries
 * external name 'Interpolate.so(ts_interp)'
 * language c not variant;
 * 
 *
 * USAGE:
 * select Interp(stock_data) from daily_stocks where stock_id = 901;
 */

#include <stdio.h>
#include <mi.h>
#include <tseries.h>

# define TS_MAX_COLS   100
# define DATATYPE   "smallfloat"

/*
 * This example interpolates between values to fill in null elements.
 * It assumes that all columns are of type smallfloat and that there
are 
 * less than 100 columns in each element.
 */

ts_timeseries *
ts_interp(tsPtr, fParamPtr)
    ts_timeseries   *tsPtr;
    MI_FPARAM      *fParamPtr;
{
    ts_tsdesc      *descPtr;
    ts_tselem      tselem;
    ts_tscan      *scan;
    MI_CONNECTION       *conn;
    ts_typeinfo      *typeinfo;
    int         scancode;
    mi_real           *values[TS_MAX_COLS];
    mi_real           lastValues[TS_MAX_COLS], newValues[TS_MAX_COLS];
    mi_boolean      nulls[TS_MAX_COLS];
    mi_integer      minElem, curElem, elem;
    mi_integer      i;
    mi_boolean      noneYet;
    mi_integer      ncols;
    char      strbuf[100];
    
    /* get a connection for libmi */
    conn = mi_open(NULL,NULL,NULL);

    /* open a descriptor for the timeseries */
    descPtr = ts_open(conn, tsPtr, mi_fp_rettype(fParamPtr, 0), 0);

    if ((ncols = (mi_integer) mi_fp_funcstate(fParamPtr)) == 0) {
   ncols = ts_col_cnt(descPtr);

   if (ncols > TS_MAX_COLS) {
       sprintf(strbuf, "Timeseries elements have too many columns,
100 is 
the max, got %d instead.", ncols);
       mi_db_error_raise(NULL, MI_FATAL, strbuf, 0);
   }

   for (i = 1; i < ncols; i++) {
       typeinfo = ts_colinfo_number(descPtr, i);

       if (strlen(typeinfo->ti_typename) != strlen(DATATYPE) &&
      memcmp(typeinfo->ti_typename, DATATYPE, strlen(DATATYPE)) !=
0){
      sprintf(strbuf, "column was not a %s, got %s instead.", DATATYPE,

typeinfo->ti_typename);
      mi_db_error_raise(NULL, MI_FATAL, strbuf, 0);
       }
   }

   mi_fp_setfuncstate(fParamPtr, (void *) ncols);
    }

    noneYet = MI_TRUE;
    minElem = -1;
    curElem = 0;
    /* begin a scan of the whole timeseries */
    scan = ts_begin_scan(descPtr, 0, NULL, NULL);
    while ((scancode = ts_next(scan, &tselem)) != TS_SCAN_EOS)
{
   switch(scancode) {
       case TS_SCAN_ELEM:
      /* if this element is not null expand its values */
      noneYet = MI_FALSE;
      ts_get_all_cols(descPtr, tselem, (void **) values, nulls, curElem);
      if (minElem == -1) {
          /* save each element */
          for (i = 1; i < ncols; i++) 
         lastValues[i] = *values[i];
      }
      else {
          /* calculate the average */
          for (i = 1; i < ncols; i++) {
         newValues[i] = (*values[i] + lastValues[i])/2.0;
         lastValues[i] = *values[i];
         values[i] = &newValues[i];
          }

          /* update the missing elements */

          tselem = ts_make_elem(descPtr, (void **) values, nulls, &elem);
          for (elem = minElem; elem < curElem; elem++)
         ts_put_nth_elem(descPtr, tselem, elem);

          minElem = -1;
      }
          
      break;
       case TS_SCAN_NULL:
      if (noneYet) 
          break;
      /* remember the first null element */
      if (minElem == -1)
          minElem = curElem;
      break;
   }

   curElem++;
    }
    ts_end_scan(scan);
    ts_close(descPtr);
    return(tsPtr);
}