Overview¶
Table 1 lists the mathematical functions supported by DLI.
Syntax | Value Type | Description |
|---|---|---|
abs(DOUBLE a) | DOUBLE or INT | Returns the absolute value. |
acos(DOUBLE a) | DOUBLE | Returns the arc cosine value of a. |
asin(DOUBLE a) | DOUBLE | Returns the arc sine value of a. |
atan(DOUBLE a) | DOUBLE | Returns the arc tangent value of a. |
bin(BIGINT a) | STRING | Returns a number in binary format. |
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(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(DOUBLE a) | DOUBLE | Returns the cube root of a. |
ceil(DOUBLE a) | DECIMAL | Returns the smallest integer that is greater than or equal to a. For example, ceil(21.2) = 22. |
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(DOUBLE a) | DOUBLE | Returns the cosine value of a. |
cot1(DOUBLE a) | DOUBLE or DECIMAL | Returns the cotangent of a specified radian value. |
degrees(DOUBLE a) | DOUBLE | Returns the angle corresponding to the radian. |
e() | DOUBLE | Returns the value of e. |
exp(DOUBLE a) | DOUBLE | Returns the value of e raised to the power of a. |
factorial(INT a) | BIGINT | Returns the factorial of a. |
floor(DOUBLE a) | BIGINT | Returns the largest integer that is less than or equal to A. For example, floor(21.2) = 21. |
greatest(T v1, T v2, ...) | DOUBLE | Returns the greatest value of a list of values. |
hex(BIGINT a) hex(STRING a) | STRING | Converts an integer or character into its hexadecimal representation. |
least(T v1, T v2, ...) | DOUBLE | Returns the least value of a list of values. |
ln(DOUBLE a) | DOUBLE | Returns the natural logarithm of a given value. |
log(DOUBLE base, DOUBLE a) | DOUBLE | Returns the natural logarithm of a given base and exponent. |
log10(DOUBLE a) | DOUBLE | Returns the base-10 logarithm of a given value. |
log2(DOUBLE a) | DOUBLE | Returns the base-2 logarithm of a given value. |
median(colname) | DOUBLE or DECIMAL | Returns the median. |
negative(INT a) | DECIMAL or INT | Returns the opposite number of a. For example, if negative(2) is given, -2 is returned. |
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. |
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() | DOUBLE | Returns the value of pi. |
pmod(INT a, INT b) | DECIMAL or INT | Returns the positive value of the remainder after division of x by y. |
positive(INT a) | DECIMAL, DOUBLE, or INT | Returns the value of a, for example, positive(2) = 2. |
pow(DOUBLE a, DOUBLE p), power(DOUBLE a, DOUBLE p) | DOUBLE | Returns the value of a raised to the power of p. |
radians(DOUBLE a) | DOUBLE | Returns the radian corresponding to the angle. |
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(DOUBLE a) | DOUBLE | Round off |
round(DOUBLE a, INT d) | DOUBLE | Rounds a to d decimal places, for example, round(21.263,2) = 21.26. |
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(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(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(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(DOUBLE a) | DOUBLE | Returns the sine value of the given angle a. |
sqrt(DOUBLE a) | DOUBLE | Returns the square root of a. |
tan(DOUBLE a) | DOUBLE | Returns the tangent value of the given angle a. |