SQL Sequence Extrema Function

The seq_extrema() function returns a sequence containing the position of the extrema (the “local” maxima and minima) for the input sequence. The second argument specifies which extremum: a negative value indicates that the local minimum is desired; a positive value indicates that the local maximum is desired; a value of 0 indicates that both local minimum and maximum are desired.

Following is an example script demonstrating the seq_extrema() function:

 
    INSERT INTO SimpleSequence(testNumber,iVal1)
    VALUES(9,'{-3,-2,0,3,2}');
     
    SELECT iVal1,seq_extrema(iVal1,-1) AS "extrema-neg",
    seq_extrema(iVal1,1) AS "extrema-pos",
    seq_extrema(iVal1,0) AS "extrema-0"
    FROM SimpleSequence WHERE testNumber=9;
     
    iVal1{}
    extrema-neg{}
    extrema-pos{}
    extrema-0{}
    --------------------------------------------------
    {-3, -2, 0, 3, 2}
    {}
    {3}
    {3}
     

Note that extrema are defined as points where the derivative of a function is 0. But since the derivative calculation requires adjacent points on either side, the first and last elements of a sequence cannot be extrema.

So, in the example above the result sequences are obtained as follows:

• extrema-neg{}: Though the first element of iVal1 (-3) is actually the “absolute” minimum of the sequence, because it has no immediately adjacent element to its left it does not qualify as a “local” minima (see note above). So the result sequence is empty.
• extrema-pos{}: The elements 2, 3 and 4 of iVal1 (0,3,2) show a “local” maxima at position 3. So the position 3 is copied to the result sequence.
• extrema-0{}: There are no other extrema in this sequence besides the maxima at position 3. So the single position 3 is copied to the result sequence.

Sample script

A sample script to demonstrate this select statement using xSQL can be run from the samples/xsql/scripts/financial directory with the following command:

     
    f 5