admin - admin
Forum Replies Created
-
AuthorPosts
-
adminKeymasternyi = Not Yet Implemented
Currently you can’t flip a keyed table in kdb, either use 0!t or() xkey t
to remove the key and then flip the table.
q)t:([a:1 2 3] b:`p`o`i)
q)t
a| b
-| -
1| p
2| o
3| i
q)flip t
'nyiq)flip 0!t
a| 1 2 3
b| p o i
q)flip () xkey t
a| 1 2 3
b| p o i
adminKeymasterMike,
Unfortunately you cannot delete a namespace: http://www.timestored.com/kdb-guides/q-quirks#deleteNamespace
You can delete all the variables contained in a namespace with
delete from `.ns
To delete from the default namespace:
delete from `.
adminKeymasterHi Dan,
So here is the short qsql you mentioned for selecting the last entries by a grouping, simple “select by”:
q)t:([] sym:`A`A`B`A`B`C`C`C; ex:`p`p`p`p`o`o`o`o; v:til 8; p:asc 8?100.)
q)t
sym ex v p
-----------------
A p 0 12.53237
A p 1 16.94894
B p 2 26.85287
A p 3 37.97537
B o 4 50.51764
C o 5 56.72789
..
q)select by sym from t
sym| ex v p
---| -------------
A | p 3 37.97537
B | o 4 50.51764
C | o 7 88.14778
q)select by sym,ex from t
sym ex| v p
------| ----------
A p | 3 37.97537
B o | 4 50.51764
B p | 2 26.85287
C o | 7 88.14778
If we wrote this the long way and considered the parse tree:
q)parse "select last v, last p by sym from t"
?
`t
()
(,`sym)!,`sym
`v`p!((last;`v);(last;`p))
We could create the functional query dynamically.
We could then replace last with first, to dynamically generate and run our query.
q){ [t; byCols] c:cols[t] except byCols; ?[t; (); ((),byCols)!(),byCols; c!{(last;x)} each c]} [`t; `sym]
sym| ex v p
---| -------------
A | p 3 37.97537
B | o 4 50.51764
C | o 7 88.14778q){ [t; byCols] c:cols[t] except byCols; ?[t; (); ((),byCols)!(),byCols; c!{(first;x)} each c]} [`t; `sym`ex]
sym ex| v p
------| ----------
A p | 0 12.53237
B o | 4 50.51764
B p | 2 26.85287
C o | 5 56.72789
Functional queries sometimes have their uses. Aaron Davies has some notes on them: http://www.q-ist.com/2013/03/my-kdb-user-meeting-presentation.html
adminKeymasterThe length error with xcols is being caused by your table being keyed. There’s no real reason for this other than that xcols currently does not support keyed tables. You can use either ` xkey t or the shortcut 0!t to unkey the table and then use xcols. e.g.
q)t:2!([] date:.z.d+til 3; time:3?.z.t; sym:`p`o`i; price:1 2 3.; size:100*1 2 3)
q)t
date time | sym price size
-----------------------| --------------
2013.04.02 05:19:46.499| p 1 100
2013.04.03 08:06:26.022| o 2 200
2013.04.04 05:40:18.945| i 3 300
q)`size`price xcols t / reproduce same length error
k){(x,f@&~(f:cols y)in x)#y}
'length
#
`size`price`date`time`sym
(+`date`time!(2013.04.02 2013.04.03 2013.04.04;05:19:46.499 08:06:26.022 05:4..
q.q))\q)0!t
date time sym price size
--------------------------------------
2013.04.02 05:19:46.499 p 1 100
2013.04.03 08:06:26.022 o 2 200
2013.04.04 05:40:18.945 i 3 300q)`size`price xcols 0!t
size price date time sym
--------------------------------------
100 1 2013.04.02 05:19:46.499 p
200 2 2013.04.03 08:06:26.022 o
300 3 2013.04.04 05:40:18.945 i
adminKeymasterq code is has no order of precedence, it is interpreted from right to left. e.g.
q)3%2-100+10*neg 11+3
0.07142857
q)3 % (2- (100+ (10*(neg (11+3)))))
0.07142857
adminKeymasterThe assign error occurs when you try to assign a value to a kdb keyword. “type” is a reserved word. Notice your code works when I change that one word.
q)UK_US_ORDERS:([] date:(); time:(); sym:(); size:(); price:(); TYPE:())
q)UK_US_ORDERS
date time sym size price TYPE
-----------------------------
q)
It is possible to workaround this limitation to make your column named type but it would only leave your table extremely hard to query so it is not recommended.
adminKeymasterHi Dan,
An alternative method of saving a table is using 0:
filehandle 0: “separator” 0: tableFor example:
q)t
sym time price size
------------------------------
kjd 04:37:40.682 88.49717 28
iik 07:54:24.777 66.8001 904
oje 03:09:02.404 76.41439 511
ndo 07:34:51.930 48.54351 139
lkc 02:32:20.516 21.3479 775
all 04:41:36.592 6.323408 353
..
q)1_"," 0: t / convert to comma separated and drop first row
"kjd,04:37:40.682,88.49717,28"
"iik,07:54:24.777,66.8001,904"
"oje,03:09:02.404,76.41439,511"
"ndo,07:34:51.930,48.54351,139"
"lkc,02:32:20.516,21.3479,775"
"all,04:41:36.592,6.323408,353"
"eln,02:16:35.865,75.98419,740"
"bdi,10:33:07.085,6.746338,251"
..
q)`:/temp/a.csv 0: 1_"," 0: t / save to file
`:/temp/a.csv
q)read0 `:/temp/a.csv / check what was written
"kjd,04:37:40.682,88.49717,28"
"iik,07:54:24.777,66.8001,904"
"oje,03:09:02.404,76.41439,511"
"ndo,07:34:51.930,48.54351,139"
"lkc,02:32:20.516,21.3479,775"
"all,04:41:36.592,6.323408,353"
"eln,02:16:35.865,75.98419,740"
"bdi,10:33:07.085,6.746338,251"
..
adminKeymasterIPC transfer of data is limited to objects under 2GB in size, when an object is too large to transfer you will see a limit error.
http://www.timestored.com/kdb-guides/kdb-database-limits#limit-error
Try pulling the columns separately then join-each like so:
q)a:h "select sym,time from t"; b:h "select price,size from t"; r:a,'b
q)r
sym time price size
------------------------------
kjd 04:37:40.682 88.49717 28
iik 07:54:24.777 66.8001 904
oje 03:09:02.404 76.41439 511
ndo 07:34:51.930 48.54351 139
lkc 02:32:20.516 21.3479 775
all 04:41:36.592 6.323408 353
..
Be careful as if there is an insert between your calls, the join will fail with a length error. The alternative is to pull rows 0-50000, 50000-10000, 100000+ … in separate calls.
adminKeymaster\P sets the precision of data shown throughout kdb.
The number of decimal places shown for a floating point number can be adjusted as follows:
q)\P
7i
q)1.10123010310312
1.10123
q)\P 15
q)1.10123010310312
1.10123010310312
this controls the output to the web browser and saving csv’s etc.
adminKeymasterkdb supports the full range of execution controls, while, if, do, exceptions.
Consider the following while loop:
q){ i:0; while[i<5; show "hello ",string i; i+:1] }[]
"hello 0"
"hello 1"
"hello 2"
"hello 3"
"hello 4"
If we wanted to break from it early dependent on a condition we could do:
q){ i:0; while[i<5; show "hello ",string i; if[i~3; :`p]; i+:1] }[]
"hello 0"
"hello 1"
"hello 2"
"hello 3"
`p
The single colon at :`p with nothing on the left is the equivalent of return in most other languages.
Some more details on debugging and exceptions can be found at:
http://www.timestored.com/kdb-guides/debugging-kdb
adminKeymasterYou can use \t to record how long a query takes:
q)trade:`date xasc 100000?([] sym:1000?`RBS`o`i; size:1000?1000; price:1000?10.; date:1000?.z.d)
q)select vwap:size wavg price by date from trade where sym =`RBS
date | vwap
----------| ---------
2000.01.10| 6.852109
2000.01.18| 7.724738
2000.01.22| 7.328888
2000.01.30| 7.99496
2000.02.08| 0.8450724
2000.02.10| 0.276614
..
q)\t select vwap:size wavg price by date from trade where sym =`RBS
7
q)\t select vwap:size wavg price by date from trade where sym =`RBS
4
To get a reliable timing use a do loop to repeat the action a number of times:
q)\t do[100; select vwap:size wavg price by date from trade where sym =`RBS]
238
q)\t do[100; select vwap:size wavg price by date from trade where sym =`RBS]
225
\ts can be used to return both the time taken and the space used in bytes. Alternatively you can use the system format:
q)\ts do[100; select vwap:size wavg price by date from trade where sym =`RBS]
219 1772336
q)system "ts do[100; select vwap:size wavg price by date from trade where sym =`RBS]"
245 1772336
adminKeymasterGood news qStudio 1.26 already supports both exporting to excel and row numbers on results.
Simply right click on the result to export.
adminKeymasterqStudio 1.26 Released http://www.timestored.com/b/qstudio-for-kdb-1-26-released/
-
AuthorPosts