In DolphinDB, how to perform aggregated calculations on a table, grouped by ranges specified in another table?

Question

I intend to group the “index” column in the left table according to the range of [beg_idx, end_idx) specified by the right table and sum up the values of “val“ of each group, as shown in the following figure.

I want to achieve this by using SQL statements to directly perform calculations by group, as the function group by does. How to achieve this in DolphinDB?

Answer 1

You can use an index to retrieve data for sum calculation, as shown in the following script:

tb=table(1..20 as index,1..20 as val)
index_tb = table(1..5 as index, [1,4,7,12,15] as beg_idx, [4,9,14,15,19] as end_idx)
def mySum(tb, beg_idx, end_idx){
        val=tb.val
        index=tb.index
        return sum(val[index >= beg_idx[0] and index < end_idx[0]])
}
select mySum(tb,beg_idx,end_idx) from index_tb group by index