Overview
Table 1 lists the mathematical functions supported by DLI.
| Function | 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 | 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. |
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.
