Math Functions¶
This page lists all math functions available in Spark SQL.
%¶
expr1 % expr2, or mod(expr1, expr2) - Returns the remainder after expr1/expr2.
Examples:
> SELECT 2 % 1.8;
0.2
> SELECT MOD(2, 1.8);
0.2
Since: 1.0.0
*¶
expr1 * expr2 - Returns expr1*expr2.
Examples:
> SELECT 2 * 3;
6
Since: 1.0.0
+¶
expr1 + expr2 - Returns expr1+expr2.
Examples:
> SELECT 1 + 2;
3
Since: 1.0.0
-¶
expr1 - expr2 - Returns expr1-expr2.
Examples:
> SELECT 2 - 1;
1
Since: 1.0.0
/¶
expr1 / expr2 - Returns expr1/expr2. It always performs floating point division.
Examples:
> SELECT 3 / 2;
1.5
> SELECT 2L / 2L;
1.0
Since: 1.0.0
abs¶
abs(expr) - Returns the absolute value of the numeric or interval value.
Examples:
> SELECT abs(-1);
1
> SELECT abs(INTERVAL -'1-1' YEAR TO MONTH);
1-1
Since: 1.2.0
acos¶
acos(expr) - Returns the inverse cosine (a.k.a. arc cosine) of expr, as if computed by
java.lang.Math.acos.
Examples:
> SELECT acos(1);
0.0
> SELECT acos(2);
NaN
Since: 1.4.0
acosh¶
acosh(expr) - Returns inverse hyperbolic cosine of expr.
Examples:
> SELECT acosh(1);
0.0
> SELECT acosh(0);
NaN
Since: 3.0.0
asin¶
asin(expr) - Returns the inverse sine (a.k.a. arc sine) the arc sin of expr,
as if computed by java.lang.Math.asin.
Examples:
> SELECT asin(0);
0.0
> SELECT asin(2);
NaN
Since: 1.4.0
asinh¶
asinh(expr) - Returns inverse hyperbolic sine of expr.
Examples:
> SELECT asinh(0);
0.0
Since: 3.0.0
atan¶
atan(expr) - Returns the inverse tangent (a.k.a. arc tangent) of expr, as if computed by
java.lang.Math.atan
Examples:
> SELECT atan(0);
0.0
Since: 1.4.0
atan2¶
atan2(exprY, exprX) - Returns the angle in radians between the positive x-axis of a plane
and the point given by the coordinates (exprX, exprY), as if computed by
java.lang.Math.atan2.
Arguments:
- exprY - coordinate on y-axis
- exprX - coordinate on x-axis
Examples:
> SELECT atan2(0, 0);
0.0
Since: 1.4.0
atanh¶
atanh(expr) - Returns inverse hyperbolic tangent of expr.
Examples:
> SELECT atanh(0);
0.0
> SELECT atanh(2);
NaN
Since: 3.0.0
bin¶
bin(expr) - Returns the string representation of the long value expr represented in binary.
Examples:
> SELECT bin(13);
1101
> SELECT bin(-13);
1111111111111111111111111111111111111111111111111111111111110011
> SELECT bin(13.3);
1101
Since: 1.5.0
bround¶
bround(expr, d) - Returns expr rounded to d decimal places using HALF_EVEN rounding mode.
Examples:
> SELECT bround(2.5, 0);
2
> SELECT bround(25, -1);
20
Since: 2.0.0
cbrt¶
cbrt(expr) - Returns the cube root of expr.
Examples:
> SELECT cbrt(27.0);
3.0
Since: 1.4.0
ceil¶
ceil(expr[, scale]) - Returns the smallest number after rounding up that is not smaller than expr. An optional scale parameter can be specified to control the rounding behavior.
Examples:
> SELECT ceil(-0.1);
0
> SELECT ceil(5);
5
> SELECT ceil(3.1411, 3);
3.142
> SELECT ceil(3.1411, -3);
1000
Since: 3.3.0
ceiling¶
ceiling(expr[, scale]) - Returns the smallest number after rounding up that is not smaller than expr. An optional scale parameter can be specified to control the rounding behavior.
Examples:
> SELECT ceiling(-0.1);
0
> SELECT ceiling(5);
5
> SELECT ceiling(3.1411, 3);
3.142
> SELECT ceiling(3.1411, -3);
1000
Since: 3.3.0
conv¶
conv(num, from_base, to_base) - Convert num from from_base to to_base.
Examples:
> SELECT conv('100', 2, 10);
4
> SELECT conv(-10, 16, -10);
-16
Since: 1.5.0
cos¶
cos(expr) - Returns the cosine of expr, as if computed by
java.lang.Math.cos.
Arguments:
- expr - angle in radians
Examples:
> SELECT cos(0);
1.0
Since: 1.4.0
cosh¶
cosh(expr) - Returns the hyperbolic cosine of expr, as if computed by
java.lang.Math.cosh.
Arguments:
- expr - hyperbolic angle
Examples:
> SELECT cosh(0);
1.0
Since: 1.4.0
cot¶
cot(expr) - Returns the cotangent of expr, as if computed by 1/java.lang.Math.tan.
Arguments:
- expr - angle in radians
Examples:
> SELECT cot(1);
0.6420926159343306
Since: 2.3.0
csc¶
csc(expr) - Returns the cosecant of expr, as if computed by 1/java.lang.Math.sin.
Arguments:
- expr - angle in radians
Examples:
> SELECT csc(1);
1.1883951057781212
Since: 3.3.0
degrees¶
degrees(expr) - Converts radians to degrees.
Arguments:
- expr - angle in radians
Examples:
> SELECT degrees(3.141592653589793);
180.0
Since: 1.4.0
div¶
expr1 div expr2 - Divide expr1 by expr2. It returns NULL if an operand is NULL or expr2 is 0. The result is casted to long.
Examples:
> SELECT 3 div 2;
1
> SELECT INTERVAL '1-1' YEAR TO MONTH div INTERVAL '-1' MONTH;
-13
Since: 3.0.0
e¶
e() - Returns Euler's number, e.
Examples:
> SELECT e();
2.718281828459045
Since: 1.5.0
exp¶
exp(expr) - Returns e to the power of expr.
Examples:
> SELECT exp(0);
1.0
Since: 1.4.0
expm1¶
expm1(expr) - Returns exp(expr) - 1.
Examples:
> SELECT expm1(0);
0.0
Since: 1.4.0
factorial¶
factorial(expr) - Returns the factorial of expr. expr is [0..20]. Otherwise, null.
Examples:
> SELECT factorial(5);
120
Since: 1.5.0
floor¶
floor(expr[, scale]) - Returns the largest number after rounding down that is not greater than expr. An optional scale parameter can be specified to control the rounding behavior.
Examples:
> SELECT floor(-0.1);
-1
> SELECT floor(5);
5
> SELECT floor(3.1411, 3);
3.141
> SELECT floor(3.1411, -3);
0
Since: 3.3.0
greatest¶
greatest(expr, ...) - Returns the greatest value of all parameters, skipping null values.
Examples:
> SELECT greatest(10, 9, 2, 4, 3);
10
Since: 1.5.0
hex¶
hex(expr) - Converts expr to hexadecimal.
Examples:
> SELECT hex(17);
11
> SELECT hex('Spark SQL');
537061726B2053514C
Since: 1.5.0
hypot¶
hypot(expr1, expr2) - Returns sqrt(expr1² + expr2²).
Examples:
> SELECT hypot(3, 4);
5.0
Since: 1.4.0
least¶
least(expr, ...) - Returns the least value of all parameters, skipping null values.
Examples:
> SELECT least(10, 9, 2, 4, 3);
2
Since: 1.5.0
ln¶
ln(expr) - Returns the natural logarithm (base e) of expr.
Examples:
> SELECT ln(1);
0.0
Since: 1.4.0
log¶
log(base, expr) - Returns the logarithm of expr with base.
Examples:
> SELECT log(10, 100);
2.0
Since: 1.5.0
log10¶
log10(expr) - Returns the logarithm of expr with base 10.
Examples:
> SELECT log10(10);
1.0
Since: 1.4.0
log1p¶
log1p(expr) - Returns log(1 + expr).
Examples:
> SELECT log1p(0);
0.0
Since: 1.4.0
log2¶
log2(expr) - Returns the logarithm of expr with base 2.
Examples:
> SELECT log2(2);
1.0
Since: 1.4.0
mod¶
expr1 % expr2, or mod(expr1, expr2) - Returns the remainder after expr1/expr2.
Examples:
> SELECT 2 % 1.8;
0.2
> SELECT MOD(2, 1.8);
0.2
Since: 2.3.0
negative¶
negative(expr) - Returns the negated value of expr.
Examples:
> SELECT negative(1);
-1
Since: 1.0.0
pi¶
pi() - Returns pi.
Examples:
> SELECT pi();
3.141592653589793
Since: 1.5.0
pmod¶
pmod(expr1, expr2) - Returns the positive value of expr1 mod expr2.
Examples:
> SELECT pmod(10, 3);
1
> SELECT pmod(-10, 3);
2
Since: 1.5.0
positive¶
positive(expr) - Returns the value of expr.
Examples:
> SELECT positive(1);
1
Since: 1.5.0
pow¶
pow(expr1, expr2) - Raises expr1 to the power of expr2.
Examples:
> SELECT pow(2, 3);
8.0
Since: 1.4.0
power¶
power(expr1, expr2) - Raises expr1 to the power of expr2.
Examples:
> SELECT power(2, 3);
8.0
Since: 1.4.0
radians¶
radians(expr) - Converts degrees to radians.
Arguments:
- expr - angle in degrees
Examples:
> SELECT radians(180);
3.141592653589793
Since: 1.4.0
rand¶
rand([seed]) - Returns a random value with independent and identically distributed (i.i.d.) uniformly distributed values in [0, 1).
Examples:
> SELECT rand();
0.9629742951434543
> SELECT rand(0);
0.7604953758285915
> SELECT rand(null);
0.7604953758285915
Note:
The function is non-deterministic in general case.
Since: 1.5.0
randn¶
randn([seed]) - Returns a random value with independent and identically distributed (i.i.d.) values drawn from the standard normal distribution.
Examples:
> SELECT randn();
-0.3254147983080288
> SELECT randn(0);
1.6034991609278433
> SELECT randn(null);
1.6034991609278433
Note:
The function is non-deterministic in general case.
Since: 1.5.0
random¶
random([seed]) - Returns a random value with independent and identically distributed (i.i.d.) uniformly distributed values in [0, 1).
Examples:
> SELECT random();
0.9629742951434543
> SELECT random(0);
0.7604953758285915
> SELECT random(null);
0.7604953758285915
Note:
The function is non-deterministic in general case.
Since: 3.0.0
rint¶
rint(expr) - Returns the double value that is closest in value to the argument and is equal to a mathematical integer.
Examples:
> SELECT rint(12.3456);
12.0
Since: 1.4.0
round¶
round(expr, d) - Returns expr rounded to d decimal places using HALF_UP rounding mode.
Examples:
> SELECT round(2.5, 0);
3
Since: 1.5.0
sec¶
sec(expr) - Returns the secant of expr, as if computed by 1/java.lang.Math.cos.
Arguments:
- expr - angle in radians
Examples:
> SELECT sec(0);
1.0
Since: 3.3.0
sign¶
sign(expr) - Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.
Examples:
> SELECT sign(40);
1.0
> SELECT sign(INTERVAL -'100' YEAR);
-1.0
Since: 1.4.0
signum¶
signum(expr) - Returns -1.0, 0.0 or 1.0 as expr is negative, 0 or positive.
Examples:
> SELECT signum(40);
1.0
> SELECT signum(INTERVAL -'100' YEAR);
-1.0
Since: 1.4.0
sin¶
sin(expr) - Returns the sine of expr, as if computed by java.lang.Math.sin.
Arguments:
- expr - angle in radians
Examples:
> SELECT sin(0);
0.0
Since: 1.4.0
sinh¶
sinh(expr) - Returns hyperbolic sine of expr, as if computed by java.lang.Math.sinh.
Arguments:
- expr - hyperbolic angle
Examples:
> SELECT sinh(0);
0.0
Since: 1.4.0
sqrt¶
sqrt(expr) - Returns the square root of expr.
Examples:
> SELECT sqrt(4);
2.0
Since: 1.1.1
tan¶
tan(expr) - Returns the tangent of expr, as if computed by java.lang.Math.tan.
Arguments:
- expr - angle in radians
Examples:
> SELECT tan(0);
0.0
Since: 1.4.0
tanh¶
tanh(expr) - Returns the hyperbolic tangent of expr, as if computed by
java.lang.Math.tanh.
Arguments:
- expr - hyperbolic angle
Examples:
> SELECT tanh(0);
0.0
Since: 1.4.0
try_add¶
try_add(expr1, expr2) - Returns the sum of expr1and expr2 and the result is null on overflow. The acceptable input types are the same with the + operator.
Examples:
> SELECT try_add(1, 2);
3
> SELECT try_add(2147483647, 1);
NULL
> SELECT try_add(date'2021-01-01', 1);
2021-01-02
> SELECT try_add(date'2021-01-01', interval 1 year);
2022-01-01
> SELECT try_add(timestamp'2021-01-01 00:00:00', interval 1 day);
2021-01-02 00:00:00
> SELECT try_add(interval 1 year, interval 2 year);
3-0
Since: 3.2.0
try_divide¶
try_divide(dividend, divisor) - Returns dividend/divisor. It always performs floating point division. Its result is always null if expr2 is 0. dividend must be a numeric or an interval. divisor must be a numeric.
Examples:
> SELECT try_divide(3, 2);
1.5
> SELECT try_divide(2L, 2L);
1.0
> SELECT try_divide(1, 0);
NULL
> SELECT try_divide(interval 2 month, 2);
0-1
> SELECT try_divide(interval 2 month, 0);
NULL
Since: 3.2.0
try_mod¶
try_mod(dividend, divisor) - Returns the remainder after expr1/expr2. dividend must be a numeric. divisor must be a numeric.
Examples:
> SELECT try_mod(3, 2);
1
> SELECT try_mod(2L, 2L);
0
> SELECT try_mod(3.0, 2.0);
1.0
> SELECT try_mod(1, 0);
NULL
Since: 4.0.0
try_multiply¶
try_multiply(expr1, expr2) - Returns expr1*expr2 and the result is null on overflow. The acceptable input types are the same with the * operator.
Examples:
> SELECT try_multiply(2, 3);
6
> SELECT try_multiply(-2147483648, 10);
NULL
> SELECT try_multiply(interval 2 year, 3);
6-0
Since: 3.3.0
try_subtract¶
try_subtract(expr1, expr2) - Returns expr1-expr2 and the result is null on overflow. The acceptable input types are the same with the - operator.
Examples:
> SELECT try_subtract(2, 1);
1
> SELECT try_subtract(-2147483648, 1);
NULL
> SELECT try_subtract(date'2021-01-02', 1);
2021-01-01
> SELECT try_subtract(date'2021-01-01', interval 1 year);
2020-01-01
> SELECT try_subtract(timestamp'2021-01-02 00:00:00', interval 1 day);
2021-01-01 00:00:00
> SELECT try_subtract(interval 2 year, interval 1 year);
1-0
Since: 3.3.0
unhex¶
unhex(expr) - Converts hexadecimal expr to binary.
Examples:
> SELECT decode(unhex('537061726B2053514C'), 'UTF-8');
Spark SQL
Since: 1.5.0
uniform¶
uniform(min, max[, seed]) - Returns a random value with independent and identically distributed (i.i.d.) values with the specified range of numbers. The random seed is optional. The provided numbers specifying the minimum and maximum values of the range must be constant. If both of these numbers are integers, then the result will also be an integer. Otherwise if one or both of these are floating-point numbers, then the result will also be a floating-point number.
Examples:
> SELECT uniform(10, 20, 0) > 0 AS result;
true
Since: 4.0.0
width_bucket¶
width_bucket(value, min_value, max_value, num_bucket) - Returns the bucket number to which
value would be assigned in an equiwidth histogram with num_bucket buckets,
in the range min_value to max_value."
Examples:
> SELECT width_bucket(5.3, 0.2, 10.6, 5);
3
> SELECT width_bucket(-2.1, 1.3, 3.4, 3);
0
> SELECT width_bucket(8.1, 0.0, 5.7, 4);
5
> SELECT width_bucket(-0.9, 5.2, 0.5, 2);
3
> SELECT width_bucket(INTERVAL '0' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
1
> SELECT width_bucket(INTERVAL '1' YEAR, INTERVAL '0' YEAR, INTERVAL '10' YEAR, 10);
2
> SELECT width_bucket(INTERVAL '0' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
1
> SELECT width_bucket(INTERVAL '1' DAY, INTERVAL '0' DAY, INTERVAL '10' DAY, 10);
2
Since: 3.1.0