Math Functionsedit
This functionality is in technical preview and may be changed or removed in a future release. Elastic will work to fix any issues, but features in technical preview are not subject to the support SLA of official GA features.
All math and trigonometric functions require their input (where applicable) to be numeric.
Genericedit
ABS
edit
Synopsis:
Input:
Output: numeric
Description:Returns the absolute value of numeric_exp
. The return type is the same as the input type.
SELECT ABS(-123.5), ABS(55); ABS(-123.5) | ABS(55) ---------------+--------------- 123.5 |55
CBRT
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the cube root of numeric_exp
.
SELECT CBRT(-125.5); CBRT(-125.5) ------------------- -5.0066577974783435
CEIL/CEILING
edit
Synopsis:
Input:
Output: integer or long numeric value
Description:Returns the smallest integer greater than or equal to numeric_exp
.
SELECT CEIL(125.01), CEILING(-125.99); CEIL(125.01) | CEIL(-125.99) ---------------+--------------- 126 |-125
E
edit
Synopsis:
E()
Input: none
Output: 2.718281828459045
Description:Returns Euler’s number.
SELECT E(), CEIL(E()); E | CEIL(E) -----------------+--------------- 2.718281828459045|3
EXP
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns Euler’s number at the power of numeric_exp
enumeric_exp.
SELECT EXP(1), E(), EXP(2), E() * E(); EXP(1) | E | EXP(2) | (E) * (E) -----------------+-----------------+----------------+------------------ 2.718281828459045|2.718281828459045|7.38905609893065|7.3890560989306495
EXPM1
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns Euler’s number at the power of numeric_exp
minus 1 (enumeric_exp - 1).
SELECT E(), EXP(2), EXPM1(2); E | EXP(2) | EXPM1(2) -----------------+----------------+---------------- 2.718281828459045|7.38905609893065|6.38905609893065
FLOOR
edit
Synopsis:
Input:
Output: integer or long numeric value
Description:Returns the largest integer less than or equal to numeric_exp
.
SELECT FLOOR(125.01), FLOOR(-125.99); FLOOR(125.01) |FLOOR(-125.99) ---------------+--------------- 125 |-126
LOG
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the natural logarithm of numeric_exp
.
SELECT EXP(3), LOG(20.085536923187668); EXP(3) |LOG(20.085536923187668) ------------------+----------------------- 20.085536923187668|3.0
LOG10
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the base 10 logarithm of numeric_exp
.
SELECT LOG10(5), LOG(5)/LOG(10); LOG10(5) |(LOG(5)) / (LOG(10)) ------------------+-------------------- 0.6989700043360189|0.6989700043360187
PI
edit
Synopsis:
PI()
Input: none
Output: 3.141592653589793
Description:Returns PI number.
SELECT PI(); PI ----------------- 3.141592653589793
POWER
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the value of numeric_exp
to the power of integer_exp
.
SELECT POWER(3, 2), POWER(3, 3); POWER(3,2) | POWER(3,3) ---------------+--------------- 9.0 |27.0
SELECT POWER(5, -1), POWER(5, -2); POWER(5,-1) | POWER(5,-2) ---------------+--------------- 0.2 |0.04
RANDOM
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns a random double using the given seed.
SELECT RANDOM(123); RANDOM(123) ------------------ 0.7231742029971469
ROUND
edit
Synopsis:
Input:
Output: numeric
Description:Returns numeric_exp
rounded to integer_exp
places right of the decimal point. If integer_exp
is negative,
numeric_exp
is rounded to |integer_exp
| places to the left of the decimal point. If integer_exp
is omitted,
the function will perform as if integer_exp
would be 0. The returned numeric data type is the same as the data type
of numeric_exp
.
SELECT ROUND(-345.153, 1) AS rounded; rounded --------------- -345.2
SELECT ROUND(-345.153, -1) AS rounded; rounded --------------- -350.0
SIGN
edit
Synopsis:
Input:
Output: [-1, 0, 1]
Description:Returns an indicator of the sign of numeric_exp
. If numeric_exp
is less than zero, –1 is returned. If numeric_exp
equals zero, 0 is returned. If numeric_exp
is greater than zero, 1 is returned.
SELECT SIGN(-123), SIGN(0), SIGN(415); SIGN(-123) | SIGN(0) | SIGN(415) ---------------+---------------+--------------- -1 |0 |1
SQRT
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns square root of numeric_exp
.
SELECT SQRT(EXP(2)), E(), SQRT(25); SQRT(EXP(2)) | E | SQRT(25) -----------------+-----------------+--------------- 2.718281828459045|2.718281828459045|5.0
TRUNCATE
edit
Synopsis:
Input:
Output: numeric
Description:Returns numeric_exp
truncated to integer_exp
places right of the decimal point. If integer_exp
is negative,
numeric_exp
is truncated to |integer_exp
| places to the left of the decimal point. If integer_exp
is omitted,
the function will perform as if integer_exp
would be 0. The returned numeric data type is the same as the data type
of numeric_exp
.
SELECT TRUNCATE(-345.153, 1) AS trimmed; trimmed --------------- -345.1
SELECT TRUNCATE(-345.153, -1) AS trimmed; trimmed --------------- -340.0
Trigonometricedit
ACOS
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the arccosine of numeric_exp
as an angle, expressed in radians.
SELECT ACOS(COS(PI())), PI(); ACOS(COS(PI)) | PI -----------------+----------------- 3.141592653589793|3.141592653589793
ASIN
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the arcsine of numeric_exp
as an angle, expressed in radians.
SELECT ROUND(DEGREES(ASIN(0.7071067811865475))) AS "ASIN(0.707)", ROUND(SIN(RADIANS(45)), 3) AS "SIN(45)"; ASIN(0.707) | SIN(45) ---------------+--------------- 45.0 |0.707
ATAN
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the arctangent of numeric_exp
as an angle, expressed in radians.
SELECT DEGREES(ATAN(TAN(RADIANS(90)))); DEGREES(ATAN(TAN(RADIANS(90)))) ------------------------------- 90.0
ATAN2
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the arctangent of the ordinate
and abscisa
coordinates specified as an angle, expressed in radians.
SELECT ATAN2(5 * SIN(RADIANS(45)), 5 * COS(RADIANS(45))) AS "ATAN2(5*SIN(45), 5*COS(45))", RADIANS(45); ATAN2(5*SIN(45), 5*COS(45))| RADIANS(45) ---------------------------+------------------ 0.7853981633974483 |0.7853981633974483
COS
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the cosine of numeric_exp
, where numeric_exp
is an angle expressed in radians.
SELECT COS(RADIANS(180)), POWER(SIN(RADIANS(54)), 2) + POWER(COS(RADIANS(54)), 2) AS pythagorean_identity; COS(RADIANS(180))|pythagorean_identity -----------------+-------------------- -1.0 |1.0
COSH
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the hyperbolic cosine of numeric_exp
.
SELECT COSH(5), (POWER(E(), 5) + POWER(E(), -5)) / 2 AS "(e^5 + e^-5)/2"; COSH(5) | (e^5 + e^-5)/2 -----------------+----------------- 74.20994852478785|74.20994852478783
COT
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the cotangent of numeric_exp
, where numeric_exp
is an angle expressed in radians.
SELECT COT(RADIANS(30)) AS "COT(30)", COS(RADIANS(30)) / SIN(RADIANS(30)) AS "COS(30)/SIN(30)"; COT(30) | COS(30)/SIN(30) ------------------+------------------ 1.7320508075688774|1.7320508075688776
DEGREES
edit
Synopsis:
Input:
Output: double numeric value
Description:Convert from radians to degrees.
SELECT DEGREES(PI() * 2), DEGREES(PI()); DEGREES((PI) * 2)| DEGREES(PI) -----------------+--------------- 360.0 |180.0
RADIANS
edit
Synopsis:
Input:
Output: double numeric value
Description:Convert from degrees to radians.
SELECT RADIANS(90), PI()/2; RADIANS(90) | (PI) / 2 ------------------+------------------ 1.5707963267948966|1.5707963267948966
SIN
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the sine of numeric_exp
, where numeric_exp
is an angle expressed in radians.
SELECT SIN(RADIANS(90)), POWER(SIN(RADIANS(67)), 2) + POWER(COS(RADIANS(67)), 2) AS pythagorean_identity; SIN(RADIANS(90))|pythagorean_identity ----------------+-------------------- 1.0 |1.0
SINH
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the hyperbolic sine of numeric_exp
.
SELECT SINH(5), (POWER(E(), 5) - POWER(E(), -5)) / 2 AS "(e^5 - e^-5)/2"; SINH(5) | (e^5 - e^-5)/2 -----------------+----------------- 74.20321057778875|74.20321057778874
TAN
edit
Synopsis:
Input:
Output: double numeric value
Description:Returns the tangent of numeric_exp
, where numeric_exp
is an angle expressed in radians.
SELECT TAN(RADIANS(66)) AS "TAN(66)", SIN(RADIANS(66))/COS(RADIANS(66)) AS "SIN(66)/COS(66)=TAN(66)"; TAN(66) |SIN(66)/COS(66)=TAN(66) ------------------+----------------------- 2.2460367739042164|2.246036773904216