Mathematical Functions

The mathematical functions that you insert into Acho Studio’s Formula action are supported in standard SQL expressions.

ABS

ABS(X)

Description

Computes the absolute value of the argument within the parenthesis. This operation can only be applied to numeric values, an error will occur, and the new column generation will be voided if the argument within the parenthesis is of non-numeric type such as strings.

Example:

ABS(25) = 25
ABS(-25) = 25

SIGN

SIGN(X)

Description

Returns -1 for negative, 0 for zero, and +1 for positive arguments respectively. This operation can only be applied to numeric values or expressions.

SIGN(25) = 1
SIGN(0) = 0
SIGN(-25) = -1

IS_INF

IS_INF(X)

Description

Is_inf is a binary operator which will return TRUE if the value is positive or negative infinity. If the expression within parenthesis is Null then the operator will also return Null.

Example:

IS_INF(+inf) = TRUE
IS_INF(-inf) = TRUE
IS_INF(25) = FALSE

IS_NAN

IS_NAN(X)

Description

Returns TRUE if the value is a NaN value. Returns NULL for NULL inputs.

Example

IS_NAN(NaN) = TRUE
IS_NAN(25) = FALSE

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Description

Ieee_divide operator is a super-powered division operator. The normal “/” division operator will void if any of the division result returns none-numeric values. Ieee_divide will execute even if there are none-sensical special values.

Special cases:

• If the result overflows, returns +/-inf.

• If Y=0 and X=0, returns NaN.

• If Y=0 and X!=0, returns +/-inf.

• If X = +/-inf and Y = +/-inf, returns NaN.

Example

IEEE_DIVIDE(20, 4) = 5
IEEE_DIVIDE(20, 0) = NaN

RAND

RAND()

Description

Rand() is the basic random function that will return a pseudo-random value between 0 and 1. Please note that the exact range is inclusive of zero but exclusive of 1. Rand() does not require any function inside of the parenthesis.

Example

RAND() = 0.28263632192554994

New_field = confirmed_cases * rand()

SQRT

SQRT(X)

Description

Computes the square root value of the argument within parenthesis, and the argument must be numeric and greater than or equal to zero. Please note that the operation will void and error if any of the. Returns +inf if X is +inf.

Example

SQRT(25) = 5

New_field = SQRT(deaths/confirmed_cases)

POWER

POWER(X, Y)

Description

POW is the power or exponential function, where the argument X will be raised to the power of argument Y. Please note that both arguments must be numeric and there are two special cases where this operator will void and error out:

• When X is negative and Y is not an integer

• When X is zero and Y is negative

Example

POWER(5,2) = 5^2 = 25

EXP

EXP(X)

Description

Natural exponential operator where the value e (2.718281828…) is raised to the power of X. If the result underflows or is too small, this function returns a zero. If the result overflows or is too large, the operator will void and error. Please note that since this is a power function the result can get very large very easily. When X is above value 700 the function will error out.

Example

EXP(0) = 1

New_field = exp(deaths/confirmed_cases)

LN

LN(X)

Description

Computes the natural logarithm of X with a base of natural value e (2.718281828…). Generates an error if X negative or equal to zero.

Example

LN(1) = 0

New_field = ln(deaths/confirmed_cases+1)

LOG

LOG(X [, Y])

Description

Log is the more comprehensive logarithm function operator which allows user to choose the logarithmic base. The parameter Y is optional, when Y is ignored, log(x) will return the exact same result as ln(X) as it will assume the natural base e. If both X and Y are present log will output the logarithm of X to base Y. Please note that this function is more prone to error since there are more parameters, the following special cases will error:

• X is negative or equal to zero

• Y is equal to the value 1

• Y is negative or equal to zero.

Example

LOG(100, 10) = 2

New_field = log(confirmed_cases,10)

LOG10

LOG10(X)

Description

Log10 operator is very similar to Ln and Log operators, it computes the logarithm of argument X on base 10. The following example will output the exact same results as the previous example in log function.

Example

LOG(100) = 2

New_field = log10(confirmed_cases)

GREATEST

GREATEST(X1,...,XN)

Description

Compare all values(fields) listed in the parenthesis and output the maximum. Note that the arguments should all be numeric although Null values are accepted. If any of the input arguments is Null or NaN, the result will return Null or NaN respectively.

Example

GREATEST(1,2,3) = 3
GREATEST(1,2,NULL) = NULL

In Formula:

max_number = GREATEST(never, rarely, sometimes, frequently, always)

In SQL Editor:

SELECT GREATEST(never, rarely, sometimes, frequently, always) AS max_number 
FROM mask_use_by_county;

LEAST

LEAST(X1,...,XN)

Description

Similar to the Greatest operator but the opposite, will output the minimum value among listed arguments within the parenthesis.

Ex. New_field = least(never, rarely, sometimes,frequently, always)

In Formula:

max_number = GREATEST(never, rarely, sometimes, frequently, always)

In SQL Editor:

SELECT GREATEST(never, rarely, sometimes, frequently, always) AS max_number 
FROM mask_use_by_county;

DIV

DIV(X, Y)

Description

Div operator can be called the integer division function. It will only consume integer parameters X and Y, and will only output the integer portion of the division result of X divided by Y, in other words the floor of X/Y. Please note that divide by zero will error and void the operation.

Ex. New_field = div(confirmed_cases,deaths)

MOD

MOD(X, Y)

Description

Mod is the Modulo function, it returns the remainder of the division of X by Y. It can be viewed as the complement function of Div because it too only takes in integer input parameters and output integer results. Returned value has the same sign as X. The operator will error and void if any of the values for Y is zero. Note that if we are looking at the relationship between intergers X and Y, X = div(X,Y)*Y + mod(X,Y).

Example:

MOD(5,3) = 2

MOD(5,0) = Error

SAFE_DIVIDE

SAFE_DIVIDE(number1, number2)

Description:

Equivalent to the division operator (number1 / number2), but returns NULL if an error occurs, such as a division by zero error.

Example:

SAFE_DIVIDE(20,4) = 5

SAFE_DIVIDE(20,0) = NULL

SAFE_MULTIPLY

SAFE_MULTIPLY(number1, number2)

Description:

Equivalent to the multiplication operator (number1*number2), but returns NULL if overflow occurs.

Example:

SAFE_MULTIPLY(5,4) = 20

SAFE_NEGATE

SAFE_NEGATE(number)

Description:

Equivalent to the unary minus operator (-), but returns NULL if overflow occurs.

Example:

SAFE_NEGATE(20) = -20

SAFE_NEGATE(-20) = 20

SAFE_ADD

SAFE_ADD(number1, number2)

Description:

Equivalent to the addition operator (number1 + number2), but returns NULL if overflow occurs.

Example:

SAFE_ADD(5,4) = 9

SAFE_SUBSTRACT

SAFE_SUBTRACT(number1, number2)

Description:

Equivalent to the subtraction operator (number1-number2), but returns NULL if overflow occurs.

Example:

SAFE_SUBTRACT(5,4) = 1

ROUND

ROUND(number, n_decimals)

Description:

Round a number to the nearest integer value or a specified number of decimals.

Example:

ROUND(2.3) = 2

ROUND(2.8) = 3

TRUNC

TRUNC(number, [n_decimals])

Description:

Round a number to the nearest integer value. The absolute value of the integer is not greater than the absolute value of the original number. You can optionally specify n_decimals to decide the number of decimals.

Example:

ROUND(2.8) = 2 ( |2| < |2.8|)

ROUND(-2.8) = -2 (|-2| < |-2.8|)

CEIL

CEIL(number)

Description:

Round the number to the nearest integer value, which is not less than the original number.

Example:

CEIL(2.3)

Result: 3.0 (3.0 > 2.3)

CEIL(-5.6)

Result: -5.0 (-5.0 > -5.6)

FLOOR

FLOOR(number)

Description:

Round the number to the nearest integer value, which is not greater than the original number.

Example:

FLOOR(2.3)

Result: 2.0 (2.0 < 2.3)

FLOOR(-5.6)

Result: -6.0 (-6 < -5.6)

COS

COS(angle)

Description:

Return the cosine of an angle, which is specified in radians.

Example:

COS(1) = 0.5403023058681398

ACOS

ACOS(number)

Description:

Return the arccosine of a number. The result is in radians.

Example:

ACOS(1) = 0

SIN

SIN(angle)

Description:

Return the sine of an angle, which is specified in radians.

Example:

SIN(1) = 0.8415

ASIN

ASIN(number)

Description:

Return the arcsine of a number. The result is in radians.

Example:

ASIN(1) =1.5708

TAN

TAN(numer)

Description:

Return the tangent of an angle, which is specified in radians.

Example:

TAN(1) = 1.5574

ATAN

ATAN(number)

Description:

Return the arctangent of a number. The result is in radians.

Example:

ATAN(1) = 0.7854

ATAN2

ATAN2(y number, x number)

Description:

Return the arctangent of two given numbers (x and y. The result is in radians.

Example:

ATAN2(3,4) = 0.6435