# 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 ``` ### ###