Numeric Functions

Functions for performing mathematical operations on numbers. These examples are self-contained; replace literals with model concepts for per-row calculations. Use +, -, *, /, %, and ** for arithmetic expressions.

ABS

Computes the absolute value of a numeric argument.

Inputs:

INTEGERFLOATNUMBER

Output:

Same as input type

Purpose:

property

Example:

select abs(-12.5) as absolute_change;

ROUND

Rounds the first numeric argument to the number of decimal places specified by the second integer argument. If the second argument is omitted or zero, rounds to the nearest integer.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERICINTEGER

Output:

Same as first input type

Purpose:

property

Example:

select round(4.76, 1) as rounded_rating;

SQRT

Calculates the square root of a non-negative numeric argument. The result is typically a FLOAT or NUMBER.

Inputs:

INTEGERFLOATNUMBERNUMERIC

Output:

FLOAT or NUMBER

Purpose:

property

Example:

select sqrt(81) as standard_deviation;

RANDOM

Returns a random floating-point value between 0.0 (inclusive) and 1.0 (exclusive) at the grain of the provided concept. If no concept is provided, all rows have the same value.

Inputs:

ANY

Output:

FLOAT

Purpose:

property

Example:

auto order_id <- unnest([1, 2, 3]); select order_id, random(order_id) as random_sort_key order by random_sort_key;

ADD

Adds two numeric arguments. Chain the + operator to add more values.

Inputs:

NUMERIC (left)NUMERIC (right)

Output:

Appropriate numeric type based on inputs

Purpose:

property

Example:

select add(100, 8) + 5 as grand_total;

SUBTRACT

Subtracts the second numeric argument from the first. Takes two arguments.

Inputs:

NUMERIC (left)NUMERIC (right)

Output:

Appropriate numeric type based on inputs

Purpose:

property

Example:

select subtract(120, 80) as profit;

MULTIPLY

Multiplies two numeric arguments. Chain the * operator for more factors.

Inputs:

NUMERIC (left)NUMERIC (right)

Output:

Appropriate numeric type based on inputs

Purpose:

property

Example:

select multiply(3, 19.5) as line_item_total;

DIVIDE

Divides the first numeric argument by the second. Takes two arguments. Use nullif(denominator, 0) when a zero denominator should produce NULL.

Inputs:

NUMERIC (numerator)NUMERIC (denominator)

Output:

FLOAT or NUMBER (usually)

Purpose:

property

Example:

select divide(120.0, 4) as price_per_unit;

MOD

Computes the modulo (remainder) of the first numeric argument divided by the second integer argument.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERICINTEGER

Output:

INTEGER

Purpose:

property

Example:

auto user_id <- unnest([1, 2, 3, 4]); select user_id where mod(user_id, 2) = 0;

POWER

Use the ** operator to raise a number to a power.

Inputs:

NUMERICNUMERIC

Output:

NUMERIC

Purpose:

property

Example:

select 2 ** 3 as eight;

LOG

Calculates the logarithm of the first argument in the integer base supplied as the optional second argument. The default base is 10.

Inputs:

NUMERIC (value)INTEGER (base, optional)

Output:

FLOAT

Purpose:

property

Example:

select log(100, 10) as two;

FLOOR

Rounds a number down to the nearest integer.

Inputs:

NUMERIC

Output:

INTEGER

Purpose:

property

Example:

select floor(3.14) as three;

CEIL

Rounds a number up to the nearest integer.

Inputs:

NUMERIC

Output:

INTEGER

Purpose:

property

Example:

select ceil(3.14) as four;