kqlazure-data-explorer

Kusto query to accumulate values in array


I have a table in which values are stored as follows

source table

I would like to add a column that contains an array with the values accumulated. For that purpose, I used the following function

.create-or-alter function calculateCumulativeSum(input: dynamic) {
 range i from 0 to array_length(input) - 1 step 1
| extend value = toint(input[i])
| extend cumulativeSum = row_cumsum(value)
| summarize make_list(cumulativeSum) }

This function works correctly

calculateCumulativeSum(dynamic([1,2,3,4])) gives the correct result [1,3,6,10]

But when I try to apply it to the table

inputTable | extend l = calculateCumulativeSum(fw) 

I get an error "Tabular expression is not expected in the current context"

I know it's possible to do it through mv-expand, row_cumsum, and summarize again - but is there a way to do it through a function ? I've tried to force the function to return a scalar (toscalar()), but then I get another error: can't use 'fw' as it is defined outside its row-context scope. (see: https://aka.ms/toscalar_limitations)


Solution

  • It seems possible using series_iir function

    let rith_table = datatable(id:int, rith_col: dynamic)
    [
        1, dynamic([1,2,3,4]),
        2, dynamic([4,8,12]),
        3, dynamic([7,14,21,28])
    ];
    rith_table
    | extend cum = series_iir(rith_col,dynamic([1]), dynamic([1,-1]))
    

    This gives the expected result : enter image description here