(q/kdb+) Update rows using results from previous rows

  • A+
Category:Languages

Using the table

t:([]c1: 3 4 7 2 4.0;c2: 2 8 10 1 9.0;c3:5 8 13 2 11.0)  c1  c2  c3 3   2   5 4   8   8 7   10  13 2   1   2 4   9   11 

I need to update 3 new columns (c1M,c2M,c3M) where:

  • for the first row:

    • c1M:avg(c1,c2,c3)
    • c2M:avg(c1,c2)
    • c3M:c3
  • other rows:

    • c1M:avg(c1,c2,c3)
    • c2M:avg(prev c1M,prev c2M)
    • c3M:max(c3,c1M,c2M)

I can do first row using

t:update c1M:avg(c1,c2,c3),c2M:avg(c1,c2),c3M:c3 from t where i=0  c1  c2  c3  c1M c2M c3M 3   2   5   3.3 2.5 5 4   8   8   0n  0n  0N 7   10  13  0n  0n  0N 2   1   2   0n  0n  0N 4   9   11  0n  0n  0N 

Not sure how to proceed tough. I tried something like:

update c1M:avg(c1;c2;c3),c2M:avg(prev c1M;prev c2M),c3M:c3|c1M|c2M from t where i>0 

But no luck.

The result for this example should be:

c1  c2  c3  c1M c2M c3M 3   2   5   3.3 2.5 5.0 4   8   8   6.7 2.9 8.0 7   10  13  10  4.8 13.0 2   1   2   1.7 7.4 7.4 4   9   11  8.0 4.5 11.0 

Can someone help me?

 


I think what you're looking for here is over: http://code.kx.com/q/ref/adverbs/#over

It will iterate through each row of the table

q)({update c3M:max(c3;c1M;c2M),c2M:c2M^avg(prev c1M;prev c2M),c1M:c1M^avg(c1;c2;c3) from x}/)[update c1M:avg(c1,c2,c3),c2M:avg(c1,c2),c3M:c3 from t where i=0]  c1 c2 c3 c1M c2M c3M -------------------- 3  2  5  3.3 2.5 5   4  8  8  6.7 2.9 8   7  10 13 10  4.8 13  2  1  2  1.7 7.4 7.4 4  9  11 8   4.5 11      

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: