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) q)t _
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
- Result columns are separated by ‘,’ and are sequenced left- to-right.
- Any q expressions inside select are evaluated right-to-left, as usual.
- As was the case with table definition syntax, instances of ‘:’ are not assignment; rather, they are syntactic markers separating a column name to its left from the q expression to its right, which computes the column.
- Arbitrary q expressions can be used to produce result columns, provided all column lengths are the same.
- There are optional by and where phrases for grouping and constraints.
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 _
Reprinted with the author's permission from: q for Mortals Version 3, An Introduction to Q Programming by Jeffry A. Borror.
©2015 Jeffry A. Borror/ q4m LLC