8. Function & Operators

8.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
ma	Moving average	Double, int64	Double, int64	Linear	No
moving_average	Moving average	Double, int64	Double, int64	Linear	No
quadratic_mean	Quadratic mean	Double, int64	Double, int64	Linear	No
Statistics
distinct_count	Number of distinct elements	Any	Int64	Linear	No
adjacent_count	Number of differences between successive elements	Any	Int64	Linear	No
cumsum	Cumulative sum	Double, int64	Double, int64	Linear	No
cumulative_sum	Cumulative sum	Double, int64	Double, 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

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

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

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

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

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

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

8.7. IP related functions

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

Profile	Description	Input value	Return value
ip(string) -> int64	Transform an IPv4 address into an integer for efficient storage into an integer column.	IPv4 string	int64 code

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

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

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

8.10. Cast function

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