Search code examples
kdb

How can I calculate a time-weighted average in kdb?

Calculating weighted averages is easy in kdb thanks to the wavg function. How can I calculate the delay between two consecutive row times and use those values as the weightings parameter to wavg?

Something like:

`date`ts xasc select date,ts,mytw:(next ts - ts) wavg mycolumnval from pmd where date=2013.05.22

next gives the following row column in the query, but I can't figure how to subtract the two ts values and use them in wavg.

UPDATE The solution was to use parenthesis: ((next ts) - ts). Note that I can't use deltas[ts]. This would produce different weights:

08:15:19.811    00:00:00.000
08:15:19.811    00:00:00.001
08:15:19.812    00:00:00.014
08:15:19.826    00:00:07.305
08:15:27.131    NULL

This is deltas[ts]:

08:15:19.811    08:15:19.811
08:15:19.811    00:00:00.000
08:15:19.812    00:00:00.001
08:15:19.826    00:00:00.014
08:15:27.131    00:00:07.305
Solution

  • Needed parenthesis around 'next ts':

    `date`ts xasc select date,ts,mytw:((next ts) - ts) wavg mycolumnval from pmd where date=2013.05.22