Category: kdbplus

Q Language – Functions ”; Previous Next Types of Functions Functions can be classified in a number of ways. Here we have classified them based on the number and type of argument they take and the result type. Functions can be, Atomic − Where the arguments are atomic and produce atomic results Aggregate − atom from list Uniform (list from list) − Extended the concept of atom as they apply to lists. The count of the argument list equals the count of the result list. Other − if the function is not from the above category. Binary operations in mathematics are called dyadic functions in q; for example, “+”. Similarly unary operations are called monadic functions; for example, “abs” or “floor”. Frequently Used Functions There are quite a few functions used frequently in q programming. Here, in this section, we will see the usage of some popular functions − abs q) abs -9.9 / Absolute value, Negates -ve number & leaves non -ve number 9.9 all q) all 4 5 0 -4 / Logical AND (numeric min), returns the minimum value 0b Max (&), Min (|), and Not (!) q) /And, Or, and Logical Negation q) 1b & 1b / And (Max) 1b q) 1b|0b / Or (Min) 1b q) not 1b /Logical Negate (Not) 0b asc q)asc 1 3 5 7 -2 0 4 / Order list ascending, sorted list / in ascending order i s returned `s#-2 0 1 3 4 5 7 q)/attr – gives the attributes of data, which describe how it”s sorted. `s denotes fully sorted, `u denotes unique and `p and `g are used to refer to lists with repetition, with `p standing for parted and `g for grouped avg q)avg 3 4 5 6 7 / Return average of a list of numeric values 5f q)/Create on trade table q)trade:([]time:3?(.z.Z-200);sym:3?(`ibm`msft`apple);price:3?99.0;size:3?100) by q)/ by – Groups rows in a table at given sym q)select sum price by sym from trade / find total price for each sym sym | price —— | ——– apple | 140.2165 ibm | 16.11385 cols q)cols trade / Lists columns of a table `time`sym`price`size count q)count (til 9) / Count list, count the elements in a list and / return a single int value 9 port q)p 9999 / assign port number q)/csv – This command allows queries in a browser to be exported to excel by prefixing the query, such as http://localhost:9999/.csv?select from trade where sym =`ibm cut q)/ cut – Allows a table or list to be cut at a certain point q)(1 3 5) cut “abcdefghijkl” / the argument is split at 1st, 3rd and 5th letter. “bc” “de” “fghijkl” q)5 cut “abcdefghijkl” / cut the right arg. Into 5 letters part / until its end. “abcde” “fghij” “kl” Delete q)/delete – Delete rows/columns from a table q)delete price from trade time sym size ————————————— 2009.06.18T06:04:42.919 apple 36 2009.11.14T12:42:34.653 ibm 12 2009.12.27T17:02:11.518 apple 97 Distinct q)/distinct – Returns the distinct element of a list q)distinct 1 2 3 2 3 4 5 2 1 3 / generate unique set of number 1 2 3 4 5 enlist q)/enlist – Creates one-item list. q)enlist 37 ,37 q)type 37 / -ve type value -7h q)type enlist 37 / +ve type value 7h Fill (^) q)/fill – used with nulls. There are three functions for processing null values. The dyadic function named fill replaces null values in the right argument with the atomic left argument. q)100 ^ 3 4 0N 0N -5 3 4 100 100 -5 q)`Hello^`jack`herry“john` `jack`herry`Hello`john`Hello Fills q)/fills – fills in nulls with the previous not null value. q)fills 1 0N 2 0N 0N 2 3 0N -5 0N 1 1 2 2 2 2 3 3 -5 -5 First q)/first – returns the first atom of a list q)first 1 3 34 5 3 1 Flip q)/flip – Monadic primitive that applies to lists and associations. It interchange the top two levels of its argument. q)trade time sym price size —————————————————— 2009.06.18T06:04:42.919 apple 72.05742 36 2009.11.14T12:42:34.653 ibm 16.11385 12 2009.12.27T17:02:11.518 apple 68.15909 97 q)flip trade time | 2009.06.18T06:04:42.919 2009.11.14T12:42:34.653 2009.12.27T17:02:11.518 sym | apple ibm apple price | 72.05742 16.11385 68.15909 size | 36 12 97 iasc q)/iasc – Index ascending, return the indices of the ascended sorted list relative to the input list. q)iasc 5 4 0 3 4 9 2 3 1 4 0 5 Idesc q)/idesc – Index desceding, return the descended sorted list relative to the input list q)idesc 0 1 3 4 3 2 1 0 in q)/in – In a list, dyadic function used to query list (on the right-handside) about their contents. q)(2 4) in 1 2 3 10b insert q)/insert – Insert statement, upload new data into a table. q)insert[`trade;((.z.Z);`samsung;48.35;99)],3 q)trade time sym price size —————————————————— 2009.06.18T06:04:42.919 apple 72.05742 36 2009.11.14T12:42:34.653 ibm 16.11385 12 2009.12.27T17:02:11.518 apple 68.15909 97 2015.04.06T10:03:36.738 samsung 48.35 99 key q)/key – three different functions i.e. generate +ve integer number, gives content of a directory or key of a table/dictionary. q)key 9 0 1 2 3 4 5 6 7 8 q)key `:c: `$RECYCLE.BIN`Config.Msi`Documents and Settings`Drivers`Geojit`hiberfil.sys`I.. lower q)/lower – Convert to lower case and floor q)lower (“JoHn”;`HERRY`SYM) “john” `herry`sym Max and Min (i.e. | and &) q)/Max and Min / a|b and a&b q)9|7 9 q)9&5 5 null q)/null – return 1b if the atom is a null else 0b from the argument list q)null 1 3 3 0N 0001b Peach q)/peach – Parallel each, allows process across slaves q)foo peach list1 / function foo applied across the slaves named in list1 ”list1 q)foo:{x+27} q)list1:(0 1 2 3 4) q)foo peach list1 / function foo applied across the slaves named in list1 27 28 29 30 31 Prev q)/prev – returns the previous element i.e. pushes list forwards q)prev 0 1 3 4 5 7 0N 0 1 3 4 5 Random( ?) q)/random – syntax – n?list, gives random sequences of ints and floats q)9?5 0 0 4 0 3

Q Language – Table Arithmetic ”; Previous Next In this chapter, we will learn how to operate on dictionaries and then tables. Let’s start with dictionaries − q)d:`u`v`x`y`z! 9 18 27 36 45 / Creating a dictionary d q)/ key of this dictionary (d) is given by q)key d `u`v`x`y`z q)/and the value by q)value d 9 18 27 36 45 q)/a specific value q)d`x 27 q)d[`x] 27 q)/values can be manipulated by using the arithmetic operator +-*% as, q)45 + d[`x`y] 72 81 If one needs to amend the dictionary values, then the amend formulation can be − q)@[`d;`z;*;9] `d q)d u | 9 v | 18 x | 27 y | 36 q)/Example, table tab q)tab:([]sym:`;time:0#0nt;price:0n;size:0N) q)n:10;sym:`IBM`SAMSUNG`APPLE`MSFT q)insert[`tab;(n?sym;(“t”$.z.Z);n?100.0;n?100)] 0 1 2 3 4 5 6 7 8 9 q)`time xasc `tab `tab q)/ to get particular column from table tab q)tab[`size] 12 10 1 90 73 90 43 90 84 63 q)tab[`size]+9 21 19 10 99 82 99 52 99 93 72 z | 405 q)/Example table tab q)tab:([]sym:`;time:0#0nt;price:0n;size:0N) q)n:10;sym:`IBM`SAMSUNG`APPLE`MSFT q)insert[`tab;(n?sym;(“t”$.z.Z);n?100.0;n?100)] 0 1 2 3 4 5 6 7 8 9 q)`time xasc `tab `tab q)/ to get particular column from table tab q)tab[`size] 12 10 1 90 73 90 43 90 84 63 q)tab[`size]+9 21 19 10 99 82 99 52 99 93 72 q)/Example table tab q)tab:([]sym:`;time:0#0nt;price:0n;size:0N) q)n:10;sym:`IBM`SAMSUNG`APPLE`MSFT q)insert[`tab;(n?sym;(“t”$.z.Z);n?100.0;n?100)] 0 1 2 3 4 5 6 7 8 9 q)`time xasc `tab `tab q)/ to get particular column from table tab q)tab[`size] 12 10 1 90 73 90 43 90 84 63 q)tab[`size]+9 21 19 10 99 82 99 52 99 93 72 q)/We can also use the @ amend too q)@[tab;`price;-;2] sym time price size ——————————————– APPLE 11:16:39.779 6.388858 12 MSFT 11:16:39.779 17.59907 10 IBM 11:16:39.779 35.5638 1 SAMSUNG 11:16:39.779 59.37452 90 APPLE 11:16:39.779 50.94808 73 SAMSUNG 11:16:39.779 67.16099 90 APPLE 11:16:39.779 20.96615 43 SAMSUNG 11:16:39.779 67.19531 90 IBM 11:16:39.779 45.07883 84 IBM 11:16:39.779 61.46716 63 q)/if the table is keyed q)tab1:`sym xkey tab[0 1 2 3 4] q)tab1 sym | time price size ——— | ———————————- APPLE | 11:16:39.779 8.388858 12 MSFT | 11:16:39.779 19.59907 10 IBM | 11:16:39.779 37.5638 1 SAMSUNG | 11:16:39.779 61.37452 90 APPLE | 11:16:39.779 52.94808 73 q)/To work on specific column, try this q){tab1[x]`size} each sym 1 90 12 10 q)(0!tab1)`size 12 10 1 90 73 q)/once we got unkeyed table, manipulation is easy q)2+ (0!tab1)`size 14 12 3 92 75 Print Page Previous Next Advertisements ”;

Q Language – Inter-Process Communication ”; Previous Next KDB+ allows one process to communicate with another process through interprocess communication. Kdb+ processes can connect to any other kdb+ on the same computer, the same network, or even remotely. We just need to specify the port and then the clients can talk to that port. Any q process can communicate with any other q process as long as it is accessible on the network and is listening for connections. a server process listens for connections and processes any requests a client process initiates the connection and sends commands to be executed Client and server can be on the same machine or on different machines. A process can be both a client and a server. A communication can be, Synchronous (wait for a result to be returned) Asynchronous (no wait and no result returned) Initialize Server A q server is initialized by specifying the port to listen on, q –p 5001 / command line p 5001 / session command Communication Handle A communication handle is a symbol that starts with “:” and has the form − `:[server]:port-number Example `::5001 / server and client on same machine `:jack:5001 / server on machine jack `:192.168.0.156 / server on specific IP address `:www.myfx.com:5001 / server at www.myfx.com To start the connection, we use the function “hopen” which returns an integer connection handle. This handle is used for all subsequent client requests. For example − q)h:hopen `::5001 q)h”til 5″ 0 1 2 3 4 q)hclose h Synchronous and Asynchronous Messages Once we have a handle, we can send a message either synchronously or asynchronously. Synchronous Message − Once a message is sent, it waits on and returns the result. Its format is as follows − handle “message” Asynchronous Message − After sending a message, start processing the next statement immediately without having to wait and return a result. Its format is as follows − neg[handle] “message” Messages that require a response, for example function calls or select statements, will normally use the synchronous form; while messages that need not return an output, for example inserting updates to a table, will be asynchronous. Print Page Previous Next Advertisements ”;

Q Language – Table ”; Previous Next Tables are at the heart of kdb+. A table is a collection of named columns implemented as a dictionary. q tables are column-oriented. Creating Tables Tables are created using the following syntax − q)trade:([]time:();sym:();price:();size:()) q)trade time sym price size ——————- In the above example, we have not specified the type of each column. This will be set by the first insert into the table. Another way, we can specify column type on initialization − q)trade:([]time:`time$();sym:`$();price:`float$();size:`int$()) Or we can also define non-empty tables − q)trade:([]sym:(`a`b);price:(1 2)) q)trade sym price ————- a 1 b 2 If there are no columns within the square brackets as in the examples above, the table is unkeyed. To create a keyed table, we insert the column(s) for the key in the square brackets. q)trade:([sym:`$()]time:`time$();price:`float$();size:`int$()) q)trade sym | time price size —– | ————— One can also define the column types by setting the values to be null lists of various types − q)trade:([]time:0#0Nt;sym:0#`;price:0#0n;size:0#0N) Getting Table Information Let’s create a trade table − trade: ([]sym:`ibm`msft`apple`samsung;mcap:2000 4000 9000 6000;ex:`nasdaq`nasdaq`DAX`Dow) q)cols trade / column names of a table `sym`mcap`ex q)trade.sym / Retrieves the value of column sym `ibm`msft`apple`samsung q)show meta trade / Get the meta data of a table trade. c | t f a —– | —– Sym | s Mcap | j ex | s Primary Keys and Keyed Tables Keyed Table A keyed table is a dictionary that maps each row in a table of unique keys to a corresponding row in a table of values. Let us take an example − val:flip `name`id!(`John`Jenny`Jonathan;9 18 27) / a flip dictionary create table val id:flip (enlist `eid)!enlist 99 198 297 / flip dictionary, having single column eid Now create a simple keyed table containing eid as key, q)valid: id ! val q)valid / table name valid, having key as eid eid | name id ——| ————— 99 | John 9 198 | Jenny 18 297 | Jonathan 27 ForeignKeys A foreign key defines a mapping from the rows of the table in which it is defined to the rows of the table with the corresponding primary key. Foreign keys provide referential integrity. In other words, an attempt to insert a foreign key value that is not in the primary key will fail. Consider the following examples. In the first example, we will define a foreign key explicitly on initialization. In the second example, we will use foreign key chasing which does not assume any prior relationship between the two tables. Example 1 − Define foreign key on initialization q)sector:([sym:`SAMSUNG`HSBC`JPMC`APPLE]ex:`N`CME`DAQ`N;MC:1000 2000 3000 4000) q)tab:([]sym:`sector$`HSBC`APPLE`APPLE`APPLE`HSBC`JPMC;price:6?9f) q)show meta tab c | t f a —— | ———- sym | s sector price | f q)show select from tab where sym.ex=`N sym price —————- APPLE 4.65382 APPLE 4.643817 APPLE 3.659978 Example 2 − no pre-defined relationship between tables sector: ([symb:`IBM`MSFT`HSBC]ex:`N`CME`N;MC:1000 2000 3000) tab:([]sym:`IBM`MSFT`MSFT`HSBC`HSBC;price:5?9f) To use foreign key chasing, we must create a table to key into sector. q)show update mc:(sector([]symb:sym))[`MC] from tab sym price mc ————————– IBM 7.065297 1000 MSFT 4.812387 2000 MSFT 6.400545 2000 HSBC 3.704373 3000 HSBC 4.438651 3000 General notation for a predefined foreign key − select a.b from c where a is the foreign key (sym), b is a field in the primary key table (ind), c is the foreign key table (trade) Manipulating Tables Let’s create one trade table and check the result of different table expression − q)trade:([]sym:5?`ibm`msft`hsbc`samsung;price:5?(303.00*3+1);size:5?(900*5);time:5?(.z.T-365)) q)trade sym price size time —————————————– msft 743.8592 3162 02:32:17.036 msft 641.7307 2917 01:44:56.936 hsbc 838.2311 1492 00:25:23.210 samsung 278.3498 1983 00:29:38.945 ibm 838.6471 4006 07:24:26.842 Let us now take a look at the statements that are used to manipulate tables using q language. Select The syntax to use a Select statement is as follows − select [columns] [by columns] from table [where clause] Let us now take an example to demonstrate how to use Select statement − q)/ select expression example q)select sym,price,size by time from trade where size > 2000 time | sym price size ————- | ———————– 01:44:56.936 | msft 641.7307 2917 02:32:17.036 | msft 743.8592 3162 07:24:26.842 | ibm 838.6471 4006 Insert The syntax to use an Insert statement is as follows − `tablename insert (values) Insert[`tablename; values] Let us now take an example to demonstrate how to use Insert statement − q)/ Insert expression example q)`trade insert (`hsbc`apple;302.0 730.40;3020 3012;09:30:17.00409:15:00.000) 5 6 q)trade sym price size time —————————————— msft 743.8592 3162 02:32:17.036 msft 641.7307 2917 01:44:56.936 hsbc 838.2311 1492 00:25:23.210 samsung 278.3498 1983 00:29:38.945 ibm 838.6471 4006 07:24:26.842 hsbc 302 3020 09:30:17.004 apple 730.4 3012 09:15:00.000 q)/Insert another value q)insert[`trade;(`samsung;302.0; 3333;10:30:00.000] ”] q)insert[`trade;(`samsung;302.0; 3333;10:30:00.000)] ,7 q)trade sym price size time —————————————- msft 743.8592 3162 02:32:17.036 msft 641.7307 2917 01:44:56.936 hsbc 838.2311 1492 00:25:23.210 samsung 278.3498 1983 00:29:38.945 ibm 838.6471 4006 07:24:26.842 hsbc 302 3020 09:30:17.004 apple 730.4 3012 09:15:00.000 samsung 302 3333 10:30:00.000 Delete The syntax to use a Delete statement is as follows − delete columns from table delete from table where clause Let us now take an example to demonstrate how to use Delete statement − q)/Delete expression example q)delete price from trade sym size time ——————————- msft 3162 02:32:17.036 msft 2917 01:44:56.936 hsbc 1492 00:25:23.210 samsung 1983 00:29:38.945 ibm 4006 07:24:26.842 hsbc 3020 09:30:17.004 apple 3012 09:15:00.000 samsung 3333 10:30:00.000 q)delete from trade where price > 3000 sym price size time ——————————————- msft 743.8592 3162 02:32:17.036 msft 641.7307 2917 01:44:56.936 hsbc 838.2311 1492 00:25:23.210 samsung 278.3498 1983 00:29:38.945 ibm 838.6471 4006 07:24:26.842 hsbc 302 3020 09:30:17.004 apple 730.4 3012 09:15:00.000 samsung 302 3333 10:30:00.000 q)delete from trade where price > 500 sym price size time —————————————– samsung 278.3498 1983 00:29:38.945 hsbc 302 3020 09:30:17.004 samsung 302 3333 10:30:00.000 Update The syntax to use an Update statement is as follows − update column: newValue from table where …. Use the following syntax to update the format/datatype of a column using the cast function − update column:newValue from `table where … Let us now take an example to demonstrate how

Q Language – Lists ”; Previous Next Lists are the basic building blocks of q language, so a thorough understanding of lists is very important. A list is simply an ordered collection of atoms (atomic elements) and other lists (group of one or more atoms). Types of List A general list encloses its items within matching parentheses and separates them with semicolons. For example − (9;8;7) or (“a”; “b”; “c”) or (-10.0; 3.1415e; `abcd; “r”) If a list comprises of atoms of same type, it is known as a uniform list. Else, it is known as a general list (mixed type). Count We can obtain the number of items in a list through its count. q)l1:(-10.0;3.1415e;`abcd;”r”) / Assigning variable name to general list q)count l1 / Calculating number of items in the list l1 4 Examples of simple List q)h:(1h;2h;255h) / Simple Integer List q)h 1 2 255h q)f:(123.4567;9876.543;98.7) / Simple Floating Point List q)f 123.4567 9876.543 98.7 q)b:(0b;1b;0b;1b;1b) / Simple Binary Lists q)b 01011b q)symbols:(`Life;`Is;`Beautiful) / Simple Symbols List q)symbols `Life`Is`Beautiful q)chars:(“h”;”e”;”l”;”l”;”o”;” “;”w”;”o”;”r”;”l”;”d”) / Simple char lists and Strings. q)chars “hello world” **Note − A simple list of char is called a string. A list contains atoms or lists. To create a single item list, we use − q)singleton:enlist 42 q)singleton ,42 To distinguish between an atom and the equivalent singleton, examine the sign of their type. q)signum type 42 -1i q)signum type enlist 42 1i Print Page Previous Next Advertisements ”;

Q Language – Temporal Data ”; Previous Next The q language has many different ways of representing and manipulating temporal data such as times and dates. Date A date in kdb+ is internally stored as the integer number of days since our reference date is 01Jan2000. A date after this date is internally stored as a positive number and a date before that is referenced as a negative number. By default, a date is written in the format “YYYY.MM.DD” q)x:2015.01.22 / This is how we write 22nd Jan 2015 q)`int$x / Number of days since 2000.01.01 5500i q)`year$x / Extracting year from the date 2015i q)x.year / Another way of extracting year 2015i q)`mm$x / Extracting month from the date 1i q)x.mm / Another way of extracting month 1i q)`dd$x / Extracting day from the date 22i q)x.dd / Another way of extracting day 22i Arithmetic and logical operations can be performed directly on dates. q)x+1 / Add one day 2015.01.23 q)x-7 / Subtract 7 days 2015.01.15 The 1st of January 2000 fell on a Saturday. Therefore any Saturday throughout the history or in the future when divided by 7, would yield a remainder of 0, Sunday gives 1, Monday yield 2. Day mod 7 Saturday 0 Sunday 1 Monday 2 Tuesday 3 Wednesday 4 Thursday 5 Friday 6 Times A time is internally stored as the integer number of milliseconds since the stroke of midnight. A time is written in the format HH:MM:SS.MSS q)tt1: 03:30:00.000 / tt1 store the time 03:30 AM q)tt1 03:30:00.000 q)`int$tt1 / Number of milliseconds in 3.5 hours 12600000i q)`hh$tt1 / Extract the hour component from time 3i q)tt1.hh 3i q)`mm$tt1 / Extract the minute component from time 30i q)tt1.mm 30i q)`ss$tt1 / Extract the second component from time 0i q)tt1.ss 0i As in case of dates, arithmetic can be performed directly on times. Datetimes A datetime is the combination of a date and a time, separated by ‘T’ as in the ISO standard format. A datetime value stores the fractional day count from midnight Jan 1, 2000. q)dt:2012.12.20T04:54:59:000 / 04:54.59 AM on 20thDec2012 q)type dt -15h q)dt 2012.12.20T04:54:59.000 9 q)`float$dt 4737.205 The underlying fractional day count can be obtained by casting to float. Print Page Previous Next Advertisements ”;

Q Language – Queries ”; Previous Next Queries in q are shorter and simpler and extend the capabilities of sql. The main query expression is the ‘select expression’, which in its simplest form extracts sub-tables but it can also create new columns. The general form of a Select expression is as follows − Select columns by columns from table where conditions **Note − by & where phrases are optional, only the ‘from expression’ is mandatory. In general, the syntax will be − select [a] [by b] from t [where c] update [a] [by b] from t [where c] The syntax of q expressions look quite similar to SQL, but q expressions are simple and powerful. An equivalent sql expression for the above q expression would be as follows − select [b] [a] from t [where c] [group by b order by b] update t set [a] [where c] All the clauses execute on the columns and therefore q can take advantage of order. As Sql queries are not based on order, they cannot take that advantage. q relational queries are generally much smaller in size as compared to their corresponding sql. Ordered and functional queries do things that are difficult in sql. In a historical database, the ordering of the where clause is very important because it affects the performance of the query. The partition variable (date/month/day) always comes first followed by the sorted and indexed column (generally the sym column). For example, select from table where date in d, sym in s is much faster than, select from table where sym in s, date in d Basics Queries Let’s write a query script in notepad (as below), save (as *.q), and then load it. sym:asc`AIG`CITI`CSCO`IBM`MSFT; ex:”NASDAQ” dst:`$”:c:/q/test/data/”; /database destination @[dst;`sym;:;sym]; n:1000000; trade:([]sym:n?`sym;time:10:30:00.0+til n;price:n?3.3e;size:n?9;ex:n?ex); quote:([]sym:n?`sym;time:10:30:00.0+til n;bid:n?3.3e;ask:n?3.3e;bsize:n?9;asize:n?9;ex:n?ex); {@[;`sym;`p#]`sym xasc x}each`trade`quote; d:2014.08.07 2014.08.08 2014.08.09 2014.08.10 2014.08.11; /Date vector can also be changed by the user dt:{[d;t].[dst;(`$string d;t;`);:;value t]}; d dt/::`trade`quote; Note: Once you run this query, two folders .i.e. “test” and “data” will be created under “c:/q/”, and date partition data can be seen inside data folder. Queries with Constraints * Denotes HDB query Select all IBM trades select from trade where sym in `IBM *Select all IBM trades on a certain day thisday: 2014.08.11 select from trade where date=thisday,sym=`IBM Select all IBM trades with a price > 100 select from trade where sym=`IBM, price > 100.0 Select all IBM trades with a price less than or equal to 100 select from trade where sym=`IBM,not price > 100.0 *Select all IBM trades between 10.30 and 10.40, in the morning, on a certain date thisday: 2014.08.11 select from trade where date = thisday, sym = `IBM, time > 10:30:00.000,time < 10:40:00.000 Select all IBM trades in ascending order of price `price xasc select from trade where sym =`IBM *Select all IBM trades in descending order of price in a certain time frame `price xdesc select from trade where date within 2014.08.07 2014.08.11, sym =`IBM Composite sort − sort ascending order by sym and then sort the result in descending order of price `sym xasc `price xdesc select from trade where date = 2014.08.07,size = 5 Select all IBM or MSFT trades select from trade where sym in `IBM`MSFT *Calculate count of all symbols in ascending order within a certain time frame `numsym xasc select numsym: count i by sym from trade where date within 2014.08.07 2014.08.11 *Calculate count of all symbols in descending order within a certain time frame `numsym xdesc select numsym: count i by sym from trade where date within 2014.08.07 2014.08.11 * What is the maximum price of IBM stock within a certain time frame, and when does this first happen? select time,ask from quote where date within 2014.08.07 2014.08.11, sym =`IBM, ask = exec first ask from select max ask from quote where sym =`IBM Select the last price for each sym in hourly buckets select last price by hour:time.hh, sym from trade Queries with Aggregations * Calculate vwap (Volume Weighted Average Price) of all symbols select vwap:size wavg price by sym from trade * Count the number of records (in millions) for a certain month (select trade:1e-6*count i by date.dd from trade where date.month=2014.08m) + select quote:1e-6*count i by date.dd from quote where date.month=2014.08m * HLOC – Daily High, Low, Open and Close for CSCO in a certain month select high:max price,low:min price,open:first price,close:last price by date.dd from trade where date.month=2014.08m,sym =`CSCO * Daily Vwap for CSCO in a certain month select vwap:size wavg price by date.dd from trade where date.month = 2014.08m ,sym = `CSCO * Calculate the hourly mean, variance and standard deviation of the price for AIG select mean:avg price, variance:var price, stdDev:dev price by date, hour:time.hh from trade where sym = `AIG Select the price range in hourly buckets select range:max[price] – min price by date,sym,hour:time.hh from trade * Daily Spread (average bid-ask) for CSCO in a certain month select spread:avg bid-ask by date.dd from quote where date.month = 2014.08m, sym = `CSCO * Daily Traded Values for all syms in a certain month select dtv:sum size by date,sym from trade where date.month = 2014.08m Extract a 5 minute vwap for CSCO select size wavg price by 5 xbar time.minute from trade where sym = `CSCO * Extract 10 minute bars for CSCO select high:max price,low:min price,close:last price by date, 10 xbar time.minute from trade where sym = `CSCO * Find the times when the price exceeds 100 basis points (100e-4) over the last price for CSCO for a certain day select time from trade where date = 2014.08.11,sym = `CSCO,price > 1.01*last price * Full Day Price and Volume for MSFT in 1 Minute Intervals for the last date in the database select last price,last size by time.minute from trade where date = last date, sym = `MSFT Print Page Previous Next Advertisements ”;

Q Language – Built-in Functions ”; Previous Next The q programming language has a set of rich and powerful built-in functions. A built-in function can be of the following types − String function − Takes a string as input and returns a string. Aggregate function − Takes a list as input and returns an atom. Uniform function − Takes a list and returns a list of the same count. Mathematical function − Takes numeric argument and returns a numeric argument. Miscellaneous function − All functions other than above mentioned. String Functions Like − pattern matching q)/like is a dyadic, performs pattern matching, return 1b on success else 0b q)”John” like “J??n” 1b q)”John My Name” like “J*” 1b ltrim − removes leading blanks q)/ ltrim – monadic ltrim takes string argument, removes leading blanks q)ltrim ” Rick ” “Rick ” rtrim − removes trailing blanks q)/rtrim – takes string argument, returns the result of removing trailing blanks q)rtrim ” Rick ” ” Rick” ss − string search q)/ss – string search, perform pattern matching, same as “like” but return the indices of the matches of the pattern in source. q)”Life is beautiful” ss “i” 1 5 13 trim − removes leading and trailing blanks q)/trim – takes string argument, returns the result of removing leading & trailing blanks q)trim ” John ” “John” Mathematical Functions acos − inverse of cos q)/acos – inverse of cos, for input between -1 and 1, return float between 0 and pi q)acos 1 0f q)acos -1 3.141593 q)acos 0 1.570796 cor − gives correlation q)/cor – the dyadic takes two numeric lists of same count, returns a correlation between the items of the two arguments q)27 18 18 9 0 cor 27 36 45 54 63 -0.9707253 cross − Cartesian product q)/cross – takes atoms or lists as arguments and returns their Cartesian product q)9 18 cross `x`y`z 9 `x 9 `y 9 `z 18 `x 18 `y 18 `z var − variance q)/var – monadic, takes a scaler or numeric list and returns a float equal to the mathematical variance of the items q)var 45 0f q)var 9 18 27 36 101.25 wavg q)/wavg – dyadic, takes two numeric lists of the same count and returns the average of the second argument weighted by the first argument. q)1 2 3 4 wavg 200 300 400 500 400f Aggregate Functions all − & operation q)/all – monadic, takes a scaler or list of numeric type and returns the result of & applied across the items. q)all 0b 0b q)all 9 18 27 36 1b q)all 10 20 30 1b Any − | operation q)/any – monadic, takes scaler or list of numeric type and the return the result of | applied across the items q)any 20 30 40 50 1b q)any 20012.02.12 2013.03.11 ”20012.02.12 prd − arithmetic product q)/prd – monadic, takes scaler, list, dictionary or table of numeric type and returns the arithmetic product. q)prd `x`y`z! 10 20 30 6000 q)prd ((1 2; 3 4);(10 20; 30 40)) 10 40 90 160 Sum − arithmetic sum q)/sum – monadic, takes a scaler, list,dictionary or table of numeric type and returns the arithmetic sum. q)sum 2 3 4 5 6 20 q)sum (1 2; 4 5) 5 7 Uniform Functions Deltas − difference from its previous item. q)/deltas -takes a scalar, list, dictionary or table and returns the difference of each item from its predecessor. q)deltas 2 3 5 7 9 2 1 2 2 2 q)deltas `x`y`z!9 18 27 x | 9 y | 9 z | 9 fills − fills nulls value q)/fills – takes scalar, list, dictionary or table of numeric type and returns a c copy of the source in which non-null items are propagated forward to fill nulls q)fills 1 0N 2 0N 4 1 1 2 2 4 q)fills `a`b`c`d! 10 0N 30 0N a | 10 b | 10 c | 30 d | 30 maxs − cumulative maximum q)/maxs – takes scalar, list, dictionary or table and returns the cumulative maximum of the source items. q)maxs 1 2 4 3 9 13 2 1 2 4 4 9 13 13 q)maxs `a`b`c`d!9 18 0 36 a | 9 b | 18 c | 18 d | 36 Miscellaneous Functions Count − return number of element q)/count – returns the number of entities in its argument. q)count 10 30 30 3 q)count (til 9) 9 q)count ([]a:9 18 27;b:1.1 2.2 3.3) 3 Distinct − return distinct entities q)/distinct – monadic, returns the distinct entities in its argument q)distinct 1 2 3 4 2 3 4 5 6 9 1 2 3 4 5 6 9 Except − element not present in second arg. q)/except – takes a simple list (target) as its first argument and returns a list containing the items of target that are not in its second argument q)1 2 3 4 3 1 except 1 2 3 4 3 fill − fill null with first argument q)/fill (^) – takes an atom as its first argument and a list(target) as its second argument and return a list obtained by substituting the first argument for every occurrence of null in target q)42^ 9 18 0N 27 0N 36 9 18 42 27 42 36 q)”;”^”Life is Beautiful” “Life;is;Beautiful” Print Page Previous Next Advertisements ”;

Q Programming Language ”; Previous Next Kdb+ comes with its built-in programming language that is known as q. It incorporates a superset of standard SQL which is extended for time-series analysis and offers many advantages over the standard version. Anyone familiar with SQL can learn q in a matter of days and be able to quickly write her own ad-hoc queries. Starting the “q” Environment To start using kdb+, you need to start the q session. There are three ways to start a q session − Simply type “c:/q/w32/q.exe” on your run terminal. Start the MS-DOS command terminal and type q. Copy the q.exe file onto “C:WindowsSystem32” and on the run terminal, just type “q”. Here we are assuming that you are working on a Windows platform. Data Types The following table provides a list of supported data types − Name Example Char Type Size boolean 1b b 1 1 byte 0xff x 4 1 short 23h h 5 2 int 23i i 6 4 long 23j j 7 8 real 2.3e e 8 4 float 2.3f f 9 8 char “a” c 10 1 varchar `ab s 11 * month 2003.03m m 13 4 date 2015.03.17T18:01:40.134 z 15 8 minute 08:31 u 17 4 second 08:31:53 v 18 4 time 18:03:18.521 t 19 4 enum `u$`b, where u:`a`b * 20 4 Atom and List Formation Atoms are single entities, e.g., a single number, a character or a symbol. In the above table (of different data types), all supported data types are atoms. A list is a sequence of atoms or other types including lists. Passing an atom of any type to the monadic (i.e. single argument function) type function will return a negative value, i.e., –n, whereas passing a simple list of those atoms to the type function will return a positive value n. Example 1 – Atom and List Formation / Note that the comments begin with a slash “ / ” and cause the parser / to ignore everything up to the end of the line. x: `mohan / `mohan is a symbol, assigned to a variable x type x / let’s check the type of x -11h / -ve sign, because it’s single element. y: (`abc;`bca;`cab) / list of three symbols, y is the variable name. type y 11h / +ve sign, as it contain list of atoms (symbol). y1: (`abc`bca`cab) / another way of writing y, please note NO semicolon y2: (`$”symbols may have interior blanks”) / string to symbol conversion y[0] / return `abc y 0 / same as y[0], also returns `abc y 0 2 / returns `abc`cab, same as does y[0 2] z: (`abc; 10 20 30; (`a`b); 9.9 8.8 7.7) / List of different types, z 2 0 / returns (`a`b; `abc), z[2;0] / return `a. first element of z[2] x: “Hello World!” / list of character, a string x 4 0 / returns “oH” i.e. 4th and 0th(first) element Print Page Previous Next Advertisements ”;

Q Language – Verb & Adverbs ”; Previous Next Kdb+ has nouns, verbs, and adverbs. All data objects and functions are nouns. Verbs enhance the readability by reducing the number of square brackets and parentheses in expressions. Adverbs modify dyadic (2 arguments) functions and verbs to produce new, related verbs. The functions produced by adverbs are called derived functions or derived verbs. Each The adverb each, denoted by ( ` ), modifies dyadic functions and verbs to apply to the items of lists instead of the lists themselves. Take a look at the following example − q)1, (2 3 5) / Join 1 2 3 5 q)1, ”( 2 3 4) / Join each 1 2 1 3 1 4 There is a form of Each for monadic functions that uses the keyword “each”. For example, q)reverse ( 1 2 3; “abc”) /Reverse a b c 1 2 3 q)each [reverse] (1 2 3; “abc”) /Reverse-Each 3 2 1 c b a q)”[reverse] ( 1 2 3; “abc”) 3 2 1 c b a Each-Left and Each-Right There are two variants of Each for dyadic functions called Each-Left (:) and Each-Right (/:). The following example explains how to use them. q)x: 9 18 27 36 q)y:10 20 30 40 q)x,y / join 9 18 27 36 10 20 30 40 q)x,”y / each 9 10 18 20 27 30 36 40 q)x: 9 18 27 36 q)y:10 20 30 40 q)x,y / join 9 18 27 36 10 20 30 40 q)x,”y / each, will return a list of pairs 9 10 18 20 27 30 36 40 q)x, :y / each left, returns a list of each element / from x with all of y 9 10 20 30 40 18 10 20 30 40 27 10 20 30 40 36 10 20 30 40 q)x,/:y / each right, returns a list of all the x with / each element of y 9 18 27 36 10 9 18 27 36 20 9 18 27 36 30 9 18 27 36 40 q)1 _x / drop the first element 18 27 36 q)-2_y / drop the last two element 10 20 q) / Combine each left and each right to be a / cross-product (cartesian product) q)x,/::y 9 10 9 20 9 30 9 40 18 10 18 20 18 30 18 40 27 10 27 20 27 30 27 40 36 10 36 20 36 30 36 40 Print Page Previous Next Advertisements ”;