Sequel to qsql

David Martin kdb+ Leave a Comment

This post is a continuation of the earlier blog post titled “An Introduction to qsql”, found here, which would be helpful to read before continuing further into this topic. This part will deal with the use of delete and update. There will be a part on aggregations and it will end with a look at both by and fby.

Let’s start by defining the table that was in the previous blog post.

q)tab:([]sym:`GOOG`AMAZ`APPL`GOOG;price:3.1 1.1 2.1 1;qty:1200 2000 2500 2500;side: `b`b`s`s;tradedBy:("George";"George";"George";"Paul"))

q)tab
sym  price qty  side tradedBy
-----------------------------
GOOG 3.1   1200 b    "George"
AMAZ 1.1   2000 b    "George"
APPL 2.1   2500 s    "George"
GOOG 1     2500 s    "Paul"

Delete and Update

Update can be used to create new columns or to change data that is already in the table. Here is update is being used to add a column called src to the table.

q)update src:`BARX`DB`GETGO`SUN from tab
sym  price qty  side tradedBy src
-----------------------------------
GOOG 3.1   1200 b    "George" BARX
AMAZ 1.1   2000 b    "George" DB
APPL 2.1   2500 s    "George" GETGO
GOOG 1     2500 s    "Paul"   SUN

As can be seen in the above example, the update statement can both add the new column and insert the new data all in one statement. Delete is useful for deleting columns or rows. Here is the deletion of a column, this is the same as the DROP COLUMN command in SQL.

q: delete side from tab
SQL equivalent: ALTER TABLE tab DROP COLUMN side
q)delete side from tab
sym  price qty  tradedBy
------------------------
GOOG 3.1   1200 "George"
AMAZ 1.1   2000 "George"
APPL 2.1   2500 "George"
GOOG 1     2500 "Paul"

To delete certain rows you can use a where clause to filter out the data that you want to remove. This statement is very similar to SQL the main difference being that q uses the symbol datatype that starts with a back-tick(`).

q: delete from tab where sym=`AMAZ
SQL equivalent: DELETE FROM tab WHERE SYM='AMAZ'
q)delete from tab where sym=`AMAZ
sym  price qty  side tradedBy
-----------------------------
GOOG 3.1   1200 b    "George"
APPL 2.1   2500 s    "George"
GOOG 1     2500 s    "Paul"

Similarly the where clause can also be used with an update to select the rows that you want to change. In this case the where clause lets us add one to all rows where the sym is GOOG.

q: update price+1 from tab where sym=`GOOG
SQL equivalent: UPDATE tab SET price=price + 1 WHERE sym = 'GOOG'
q)update price+1 from tab where sym=`GOOG
sym  price qty  side tradedBy
-----------------------------
GOOG 4.1   1200 b    "George"
AMAZ 1.1   2000 b    "George"
APPL 2.1   2500 s    "George"
GOOG 2     2500 s    "Paul"

So far the examples that have been shown will create a temporary table. To make the changes to the actual table, a ` must be used before the table name.

q)delete side from tab
sym  price qty  tradedBy
------------------------
GOOG 3.1   1200 "George"
AMAZ 1.1   2000 "George"
APPL 2.1   2500 "George"
GOOG 1     2500 "Paul"

q)tab
sym  price qty  side tradedBy
-----------------------------
GOOG 3.1   1200 b    "George"
AMAZ 1.1   2000 b    "George"
APPL 2.1   2500 s    "George"
GOOG 1     2500 s    "Paul"

When a ` is not used, temporary table is displayed but when the table is called again the changes have not been applied.

q)delete side from `tab
`tab
q)tab
sym  price qty  tradedBy
------------------------
GOOG 3.1   1200 "George"
AMAZ 1.1   2000 "George"
APPL 2.1   2500 "George"
GOOG 1     2500 "Paul"

A colon can be used to assign the table to save in place.

q)tab:delete side from tab

Update and delete will only work on tables in memory, to get around this they can be used with a select statement. This can be used on tables not in memory as the select statement will load the table in first. Since this will be used on a partitioned database that could contain a great deal of records, a while clause should be used. Though the table on disk will still not be changed by this so the changes will still need to be saved down.

q: delete side,tradedBy from select from tab
SQL equivalent: ALTER TABLE tab DROP COLUMN side,tradeBy
q)delete side,tradedBy from select from tab
sym  price qty
---------------
GOOG 3.1   1200
AMAZ 1.1   2000
APPL 2.1   2500
GOOG 1     2500

Aggregations and Groupings

In q there are a number of aggregations that are used to manipulate the data. These range from mathematical functions to sorts and filters.

q: select sum price from tab
SQL equivalent: SELECT SUM(price) FROM tab
q)select sum price from tab
price
-----
7.3

The by clause can be used to preform an aggregation on a table, wherein the result will be grouped by the selected column. In this example the sum of the qty of each sym is obtained. The SQL equivalent to by is GROUP BY.

q: select sum qty by sym from tab
SQL equivalent: SELECT sym,SUM(qty) FROM tab GROUP BY sym
q)select sum qty by sym from tab
sym | qty
----| ----
AMAZ| 2000
APPL| 2500
GOOG| 3700

Lets return to update, this time using it with a by clause. The first of the examples shows the total quantity of each sym and the second shows the running total quantity by sym.

q)update total:sum qty by sym from select from tab
sym  price qty  side tradedBy total
-----------------------------------
GOOG 3.1   1200 b    "George" 3700
AMAZ 1.1   2000 b    "George" 2000
APPL 2.1   2500 s    "George" 2500
GOOG 1     2500 s    "Paul"   3700
q: update runTotal:sums qty by sym from `sym`side xasc select from tab
SQL equivalent: 
ALTER TABLE tab
ADD runTotal integer NULL;

WITH runTotals AS 
(SELECT sym,price,qty,side,tradedBy,
               SUM(qty) OVER(PARTITION BY sym ORDER BY sym,side ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS runTotal
   FROM tab)
 
UPDATE tab SET runTotals = rt.runTotal FROM runTotals rt
WHERE tab.sym = rt.sym
      AND tab.price = rt.price
      AND tab.qty = rt.qty
      AND tab.side = rt.side
     AND tab.tradedBy = rt.tradedBy;
q)update runTotal:sums qty by sym from `sym`side xasc select from tab
sym  price qty  side tradedBy runTotal
--------------------------------------
AMAZ 1.1   2000 b    "George" 2000
APPL 2.1   2500 s    "George" 2500
GOOG 3.1   1200 b    "George" 1200
GOOG 1     2500 s    "Paul"   3700

For sorting a table xasc and xdesc are used for ascending or descending order and when using these functions the table will be sorted by a certain column or even multiple columns. This is the same as using ORDER BY in SQL. As can be seen from the examples, this can be done with multiple columns. In these cases it will sort by each column in turn.

q: `sym xasc tab
SQL equivalent: SELECT * FROM tab ORDER BY sym
q: `sym`price xasc tab
SQL equivalent: SELECT * FROM tab ORDER BY sym, price 
q: `qty xdesc tab
SQL equivalent: SELECT * FROM tab ORDER BY qty DESC

A strength in q is that there is a great variety of key words that you can run as can be seen here on the kx website.

In q there is a useful function called fby which can be used to perform an aggregation on sub lists. The results of these sub lists are returned as a vector that has the same count as the table that you started with. In the first case the sub lists are set by the syms in the table and it shows the maximum price for each particular sym. The second case shows the last sym that each trader worked on.

q)select from tab where price=(max;price) fby sym
sym  price qty  side tradedBy
-----------------------------
GOOG 3.1   1200 b    "George"
AMAZ 1.1   2000 b    "George"
APPL 2.1   2500 s    "George"

q)select from tab where sym=(last;sym) fby tradedBy
sym  price qty  side tradedBy
-----------------------------
APPL 2.1   2500 s    "George"
GOOG 1     2500 s    "Paul"

The s.k file, from kx, that is loaded into q let’s SQL commands be run by putting s)at the start.

q)s)SELECT * FROM tab
sym  price qty  side tradedBy
-----------------------------
GOOG 3.1   1200 b    "George"
AMAZ 1.1   2000 b    "George"
APPL 2.1   2500 s    "George"
GOOG 1     2500 s    "Paul"

Thanks for reading and for more training in the q language you should visit the AquaQ training site.

David MartinSequel to qsql

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax