QforMortals3/q-sql 101

From Kx Wiki
Jump to: navigation, search

1.16 q-sql 101

There are multiple ways to operate on tables. First, you can treat a table as the column dictionary that it is and perform basic dictionary operations on it. Qbies who are familiar with SQL may find it easier to use q’s version of SQL-like syntax, called q-sql. In this section we explore basic q-sql features. The fundamental q-sql operation is the select template, We say template because, unlike other q primitives, it is not evaluated right- to-left. Rather, it is syntactic sugar designed to mimic SQL SELECT. That said, we emphasize that although select does act like SQL SELECT in some respects, there is one fundamental difference. Whereas SQL SELECT operates on fields on a row-by-row basis, select performs vector operations on column lists. Insisting on thinking in rows with q tables will end in tears.

We construct a simple table for our examples.

q)t:([] c1:1000+til 6; c2:`a`b`c`a`b`a; c3:10*1+til 6) 

The simplest form of select retrieves all the records and columns of the table by leaving unspecified which rows or columns—there is no need for the wildcard * of SQL. The ‘select’ and ‘from’ must occur together.

q)select from t 

The next example shows how to specify which columns to return and optional names to associate with them.

q)select c1, val:2*c3 from t 

We make several observations

The next example demonstrates using the by phrase of select to perform grouping. The basic usage is similar to GROUP BY in SQL, in which the column expressions involve aggregate functions. All records having common values in the by column(s) are grouped together and then aggregation is performed within each group.

q)select count c1, sum c3 by c2 from t 
c2| c1 c3 
--| ------
a | 3  110
b | 2  70 
c | 1  30

An advantage of q-sql by is that you can group on a computed column.

q)select count c2 by ovrund:c3<=40 from t 
ovrund| c2
------| --
0     | 2 
1     | 4 

Closely related to select is the update template. It has the same syntax as select but semantically the names to the left of : are interpreted as columns to modify (or add, if not present). As with select, you can specify an optional where phrase, which limits the action to just those records satisfying specified constraint(s). Here is how to scale the c3 column of t just in the positions having c2 equal to `a.

q)update c3:10*c3 from t where c2=`a 
c1   c2 c3 
1000 a  100
1001 b  20 
1002 c  30 
1003 a  400
1004 b  50 
1005 a  600

We emphasize that the operations in update are vector operations on columns, not row-by-row.

Not all of q-sql is included in the templates. For example, to sort a table ascending by column(s), use (xasc) with left operand the symbol column name(s) in major-to-minor order.

q)`c2 xasc t 

Prev: Dictionaries and Tables 101, Next: Example: Trades Table

Reprinted with the author's permission from: q for Mortals Version 3, An Introduction to Q Programming by Jeffry A. Borror.

The book is available on Amazon. In the United Kingdom, it is available at Amazon UK.

©2015 Jeffry A. Borror/ q4m LLC

Personal tools