Numeric Functions

Functions for performing mathematical operations on numbers.

ABS

Computes the absolute value of a numeric argument.

Inputs:

INTEGERFLOATNUMBER

Output:

Same as input type

Purpose:

property

Example:

SELECT ABS(price_change) AS abs_price_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(average_rating, 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(variance) 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:

SELECT order_id, RANDOM(order_id) AS random_sort_key ORDER BY random_sort_key;

ADD

Performs arithmetic addition on two or more numeric arguments.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERIC...

Output:

Appropriate numeric type based on inputs

Purpose:

property

Example:

SELECT ADD(subtotal, tax, shipping_fee) AS grand_total;

SUBTRACT

Performs arithmetic subtraction. If two arguments, subtracts the second from the first. If more, behavior might vary (e.g., subtract all subsequent from first).

Inputs:

INTEGER/FLOAT/NUMBER/NUMERIC...

Output:

Appropriate numeric type based on inputs

Purpose:

property

Example:

SELECT SUBTRACT(total_revenue, total_cost) AS profit;

MULTIPLY

Performs arithmetic multiplication on two or more numeric arguments.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERIC...

Output:

Appropriate numeric type based on inputs

Purpose:

property

Example:

SELECT MULTIPLY(quantity, unit_price) AS line_item_total;

DIVIDE

Performs arithmetic division. If two arguments, divides the first by the second. Division by zero typically results in an error or NULL/Infinity. Result type is often FLOAT or NUMBER.

Inputs:

INTEGER/FLOAT/NUMBER/NUMERIC...

Output:

FLOAT or NUMBER (usually)

Purpose:

property

Example:

SELECT DIVIDE(total_sales, number_of_units) 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:

SELECT user_id WHERE MOD(user_id, 2) = 0; # Even user IDs

POWER

Raises a number to the power of another number.

Inputs:

NUMERICNUMERIC

Output:

NUMERIC

Purpose:

property

Example:

SELECT POWER(2, 3) AS eight;

LOG

Calculates the logarithm of a number with a given base.

Inputs:

NUMERICNUMERIC

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;