Tuesday, July 31, 2007

SQL on matrices in Matrex

I'm planning to write a new Matrex template to query matrices (only the ones with one column) using an SQL-like syntax
The idea is simple:
  • consider the input matrices/vectors as columns of tables
  • query them using SQL
  • the SQL query result are the output matrices/vectors of the function.

The query is written in a language that is very similar to SQL. The differences are:
  • the tables are defined in the query as sets of input matrices
  • sub-queries are not allowed
Operators (+, - ...) and aggregate functions (sum, max...) are available.
For example, a query can look like:

select sum(a.price) as sumprice, sum(a.volume) as sumvolume, b.ticker as ticker, b.validity as validity
from [dealticker, price, volume] a, [ticker, validity] b
where a.dealticker = b.ticker
group by b.ticker

Here the matrices dealticker, price, volume are grouped together to form the table a, of which they are columns;
the matrices ticker, validity are grouped together to form the table b, of which they are columns.

Every result column needs to be assigned a name to; that is done using the as keyword.

All the table columns (e.g. a.price, b.ticker...) need to be expressed in the form [table].[column] (e.g. a.price).

This will be one of the most powerful templates in Matrex. With it it will be possible to express very easily and clearly problems that otherwise could require the combination of multiple functions.

Since it is not dependent by new features of the upcoming version 1.2, it is possible that the template will be available before 1.2 is released.