I used to hate functional queries. Part of me still does. They are hard to write, look like gibberish and not as simple to understand as qsql queries. I can’t believe I am saying this but as you get more familiar with q, you start seeing their advantages and point of their existence. q is a functional language and the best way to unlock its full potential is via functions.
For me, functional queries are most useful when I am trying to run them on a remote process/db. Instead of wrapping a qsql query in string and then sending it via IPC, I prefer to use functional queries. This is especially important when you want to pass arguments to a query…yes, basically treating it as a function. That is when having a functional query helps because it allows you to easily pass locally defined parameters to a remote process. Anyways, that’s a discussion for another time. Today, we will look at how to actually write them.
For our examples, we will look at this sample trade table:
t:([];time:5?.z.t;sym:5?`AAPL`MSFT`IBM;price:5?100;size:5?200)
A functional query consists of 4 values:t – tablec – constraintsb – group bya – aggregate
If we were to dissect a qsql query:
select minP:min price by sym from t where time>04:00
t - t c - where time>04:00 b - sym a - minP:min price
So far so good? Let’s look at different types of functional queries.
Select
Syntax for a select query is:
?[t;c;b;a]
To run the query mentioned previously in its functional form, you would write
q)?[t;enlist (>;`time;04:00);(enlist `sym)!enlist `sym;(enlist `minP)!enlist (min;`price)] sym | minP ----| ---- AAPL| 88 IBM | 30 MSFT| 82
Oh boy…that looks scary!
Let’s dissect it:
t - t (this is the table you are referring to) c - enlist (>;`time;04:00)
Since we have only one constraint here, we need to enlist (hey! that’s close to the name of this blog) it. If there were more than one constraint, we wouldn’t need enlist. For example:
q)?[t;((=;`sym;enlist`MSFT);(>;`time;04:00));(enlist `sym)!enlist `sym;(enlist `minP)!enlist (min;`price)] sym | minP ----| ---- MSFT| 82
As you can see, we don’t need that specific enlist but had to add another one to compare sym. You get the point. It will take some time to understand fully where you need enlist.
b - (enlist `sym)!enlist `sym
The b and a arguments both need a dictionary. The key part of the dictionary is the final name you want to assign and value part is the actual column you want to use. In this case, we want to just use `sym as our by clause so we define a dictionary…and since we are just dealing with one by clause, we need to enlist both key and value.
a - (enlist `minP)!enlist (min;`price)
In this case, I want to rename the final column to minP and that’s why I specified that as the key of the dictionary and then the actual formula in the value (min price). Again, since you are talking about just one column, you need to enlist them.
Now, we will get max size as well…notice how enlist is not needed anymore for a clause:
q)?[t;((=;`sym;enlist`MSFT);(>;`time;04:00));(enlist `sym)!enlist `sym;(`minP`maxS)!((min;`price);(max;`size))] sym | minP maxS ----| --------- MSFT| 82 130
If you don’t want to have a by clause, you simple say 0b.
q)?[t;((=;`sym;enlist`MSFT);(>;`time;04:00));0b;(enlist `minP)!enlist (min;`price)] minP ---- 82
Exec
Exec statements are the same as select statements with minor differences as shown in examples below.
q)?[t;enlist (>;`time;04:00);();()] time | 14:35:31.860 sym | `IBM price| 30 size | 105 q)exec sym from t `MSFT`IBM`IBM`MSFT`AAPL q)?[t;();();`sym] `MSFT`IBM`IBM`MSFT`AAPL q)exec sym,price from t sym | MSFT IBM IBM MSFT AAPL price| 73 90 43 90 84 q)?[t;();();(`sym`price)!`sym`price] sym | MSFT IBM IBM MSFT AAPL price| 73 90 43 90 84 q)exec price by sym from t AAPL| ,84 IBM | 90 43 MSFT| 73 90 q)?[t;();`sym;`price] AAPL| ,84 IBM | 90 43 MSFT| 73 90
Update
Update and Delete use similar syntax as select/exec but instead of ?, they use !
![t;c;b;a]
Let’s say we want to add a column minP which has values for minimum price to our original table t.
In qsql, that would be:
q)update minP:min price by sym from t time sym price size minP --------------------------------- 04:25:17.604 MSFT 82 130 82 01:47:51.333 IBM 40 167 30 15:50:12.140 AAPL 88 64 77 02:59:16.636 AAPL 77 177 77 14:35:31.860 IBM 30 105 30
For functional form:
t: t c: nothing b: sym a: minP;min price
q)![t;();(enlist`sym)!enlist `sym;(enlist `minP)!enlist (min;`price)] time sym price size minP --------------------------------- 04:25:17.604 MSFT 82 130 82 01:47:51.333 IBM 40 167 30 15:50:12.140 AAPL 88 64 77 02:59:16.636 AAPL 77 177 77 14:35:31.860 IBM 30 105 30
As you can see, the logic is same as select statement but instead of ?, we use !.
Delete
To delete a column, we use the same syntax as update but the by clause is 0b.
To delete the rows with sym=`MSFT:
q)![t;enlist (=;`sym;enlist`MSFT);0b;`symbol$()] time sym price size ---------------------------- 01:47:51.333 IBM 40 167 15:50:12.140 AAPL 88 64 02:59:16.636 AAPL 77 177 14:35:31.860 IBM 30 105
Parse
If there is a complicated qsql query that you want to rewrite in functional form, you can always use the parse function. For example:
q)parse"select from t where price>75" ? `t ,,(>;`price;75) 0b ()
This returns the individual values for a functional form (using ? vs ! and values for t,c,b,a). It makes life very easy for more complicated queries.
q)parse"select minP:min price, maxP:max size from t by sym,price where price>75,time within 10:00 12:00" ? (`t;(`by;(,;`sym;`price))) ,((>;`price;75);(within;`time;10:00 12:00)) 0b `minP`maxP!((min;`price);(max;`size))
As you can see, all you have to do is learn how to properly identify t,c,b and a values and the rest is easy. The sooner you get into the habit of writing functional queries, the better off you will be. In my next post, I will highlight the benefits of functional queries in more detail.
Special thanks to Rolf Neill for feedback and pointing out some mistakes!