Source: https://code.kx.com/q4m3/9_Queries_q-sql/
Timestamp: 2019-04-24 14:34:55+00:00

Document:
We call the collection of functions for manipulating tables q-sql, since many of them resemble their SQL counterparts in form or function. The usual suspects such as insert, select, update, are present, as well as functionality that is not available in traditional SQL. But appearances can be deceiving: there are some significant differences in the syntax and behavior.
The first important difference is that a q table has ordered rows and columns. This is particularly useful when dealing with the situation where records arrive in time order. Appending them to a table ensures that they enter – and stay – in order. Subsequent select operations always retrieve the records in order without any need for sorting.
A second difference is that a q table is stored physically as a collection of column lists. This means that operations on column data are vector operations. Moreover, for simple column lists, atomic, aggregate and uniform functions applied to columns are especially simple and fast since they reduce to direct memory addressing.
A third difference is that q-sql provides upsert semantics. Recall that upsert semantics on a dictionary mean that when a key-value pair is applied with , and the key is present, the value is updated; otherwise the pair is inserted. In the context of tables and keyed tables, which are both dictionaries, this has far-reaching consequences for many common operations, including joins. Upsert semantics permeate q-sql.
In this chapter, we cover the important features of q-sql, beginning with simple examples for each. Eventually more complex examples are introduced.
You should load and display these tables in your console session now.
There are multiple ways to insert – i.e., append – records in q.
The upsert function is superior to insert and is to be preferred. We include insert for nostalgia only.
Since a table is (logically) a list of records, it is possible to append records in place using ,:. Type checking on field values is performed.
Amend can also be used with a row of naked field values provided the fields align exactly with the target columns.
Applying amend to a table repeatedly with the same argument results in duplicate records.
You can use amend to append to a keyed table using the full record form, but you will quickly see why no one does this.
It is much easier to use naked field values, provided they align exactly with both the key and value columns.
Amend has upsert semantics on keyed tables, so repeated operation on the same key will retain only the last values.
Those who prefer the familiarity of SQL can append records to an existing global table using the dyadic function insert whose left operand is a symbol containing the name of a global table (target) and whose right argument is a record, or list of records, conforming to the target. The result is a list of integers representing the row number(s) of the appended record(s).
Since insert essentially reduces to amend in place, fields are type checked.
If the target column is simple, the type must match exactly.
If the target column is an untyped empty list, the result will take the type of the field in the initial insert.
For a regular – i.e., non-keyed – table, the effect of insert is to append a new record with the specified field values. Let's use our simple example.
Here is how to insert a single record or the equivalent row list.
Repeatedly applying insert to a table with the same argument results in duplicate records.
Since a list of conforming records is a table, inserting a list of records is the same as inserting a table. Observe that use of 3# to truncate the table.
We have seen that it is possible to insert a naked list of row values instead of the full record dictionary. We have also seen that it is possible to bulk insert a list of conforming records, which is just a table with the same schema as the target.
It is also possible to bulk insert naked field values but there is a twist. To bulk insert naked field values, you provide a list of columns not a list of rows.
After a brief q Zen meditation, you will realize that this is consistent with the previous bulk insert of a table, since a table is a collection of columns. From this perspective, we should view the insertion of a single naked row more correctly as a trivial list of column atoms.
Inserting into a table that has been initialized with empty lists of general type causes the result table to take the type of the first record inserted. In particular, an atomic field in the inserted record results in a simple column with its type.
This is fine as long as all the types in the initial record are correct. Should any field have an unintended type, subsequent records of the correct type will all be rejected.
It is good practice to type all columns in an empty table. This will ensure that incorrect types are rejected and correct ones accepted.
It is also possible to insert a list of conforming records (i.e., a table) into a table that does not exist. This is the same as assigning the table to a variable of the specified name.
When inserting data into a table that has foreign key(s), the values destined for the foreign key column(s) are checked to ensure that they appear in the primary key column(s) pointed to by the foreign key(s). This is referential integrity (well, half of it).
Returning to our previous foreign-key example.
The first insert in the following succeeds but the second fails when trying to enumerate the foreign-key value 1042 that does not appear in kt.
Recall that enumeration is a form of cast.
You can use insert to append data to a keyed table, but this probably does not have the desired semantics. Specifically, you can insert into a keyed table only if the key value is not already in the table. For this and other reasons, upsert should normally be preferred over insert.
Since a keyed table is a dictionary, to use insert we should properly provide a dictionary entry comprising a key record and a value record.
Yikes! Nobody does this. Instead, you provide a list of raw field values with the proviso that they align exactly across the key and value columns.
Repeating the last insert now fails because the key value 1004 already exists.
The records in the keyed table are stored in insert order rather than key order.
The upsert template is like insert, only better. Except for the last sub-section on keyed tables, all the examples in the previous section all work the same for upsert.
Here we repeat some examples from the previous section to demonstrate that upsert can (and should) be used in place of insert for appending rows to a table.
To bulk upsert naked field values, use rows instead of columns.
A limitation of insert is that it uses pass-by-name, so it can only operate against global tables. In contrast, upsert supports both pass-by-name and pass-by-value. Thus it can be used with anonymous or local tables.
We have seen that insert has undesirable semantics on keyed tables – i.e., it rejects “duplicate” keys. What we really want is, well, upsert semantics.
This is the second reason to use upsert instead of insert.
We serialize a table and then append a row to it.
Upserting to a serialized table reads the entire table into memory, updates it and writes out the result.
Next we splay a table and then append a row to it. Observe that we ensure that all symbols are enumerated, as required for splayed tables.
Upserting to a splayed table does not read the persisted image into memory; rather, it appends to the ends of the column files. This allows incremental creation of large splayed (or partitioned) tables by upserting chunks that comfortably fit into memory. See §11.3 for more details on splayed tables.
Upserting to a table in either form will destroy any attributes on table columns. You will have to reapply them.
In this section we investigate the general form of select, which like all q-sql templates, has required and optional elements. The template elements contain phrases that are expressions (presumably) involving column values of a specified table. The template is converted by the interpreter into a functional form and is applied against the table to produce a result table. While the syntax and behavior of select resemble the analogous SQL statement, the underlying mechanics are quite different.
The result of select is always a table.
We examine each of the constituents of select in detail. We introduce the concepts with illustrative examples using trivial tables so that the basic mechanics are not obscured by large or complex data.
The select template has the following form, where elements enclosed in matching angle brackets <...> are optional.
The select and from keywords are required; omission or mistyping either results in an error. The table expression texp, which is any q expression whose value is a table or keyed table, is also required. The remaining elements ps, pb and pw are optional. They are called the select, the by and the where phrases, respectively.
If where is present and texp is itself a select expression, the inner expression should be enclosed in parentheses to avoid confusion in the binding of the where phrase.
Each phrase in the select template is a comma-separated list of subphrases. A subphrase is an arbitrary q expression (presumably) involving columns of texp or columns of another table accessed via foreign key. The evaluation of subphrases within a phrase is sequenced left-to-right by the commas, but each subphrase expression is evaluated right-to-left, like any q expression.
The commas separating the subphrases are separators, so it is not necessary to enclose a subphrase in parentheses unless the expression contains the join operator ,. Any expression containing the operator , within any template phrase must be enclosed in parentheses or it will be interpreted as the separator. Forgetting this is both easy and painful.
We begin our examples with the select phrase because it is the easiest. We shall use the following table for our examples.
The select phrase specifies the columns in the result table, one per subphrase. If the select phrase ps is absent, all columns are returned. There is no need for the * wildcard of SQL.
To specify result columns, list them separated by commas. Whitespace after commas is optional but some think it improves readability, especially for complicated queries.
To specify names for the result columns, place the name followed by colon before the subphrase.
The colon used to specify a name is not assignment; it is simply part of the syntax of the template.
Just as with the use of colon in a variable assignment, the column name is part of the syntactic sugar. It is not a symbol and cannot be parameterized. Use functional form if you need this – see §9.12.
Unlike in SQL, columns in the select phrase do not actually exist until the final result table is returned. Thus a computed column cannot be used in other column expressions.
If you do not provide a name for a computed column, q determines one.
Normally the name is taken from the left-most term in the column expression.
When q cannot determine a name it uses ‘x’.
If q’s chosen name duplicates a previously determined column name, it will suffix it with ‘1’, ‘2’, etc. to make it unique. Recall that columns are sequenced from left to right.
a  a   20 11.1 "1.1"
b  b   40 22.2 "2.2"
c  c   60 33.3 "3.3"
A virtual column i represents the offset of each record in the table – i.e., i is the row number. It is implicitly available in the select phrase.
Observe that select does not carry the name i into the result.
You can name the result column ix to avoid confusion with the always-present virtual column i.
The special form select distinct returns only unique records in the result – i.e., it eliminates duplicates.
You can return the first or last n records in a select by using function parameter syntax after select. A positive integer parameter returns the first records, a negative parameter the last.
We could achieve the same result using # after the select (i.e., to its left).
The difference is that the # construct requires computing the entire result set and then keeping only the desired rows, whereas select[n] only extracts the desired number of rows. The latter will be faster and consume less memory for large tables.
This syntax is extended to select[n m] where m is the starting row number and n is the number of rows.
One final extension of the syntax specifies a sorting criterion inside the brackets. For ascending sort, place < before a column name and for descending sort use >.
You can combine the two syntax extensions by separating them with a semicolon.
The items inside the brackets must be in this order.
You can use select on tables with nested columns but things become more complicated. The rule of thumb is that you will need adverbs… lots of adverbs.
What can we do with this table? We cannot apply an aggregate or uniform operation straight to c2 since the fields do not conform.
We can apply an aggregate or uniform function to each field of c2 in tnest.
Similarly we can use each-both to compute a weighted average using two columns.
The where phrase controls which records of the input table are actually used in the query. The effect of the where phrase is to include only the records that meet its criteria.
The action generalizes the built-in where function on lists (See §3.12.3). Recall that a table is logically a list of records.
In fact, you can provide a boolean list to where.
The virtual column i is useful for paginating a table. Use within, which returns a boolean indicating whether the left operand is in the closed interval specified in the right operand, to determine the bounds of the page.
To use this construct with non-literal values as the endpoints, you must use general-list notation, since simple-list notation cannot be used with variables.
Each where subphrase is a predicate expression that produces a boolean result vector corresponding to records passing or failing a criterion. The logical effect of multiple subphrases is to join them with “and”. Note that the parentheses are necessary in the second query.
However, since the where subphrases are sequenced from left-to-right, their order affects the actual processing. As each subphrase is applied, only the records it passes are tested in the next subphrase. The net effect is a progressively narrowed sublist of rows to consider. There is often an optimal order that significantly narrows in the first one or few subphrases, which in turn reduces the amount of processing.
Place the most limiting where subphrase first, followed by others in decreasing strictness.
Consider the following table comprising a million observations of two variables a and b taken every millisecond starting at midnight. On the author’s laptop, the version of the query that narrows the time interval first executes in under a millisecond whereas the one specifying the variable name first takes 15 milliseconds.
Nested columns generally require adverbs, and the where phrase is no different. A common mistake made by qbies is, when trying to find a specific string in a column of strings, they forget that strings are not first-class in q.
q)select from t where s="ef"
q)select from t where s~"ef"
q)select from t where s~\:"ef"
The first query is in error because it tests atomic equality between a simple list and a nested list. The second query does not achieve the desired result because it asks if the entire column matches the specified string. The final query works because it tests the specified string for match against each string in the column.
For matching strings, the like operator is more efficient. See §A.45 for a detailed description but we only need the special case that asks if two strings match exactly – i.e., no wildcards. The fact that like is pseudo-atomic in the left operand makes it suited for comparing against a column. It is actually faster than the above expression with ~\: for large lists.
q)select from t where s like "ef"
A common use case in a query is to filter on groups. For example, in the following example using the table p from the distribution samples, we wish to include only the records having the maximum weight in their respective cities. We could start with a correlated subquery.
But you run into the issue of what to do with the other fields in the subquery. Do without aggregation, which loses the information of where the maximum occurs?
Or do you apply other aggregates that will pick values that are uncorrelated with the original?
In SQL you would use HAVING, but q is having none of that. Instead use fby in the where phrase. Since it returns the value of the aggregate across each group, you simply compare the target column to the fby result to get a boolean vector with 1b at precisely the records whose fields match the aggregate on the group.
The left operand is a two-item list comprising an aggregate function fagg and a column expression exprcol on which the function will be applied. The right operand c is the column to be grouped.
Now we include another where phrase for the desired result.
To group on multiple columns, encapsulate them in an anonymous table in the right operand of fby.
In contrast to SQL, where grouping and aggregation are performed together, in q-sql they are independent. In this section we use the tables defined in the sp.q script included in the distribution.
When an aggregate function is applied against a column of simple type in the select phrase, the result is an atom. If all columns in the select phrase are computed with aggregation and there is no grouping, the result will be a table with a single row – e.g., a summary or rollup. While q has many built-in aggregates, you can also define and use your own.
Here we calculate the total and mean order quantity using the built-in aggregates sum and avg.
The by phrase groups rows having common values in specified column(s), much like GROUP BY in SQL. The result of a query including a by phrase is a keyed table whose key column(s) are those in the by phrase. This is well-defined because the grouping along like values ensures uniqueness of the keys.
The action of the by phrase is a generalization of the built-in function group on lists (See §3.12.4). A query that groups without aggregation results in nested columns. One way to think of this is that each group of values is folded into a single field in the result.
Grouping without aggregation is the most common way qbies unintentionally create nested columns. Nested columns are slower, more cumbersome to use (they require a heavy dose of adverbs) and are usually unnecessary. And you can’t just cast a nested column to a simple one; you must apply an aggregate or some other operation that flattens a list.
Observe that ungroup can be used to reverse the nested result of grouping without aggregation. It is not quite an inverse since it returns the original records ordered on the by column(s).
There are use cases that group on specified column(s) and want all the remaining columns to be nested in the result. For this use xgroup, which takes the symbolic column name(s) to be grouped as left operand and a table as right operand. The result is a keyed table that is that same as listing all the non-grouped columns in the equivalent select.
Again ungroup is an inverse up to record order.
Normally you will group using by together with aggregation in the select phrase. The effect is to aggregate along the groups, collapsing each group of rows into a single record. The result is a keyed table whose key columns are the grouped column(s).
To group on multiple columns, specify multiple by subphrases, which results in a compound key in the result.
In contrast to SQL, every column in the by phrase is automatically included in the key column(s) of the result and should not be duplicated in the select phrase.
A by subphrase can be a q expression, meaning that you can group on computed columns. This is very powerful and is not present in SQL. Following is a useful example that averages the observations of our time series in 100 millisecond buckets.
In an unexpected special case of implicit grouping with aggregation, specifying a by phrase together with empty select phrase is equivalent to applying last to all columns. A moment’s thought reveals this is quite useful for financial time series, where you often want the most recent value of a group.
The syntax of the exec template is identical to that of select.
Whereas select always returns a table, the result type of exec depends on the number of columns in its select phrase. One column yields a list; more than one column yields a dictionary.
When more than one column is specified the select phrase, the result is a dictionary mapping column names to the column lists produced. The essential difference from select is that the column lists do not have to be rectangular – i.e., they are not required to have the same length – and the resulting dictionary is not flipped into a table. For example, the following query fails with select because the proposed column dictionary is not rectangular but it succeeds with exec.
When using exec to extract a single column of a table, you can place constraints on other columns.
The update template has identical syntax to select.
The semantic difference is that colons in the update phrase pu identify modified or new columns instead of simply assigning column names. If the left of a colon is a column that exists in the table, that column is updated with the result of the expression to the right of the colon. If the left of a colon is not a column in the table, a new column of that name with the result of the expression is added to the end of the column list. The original table is not affected.
In the examples above, the table was passed by value and so the original was not modified. To modify the table in place, pass it by name.
Qbies coming from DDL may not immediately appreciate how useful it is that update can add new columns dynamically. For example, you can add a “constant” column by taking advantage of the fact that scalars are extended to match vectors. This is not a default value for the column in subsequent records.
Often you will apply update with a where phrase that limits the scope of the modification.
The actions in the where phrase and the update phrase are vector operations on entire column lists. This is the Zen of update.
If you add a new column in update with a where phrase, the fields in the non-selected rows will have the null value of the appropriate type.
You can use any q expression to the right of the colon providing the size and type match the targeted location.
When the by phrase is present, the update operation is performed along groups. This is most useful with aggregate and uniform functions. For an aggregate function, the entire group gets the value of the aggregation on the group.
A uniform function is applied along the group in place. This can be used to compute cumulative volume of orders, for example.
The final template, delete, allows either rows or columns to be deleted. Its syntax is a simplified form of select, with the restriction that either pcols or pw can be present but not both.
If pcols is present as a comma-separated list of columns, the result is texp with the specified columns removed. If pw is present, the result is texp after records meeting the criteria of pw are removed. Someone always asks, can you delete rows and column simultaneously? But the rest of us meditating on the Zen of q realize this makes no sense.
When you want to select all but a few columns, it is easier to delete the ones you don’t want than list all the ones you do.
Recall that tables and keyed tables comprise lists of records and therefore have an inherent order. A table or keyed table can be reordered by sorting on any column(s). In contrast to SQL, there is no equivalent to ORDER BY in the select template. Instead, built-in functions that sort tables are applied after select.
The dyadic xasc takes a scalar, or list of, symbolic column name(s) as its left operand and a table or table name as its right operand. It returns the records of the table sorted ascending on the specified column(s). The sort column list is specified in major-to-minor sort order.
To work on a copy, pass by value.
To sort in place, pass by name.
The dyadic xdesc behaves exactly as xasc, except that the sort is performed in descending order.
Since a table is the flip of a column dictionary, its columns are both named and ordered. There are built-in primitives to rename and reorder columns. Their names are unfortunately chosen and their usage may seem awkward at first since they focus on the left-most columns. Nonetheless, they work well in practice.
We use the following table in this section.
The dyadic xcol takes a scalar, or list of, symbolic column name(s) as its left operand (names) and a table or keyed table (source) as its right operand. The columns in names must appear in source. The result is a table obtained by renaming the left-most columns of source according to names.
There is no pass-by-name version of xcol. To modify the source, reassign it.
You can use constructs such as the following to rename isolated columns if your table has many columns and the targeted columns area not left-most.
Now you will see what we mean about the unfortunate naming convention. The dyadic xcols takes a scalar, or list of, symbolic column name(s) as its left argument (names) and a table (source) as its right argument. The output is the result of reordering the columns in names so that those in names occur at the beginning – i.e., left-most in the display. Columns not specified in names are left in original order at the end – i.e., right-most.
The source operand cannot be a keyed table.
There is no pass-by-name version of xcols. To modify the source, you must reassign it.
The essence of relational database design is normalizing data using relations and keys and then reassembling with joins. Normalization eliminates duplicate data, which takes up space and is hard to keep consistent. Joins restore the original flat rectangular form that makes data easy to work with (there’s a reason spreadsheets are so popular). Simple use cases include a master-detail relation or a lookup table.
In SQL the relational structure with primary and foreign keys is static. It must be defined in a separate language (DDL) before the data can be used. Foreign key/primary key relations must be pre-established in order for joins to take place.
In q, tables are first-class entities in the language. You can define tables and relations statically, but it is easy to create them dynamically. It is even possible to join tables that could have a relation but don’t.
A join sews back together along a foreign key/primary linkage data that has been factored into normal form. A join can be classified as inner or outer. An inner join pairs only records in both operands having matching keys. A left outer join includes all records in the left operand, pairing them with records having matching key in the right operand, should such exist. A right outer join reverses left and right in this description.
In q there are built-in inner and left outer joins; if you need a right join, reverse the operands. There is no operator for a full outer join but you can construct one; be careful what you wish for with large tables.
Joins can also be classified by how key matching is determined. Most joins are equijoins, meaning that the keys must be equal. In q there are also non-equijoins, called as of joins, in which a key is tested for less-than-or-equal against keys in another table.
A SELECT statement for this join refers to columns in the join by using dot notation based on the constituent tables.
As we saw in Chapter 8, a foreign key in q is accomplished with an enumeration over the key column(s) of a keyed table. A left join is implicit in the following query on the detail table.
For example, in the sp.q distribution script, the table sp has foreign keys to both s and p. We can extract columns from the left join with s by issuing a query against sp and using dot notation on the foreign key to get columns in s.
This generalizes to the situation where d has multiple foreign keys. For example, in the sp.q distribution script, we can select records from the join of sp, s and p from a query against sp.
Implicit joins extend to the situation in which the targeted keyed table itself has a foreign key to another keyed table.
To create an ad-hoc left outer join between tables that could have a foreign-key relationship, use the dyadic lj. When the foreign key exists, that linkage is used; otherwise, the linkage is constructed dynamically. The join is 2-3 times faster if the foreign key already exists.
If you intend to perform the join more than a few times, it pays to create the foreign key up front.
The right operand is a keyed table (target) and the left operand is a table or keyed table (source) having either a foreign key to target or column(s) that match the key column(s) of target in name and type. The result is all the records and columns of source augmented with the records and columns of target along matching keys. For those records in source having no matching key, the augmented columns contain null values.
In the following example we see all the records of t in the result, with null values in the kt column(s) where there is no matching key.
Observe that when the source table has a foreign key, an ad-hoc left join is equivalent to listing all columns from both tables in an implicit join.
When the tables source and target have duplicate non-key columns, the operation has upsert semantics.
That is, the values in the right operand (target) columns prevail over those in the left operand (source). This is different from SQL where the result contains both columns with suffixes to ensure unique names.
You can also use lj with a left operand keyed table.
You can perform a column lookup against a keyed table within a query without using a join. The insight is that a keyed table is a dictionary whose key list comprises its key records, so it will perform the lookup provided we put the column in an anonymous table conforming to those key records – see §8.4.5. Here we demonstrate the case where the lookup column names do not natively match the key columns, so we rename columns to match in the anonymous table.
Especially for a single column, this is simpler (and more impressive to your colleagues) than the equivalent join.
Here is an example using compound keys and column renaming to match the lookup table.
The dyadic inner-join operator ij performs an inner join between two tables that could have a foreign key relationship.
The same performance observations hold as for lj.
As with lj, the right operand is a keyed table (target) and the left operand is a table or keyed table (source) having column(s) that are either foreign key(s) over target or exactly match the key column(s) of target in name and type. The matching is done via the foreign key or by common column name(s) between source and the key column(s) of target if there is no foreign-key relationship. The result contains the columns from source and target joined along common keys.
The following example shows that ij returns just those records with matching keys in both tables.
As with lj, upsert semantics holds for duplicate columns.
You can also use ij with a left operand keyed table.
The triadic equijoin operator ej corresponds to a SQL inner join between tables in the second and third parameters along specified column names in the first parameter. The right operand does not have to be a keyed table. Unlike ij, all matching records in the right table appear in the result. As with any join, upsert semantics holds on duplicate columns.
Plus join pj is a left join that replaces upsert semantics for duplicate column names with addition. This is useful when you have two tables with identical schemas having all non-key columns numeric. For example, you have an organizational hierarchy and you want to roll up numeric results.
The operands of pj are the same as for lj with the additional requirement that all non-key columns are numeric. The semantics are that duplicate columns are added along matching keys and missing or null values are treated as zero.
The equivalent of an ordinary SQL union on tables with matching schemas is simply ,. Indeed, it joins two lists of compatible records.
Union join uj is more powerful in that it vertically combines any two tables, or keyed tables. The records of the right operand are appended to those of the left operand in the following manner. The result table is widened with new columns of the same name and type for the columns of the right operand that do not appear in the left operand. The initial records in the result come from the left operand and have nulls in the new columns. Records from the right operand have their field values under the appropriate columns. An example is worth a thousand words.
Continuing the previous example, you can use uj/ to combine a list of disparate tables.
You can also apply uj to keyed tables, where upsert semantics hold on both rows and columns.
The operation of uj is expensive so it should only be used when necessary; in particular, when the tables conform, use , or raze. Be especially cautious when low latency is paramount.
As-of joins are so-named because they most often join tables along time columns to obtain a value in one table that is current as of a time in another table. As-of joins are non-equijoins that match on less-than-or-equal. They are not restricted to time values.
where `c1...`cn is a simple list of symbol column names common to t1 and t2, which are the tables to be joined. There is no requirement for any of the join columns to be keys but the join will be faster on keys. The columns of both tables are brought into the result.
The semantics of aj are as follows. The match on all specified columns except the last is by equals. Assuming the records are sequenced by cn in both tables, for a given cn value in t1, the match picks the greatest cn in t2 less than or equal to the given value in t1. Specifically, if the cn columns are sequenced temporal values, for each cn value in t1 the match picks the t2 row whose cn value is in effect “as of” the time in t1.
The canonical example for aj is matching trades with quotes. To know if a trade represents best execution, you want to compare the trade price to the current quote – i.e., the most recent quote up to and including the time of the trade. When matching the trades and quotes, you clearly want an equi-join and the date and symbol and a non-equijoin on the time.
It is worth stepping through this example in detail. We begin with the first record in t. We look for the quote records matching `msft as of the trade time 10:01:01. There are two such records, both stamped 10:01:01 but the last one has price 101, which is reflected in the result. Next we look for the last quote record for `ibm as of 10:01.02, which is the quote record stamped 10:00:02 with price 98. Finally there are no quote records matching `ge at 10:01:04.
If you want the time of the matching quote in the result instead of the time of the trade, use aj0.
The simpler function asof performs the same match as aj but with a table against a single record. The result picks out the remaining columns in the matched row of the table.
A list of such dictionary records conforms – i.e., is a table – and asof matches against each record.
As-of joins are also useful with non-numeric data. Suppose we have a table containing a history of state changes – e.g., employee promotions. An as-of join will determine the state “as of” any point in time.
Window joins are a generalization of as-of joins and are specifically geared for analyzing the relationship between trades and quotes in finance. The idea is that you want to investigate the behavior of quotes in a neighborhood of each trade. For example, to determine how well a trade was executed, you need to examine the range of bid and ask prices that were prevalent around the trade time.
Writing such a query manually would be cumbersome. Instead, q provides the built-in window join wj that computes on an interval around each trade. Specifying the set up takes a bit of work but then all the hard work is done for you.
So that things fit on the page, we use a simple example involving trades for one ticker symbol `aapl and times that are in seconds. The general case is essentially the same. First we create the trades and quotes tables; note that they are required to be unkeyed.
For readability we place the names of the sym and time columns in a variable.
Here w is a list of windows, c is the list of sym and time column names, t is the trades table. The final parameter is a list with the quotes table and a pair of lists, each with an aggregate function and a column name.
To see all the values in each window, pass the identity function :: in place of the aggregates. The result is similar to grouping without aggregate in a query and is helpful to see what is happening within each window.
Now we apply wj with actual aggregates for the maximum ask and minimum bid over each window. Here you see the results of the aggregate functions run within each window.
As opposed to the aggregates being run over only the quote values within each window, you can also include the quotes that are current at the beginning of the window. This matters if there is no quote precisely at the beginning time of the window. For this case use wj1, whose syntax is identical to wj.
Relational databases have stored procedures, which are database programs incorporating SQL statements. The programming languages are not part of the SQL standard. They vary significantly across vendors but are generally third-generation imperative languages.
This situation forces a programmer to make a choice: learn a proprietary language to place functionality close to the data, or extract the data into an application server to perform calculations. Various multi-tier architectures have evolved to address this problem, but they increase system cost and complexity.
This separation is obviated in kdb+, since q is the stored procedure language and it has the power and performance to process big data. Any q function that operates on a table is effectively a stored procedure. Function parameters can be used to supply specific values for queries.
In particular, the select, exec, update and delete templates can be invoked within a function with parameters to yield a parameterized query.
A column cannot be passed as a parameter since columns are part of the syntactic sugar of the template. They only become symbols under the covers.
Multiple parameters cannot be implicit – i.e., they must be declared explicitly. Although this seems to work for a single parameter, we recommend making all parameters explicit.
We follow our own advice in the following examples.
You can pass a table as a parameter to a template, either by value of by name.
There is no need to restrict stored procs in q to the templates. Any expression that operates on tables can serve.
You can effectively parameterize column names in two ways, only one of which is good practice. First, you can mimic a common technique from SQL in which the query is created dynamically: build the query text in a string and then invoke the interpreter programmatically using value. This is comparatively slow. Worse, it exposes your application to injection attacks, since any q expression that appears inside the text will be executed.
The preferred method is to use the functional form for queries – see §9.12 – which is fast and secure. In functional form, all columns are referred to by symbolic name, so column names can be passed as symbols. In fact, any component of a query can be passed as an argument in functional form.
A SQL view is effectively a query expression whose result set can be used like a table. Views are useful for encapsulating data – e.g., hiding columns, or simplifying complex queries.
A q-sql view is a named table expression created as an alias with the double-colon operator ::. It is common to use the templates in views but this is not a limitation.
In the following example, contrast u, which is a q variable that is assigned the result of a query, to the view (alias) v that is the query expression itself.
Observe that when the underlying table changed, u did not change but the next reference to v does reflect the update.
To find the underlying query of a view, or any alias, apply the function view to the symbol alias name.
"select from t where c2>15"
Full disclosure: functional form is difficult. Along with adverbs and generalized application, it completes the Big Three aspects of q that separate q pretenders from contenders. Fortunately there is a cheat that can be helpful in most situations.
The function parse can be applied to a string containing a template query to produce a parse tree whose items (nearly) work in the equivalent functional form. A complication is that the operators are displayed in k form instead of q.
The constraint portion of the result of parse applied to a string containing a q-sql query template generally has an extra level of enlist that should be removed for the corresponding functional form. The console display of the parse tree shows the k form of enlist – i.e., , – so you will actually see ,,.
The functional forms of the four templates select, exec, update and delete are powerful because they allow all constituents to be parameterized. They can be used for any query but are especially handy for queries that are generated or completed programmatically. The q interpreter parses the syntactic sugar of the templates into their equivalent functional forms, so there is no performance difference.
We recommend writing table operations in fundamental or template form unless you anticipate needing to parameterize column names. The folks who assume your code will thank you.
c is a list of constraints.
Consequently, any literal symbols, or lists of symbols, appearing in the expressions must be distinguished. This is done by enlisting them. Really.
We warned you this wasn’t going to be easy.
We shall use the following sample tables in our examples.
We begin with the functional form corresponding to the simplest select query, which uses (yet another) overload of ?.
Observe that we can pass the table by name and we can also parameterize the table by substituting any q expression that returns a table or keyed table.
Now we add a constraint – i.e., a where phrase. The constraint parameter must be a list of q parse trees, one item for each subphrase in the where phrase. The parse tree for each subphrase can be obtained by converting the expression fully to prefix form and then transforming each function into a list with it followed by all its arguments. Remember, columns names become symbols and literal symbols get enlisted.
Next we demonstrate aggregation without grouping. The aggregate parameter is a dictionary whose keys are column names and whose value list is a list of parse trees, one for each subphrase of the aggregate phrase. Again, column names are symbols, and literal symbols get enlisted.
While q will assign default columns names in the templates, you must explicitly provide all columns names in functional form.
Next we demonstrate grouping without aggregation. As with the aggregate parameter, we construct a dictionary with column names and parse trees of the by subphrases. In this particular example, we demonstrate how to handle the case of grouping on a single column. Recall that a singleton dictionary requires the key and value to be enlisted – this enlist is separate from enlisting literal symbols in functional form.
Finally we put the pieces together. We find code to be more readable if you separately construct the three parameters as variables and pass these in to the functional form.
Now that wasn’t so bad, was it? OK, maybe it was.
Next we show the functional form for the special case select distinct. In this case set the by parameter to 1b.
The extended form select[n] adds a fifth parameter to the functional form.
The extended form select[>ci] adds two additional parameters to the functional form. The first is the initial value for the comparison and the second is a list with the k form – seriously! – of the comparison operator for the sort along with the column name for the sort.
The functional form for exec is nearly identical to that of select. Slight variations in the parameters indicate which is intended. Since the constraint parameter carries over unchanged, we omit its discussion in this section.
The functional form for exec on a single result column depends on whether you want a list or dictionary to be returned. Assuming there is no grouping, use the empty list for the by parameter. For a list result, specify the aggregate as a parse tree; for a dictionary result, specify a dictionary mapping the result name to its parse tree.
For exec on multiple columns without grouping, specify the by parameter as the general empty list and the aggregate parameter as a dictionary mapping column names to a list of parse trees.
To group on a single column, specify its symbol name in the by parameter.
More complex grouping in an exec seems to revert to the equivalent select.
distinguishing literal symbols from column names.
Observe that we switched to passing by name in the last functional query.
The syntax of functional delete is a simplified form of functional update.
where t is a table, or the name of a table, c is a list of parse trees for where subphrases and a is a list of column names. Either c or a, but not both, must be present. If c is present, it specifies which rows are to be deleted. If a is present it is a list of symbol column names to be deleted. In the former case you must specify a as an empty list of symbols.
As before, distinguish the various uses of enlist in the examples.
Again observe that we switched to call by name in the last query.
At this point we know enough about tables to do some damage. You should go back and (re)read §1. Q Shock and Awe. It will now be straightforward.
We shall demonstrate further examples in this section based on the trades table created there. Here we encapsulate the steps in a function so that it can be run for an arbitrary list of tickers and an arbitrary number of rows. On the author’s two-year-old MacBook Pro, it takes a few seconds to create a table with 10,000,000 rows.
You can’t actually define multiline functions in the q session like that. But you can in a script. Ed.
The instr table is a static keyed table containing basic information about the companies whose financial instruments (stocks in our case) are traded. Its schema has fields for the stock symbol, the name of the company and the industry classification of the company.
We populate instr using upsert.
Now we make sym a foreign key to trades.
Here we demonstrate basic operations against the trades and instrument tables.
We can count the total number of trades in several ways. The last version causes q to do the most work and should be avoided. Observe that q does not carry the name of its own virtual column i to the result of select.
Let’s count the number of trades for an individual symbol.
For splayed and partitioned tables, only the select expression works.
We can count the number of trades across all symbols. The second version unkeys the result.
We find one day's trades for AAPL.
We find all lunch-hour trades for GOOG. Note that within includes both endpoints. Also recall that you must use general-list notation if the endpoints are variables.
We find the maximum daily price for AAPL. Due to our uniform distribution, it is statistically constant.
We find the minimum and maximum trade price over the trading range for each symbol by company name. We point out that this is actually grouping on a computed column since the foreign key is resolved to the instrument table via an implicit join.
We find the total and average trade volume for specific symbols. Due to our simplistic construction, the volumes are statistically the same.
We find the high, low and close over one-minute intervals for Google.
We demonstrate how to use our own functions in queries. Suppose we define a funky average that weights items by their position. Notice that we use intra-line assignment to avoid counting x three times.
Then we can apply favg just as we did the built-in q function avg.
In this section, we demonstrate more interesting q-sql examples against the trades table.
We find volume-weighted average price by day and for 100-millisecond buckets for AAPL. Note that latter takes a few seconds for 10,000,000 trades on the author’s laptop.
We use fby to select records attaining the maximum price each day for each sym.
We use favg from the previous section to demonstrate that user functions can appear in any phrase of the query.
We find the average daily volume and price for all instruments and store the result for the following example.
We find the days when the average price went up. Note that create our own deltas0 to return 0 for the initial item since the behavior of the built-in deltas doesn’t work well for this purpose. Observe that the nested result columns scroll off the page.
aapl| 2015.01.02 2015.01.03 2015.01.05 2015.01.07 2015.01.08 2015.01.10..
goog| 2015.01.01 2015.01.02 2015.01.03 2015.01.05 2015.01.07 2015.01.00..
ibm | 2015.01.05 2015.01.07 2015.01.08 2015.01.10 2015.01.11 2015.01.13..
To compact the display, take only the first few field values.
In order to demonstrate operations on nested columns, we denormalize trades into a keyed table with one row and complex columns for each symbol. We can do this either by grouping without aggregation using select or by using xgroup. The difference is that xgroup does not automatically sort on the result key column.
We display the first two items of each field to make the structure more evident.
In such a table with compound columns – i.e., lists of simple lists of the same type – you will need adverbs for column operations. Lots of adverbs.
We can apply our own monadic favg function.
To find the volume-weighted average price by we use the each-both adverb ' with the dyadic wavg.
Note that this adverb generalizes to n-adic functions for any n>1.
We find the profit of the ideal transaction over the month for each symbol. This is the maximum amount of money that could be made with complete foreknowledge of the market. In other words, find the largest profit obtainable by buying at some traded price and selling at the highest subsequently traded price. To solve this, we reverse the perspective. For each traded price, we look at the minimum of prices that preceded it. The largest such difference is our answer. The maximum draw down is dual.
The objective is to pivot a table on a column (or columns) as in Excel. The construct revolves (pun intended) around three columns. The distinct values of the pivot column become the column names in the result. Each field in the value column is placed under the pivot column whose name appears in its original row. The destination row of a value column field is determined by the key column field in its original row. This is a pivot in the sense that the original vertical arrangement is transformed to horizontal and rows transform to columns. As usual, an example is worth a thousand words.
We begin with the simple table.
The desired result pvt is as follows. Observe that this is actually a keyed table but it is simple to unkey it with xkey.
We break the solution into multiple steps, beginning with our sample table and then successively generalizing to handle a wider class of use cases.
Collect the unique values of the pivot column p into a list P.
Write a query that extracts the key-value pairs for p and v grouped by k.
Enhance the previous query to produce a keyed table by rectangularizing the dictionaries by filling missing values using P#. Magic happens. Observe that we need to name the resulting key column explicitly.
Write the query to extract the unique values of the pivot column in functional form.
Convert the pivot query to functional form.
Place the previous functional forms in a function that takes the table and the column names as parameters and returns the pivot result.
Write an expression that converts the fields of the pivot column to valid names when they are not – e.g., integers. In general, producing unique, valid names is a non-trivial exercise. Find a solution that (at least) works on tn below.
Next, we generalize to the case when there are multiple entries in v for a single key value. For example, applied to tr below, our current dopivot misses the 1000 value in v.
Modify dopivot to take an aggregate function agg and apply it to v as part of the pivot process. Test it on tr using sum.
We would like to handle the case of compound keys – e.g., k1 and k2 in tk below.
Modify dopivot to take a list of key column names and test it on tk.
Modify dopivot to accept a string argument representing a valid where phrase for the input table and use it to constrain the pivot. Test it against tk with the phrase k1<>2.
Finally, modify dopivot to accept an empty list in the aggregate or the where parameters. In the former case, use first as the default aggregate; in the latter, perform no constraint. Test against tk with empty list arguments for both.
We test the efficiency of dopivot by applying it to a modestly large table with a million rows of timeseries data. Take that, Excel!
It is an instructive exercise to write unpivot, which takes a pivoted table and reverts the columns back to rows.

References: §11
 §9
 §3
 §3
 §8
 §9
 §1
 v.