Links

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

​

​

​