Overview

Table 1 lists the mathematical functions supported by DLI.

**Table 1** Mathematical functions
Function	Syntax	Value Type	Description
abs	abs(DOUBLE a)	DOUBLE or INT	Returns the absolute value.
acos	acos(DOUBLE a)	DOUBLE	Returns the arc cosine value of a.
asin	asin(DOUBLE a)	DOUBLE	Returns the arc sine value of a.
atan	atan(DOUBLE a)	DOUBLE	Returns the arc tangent value of a.
bin	bin(BIGINT a)	STRING	Returns a number in binary format.
bround	bround(DOUBLE a)	DOUBLE	In HALF_EVEN rounding, the digit 5 is rounded up if the digit before 5 is an odd number and rounded down if the digit before 5 is an even number. For example, bround(7.5) = 8.0, bround(6.5) = 6.0.
bround	bround(DOUBLE a, INT d)	DOUBLE	The value is rounded off to d decimal places in HALF_EVEN mode. The digit 5 is rounded up if the digit before 5 is an odd number and rounded down if the digit before 5 is an even number. For example, bround(8.25, 1) = 8.2, bround(8.35, 1) = 8.4.
cbrt	cbrt(DOUBLE a)	DOUBLE	Returns the cube root of a.
ceil	ceil(DOUBLE a)	DECIMAL	Returns the smallest integer that is greater than or equal to a. For example, ceil(21.2) = 22.
conv	conv(BIGINT num, INT from_base, INT to_base), conv(STRING num, INT from_base, INT to_base)	STRING	Converts a number from from_base to to_base. For example, convert 5 from decimal to quaternary using conv(5,10,4) = 11.
cos	cos(DOUBLE a)	DOUBLE	Returns the cosine value of a.
cot1	cot1(DOUBLE a)	DOUBLE or DECIMAL	Returns the cotangent of a specified radian value.
degress	degrees(DOUBLE a)	DOUBLE	Returns the angle corresponding to the radian.
e	e()	DOUBLE	Returns the value of e.
exp	exp(DOUBLE a)	DOUBLE	Returns the value of e raised to the power of a.
factorial	factorial(INT a)	BIGINT	Returns the factorial of a.
floor	floor(DOUBLE a)	BIGINT	Returns the largest integer that is less than or equal to A. For example, floor(21.2) = 21.
greatest	greatest(T v1, T v2, ...)	DOUBLE	Returns the greatest value of a list of values.
hex	hex(BIGINT a) hex(STRING a)	STRING	Converts an integer or character into its hexadecimal representation.
least	least(T v1, T v2, ...)	DOUBLE	Returns the least value of a list of values.
ln	ln(DOUBLE a)	DOUBLE	Returns the natural logarithm of a given value.
log	log(DOUBLE base, DOUBLE a)	DOUBLE	Returns the natural logarithm of a given base and exponent.
log10	log10(DOUBLE a)	DOUBLE	Returns the base-10 logarithm of a given value.
log2	log2(DOUBLE a)	DOUBLE	Returns the base-2 logarithm of a given value.
median	median(colname)	DOUBLE or DECIMAL	Returns the median.
negative	negative(INT a)	DECIMAL or INT	Returns the opposite number of a. For example, if negative(2) is given, –2 is returned.
percentlie	percentile(colname,DOUBLE p)	DOUBLE or ARRAY	Returns the exact percentile, which is applicable to a small amount of data. Sorts a specified column in ascending order, and then obtains the exact pth percentage. The value of p must be between 0 and 1.
percentlie_approx	percentile_approx (colname,DOUBLE p)	DOUBLE or ARRAY	Returns the approximate percentile, which is applicable to a large amount of data. Sorts a specified column in ascending order, and then obtains the value corresponding to the pth percentile.
pi	pi()	DOUBLE	Returns the value of pi.
pmod	pmod(INT a, INT b)	DECIMAL or INT	Returns the positive value of the remainder after division of x by y.
positive	positive(INT a)	DECIMAL, DOUBLE, or INT	Returns the value of a, for example, positive(2) = 2.
pow	pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p)	DOUBLE	Returns the value of a raised to the power of p.
radians	radians(DOUBLE a)	DOUBLE	Returns the radian corresponding to the angle.
rand	rand(INT seed)	DOUBLE	Returns an evenly distributed random number that is greater than or equal to 0 and less than 1. If the seed is specified, a stable random number sequence is displayed.
round	round(DOUBLE a)	DOUBLE	Round off
round	round(DOUBLE a, INT d)	DOUBLE	Rounds a to d decimal places, for example, round(21.263,2) = 21.26.
shiftleft	shiftleft(BIGINT a, INT b)	INT	Bitwise signed left shift. Interprets a as a binary number and shifts the binary number b positions to the left.
shiftright	shiftright(BIGINT a, INT b)	INT	Bitwise signed right shift. Interprets a as a binary number and shifts the binary number b positions to the right.
shiftrightunsigned	shiftrightunsigned(BIGINT a, INT b)	INT	Bitwise unsigned right shift. Interprets a as a binary number and shifts the binary number b positions to the right.
sign	sign(DOUBLE a)	DOUBLE	Returns the sign of a. 1.0 is returned if a is positive. –1.0 is returned if a is negative. Otherwise, 0.0 is returned.
sin	sin(DOUBLE a)	DOUBLE	Returns the sine value of the given angle a.
sqrt	sqrt(DOUBLE a)	DOUBLE	Returns the square root of a.
tan	tan(DOUBLE a)	DOUBLE	Returns the tangent value of the given angle a.