Math Functions and Operators

math operators {{ocient}} supports addition, subtraction, multiplication, and division between any two numeric types division involving two integral types always results in an integral type decimal precision these rules determine the precision p and scale s for the result of a specified expression that involves decimals d1 and d2 , with precisions p1 and p2 , and scales s1 and s2 operation precision scale addition, subtraction max( s1 , s2 ) + max( p1 s1 , p2 s2 ) + 1 max( s1 , s2 ) multiplication p1 + p2 s1 + s2 division see note max(6, s1 + p2 + 1) for division, the resulting precision is the minimum necessary to hold the whole number portion of the result if that portion does not exist because the absolute value of the result is less than one, the resulting precision is one greater than the scale for other operations, the result might still have a lower precision (but the same scale) than specified by these rules when the result would otherwise have a leading insignificant zero if the resulting precision exceeds 31, the database decreases both precision and scale until the precision is 31, except in division, where the minimal scale is always six if the resulting precision still exceeds 31, the database truncates it to 31 the database returns an overflow error when the result exceeds the available integral portion of the result when the database decreases the resulting scale, it truncates the excess fractional digits in the result math operators for time intervals ocient supports math operators for time interval values (hour, minute, second, etc ), including when used with time series data types such as time, timestamp, and date you can use addition, subtraction, multiplication, and division on time intervals like any other numeric value division operations can produce inaccurate results because the system truncates the precision of the result to a whole number for details, see math functions and operators /#decimal precision examples add interval value to timestamp this example uses the minutes cast function to add six minutes to the timestamp value returned from the timestamp cast function of the '2023 04 05 12 15 05' string select timestamp('2023 04 05 12 15 05') + (minutes(6)); output 2023 04 05 12 21 05 000 add fractional interval value to timestamp this example shows how division can produce an inaccurate result the division operation in the example must produce a whole number in other words, the system calculates two minutes as 5 / 2 = 2 instead of two and a half minutes as 5 / 2 = 2 5 select timestamp('2023 04 05 12 15 05') + (minutes(5) / 2); output 2023 04 05 12 17 05 000 math functions these scalar math functions operate on a single value and return a single value they can appear anywhere where an ordinary value or condition can appear for a list of common math symbols supported in ocient , see math functions and operators /#math operators abs returns the absolute value of a specified floating point number syntax abs(x) example select abs( 250 5); output 250 5 acos returns the inverse cosine of a specified floating point number syntax acos(x) example select acos(0 25); output 1 318116071652818 acosh returns the hyperbolic arc cosine of a specified floating point number syntax acosh(x) example select acosh(2); output 1 3169578969248166 asin returns the inverse sine of a specified floating point number syntax asin(x) example select asin(0 25); output 0 25268025514207865 asinh returns the hyperbolic arc sine of a specified floating point number syntax asinh(x) example select asinh(2); output 1 4436354751788103 atan returns the inverse tangent of a specified floating point number syntax atan(x) example select atan(0 25); output 0 24497866312686414 atan2 returns the inverse tangent of two numeric, floating point values syntax atan2(x, y) example select atan2(0 50, 1); output 0 4636476090008061 atanh returns the hyperbolic arc tangent of a specified floating point number syntax atanh(x) example select atanh(0 25); output 0 25541281188299536 bicdf the cumulative distribution function of the standard bivariate normal distribution, which returns the probability that a random sample of (a, b) is less than x and less than y ( a <= x and b <= y ) the correlation is set to 0 in the case where you specify two arguments only you can specify an optional rho coefficient to calculate the correlation for details, see the bivariate cumulative distribution function syntax bicdf(x, y \[, rho]) example select bicdf(1,2); output 0 8222040420815764 bipdf the probability density function of the standard bivariate normal distribution returns the relative likelihood that a random sample would equal the specified values the correlation is set to 0 in the case where you specify two arguments only you can specify an optional rho coefficient to calculate the correlation for details, see the bivariate probability density function syntax bipdf(x, y, \[, rho]) example select bipdf(1,2); output 0 013064233284684893 bitfunc performs a variety of bit operations the char func can be any of these string literals 'and' , 'or' , or 'xor' this argument determines the bitwise operation that is performed on arguments x and y syntax bitfunc(char func, x, y) 161,120,375 false true left unhandled content type left unhandled content type left unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type example select bitfunc('and',11,12); output 8 example select bitfunc('or',11,12); output 15 example select bitfunc('xor',11,12); output 7 bitand alias for the math functions and operators /#bitfunc syntax bitfunc('and', x, y) bitnot returns the bitwise negation of integral x syntax bitnot(integral x) example select bitnot(1); output 2 bitor alias for the math functions and operators /#bitfunc syntax bitfunc('or', x, y) bitxor alias for the math functions and operators /#bitfunc syntax bitfunc('xor', x, y) boolfunc performs a boolean logical evaluation on arguments x and y numbers equal to 0 evaluate to false , and numbers not equal to 0 evaluate to true syntax boolfunc(char func, x, y) 161,120,375 false true left unhandled content type left unhandled content type left unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type left false unhandled content type example select boolfunc('and',0,1); output false example select boolfunc('or',0,1); output true example select boolfunc('xor',0,1); output true booland alias for the math functions and operators /#boolfunc syntax boolfunc('and', x, y) boolnot returns the logical negation of the math functions and operators /#boolfunc function numbers equal to 0 evaluate to true , and numbers not equal to 0 evaluate to false syntax boolnot(numeric x) example select boolnot(0); output true boolor alias for the math functions and operators /#boolfunc syntax boolfunc('or', x, y) boolxor alias for the math functions and operators /#boolfunc syntax boolfunc('xor', x, y) cbrt returns the cube root of the numeric value x syntax cbrt(x) example select cbrt(64); output 4 0 cdf the cumulative distribution function of the standard normal distribution returns the probability that a random sample is less than or equal to the specified value for details about the cumulative distribution function, see cumulative distribution function syntax cdf(x) example select cdf(1 5); output 0 9331927987311419 ceil returns the nearest integer greater than or equal to x syntax ceil(x) example select ceil(23 45); output 24 0 ceiling alias for ceil cos returns the cosine of x syntax cos(x) example select cos(2); output 0 4161468365471424 cosh returns the hyperbolic cosine of x syntax cosh(x) example select cosh(2); output 3 7621956910836314 cot returns the cotangent of x syntax cot(x) example select cot(2); output 0 45765755436028577 degrees returns the corresponding angle in degrees for x in radians syntax degrees(x) example select degrees(1 5); output 85 94366926962348 div returns the result of x divided by y if y is zero, returns null syntax div(x, y) example select div(45, 9); output 5 erf the error function is used for measurements that follow a normal distribution use this function to find the probability that the error of a single measurement lies between \[ a, a] for details about the error function, see error function syntax erf(x) example select erf(8); output 1 0 erfc the complement of the error function erfc(x) = 1 erf(x) syntax erfc(x) example select erfc(8); output 1 1224297172982928e 29 exp returns the exponential of x (e raised to the power of x ) syntax exp(x) example select exp(1); output 2 718281828459045 floor returns the nearest integer less than or equal to x syntax floor(x) example select floor(22 75); output 22 0 gamma compute the gamma function of the specified value for details about the gamma function, see the gamma function syntax gamma(x) example select gamma(9 0); output 40319 99999999999 hexbinx returns the x coordinate of the center of the nearest hexagonal bin to the point ( x , y ) hexagonal bins have a side length of 1 syntax hexbinx(x, y) example select hexbinx(5, 7); output 4 5 hexbiny returns the y coordinate of the center of the nearest hexagonal bin to the point ( x , y ) hexagonal bins have a side length of 1 syntax hexbiny(x, y) example select hexbiny(5, 7); output 7 794 ierf the inverse of the erf error function syntax ierf(x) example select ierf(0); output 0 0 ierfc the inverse of the complement of the error function syntax ierfc(x) example select ierfc(1); output 0 0 leakyrelu returns the leaky rectified linear unit function of x if unspecified, the optional numeric value slope argument defaults to 0 01 syntax leakyrelu(x \[, numeric value slope] ) example select leakyrelu( 40); output 0 4 left shift returns x shifted to the left by y bits if y is less than 0, then x shifts to the right by the absolute value the function throws an out of range error if you attempt a shift into the sign bit syntax left shift(x, y) example select left shift(1,2); output 4 ln returns the natural logarithm of x (base e or 2 171828…) syntax ln(x) example select ln(2); output 0 6931471805599453 log2 returns the base 2 logarithm of x syntax log2(x) example select log2(2); output 1 0 log returns the base 10 logarithm of x the optional base argument specifies the numeral system to use if unspecified, the function defaults to base 10 syntax log(\[ base,] x) example select log(2); output 0 3010299956639812 log gamma the natural logarithm of the absolute value of the gamma function syntax log gamma(x) example select log gamma(10); output 12 80182748008147 mod returns the remainder from x divided by y syntax mod(x, y) example select mod(12, 10); output 2 pi returns the constant value of π syntax pi() example select pi(); output 3 141592653589793 pmod returns the smallest non negative equivalence class of x % y syntax pmod(x, y) example select pmod(12,10); output 2 power returns x raised to the power of y the return type float is returned for a float input, decimal for double input, and bigint otherwise syntax power(x, y) example select power(2, 4); output 16 probit the inverse of the cumulative distribution function the function accepts only decimal values between 0 and 1 for details about the probit function, see the probit model syntax probit(x) example select probit(0 4); output 0 2533471031357998 radians returns the corresponding angle in radians for x in degrees syntax radians(x) example select radians(2); output 0 03490658503988659 rand takes no argument and returns a random double value in the range \[0, 1) syntax rand() example select rand(); output 0 6053105024272647 relu returns the rectified linear unit function of x syntax relu(x) example select relu( 1); output 0 0 right shift returns x shifted to the right by y bits if y is less than 0, then x shifts to the left by the absolute value of y in bits the function throws an out of range error if you attempt a shift into the sign bit syntax right shift(x, y) example select right shift(5,1); output 2 round returns x rounded to the nearest integer syntax round(x) example select round(12 49); output 12 0 sign returns the positive (+1), zero (0), or negative ( 1) sign of x syntax sign(x) example select sign(+12); output 1 example select sign(0); output 0 example select sign( 12); output 1 sin returns the sine of x syntax sin(x) example select sin(2); output 0 9092974268256817 sinh returns the hyperbolic sine of x syntax sinh(x) example select sinh(2); output 3 626860407847019 sqrt returns the square root of x syntax sqrt(x) example select sqrt(81); output 9 square returns the square of x syntax square(x) example select square(7); output 49 tan returns the tangent of x syntax tan(x) example select tan(1 75); output 5 52037992250933 tanh returns the hyperbolic tangent of x syntax tanh(x) example select tanh(1 75); output 0 9413755384972874 trunc returns x truncated to y decimal places syntax trunc(x,y) example select trunc(1 23456,3); output 1 234 truncate alias for trunc related links array functions and operators docid 89kk83 gif3icefcy1kuw matrix functions and operators docid\ dwjpaeks9otz2u sav2lj tuple functions and operators docid\ xunz45zvbfsvnbhzy99v5 formatting functions docid\ u47hjysi1oojk6eg2kjut data types docid\ ogtviwl gtbgv0chhrh 3 query ocient docid 4ycq1d8tkfmlsacorynf6