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