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.

Using an aggregation function on a column always requires all other columns to be grouped by.

The following query demonstrates a valid aggregation:

```
SELECT instrument_id, AVG(value) FROM measurements GROUP BY instrument_id
```

In this case, we use the `AVG`

aggregation function, and group by instrument_id. This query, however, is invalid:

```
SELECT instrument_id, AVG(value) FROM measurements
```

Grouping based on durations always requires the special `$timestamp`

field to be selected as follows:

```
SELECT $timestamp, AVG(value) FROM measurements GROUP BY 1h
```

This query will then yield the aggregation in groups of 1h.

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

Yes

Statistics

`distinct_count`

Number of distinct elements

Any

Int64

Linear

No

`adjacent_count`

Number of differences between successive elements

Any

Int64

Linear

No

`product`

Product

Double, int64

Double, int64

Linear

Yes

`spread`

Spread

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

`correlation`

Population correlation

Double, int64

Double

Linear

No

`covariance`

Population covariance

Double, int64

Double

Linear

No

Note

**Retrieving timestamps for first/last/min/max**

While most aggregation functions simply operate only on the group they operate in and return a single value, the `FIRST`

, `LAST`

, `MIN`

and `MAX`

aggregations have an additional trait: they always point to the value of a single row.

As such, when you get the result of running e.g. `MAX(value)`

, you often also want to know *when* this occured. For this reason, these specific aggregation functions also allow you to select the associated timestamp by running e.g. `MAX(value).$timestamp`

.

For example:

```
SELECT
MAX(value),
MAX(value).$timestamp,
MIN(value),
MIN(value).$timestamp
FROM measurements
```

This enables you not only calculate these aggregations, but also exactly when they occurred.

Window functions are similar to aggregation functions, but instead of aggregating “buckets”, they instead operate over a sliding window of multiple rows, and accumulate their results in the process.

Operation

Description

Input value

Return value

Complexity

Vectorized

`cumsum`

Cumulative sum

Double, int64

Double, int64

Linear

No

`cumulative_sum`

Cumulative sum

Double, int64

Double, int64

Linear

No

`cumprod`

Cumulative product

Double, int64

Double, int64

Linear

No

`cumulative_product`

Cumulative product

Double, int64

Double, int64

Linear

No

`ma`

Moving average

Double, int64

Double, int64

Linear

No

`moving_average`

Moving average

Double, int64

Double, int64

Linear

No

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

`in find(...)`

Is contained in the resulting set

Blob, double, int64, string

You can use inline key/value lookups to check if a value is contained in the set of blobs/ints resulting from it. See IN FIND example.

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

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

`||`

String concatenation

(string, string), (string, int64), (int64, string)

Aggregations with a single result can be combined with arithmetic, which allows to use expressions like `sum(b - min(a))`

.

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

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

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

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. 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.

Profile

Description

Input value

Return value

`lookup(expression, type) -> column`

Lookup keys based on the stringified result of the expression.

Expression and type

column of <type>s

`lookup(expression) -> column`

Equivalent to

`lookup(expression, STRING)`

Expression

column of strings

Type can be any of `BLOB`

, `DOUBLE`

, `INT64`

, `STRING`

, or `TIMESTAMP`

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

For compatibility, the form taking a prefix string `lookup(string, expression)`

is implicitly transformed
into string concatenation `lookup(string || expression)`

.

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

For the following table `basket`

:

timestamp

item

quantity

`2021-01-01T10:01:02`

‘apples’

3

`2021-01-01T10:01:04`

‘oranges’

2

`2021-01-01T10:01:08`

‘bananas’

1

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

Key

Value

‘price.apples’

‘0.54’

‘price.bananas’

‘0.80’

‘price.oranges’

‘0.63’

The following query:

```
select item, quantity * lookup('price.' || item, DOUBLE) as price from basket
```

Will output:

timestamp

item

price

`2021-01-01T10:01:02`

‘apples’

1.62

`2021-01-01T10:01:04`

‘oranges’

1.26

`2021-01-01T10:01:08`

‘bananas’

0.8

The Cast function enables you to transform the result of an expression.

Profile

Description

Input value

Return value

`cast(expression as EPOCH_NS) -> int64`

Number of nanoseconds since 1 January 1970.

timestamp

int64

`cast(expression as EPOCH_US) -> int64`

Number of microseconds since 1 January 1970.

timestamp

int64

`cast(expression as EPOCH_MS) -> int64`

Number of milliseconds since 1 January 1970.

timestamp

int64

`cast(expression as EPOCH_S) -> int64`

Number of seconds since 1 January 1970.

timestamp

int64