Math Functions¶
This page lists all math functions available in Spark SQL.
%¶
expr1 % expr2, or mod(expr1, expr2) - Returns the remainder after expr1/expr2.
Arguments:
- expr1 - The dividend. An expression that evaluates to a numeric.
- expr2 - The divisor. An expression that evaluates to a numeric.
Examples:
> SELECT 2 % 1.8;
0.2
> SELECT MOD(2, 1.8);
0.2
Since: 1.0.0
*¶
expr1 * expr2 - Returns expr1*expr2.
Arguments:
- expr1 - The first operand. An expression that evaluates to a numeric or interval.
- expr2 - The second operand. An expression that evaluates to a numeric or interval.
Examples:
> SELECT 2 * 3;
6
Since: 1.0.0
+¶
expr1 + expr2 - Returns expr1+expr2.
Arguments:
- expr1 - The first addend. An expression that evaluates to a numeric, interval, date, timestamp, or time.
- expr2 - The second addend. An expression that evaluates to a numeric, interval, date, timestamp, or time.
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.
Arguments:
- expr1 - The dividend. An expression that evaluates to a numeric or interval.
- expr2 - The divisor. An expression that evaluates to a numeric.
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.
Arguments:
- expr - The numeric or interval value to take the absolute value of. An expression that evaluates to a numeric or interval.
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.
Arguments:
- expr - The expression to compute the inverse cosine of. An expression that evaluates to a double.
Examples:
> SELECT acos(1);
0.0
> SELECT acos(2);
NaN
Since: 1.4.0
acosh¶
acosh(expr) - Returns inverse hyperbolic cosine of expr.
Arguments:
- expr - The expression to compute the inverse hyperbolic cosine of. An expression that evaluates to a double.
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.
Arguments:
- expr - The expression to compute the inverse sine of. An expression that evaluates to a double.
Examples:
> SELECT asin(0);
0.0
> SELECT asin(2);
NaN
Since: 1.4.0
asinh¶
asinh(expr) - Returns inverse hyperbolic sine of expr.
Arguments:
- expr - The expression to compute the inverse hyperbolic sine of. An expression that evaluates to a double.
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
Arguments:
- expr - The expression to compute the inverse tangent of. An expression that evaluates to a double.
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 An expression that evaluates to a double.
- exprX - coordinate on x-axis An expression that evaluates to a double.
Examples:
> SELECT atan2(0, 0);
0.0
Since: 1.4.0
atanh¶
atanh(expr) - Returns inverse hyperbolic tangent of expr.
Arguments:
- expr - The expression to compute the inverse hyperbolic tangent of. An expression that evaluates to a double.
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.
Arguments:
- expr - The long value to represent in binary. An expression that evaluates to a long.
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.
Arguments:
- expr - The expression to round. An expression that evaluates to a numeric.
- d - The number of decimal places to round to. An expression that evaluates to an integer. Must be a constant.
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.
Arguments:
- expr - The expression to compute the cube root of. An expression that evaluates to a double.
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.
Arguments:
- expr - The expression to round up. An expression that evaluates to a numeric.
- scale - The number of decimal places to round to. An expression that evaluates to an integer. Must be a constant.
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.
Arguments:
- expr - The expression to round up. An expression that evaluates to a numeric.
- scale - The number of decimal places to round to. An expression that evaluates to an integer. Must be a constant.
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.
Arguments:
- num - The number to convert. An expression that evaluates to a string.
- from_base - The base the number is currently in. An expression that evaluates to an integer.
- to_base - The base to convert the number to. An expression that evaluates to an integer.
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 An expression that evaluates to a double.
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 An expression that evaluates to a double.
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 An expression that evaluates to a double.
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 An expression that evaluates to a double.
Examples:
> SELECT csc(1);
1.1883951057781212
Since: 3.3.0
degrees¶
degrees(expr) - Converts radians to degrees.
Arguments:
- expr - angle in radians An expression that evaluates to a double.
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.
Arguments:
- expr1 - The dividend to be divided. An expression that evaluates to an integral, decimal, or interval.
- expr2 - The divisor to divide by. An expression that evaluates to an integral, decimal, or interval.
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.
Arguments:
- expr - The exponent to raise e to. An expression that evaluates to a double.
Examples:
> SELECT exp(0);
1.0
Since: 1.4.0
expm1¶
expm1(expr) - Returns exp(expr) - 1.
Arguments:
- expr - The exponent used to compute exp(expr) - 1. An expression that evaluates to a double.
Examples:
> SELECT expm1(0);
0.0
Since: 1.4.0
factorial¶
factorial(expr) - Returns the factorial of expr. expr is [0..20]. Otherwise, null.
Arguments:
- expr - The integer between 0 and 20 to compute the factorial of. An expression that evaluates to an integer.
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.
Arguments:
- expr - The expression to round down. An expression that evaluates to a numeric.
- scale - The number of decimal places to round to. An expression that evaluates to an integer. Must be a constant.
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.
Arguments:
- expr - The expression to convert to hexadecimal. An expression that evaluates to a long, binary, or string.
Examples:
> SELECT hex(17);
11
> SELECT hex('Spark SQL');
537061726B2053514C
Since: 1.5.0
hypot¶
hypot(expr1, expr2) - Returns sqrt(expr1² + expr2²).
Arguments:
- expr1 - The first value whose square is summed. An expression that evaluates to a double.
- expr2 - The second value whose square is summed. An expression that evaluates to a double.
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.
Arguments:
- expr - The expression to compute the natural logarithm of. An expression that evaluates to a double.
Examples:
> SELECT ln(1);
0.0
Since: 1.4.0
log¶
log(base, expr) - Returns the logarithm of expr with base.
Arguments:
- base - The base of the logarithm. An expression that evaluates to a double.
- expr - The expression to compute the logarithm of. An expression that evaluates to a double.
Examples:
> SELECT log(10, 100);
2.0
Since: 1.5.0
log10¶
log10(expr) - Returns the logarithm of expr with base 10.
Arguments:
- expr - The expression to compute the base-10 logarithm of. An expression that evaluates to a double.
Examples:
> SELECT log10(10);
1.0
Since: 1.4.0
log1p¶
log1p(expr) - Returns log(1 + expr).
Arguments:
- expr - The expression used to compute log(1 + expr). An expression that evaluates to a double.
Examples:
> SELECT log1p(0);
0.0
Since: 1.4.0
log2¶
log2(expr) - Returns the logarithm of expr with base 2.
Arguments:
- expr - The expression to compute the base-2 logarithm of. An expression that evaluates to a double.
Examples:
> SELECT log2(2);
1.0
Since: 1.4.0
mod¶
expr1 % expr2, or mod(expr1, expr2) - Returns the remainder after expr1/expr2.
Arguments:
- expr1 - The dividend. An expression that evaluates to a numeric.
- expr2 - The divisor. An expression that evaluates to a numeric.
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.
Arguments:
- expr - The expression to negate. An expression that evaluates to a numeric or interval.
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.
Arguments:
- expr1 - The dividend. An expression that evaluates to a numeric.
- expr2 - The divisor. An expression that evaluates to a numeric.
Examples:
> SELECT pmod(10, 3);
1
> SELECT pmod(-10, 3);
2
Since: 1.5.0
positive¶
positive(expr) - Returns the value of expr.
Arguments:
- expr - The input expression whose value is returned. An expression that evaluates to a numeric or interval.
Examples:
> SELECT positive(1);
1
Since: 1.5.0
pow¶
pow(expr1, expr2) - Raises expr1 to the power of expr2.
Arguments:
- expr1 - The base to be raised to a power. An expression that evaluates to a double.
- expr2 - The exponent to raise the base to. An expression that evaluates to a double.
Examples:
> SELECT pow(2, 3);
8.0
Since: 1.4.0
power¶
power(expr1, expr2) - Raises expr1 to the power of expr2.
Arguments:
- expr1 - The base to be raised to a power. An expression that evaluates to a double.
- expr2 - The exponent to raise the base to. An expression that evaluates to a double.
Examples:
> SELECT power(2, 3);
8.0
Since: 1.4.0
radians¶
radians(expr) - Converts degrees to radians.
Arguments:
- expr - angle in degrees An expression that evaluates to a double.
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).
Arguments:
- seed - The seed for the random number generator. An expression that evaluates to an integer or long. Must be a constant.
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.
Arguments:
- seed - The seed used to produce reproducible random results. An expression that evaluates to an integer or long. Must be a constant.
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).
Arguments:
- seed - The seed for the random number generator. An expression that evaluates to an integer or long. Must be a constant.
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.
Arguments:
- expr - The value to round to the closest mathematical integer. An expression that evaluates to a double.
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.
Arguments:
- expr - The expression to round. An expression that evaluates to a numeric.
- d - The number of decimal places to round to. An expression that evaluates to an integer. Must be a constant.
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 An expression that evaluates to a double.
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.
Arguments:
- expr - The expression whose sign is returned. An expression that evaluates to a double or interval.
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.
Arguments:
- expr - The expression whose sign is returned. An expression that evaluates to a double or interval.
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 An expression that evaluates to a double.
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 An expression that evaluates to a double.
Examples:
> SELECT sinh(0);
0.0
Since: 1.4.0
sqrt¶
sqrt(expr) - Returns the square root of expr.
Arguments:
- expr - The expression to compute the square root of. An expression that evaluates to a double.
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 An expression that evaluates to a double.
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 An expression that evaluates to a double.
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.
Arguments:
- expr1 - The first addend. An expression that evaluates to a numeric, interval, date, timestamp, or time.
- expr2 - The second addend. An expression that evaluates to a numeric, interval, date, timestamp, or time.
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(time'09:00:00', interval 1 hour);
10:00:00
> SELECT try_add(time'23:00:00', interval 2 hour);
NULL
> 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.
Arguments:
- dividend - The expression to be divided. An expression that evaluates to a numeric or interval.
- divisor - The expression to divide by. An expression that evaluates to 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.
Arguments:
- dividend - The expression to be divided. An expression that evaluates to a numeric.
- divisor - The expression to divide by. An expression that evaluates to 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.
Arguments:
- expr1 - The first factor. An expression that evaluates to a numeric or interval.
- expr2 - The second factor. An expression that evaluates to a numeric or interval.
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.
Arguments:
- expr1 - The minuend to subtract from. An expression that evaluates to a numeric, interval, date, timestamp, or time.
- expr2 - The subtrahend to subtract. An expression that evaluates to a numeric, interval, date, timestamp, or time.
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(time'09:00:00', interval 1 hour);
08:00:00
> SELECT try_subtract(time'00:30:00', interval 1 hour);
NULL
> SELECT try_subtract(interval 2 year, interval 1 year);
1-0
Since: 3.3.0
unhex¶
unhex(expr) - Converts hexadecimal expr to binary.
Arguments:
- expr - The hexadecimal expression to convert to binary. An expression that evaluates to a string.
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.
Arguments:
- min - The lower bound of the range of random values. An expression that evaluates to a numeric. Must be a constant.
- max - The upper bound of the range of random values. An expression that evaluates to a numeric. Must be a constant.
- seed - The seed used to produce reproducible random results. An expression that evaluates to an integer or long. Must be a constant.
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."
Arguments:
- value - The value to assign to a bucket. An expression that evaluates to a double or interval.
- min_value - The minimum value of the histogram range. An expression that evaluates to a double or interval.
- max_value - The maximum value of the histogram range. An expression that evaluates to a double or interval.
- num_bucket - The number of equiwidth buckets in the histogram. An expression that evaluates to a long.
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