Table Of Contents

6.7. Function & Operators

6.7.1. Aggregate functions

QuasarDB supports many server-side operations that can be applied on timeseries data. Below is an overview of the functions available and the value types they can operator on.

Calculations performed using these functions are distributed over the cluster, and many of them are vectorized. Since 3.0.0 QuasarDB uses light-weight indexes to speed up lookups and computations where possible.

Operation Description Input value Return value Complexity Vectorized
Basic operations
first First element Any Any Constant No
last Last element Any Any Constant No
min Minimum value Double, int64 Double, int64 Linear Yes
max Maximum value Double, int64 Double, int64 Linear Yes
count Number of elements Any Int64 Constant No
sum Sum of values Double, int64 Double, int64 Linear Yes
 
Averages
avg Arithmetic mean Double, int64 Double, int64 Linear Yes
arithmetic_mean Arithmetic mean Double, int64 Double, int64 Linear Yes
harmonic_mean Harmonic mean Double, int64 Double, int64 Linear No
geometric_mean Geometric mean Double, int64 Double, int64 Linear No
quadratic_mean Quadratic mean Double, int64 Double, int64 Linear No
 
Statistics
spread Spread Double, int64 Double, int64 Linear Yes
product Product Double, int64 Double, int64 Linear Yes
skewness Skewness Double, int64 Double, int64 Linear No
kurtosis Kurtosis Double, int64 Double, int64 Linear No
 
abs_min min(abs(x)) Double, int64 Double, int64 Linear No
abs_max max(abs(x)) Double, int64 Double, int64 Linear No
sum_of_squares Sum of squares Double, int64 Double, int64 Linear Yes
 
sample_variance Sample variance Double, int64 Double, int64 Linear No
sample_stddev Sample standard deviation Double, int64 Double, int64 Linear No
population_variance Population variance Double, int64 Double, int64 Linear No
population_stddev Population standard deviation Double, int64 Double, int64 Linear No

6.7.2. Comparison operators

For WHERE clauses, QuasarDB supports different boolean operators to filter rows from the results. Below is an overview of the operators available and the value types they can operator on.

Operator Description Applies to
= Equal Any
!= Not equal Any
< Less than Double, int64
> Greater than Double, int64
<= Less than or equal to Double, int64
>= Greater than or equal to Double, int64

6.7.3. Logical operators

You can compose boolean operators in WHERE clause with logical operators to filter rows from the results. Below is an overview of the operators available.

Operator Description
and Logical and
or Logical or
not Logical not

6.7.4. Arithmetic operator

Bitwise operators allow you to combine values in SELECT and WHERE clauses. Below is an overview of the operators available.

Operator Description Applies to
+ Addition double, int64
- Substraction double, int64
* Multiplication double, int64
/ Division double, int64

6.7.5. Bitwise operators

Bitwise operators allow you to combine values in SELECT and WHERE clauses. Below is an overview of the operators available.

Operator Description Applies to
& Bitwise and int64

6.7.6. Regular expressions

Regular expressions on blobs are possible within WHERE clauses. QuasarDB uses the extended POSIX regular expression grammar.

Operator Description Applies to
~ Regex case sensitive match blob
!~ Regex case sensitive no match blob
~* Regex case insensitive match blob
!~* Regex case insensitive no match blob

6.7.8. Geographical functions

Built-in functions are available for your convenience to store and query effiently geographical data.

Profile Description Input value Return value
geohash64(double, double) -> int64 Transforms a latitude and a longitude into a sub-meter precise geohash. Latitude and longitude int64 hash

6.7.9. Lookup function

The Lookup function enables you to map the result of an expression to the key-value store inside QuasarDB.

It stringifies the result of an expression, and prefix it with the provided string. It will then return the content of keys matching the entry.

For example, if the row contains the integer 1, it will be transformed to the string 1, and the provided string will be prefixed.

If the key is of the wrong type or missing, no row will be returned.

Profile Description Input value Return value
lookup(string, expression) -> column Lookup keys based on the result of the expression and the provided prefix. Prefix and expression column of blobs

Example

For the following table trades:

timestamp agent value
2018-01-01T10:01:02 3 4
2018-01-01T10:01:04 2 5
2018-01-01T10:01:08 1 4

And with the following blob keys (added through the key/value API):

Key Value
‘agentid.1’ ‘Bob’
‘agentid.2’ ‘Terry’
‘agentid.3’ ‘Alice’

The following query:

select lookup('agentid.', agent), value from trades where value=4

Will output:

timestamp lookup(‘agentid.’, agent) value
2018-01-01T10:01:02 ‘Alice’ 4
2018-01-01T10:01:08 ‘Bob’ 4
arrow_backPrevious
6.6. Insert
Next arrow_forward
6.8. Managing tables