CODEWITHSUNDEEP
kdb+
Jms
Jms

Reputation: 159

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

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:

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?

Upvotes: 1

Views: 2373

Answers (4)

terrylynch
terrylynch

Reputation: 13657

An alternative approach, likely not the fastest but a smaller code footprint

q){y,`c1M`c2M`c3M!a,max y[`c3],a:(avg y;avg y[`c1`c2]^x`c1M`c2M)}\[t@-1;t]
c1 c2 c3 c1M      c2M      c3M
-----------------------------------
3  2  5  3.333333 2.5      5
4  8  8  6.666667 2.916667 8
7  10 13 10       4.791667 13
2  1  2  1.666667 7.395833 7.395833
4  9  11 8        4.53125  11

Upvotes: 1

Rahul
Rahul

Reputation: 3969

q) nt:update c1m:avg(c1;c2;c3) from t 
q) nt:update c2m:-1_({avg x,y}\[avg[c1[0],c2[0]],c1m]) from nt
q) nt:update c3m:(c3[0],1_max(c3;c1m;c2m)) from nt

Upvotes: 1

R. Laidler
R. Laidler

Reputation: 649

KDB+ is much faster at vector calculations than it is at iteration. Because of this, a possible faster way to do this is to iterate only over the c2M column using an adverb, as it is the only one that requires the previous value from the column. I think what you may be looking for is: 

update c3M:c3 from (update c3M:max(c3;c1M;c2M) from update c2M:{avg x,y}\[first 
c2;first[c1],1_prev c1M] from update c1M:avg(c1;c2;c3) from t) where i=0

This runs faster than iterating and performing calculations over the whole table, like so:

q)\ts:1000 ({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]
53 7568

q)\ts:1000 update c3M:c3 from (update c3M:max(c3;c1M;c2M) from update c2M: 
{avg x,y}\[first c2;first[c1],1_prev c1M] from update c1M:avg(c1;c2;c3) from 
t) where i=0
11 6896

Upvotes: 5

jomahony
jomahony

Reputation: 1702

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

Upvotes: 1

Related Questions