CODEWITHSUNDEEP
kdb+
tenticon
tenticon

Reputation: 2913

kdb q - count subtable between 2 dates

I have a table 

t:`date xasc ([]date:100?2018.01.01+til 100;price:100?til 100;acc:100?`a`b)

and would like to have a new column in t which contains the counts of entries in t where date is in the daterange of the previous 14 days and the account is the same as in acc. For example, if there is a row 

date       price    acc prevdate    prevdate1W   countprev14
2018.01.10  37       a  2018.01.09  2018.01.03   ?

then countprev14 should contain the number of observations between 2018.01.03 and 2018.01.09 where acc=a

The way I am currently doing it can probably be improved: 

f:{[dates;ac;t]count select from t where date>=(dates 0),date<=(dates 1),acc=ac}[;;t]
(f')[(exec date-7 from t),'(exec date-1 from t);exec acc from t]

Thanks for the help

Upvotes: 1

Views: 967

Answers (2)

jomahony
jomahony

Reputation: 1692

Another method is using a window join (wj1): https://code.kx.com/q/ref/joins/#wj-wj1-window-join

dates:exec date from t; 
d:(dates-7;dates-1);
wj1[d;`acc`date;t;(`acc`date xasc t;(count;`i))]

Upvotes: 5

Ryan McCarron
Ryan McCarron

Reputation: 909

I think you're looking for something like this:

update count14:{c-0^(c:sums 1&x)y bin y-14}[i;date] by acc from t

this uses sums to get the running counts, bin to find the running count from 14 days prior, and then indexes back into the list of running counts to get the counts from that date.

The difference between the counts then and now are those from the latest 14 days.

Note the lambda here allows us to store the result from the sums easily and avoid unnecessary recomputation.

Upvotes: 5

Related Questions