All geek questions in one place

Apply formula to current and previous lines only (Q / KDB)

I have a formula that I would like to apply row-by-row, so that only the current and previous rows in any given row are included in the calculation. Consider the following data:

data:([]dt:2017.01.05D19:45:00.238248239 2017.01.05D20:46:00.282382392 2017.01.05D21:47:00.232842342 2017.01.05D22:48:00.835838442 2017.01.05D20:49:00.282382392;sym:`AAPL`GOOG`AAPL`BBRY`GOOG;price:101.20 800.20 102.30 2.20 800.50;shares:500 100 500 900 100) data: dt sym price shares 2017.01.05D19:45:00:238248239 AAPL 101.20 500 2017.01.05D20:46:00:282382392 GOOG 800.20 100 2017.01.05D21:47:00:232842342 AAPL 102.30 500 2017.01.05D22:48:00:835838442 BBRY 2.20 900 2017.01.05D20:49:00:282382392 GOOG 800.50 100

The formula select sum price from data where i=(last;i)fby sym will give the result that I need, however it will only give 1 datapoint. I need this calculation made on each row of the data set.

Scanning ("\") applies this behavior, but unfortunately I do not know how to do this when using select statements.

+4
source share
2 answers

Not quite sure what you want, but the following uses the last price for each sym to calculate the rp sum:

 q)update rp:sum each @\[()!();sym;:;price] from data dt sym price shares rp ----------------------------------------------------- 2017.01.05D19:45:00.238248239 AAPL 101.2 500 101.2 2017.01.05D20:46:00.282382392 GOOG 800.2 100 901.4 2017.01.05D21:47:00.232842342 AAPL 102.3 500 902.5 2017.01.05D22:48:00.835838442 BBRY 2.2 900 904.7 2017.01.05D20:49:00.282382392 GOOG 800.5 100 905

Which gives the same answer for the data endpoint, as you indicated above.

+2
source

You can also get the latest price for each index, for example:

 {[x;y] exec sum price from x where i<=y, i=(last;i) fby sym}[data]each til count data 101.2 901.4 902.5 904.7 905
0
source

Source: https://habr.com/ru/post/1275182/

More articles:


All Articles