Help Center/Data Lake Insight/Spark SQL Syntax Reference/Built-In Functions/Mathematical Functions/Overview
Updated on 2025-03-17 GMT+08:00
View PDF

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.

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

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.