Search code examples
kdb

How do I do a moving sum forwards in KDB?

I want to sum up the next 5 days returns (target) in my query.

It's working as:

 select date, zR, target: -5 xprev (5 msum zR) from a where sym=`AAPL

What I don't understand is why -5 msum zR doesn't work:

 select date, zR, target: -5 msum zR from a where sym=`AAPL

Is there a more elegant 'KDB' way of writing this?

Solution

  • x#y creates a list of length x made up of elements from y. So in your second example you have a date and zR column the same length as a, but your target column is of length 5. Since tables need to have columns of the same length, you will see an error in this case.

    I don't think there's anything wrong with how you are currently doing it, your query will let you know target on the start of each 5 day perdiod. You could definitely drop the parentheses around 5 msum zR as kdb doesn't care about these (evaluation is strictly right to left).

    You will be left with nulls due to the fact you are moving the elements in your array, so you will need to find a way to fill these (if that's what you want to do).