24. Functions & Operators

24.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 operate 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	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
median	Median	Double, int64	Double	Linear	No
approximate_median	Approximate median (fast)	Double, int64	Double, int64	Linear	No
quantile	Quantile	Double, int64	Double	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
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 abs_energy	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
sum_of_abs	Sum of absolute values	Double, int64	Double, int64	Linear	No
sum_of_abs_changes	Sum of absolute value changes	Double, int64	Double, int64	Linear	No
slope	Slope	Double, int64	Double, int64	Linear	No
array_accum	Accumulate arrays	Any	Array	Linear	No

Note

Retrieving timestamps for first/last/min/max/abs_min/abs_max

While most aggregation functions return a single value for a group, the FIRST, LAST, MIN, MAX, ABS_MIN and ABS_MAX aggregations also identify 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.

24.1.1. Approximate distinct count

APPROXIMATE_DISTINCT_COUNT(expression) estimates the number of distinct non-null values using a HyperLogLog sketch. It accepts DOUBLE, INT64 and TIMESTAMP values and returns an INT64 count. Empty inputs and inputs containing only nulls return zero. Use DISTINCT_COUNT when an exact count is required.

New in version 3.14.4.

SELECT APPROXIMATE_DISTINCT_COUNT(instrument_id) FROM measurements;

24.1.2. Time-weighted average

New in version 3.14.2.

AVG(TIME_WEIGHTED(value, $timestamp)) weights each numeric value by the time it remains in effect, rather than giving every sample equal weight. ARITHMETIC_MEAN can be used in place of AVG. The timestamp argument must be the special $timestamp column; other timestamp columns are not currently supported. TIME_WEIGHTED is only supported inside this average.

The result is DOUBLE. Use an explicit time range to define the interval being averaged. The final value extends to the end of the range. Samples preceding the range are not automatically read.

SELECT AVG(TIME_WEIGHTED(temperature, $timestamp))
FROM measurements IN RANGE(2024-01-01, +1h);

24.1.3. Null and value checks

New in version 3.14.2.

These functions summarize the selected rows, or each group when used with GROUP BY. They return an INT64 value: 1 for true and 0 for false. They test the presence of values, so zero counts as a value.

Function	Returns 1 when
HAS_NULL(column)	At least one selected row has a null in the column.
HAS_NO_NULL(column)	No selected row has a null in the column.
HAS_VALUE(column)	The column contains at least one non-null value.
HAS_NO_VALUE(column)	The column contains no non-null values.

For an empty selection, HAS_NULL and HAS_VALUE return 0; HAS_NO_NULL and HAS_NO_VALUE return 1.

SELECT $timestamp, HAS_NULL(temperature), HAS_VALUE(temperature)
FROM measurements GROUP BY 1h;

24.1.4. Quantile

New in version 3.14.2.

QUANTILE(expression, probability [, method]) calculates a quantile of non-null numeric values. expression accepts DOUBLE or INT64; the result is always DOUBLE. Empty inputs and inputs containing only nulls return null.

probability must be a floating-point literal between 0.0 and 1.0 inclusive. For example, 0.5 selects the median and 0.99 selects P99. method is an unquoted keyword: HISTOGRAM (the default), QDIGEST or TDIGEST. These are algorithms selected inside QUANTILE, rather than standalone SQL functions. There are no SQL parameters for changing the digest epsilon or compression.

Method	Representation	When to use
HISTOGRAM	Exact counts for each distinct value.	Exact quantiles when the number of distinct values is manageable.
QDIGEST	Compressed tree of value ranges and counts.	Approximate quantiles using a mergeable summary.
TDIGEST	Compressed, weighted centroids.	Approximate quantiles with finer resolution near the distribution tails.

For example, calculate P50, P90 and P99 per hour:

SELECT $timestamp,
       QUANTILE(latency, 0.5, TDIGEST) AS p50,
       QUANTILE(latency, 0.9, TDIGEST) AS p90,
       QUANTILE(latency, 0.99, TDIGEST) AS p99
FROM measurements GROUP BY 1h;

Different methods can return different values for the same probability, even on small inputs, because their rank selection and interpolation differ.

24.1.4.1. HISTOGRAM

The histogram stores an exact frequency count for every distinct non-null value, rather than grouping values into approximate numeric buckets. Quantiles are calculated from the sorted values and their counts. Memory use grows with the number of distinct values, which can make this method expensive for high-cardinality columns.

The implementation selects the first value whose cumulative count reaches probability * count. When this rank is exactly a cumulative-count boundary and the total count is even, it averages that value and the next distinct value, if one exists. Thus, for [2, 4, 8, 10], the histogram median is 6.0. Probabilities 0.0 and 1.0 select the minimum and maximum.

Omitting the method selects this algorithm:

SELECT QUANTILE(latency, 0.5), QUANTILE(latency, 0.5, HISTOGRAM)
FROM measurements;

24.1.4.2. Q-DIGEST

Use the keyword QDIGEST in SQL. Q-digest stores counts in a compressed tree of numeric ranges. Partial summaries can be merged when a query spans multiple shards or nodes.

The implementation uses a fixed epsilon of 0.01 to control compression. This parameter concerns rank approximation; it does not mean that the returned numeric value is within 1% of the exact value. The SQL interface does not expose an epsilon argument.

Q-digest selects a range boundary at the requested rank rather than averaging the two middle values. Its result can therefore differ from the histogram median even when little compression is needed.

SELECT QUANTILE(latency, 0.99, QDIGEST) FROM measurements;

24.1.4.3. T-DIGEST

Use the keyword TDIGEST in SQL. T-digest groups values into weighted centroids and interpolates between centroids to estimate quantiles. Its compression allocates finer resolution near the distribution tails, which is useful for latency percentiles such as P99 and P99.9. Accuracy still depends on the data and summary merging; there is no fixed percentage error guarantee.

The implementation uses compression 100. This is a compression setting, not a percentage accuracy guarantee or a strict limit of 100 centroids. The SQL interface does not expose a compression argument.

Changed in version 3.14.4: Corrected T-digest compression, merging and quantile interpolation. The minimum and maximum are preserved at probabilities 0.0 and 1.0. Approximate results can differ from earlier versions.

SELECT QUANTILE(latency, 0.999, TDIGEST) FROM measurements;

24.1.4.4. Median functions

New in version 3.14.2: MEDIAN and APPROXIMATE_MEDIAN.

MEDIAN(expression) uses the exact histogram and returns DOUBLE, including for INT64 inputs. It is equivalent to QUANTILE(expression, 0.5, HISTOGRAM).

APPROXIMATE_MEDIAN(expression) uses Q-digest, with the same fixed epsilon of 0.01 as QUANTILE(expression, 0.5, QDIGEST). It returns DOUBLE for DOUBLE inputs and INT64 for INT64 inputs. It does not use T-digest. To request a T-digest median, use QUANTILE(expression, 0.5, TDIGEST).

Both median functions ignore nulls and return null if there are no non-null values.

SELECT MEDIAN(latency), APPROXIMATE_MEDIAN(latency),
       QUANTILE(latency, 0.5, TDIGEST)
FROM measurements;

24.1.5. Counting number of rows

QuasarDB being a column-oriented database means that each column is stored separately, and null values are not stored at all. This allows for more efficient storage and higher performance. It does have the side-effect that, when counting a column, it only returns the amount of rows for that specific column: e.g. SELECT COUNT(col) FROM table will count the amount of values for col, and does not take into account any null values.

If you would like to count the total number of rows, you should instead count the $timestamp index: the index always has a value for every row. You can do this by providing COUNT($timestamp) as follows:

SELECT $timestamp, COUNT($timestamp), FIRST($timestamp), LAST($timestamp) FROM table GROUP BY 1h

This has the added benefit that only the index is ever touched, and as such read overhead is minimized; no actual data needs to be read.

The above query will return the total number of rows, the first timestamp in each 1h group, and the last timestamp in each 1h group.

24.2. Window functions

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	Cumulative sum	Double, int64	Double, int64	Linear	No
cumprod cumulative_product	Cumulative product	Double, int64	Double, int64	Linear	No
ma moving_average	Moving average	Double, int64	Double, int64	Linear	No
moving_min	Moving minimum	Double, int64	Double, int64	Linear	No
moving_max	Moving maximum	Double, int64	Double, int64	Linear	No

24.2.1. Additional moving functions

New in version 3.2.0: MOVING_SUM.

MOVING_SUM(value, window) calculates a running sum. Like MOVING_AVERAGE (MA), MOVING_MIN and MOVING_MAX, its window can be a positive integer number of points or a positive duration. These functions accept and return DOUBLE or INT64 values.

SELECT $timestamp, MOVING_SUM(value, 10), MOVING_SUM(value, 5min)
FROM measurements;

New in version 3.13.2: EXPONENTIAL_MOVING_AVERAGE and EMA.

EXPONENTIAL_MOVING_AVERAGE(value, count), also called EMA, accepts a positive integer period count. It starts with the first non-null value and updates the average using alpha = 2 / (count + 1) and average = alpha * value + (1 - alpha) * previous_average. It accepts and returns DOUBLE or INT64; integer results are truncated.

SELECT $timestamp, EMA(value, 10) FROM measurements;

New in version 3.13.2: MACD.

New in version 3.13.3: RSI.

Changed in version 3.13.7: MACD and RSI accept custom period counts.

MACD(value [, fast_periods [, slow_periods]]) and RSI(value [, periods]) are transformations that return a series of values. MACD subtracts the slow exponential moving average from the fast one; the default period counts are 12 and 26. RSI calculates the Relative Strength Index, with a default period count of 14. These functions accept and return DOUBLE or INT64 values.

SELECT $timestamp, MACD(value, 12, 26), RSI(value, 14)
FROM measurements;

24.2.2. Row numbering

New in version 3.14.0.

ROW_NUMBER(column) OVER(...) returns a one-based INT64 row number in timestamp order. OVER() numbers the entire series; OVER(PARTITION BY column) restarts numbering in each partition. The column argument and the OVER clause are required.

SELECT $timestamp, instrument_id,
       ROW_NUMBER(value) OVER(PARTITION BY instrument_id) AS row_number
FROM measurements;

For offset-based window functions, see LAG and LEAD.

24.2.3. OVER Clause

“OVER” is a feature that takes inspiration from SQL window functions. It’s like having a moving window over your data. Imagine this window sliding through your dataset, collecting and summarizing information as it goes.

With “OVER,” you can specify how this window behaves, defining the scope and limits of its movement. This allows you to perform all sorts of calculations and aggregations on your data.

It’s essential to know that it doesn’t yet fully support the official SQL syntax. Currently, it only works with time-based ranges. As this feature develops, the existing ‘transformations’ will eventually become alternative ways to use ‘OVER.

24.2.4. Examples

Cumulative Sum Over All Rows

In this example, we calculate the cumulative sum of the y column over all rows without specifying a time range.

CREATE TABLE table_name ($timestamp TIMESTAMP, x DOUBLE, y DOUBLE);
INSERT INTO table_name ($timestamp, x, y) VALUES
  (2023-01-01, 1, 2),
  (2023-01-02, 2, 4),
  (2023-01-03, 3, 6)

SELECT $timestamp, x, sum(y) OVER() FROM table_name;

$timestamp                                    x    sum(y) OVER()
-----------------------------------------------------------------
2023-01-01T00:00:00.000000000Z                1               12
2023-01-02T00:00:00.000000000Z                2               12
2023-01-03T00:00:00.000000000Z                3               12

Returned 3 rows in 17,830 us
Scanned 6 points in 17,830 us (336 rows/sec)

Specifying a Time-Based Range

SELECT $timestamp, x, sum(y) OVER(RANGE($unbounded, $unbounded)) AS z FROM table_name;

$timestamp                                    x                z
-----------------------------------------------------------------
2023-01-01T00:00:00.000000000Z                1               12
2023-01-02T00:00:00.000000000Z                2               12
2023-01-03T00:00:00.000000000Z                3               12

Returned 3 rows in 1,976 us
Scanned 6 points in 1,976 us (3,035 rows/sec)

When you use RANGE($unbounded, $unbounded) in the “OVER” clause, it means you’re including all the rows in your dataset, allowing you to calculate aggregates or cumulative values without any time-based limitations. It’s a way to analyze the entire dataset as a whole.

Defining the time-based constraints for the analytical operation can be achieved in different ways using ‘RANGE’. For example, you can use ‘($unbounded, $timestamp)’ (range that starts from the beginning and goes up to the current timestamp) or ‘(‘2023-01-02’, $timestamp)’ (range that starts from a specific timestamp (January 2, 2023) and goes up to the current timestamp).

24.2.5. Usage:

Think of “window functions” as a group of special math operations that you can do with your data. They include things like adding up numbers, finding averages, or picking the biggest or smallest values.

Now, here’s where “OVER” comes in: It’s like putting a frame around a part of your data. This frame tells those math operations exactly which data you want to work with.

Imagine you have a list of numbers. If you want to add up only the numbers in the last three positions, you use “OVER” to set up that frame. It’s like telling the math to only look at those last three numbers.

So, “window functions” are the math operations, and “OVER” helps you focus those operations on a specific part of your data. It’s super handy when you want to do things like adding up values as they slide through your data or finding the average in a moving window of time.

24.3. Distinguishing ‘OVER’ for Timeseries Tables and Aggregated Tables

QuasarDB uses the ‘OVER’ function as a window function, allowing you to perform various calculations within defined windows. It’s crucial to understand that ‘OVER’ applies to both Timeseries Tables and Aggregated Tables, with variations in their implementations.

24.3.1. ‘OVER’ for Sliding Windows (Timeseries Tables)

What It Does: Think of “OVER” in window functions like a spotlight on your data. It helps you perform calculations and find insights by focusing on specific portions of your dataset as it moves through time.

How It Works:

• Sliding Windows: In this scenario, “OVER” defines time-based windows that move with your data. It’s like a spotlight sliding over your information, highlighting different parts as it goes.

• Row-Centric: The calculations are done on individual rows, like analyzing a single frame within the sliding spotlight. Each row gets its calculations based on the data within its specific window.

• Ordered by Time: When you’re dealing with time-based windows, the order of the calculations always follows the time of your data points. It ensures that everything aligns properly.

One unique feature of “OVER” in window functions is the ability to create “sliding” windows. For instance, using an SQL-like expression such as:

MAX(value) OVER($timestamp - 15 minutes)

means that, for each row, the system calculates the maximum value within a 15-minute window relative to that row’s timestamp. This calculation is based on the position of each row, and the window slides as you move through the dataset.

24.3.2. ‘OVER’ for Fixed Windows (Aggregated Tables)

What It Does: With “OVER” in aggregated tables, you’re setting up fixed windows to analyze your data. These windows don’t move like a spotlight but rather remain in specific positions.

How It Works:

• Fixed Windows: In this context, ‘OVER’ creates absolute time-based windows. These windows are predetermined by your table’s configuration. They don’t shift or change position as data flows in.

• Window Configuration: The windows in aggregated tables are established by your table’s configuration. You don’t need to set a unique window for each data point. The window size and location are defined in advance.

• Different Result Counts: In window functions, you get as many results as there are rows, each with its own window. In aggregated tables, the number of results matches the number of predefined windows you’ve set for the table.

In a nutshell, ‘OVER’ for Sliding Windows (Timeseries Tables) allows you to perform dynamic calculations within sliding time-based windows that are relative to each data point. On the other hand, ‘OVER’ for Fixed Windows (Aggregated Tables) allows you to work with predetermined, fixed windows that are set by your table’s configuration.

Remember, the terminology like “sliding” or “tumbling” windows might sound confusing, but it’s primarily related to aggregated tables and how their predefined windows behave. In window functions, the term “sliding” may also be used, but it refers to windows centered around your data and moving as time progresses.

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

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

24.6. Arithmetic operators

Arithmetic 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
%	Modulus	double, int64
||	String concatenation	(string, string), (string, int64), (int64, string)

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

24.7. Mathematical functions

New in version 3.13.6.

These scalar functions operate on each value of an expression. Arguments can be numeric literals, columns, arithmetic expressions or aggregate results. They accept DOUBLE and INT64 inputs, including mixed numeric types for two-argument functions, and always return DOUBLE. A null argument produces a null result.

Function	Description	Example
LN(value)	Natural logarithm (base e).	LN(1) returns 0.0.
EXP(value)	Exponential, e raised to value.	EXP(0) returns 1.0.
POWER(value, exponent)	Raises value to exponent.	POWER(10, 2) returns 100.0.
SQRT(value)	Square root; equivalent to POWER(value, 0.5).	SQRT(9) returns 3.0.
CBRT(value)	Cube root implemented as POWER(value, 1.0 / 3.0).	CBRT(8) returns approximately 2.0.
LOG(value, base)	Logarithm of value in base: LN(value) / LN(base).	LOG(8, 2) returns 3.0.
LOG2(value)	Base-2 logarithm; equivalent to LOG(value, 2).	LOG2(8) returns 3.0.
LOG10(value)	Base-10 logarithm; equivalent to LOG(value, 10).	LOG10(100) returns 2.0.

LOG requires two arguments, with the value first and the base second. For a finite real logarithm, the value must be positive and the base must be positive and different from one. LN(0) returns negative infinity. Negative inputs to LN or SQRT produce null results. CBRT also returns null for negative inputs because it uses a fractional power. POWER supports negative bases with integer exponents; a negative base with a fractional exponent produces null.

For example, transform individual measurements:

SELECT $timestamp, LN(value) AS log_value, EXP(value) AS exp_value,
       POWER(value, 2) AS squared_value
FROM measurements;

Or apply a function to an aggregate result:

SELECT SQRT(COUNT(value)) AS sqrt_count FROM measurements;

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

24.9. Regular expressions

Regular expressions on text column types (STRING, SYMBOL and BLOB) 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

24.10. IP related functions

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

IP(ipv4_literal) encodes an IPv4 address literal as an INT64 for storage in an integer column. The address literal is written without quotes:

SELECT IP(127.0.0.1); -- 2130706433

New in version 3.13.0: IP(integer_expression) for decoding an IPv4 address.

IP(integer_expression) performs the reverse conversion and returns the IPv4 address as a STRING:

SELECT IP(2130706433); -- 127.0.0.1

24.11. Geographical functions

Built-in functions are available for your convenience to efficiently store and query 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

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

24.12.1. Examples

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

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

24.14. Rounding functions

New in version 3.14.1.

24.14.1. round

ROUND(number [, decimals [, method]]) rounds a numeric expression. decimals is an integer literal and defaults to zero. Positive values round to decimal places; negative values round to tens, hundreds, and so on. method is an unquoted keyword and defaults to nearest.

Method	Direction	Example
nearest	Nearest value; halfway cases round away from zero.	ROUND(3.5) returns 4.
inward	Towards zero.	ROUND(-3.5, 0, inward) returns -3.
outward	Away from zero.	ROUND(-3.5, 0, outward) returns -4.
lower	Towards negative infinity.	ROUND(-3.5, 0, lower) returns -4.
upper	Towards positive infinity.	ROUND(-3.5, 0, upper) returns -3.

24.14.2. ceil

CEIL(number [, decimals [, method]]) and its alias CEILING(number [, decimals [, method]]) use upper by default. An explicit method overrides the default.

24.14.3. floor

FLOOR(number [, decimals [, method]]) uses lower by default. An explicit method overrides the default.

These functions accept DOUBLE and INT64 expressions. With zero or negative decimals, the result is INT64; with positive decimals, the result is DOUBLE.

24.14.4. Examples

SELECT ROUND(3.456, 2);          -- 3.46
SELECT ROUND(1234, -2);          -- 1200
SELECT CEIL(3.456, 1);           -- 3.5
SELECT CEILING(3.456, 1);        -- 3.5
SELECT FLOOR(3.456, 1);          -- 3.4
SELECT ROUND(-3.5, 0, outward);  -- -4