CODEWITHSUNDEEP
kdb+
ks-man
ks-man

Reputation: 167

Create table in KDB with columns from results

I'm trying to create a table in KDB where the columns are the results of a query. For example , I have loaded in stock data and search for a given time window what prices the stock traded at. I created a function getTrades[Sybmol; Date; StartTime; StopTime]

This will search through my database and return the prices that traded between the start and stop time. So my results for Apple for a 30 second window might be: 527.10, 527.45, 527.60, 526.90 etc.

What I want to do is now create a table using xbar where I have rows of every second and columns of all the prices that trade in StartTime and StopTime. I will then place an X in the column if the price traded in that 1 second. I think I can handle most of this but the main thing I'm struggling with is converting the results I got above into the name of the table. I'm also struggling with how to make it flexible so my table will have 5 columns in one scenario (5 prices traded) but 10 in another so essentially it varies depending on how many price levels traded in the window I'm searching.

Thanks.

Upvotes: 2

Views: 3498

Answers (4)

nyi
nyi

Reputation: 3229

A more simplified version is :

q)t:`time xasc([] s:100#`s ; time:100?(.z.T+500*til 100);price:100?(100 101 102 103 104 105 106))

q)t1:update `$string price,isPrice:1b from t

q)p:(distinct asc t1`price)

q)exec p#(10b!"X ")@(price!isPrice) by time:1 xbar time.second from t1
time    | 100 101 102 103 104 105 106
--------| ---------------------------
20:39:00| X   X   X
20:39:01| X   X   X       X
20:39:02|     X
20:39:04| X
20:39:05|             X   X   X   X

Upvotes: 0

nightTrevors
nightTrevors

Reputation: 649

The best and cleanest way to do programmatic selects is with the functional form of select.

from q for mortals,

    ?[t;c;b;a]

where t is a table, a is a dictionary of aggregates, b is a dictionary of groupbys and c is a list of constraints.

In other words, select a by b from t where c.

This will allow you to dynamically create a, which can be of arbitrary size.

You can find more information here: http://code.kx.com/q4m3/9_Queries_q-sql/#912-functional-forms

Upvotes: 4

WooiKent Lee
WooiKent Lee

Reputation: 1311

Pivot Table

I think that pivot table will be suitable in this case. Using jgleeson example:

time         price
------------------
11:27:01.600 106
11:27:02.600 102
11:27:02.600 102
11:27:03.100 100
11:27:03.100 102
11:27:03.100 102
11:27:03.100 104
11:27:03.600 104
11:27:03.600 102
11:27:04.100 106
11:27:05.100 105
11:27:06.600 106
11:27:07.100 101
11:27:07.100 104
11:27:07.600 105
11:27:07.600 105
11:27:07.600 101

not null exec (exec `$string asc distinct price from s)#(`$string price)!price by time:1 xbar time.second from s:select from t where time within 11:27:00 11:27:30

and returns:

time    | 100 101 102 103 104 105 106
--------| ---------------------------
11:27:01| 0   0   0   0   0   0   1
11:27:02| 0   0   1   0   0   0   0
11:27:03| 1   0   1   0   1   0   0
11:27:04| 0   0   0   0   0   0   1
11:27:05| 0   0   0   0   0   1   0
11:27:06| 0   0   0   0   0   0   1
11:27:07| 0   1   0   0   1   1   0

It can support any numbers of unique prices.

Upvotes: 2

jgleeson
jgleeson

Reputation: 955

This looks a bit convoluted... but I think this might be what you're after.

Sample table t with time and price columns:

t:`time xasc([]time:100?(.z.T+500*til 100);price:100?(100 101 102 103 104 105 106))

This table should replicate what you get from the first step of your function call - "select time,price from trade where date=x, symbol=y, starttime=t1, endtime=t2".

To return the table in the format specified:

q) flip (`time,`$string[c])!flip {x,'y}[key a;]value a:{x in y}[c:asc distinct tt`price] each group (!) . reverse value flip tt:update time:time.second from t

time     100 101 102 103 104 105 106
------------------------------------
20:34:29 0   1   0   0   0   1   0
20:34:30 0   0   0   0   0   0   1
20:34:31 0   0   1   0   0   0   0
20:34:32 0   0   1   0   1   0   0
...

This has bools instead of X as bools are probably easier to work with.

Also please excuse the one-liner... If I get a chance I'll break it up and try to make it more readable.

Upvotes: 1

Related Questions