Apply formula to current and previous rows only (Q

问题:

I have a formula that I'd like to apply row-by-row, such that only the current and previous rows on any given row are included in calculation. Consider this 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 would yield the result I need, however it would only yield 1 datapoint. I need that calculation done at every row of the dataset.

Scan ("\") applies this behavior, but unfortunately I don't know how to do that when using select statements.

回答1:

Not entirely sure what you want but the following uses the latest price for each sym to calculate the sum rp:

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 final data point as you have given above.

回答2:

You can also get the last price at each index, like so:

{[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