# 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}