Analytics Functions Example 13: Top

Top Values

Show the top 10 close and corresponding volume values:

 
    SELECT symbol, 
        seq_top_pos_max(close, 10) as top_pos,
        close@top_pos as "top-10-close", 
        volume@top_pos as "top-10-volume"
    FROM Quote WHERE symbol='IBM';
 
    symbol
    top_pos{}
    top-10-close{}
    top-10-volume{}
    ------------------------------------------------------------------------------
    IBM
    {49, 53, 50, 52, 59,
    51, 57, 54, 55, 48}
    {215.800003, 215.059998, 214.919998, 213.440002, 213.300003,
    213.210007, 212.360001, 212.259995, 212.080002, 212.059998}
    {5505584, 3020648, 7936544, 3198577, 3752999,
    3006125, 2300240, 5830566, 3031457, 3356946}
     

Note that the function seq_top_pos_max() returns the position indexes of the 10 greatest values (top 10 maximum) in sequence close. To analyse this output:

• The sequence top_pos contains the poisiton indexes of the top 10 values within the close sequence.
• This sequence of indexes is projected onto the close and volume sequences to produce the corresponding sequences top-10-close and top-10-volume.

To run this example

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

     
    x 13