ToC path:

Sequence Manipulator SQL Functions

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

seq_repeat( n_times ) Return the sequence with each element of the object's sequence repeated n_times

seq_reverse( input ) Return the input sequence in reverse order

seq_concat( input1, input2 ) Return the input1 sequence concatenated with the input2 sequence

seq_subseq( input, from, till ) Return the sub-sequence [from,till] of the input sequence

seq_diff( input ) Return the sequence consisting of the differences between pairs of the input sequence elements

seq_unique( input ) Return only the unique elements in the ordered input sequence by "collapsing duplicates"

seq_norm( input ) Return the normalized values of the input sequence; i.e. divide each sequence element by the square root of the sum of squares of all elements

seq_trend( input ) Return the trend by comparing pairs

seq_ceil( input ) Return the sequence of nearest integer values greater than the input element values of type double

seq_floor( input ) Return the sequence of nearest integer values less than the input element values of type double

seq_stretch() Returns a sequence of the same length as argument 1 containing values from the sequence val

seq_stretch0() Returns a sequence of the same length as argument 1 containing values from the sequence val. Injects missed elements associated with argument 2 so that the corresponding timestamps of ts1 and ts2 are matched

seq_asof_join() Return the sequence of the values sequence corresponding to the timestamp of ts2 closest to the timestamp of ts1. For example, assume ts1 = {4,9}, ts2 = {1,3,6,10}, and values = {0.1,0.3,0.6,1.0}. With these input sequences the result will be {0.3,1.0}

seq_cross( input, cross_direction ) Return the positions in the sequence where it crosses zero. If the integer argument cross_direction is positive then the result sequence starts with the first cross above zero; if negative it starts with cross below zero; if cross_direction is zero then it doesn't matter, i.e. the first cross can be above or below zero.

seq_extrema( input, first_extremum ) Return the positions of extrema (local minimum and maximums) in the input sequence. If the integer argument first_extremum is positive then the result sequence starts with first local maximum; if negative it starts with with local minimum; if first_extremum is zero then it doesn't matter.

Example

Following is an example code snippet demonstrating sequence manipulator methods:
     
    -- seq_repeat, seq_reverse
            
     
    INSERT INTO SimpleSequence(testNumber,iVal1)
        VALUES(4,'{42,17}');
 
    SELECT iVal1,seq_repeat(iVal1,3) AS "repeat" FROM SimpleSequence 
    WHERE testNumber=4;
    SELECT iVal1,seq_reverse(iVal1) AS "reverse" FROM SimpleSequence 
    WHERE testNumber=4;
     
    iVal1{}   
    repeat{}
    -------------------------------------------------------------------------
    {42, 17}        
    {42, 42, 42, 17, 17, 17}
     
    iVal1{} 
      reverse{}
    -------------------------------------------------------------------------
    {42, 17} 
           {17, 42}
     
     
    -- seq_concat, seq_subseq
            
     
    INSERT INTO SimpleSequence(testNumber,iVal1,iVal2)
        VALUES(1,'{42,-13,27,19}','{2,3}');
 
    SELECT iVal1,iVal2,seq_concat(iVal1,iVal2) AS "concat" FROM SimpleSequence 
    WHERE testNumber=1;
    SELECT iVal1, seq_subseq(iVal1,1,2) AS "subseq" FROM SimpleSequence 
    WHERE testNumber = 1;
     
    iVal1{}  
     iVal2{}  
     concat{}
    -------------------------------------------------------------------------
    {42, -13, 27, 19}       
    {2, 3} 
     {42, -13, 27, 19, 2, 3}
     
    iVal1{} 
      subseq{}
    -------------------------------------------------------------------------
    {42, -13, 27, 19}       
    {-13, 27}
     
 
    -- seq_diff, seq_unique
            
 
    INSERT INTO SimpleSequence(testNumber,iVal1)
        VALUES(2,[4,9,2,9,1,4,9,7,5,3]);
    INSERT INTO SimpleSequence(testNumber,iVal1)
        VALUES(3,[4,4,7,7,7,5,4,3]);
     
    SELECT iVal1, seq_diff(iVal1) AS "diff" FROM SimpleSequence 
    WHERE testNumber = 2;
    SELECT iVal1, seq_unique(iVal1) AS "unique" FROM SimpleSequence 
    WHERE testNumber = 3;
     
    iVal1{} 
      diff{}
    -------------------------------------------------------------------------
    {4, 9, 2, 9, 1, 4, 9, 7, 5, 3}  
    {0, 5, -7, 7, -8, 3, 5, -2, -2, -2}
     
    iVal1{} 
      unique{}
    -------------------------------------------------------------------------
    {4, 4, 7, 7, 7, 5, 4, 3} 
           {4, 7, 5, 4, 3}
     
     
    -- seq_norm, seq_trend
            
     
    INSERT INTO SimpleSequence(testNumber,dVal1)
        VALUES(5,[1,2,3]);
    INSERT INTO SimpleSequence(testNumber,iVal1)
        VALUES(6,[1,2,3,3,2,2,4,5,6,5]);
         
    SELECT dVal1, seq_norm(dVal1) AS "norm" FROM SimpleSequence 
    WHERE testNumber = 5;
    SELECT iVal1, seq_trend(iVal1) AS "trend" FROM SimpleSequence 
    WHERE testNumber = 6;
     
    dVal1{} 
      norm{}
    -------------------------------------------------------------------------
    {1, 2, 3} 
          {0.267261241912424, 0.534522483824849, 0.801783725737273}
     
    iVal1{} 
      trend{}
    -------------------------------------------------------------------------
    {1, 2, 3, 3, 2, 2, 4, 5, 6, 5} 
     {0, 1, 1, 1, -1, -1, 1, 1, 1, -1}
     
    -- seq_ceil, seq_floor
            
 
    INSERT INTO SimpleSequence(testNumber,dVal1)
        VALUES(4,[2.9,3.14,4.6]);
 
    SELECT dVal1, seq_ceil(dVal1) AS "ceil" FROM SimpleSequence 
    WHERE testNumber = 4;
    SELECT dVal1, seq_floor(dVal1) AS "floor" FROM SimpleSequence 
    WHERE testNumber = 4;
     
    dVal1{} 
      ceil{}
    -------------------------------------------------------------------------
    {2.9, 3.14, 4.6}        
    {3, 4, 5}
     
    dVal1{} 
      floor{}
    -------------------------------------------------------------------------
    {2.9, 3.14, 4.6}        
    {2, 3, 4}
 
    -- seq_stretch, seq_stretch0
            
     
    INSERT INTO SimpleSequence(testNumber,ts1,ts2,dVal3)
        VALUES(5,'{1,2,3,4,5}','{2,4}','{1.1,2.2}');
    INSERT INTO SimpleSequence(testNumber,ts1,ts2,dVal3)
        VALUES(6,'{1,2,3,5}','{2,3,4}','{1.1,1.2,1.3}');
         
    SELECT ts1,ts2,dVal3,seq_stretch(ts1,ts2,dVal3) AS "stretch" FROM SimpleSequence 
    WHERE testNumber=5;
    SELECT ts1,ts2,dVal3,seq_stretch0(ts1,ts2,dVal3) AS "stretch0" FROM SimpleSequence 
    WHERE testNumber=6;
     
    ts1{}  
       ts2{} 
        dVal3{} 
      stretch{}
    -------------------------------------------------------------------------
    {1, 2, 3, 4, 5} 
    {2, 4} 
     {1.1, 2.2} 
         {1.1, 2.2, 2.2, 1, 1}
     
    ts1{} 
        ts2{} 
        dVal3{} 
      stretch0{}
    -------------------------------------------------------------------------
    {1, 2, 3, 5} 
       {2, 3, 4} 
          {1.1, 1.2, 1.3} 
    {0, 1.1, 1.2, 1.3, 0}
     
    -- _asof_join
            
    INSERT INTO SimpleSequence(testNumber,ts1,ts2,dVal3)
    VALUES(7,'{4,9}','{1,3,6,10}','{0.1,0.3,0.6,1.0}');
     
    SELECT ts1,ts2,dVal3,seq_asof_join(ts1,ts2,dVal3) AS "asof_join" FROM SimpleSequence 
    WHERE testNumber=7;
     
    ts1{} 
        ts2()
         dVal3{}   
    asof_join{}
    -------------------------------------------------------------------------
    {4, 9} 
     {1, 3, 6, 10}   
    {0.1, 0.3, 0.6, 1}      
    {0.3, 1}
     
    -- _cross
            
    INSERT INTO SimpleSequence(testNumber,iVal1)
    VALUES(8,'{-1,0,1,-3,2}');
 
    SELECT iVal1,seq_cross(iVal1,-1) AS "cross-neg", seq_cross(iVal1,1) AS "cross-pos",
        seq_cross(iVal1,0) AS "cross-0" FROM SimpleSequence 
    WHERE testNumber=8;
     
    iVal1{} 
      cross-neg{} 
          cross-pos{} 
          cross-0{}
    -------------------------------------------------------------------------
    {-1, 0, 1, -3, 2} 
          {3}     
    {1, 4} 
    {1, 3, 4}
     

seq_repeat( n_times )	Return the sequence with each element of the object's sequence repeated n_times
seq_reverse( input )	Return the input sequence in reverse order
seq_concat( input1, input2 )	Return the input1 sequence concatenated with the input2 sequence
seq_subseq( input, from, till )	Return the sub-sequence [from,till] of the input sequence
seq_diff( input )	Return the sequence consisting of the differences between pairs of the input sequence elements
seq_unique( input )	Return only the unique elements in the ordered input sequence by "collapsing duplicates"
seq_norm( input )	Return the normalized values of the input sequence; i.e. divide each sequence element by the square root of the sum of squares of all elements
seq_trend( input )	Return the trend by comparing pairs
seq_ceil( input )	Return the sequence of nearest integer values greater than the input element values of type double
seq_floor( input )	Return the sequence of nearest integer values less than the input element values of type double
seq_stretch()	Returns a sequence of the same length as argument 1 containing values from the sequence val
seq_stretch0()	Returns a sequence of the same length as argument 1 containing values from the sequence val. Injects missed elements associated with argument 2 so that the corresponding timestamps of ts1 and ts2 are matched
seq_asof_join()	Return the sequence of the values sequence corresponding to the timestamp of ts2 closest to the timestamp of ts1. For example, assume ts1 = `{4,9}`, ts2 = `{1,3,6,10}`, and values = `{0.1,0.3,0.6,1.0}`. With these input sequences the result will be `{0.3,1.0}`
seq_cross( input, cross_direction )	Return the positions in the sequence where it crosses zero. If the integer argument cross_direction is positive then the result sequence starts with the first cross above zero; if negative it starts with cross below zero; if cross_direction is zero then it doesn't matter, i.e. the first cross can be above or below zero.
seq_extrema( input, first_extremum )	Return the positions of extrema (local minimum and maximums) in the input sequence. If the integer argument first_extremum is positive then the result sequence starts with first local maximum; if negative it starts with with local minimum; if first_extremum is zero then it doesn't matter.