Get max() record and then perform sum() aggregation

[ShivaniSK]

Hi,
We have below sample dataset

epochtime     symbol  volume version
11:01:22.200   ABC      10     v1
11:01:22.200   ABC      10     v2
11:01:23.222   ABC      12     v1
11:01:23.222   ABC      11     v2
11:01:23.222   ABC      15     v3
11:02:25.800   ABC      11     v1 
11:02:25.800   ABC      14     v2

We want to perform below operations on above dataset

STEP 1: Group by epochtime,symbol -- get max version record
STEP 2: Roll up over 1m and sum up the volume

STEP 1 should give below records

11:01:22.200   ABC      10     v2
11:01:23.222   ABC      15     v3
11:02:25.800   ABC      14     v2

STEP 2 should give below records

epochtime     symbol  volume
11:02:00.000   ABC      25
11:03:00.000   ABC      14

Is there a way to achieve this with timebase query?

[alex-karpovich]

@ShivaniSK is it true that versions always increment?

is such situations possible?

epochtime     symbol  volume version
11:01:22.200   ABC      10     v1
11:01:22.200   ABC      10     v2
11:01:23.222   ABC      12     v3  -------> v3 before v1
11:01:23.222   ABC      11     v2
11:01:23.222   ABC      15     v1
11:02:25.800   ABC      11     v1 
11:02:25.800   ABC      14     v2

[ankitsoin]

Version should be incremental as mentioned by @ShivaniSK.

[ShivaniSK]

Hello @alex-karpovich , in the example given by you v3 before v1 situation will not be possible. Versions will increment always for a particular epochtime.

[alex-karpovich]

Hi @ShivaniSK.

I would recommend using custom functions in timebase: https://github.com/epam/TimeBaseTutorial/tree/main/qql-functions

I would suggest to have next custom function

@Function("SUMTS")
public class SumTs implements DoubleStatefulFunction {

    private long timestamp = Long.MIN_VALUE;
    private double sum = 0;
    private double value = 0;

    @Init
    public void init() {
    }

    @Compute
    public void compute(@BuiltInTimestampMs long timestamp, double v) {
        if (this.timestamp != timestamp) {
            sum += value;
        }

        this.value = v;
        this.timestamp = timestamp;
    }

    @Override
    @Reset
    public void reset() {
        sum = 0;
        value = 0;
    }

    @Result
    public double get() {
        return sum + value;
    }
}