Queries
Queries filter out and transform objects fulfilling some specific properties from collections of objects.
Example:
The following query returns the name and values of the constants in
the table Constants whose closest integer is 3:
select name, value
from Constants
where round(value)=3
This query requires that you have defined the Constants table. This
was done in the previous section Basic
functions. So if the query fail you
might need to go back and define the Constants table again.
OSQL queries can filter, transform, and generate objects of any kind,
not only strings and numbers stored in tables. You can make queries
over any kind of objects by specifying their types in the from
clause of the select statement.
Examples:
The following query computes the set of divisors for the integer 143:
select d
from Integer d, Integer n
where d in range(2,ceiling(n/2))
and 0 = mod(n,d)
and n = 143
This query selects the odd elements among the integers between 10 and 15:
select i
from Integer i
where i in range(10,15)
and odd(i)
SQL's select
statement is a special
case of OSQL queries. A restriction with SQL's select statement is that
variables in queries must be bound only to rows in tables. In OSQL
queries, variables can be bound to any kind of objects, e.g. strings,
numbers, vectors, matrices, tensors, bags, streams, records or user
defined objects. Combined with functions, this makes analytical models
in different domains very easy to express with OSQL.
The SQL subset of OSQL is introduced in the SQL Tutorial. You can skip that tutorial if you are already familiar with SQL.
OSQL functions can be defined by queries.
Example:
This function over numbers computes the divisors of any integer n:
create function divisors(Integer n) -> Bag of Integer
as select d
from Integer d
where d in range(2,ceiling(n/2))
and 0 = mod(n,d)
The following query returns the divisors of 143:
divisors(143)
In general, queries can be made over various kinds of collections, not only sets and bags.
Examples:
The following set query returns
the set prime numbers in the vector collection [197,143,2]:
select n
from Integer n
where n in [197,143,2]
and notany(divisors(n))
The following query returns a vector of prime numbers in [197,143,2]:
select Vector of n
from Integer n
where n in [197,143,2]
and notany(divisors(n))
The following query returns a stream of prime numbers in [197,143,2]:
select Stream of n
from Integer n
where n in [197,143,2]
and notany(divisors(n))
In the tutorial Streams it will be shown how to specify OSQL queries over live streams.
General queries over sets and bags are documented in Queries.
Queries to filter and transform elements in vectors and tensors are documented in Vector queries.
Case rules​
Case rules define functions and formulas that have different definitions depending on their arguments.
Example:
The signum of a number can be defined as:
create function signum(Real x) -> Integer
as case when x > 0 then 1
when x = 0 then 0
else -1 end
signum(-3.1)
The built-in function sign(x) does the same.
Another example of a function defined in terms of case rules is the
built in function atan2(y,x):
sourcecode("atan2")
atan2(1,2)
In general, case rules compute values conditioned on values of
variables. The case rules compute these values without
executing any actions (side effects). This is different from
conditional statements in conventional programming languages such as
if {condition} do-something; else do-something-else; where different
side effects (actions) are executed based on a condition.
Case rules can be used in queries, for example:
select case when x <= -40 then "too cold"
when x < 0 then "freezing"
when x < 18 then "cold"
when x < 23 then "plesant"
when x < 40 then "hot"
else "too hot"
end
from Real x
where x in 10*range(-4,4)
This illustrates how case rules can be used for specifying decision rules based on values of variables.
Tests in case statements succeed unless they return null.
Example:
case when sqrt(-1) then 1 else 0 end
AND, OR, NOTANY and SOME​
In the where clause of select statement clauses connected with and
operators specify restrictions on the query result (its solution
space). Similarly, or clauses extend the solution space.
Example:
The following query which returns the set of values of the vector
[1,2,3,4,5] that are less than 3 or larger than 4:
select i
from Integer i
where i in [1,2,3,4,5]
and (i<3 or i>4)
Notice that or has higher priority than and in queries, so
parentheses may be needed if both and and or are used in the same
query.
To negate disjunctive expressions we can use the aggregate function
notany(Bag b)->Boolean that returns true is the bag b is empty:
Example:
select i
from Integer i
where i in [1,2,3,4,5]
and notany(i<3 or i>4)
The aggregate function some(Bag)->Boolean test whether there is some
value in a bag.
Example:
some(range(1,10000000000))
Argmin and argmax​
OSQL queries can be used to implement the argmax operation by finding the vector index that gives the maximum value of a vector.
Example:
The following query finds the index of the maximum value
of the vector [4,3,9,1,8,5]:
select i
from Integer i, Vector v
where v = [4,3,9,1,8,5]
and v[i] = max(v)
Similarly you can use min to find the index that gives the minimum
value of a vector.
Example:
select i
from Integer i, Vector v
where v = [4,3,9,1,8,5]
and v[i] = min(v)
Should there be more than one index that satifies the relation, then all of them are returned as a bag.
Example:
select i
from Integer i, Vector v
where v = [4,3,9,1,9,5]
and v[i] = max(v)
For details on how to make general queries go to Queries in the reference documentation.
The next tutorial shows how to develop models, undo changes, and save the database of models on disk.