Math Functions

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

All math and trigonometric functions require their input (where applicable) to be numeric.

Generic

ABS

Synopsis: 

ABS(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

CBRT(numeric_exp) 

Input:

numeric expression

Output: double numeric value

Description: Returns the cube root of numeric_exp.

SELECT CBRT(-125.5);

   CBRT(-125.5)
-------------------
-5.0066577974783435

CEIL/CEILING

Synopsis: 

CEIL(numeric_exp)    
CEILING(numeric_exp) 

Input:

numeric expression

numeric expression

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

Synopsis: 

E()

Input: none

Output: 2.718281828459045

Description: Returns Euler’s number.

SELECT E(), CEIL(E());

        E        |    CEIL(E)
-----------------+---------------
2.718281828459045|3

EXP

Synopsis: 

EXP(numeric_exp) 

Input:

float numeric expression

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

Synopsis: 

EXPM1(numeric_exp) 

Input:

float numeric expression

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

Synopsis: 

FLOOR(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

LOG(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

LOG10(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

PI()

Input: none

Output: 3.141592653589793

Description: Returns PI number.

SELECT PI();

       PI
-----------------
3.141592653589793

POWER

Synopsis: 

POWER(
    numeric_exp, 
    integer_exp) 

Input:

numeric expression

integer expression

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

Synopsis: 

RANDOM(seed) 

Input:

numeric expression

Output: double numeric value

Description: Returns a random double using the given seed.

SELECT RANDOM(123);

   RANDOM(123)
------------------
0.7231742029971469

ROUND

Synopsis: 

ROUND(
    numeric_exp      
    [, integer_exp]) 

Input:

numeric expression

integer expression; optional

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

Synopsis: 

SIGN(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

SQRT(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

TRUNCATE(
    numeric_exp      
    [, integer_exp]) 

Input:

numeric expression

integer expression; optional

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

Trigonometric

ACOS

Synopsis: 

ACOS(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

ASIN(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

ATAN(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

ATAN2(
    ordinate, 
    abscisa)  

Input:

numeric expression

numeric expression

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

Synopsis: 

COS(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

COSH(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

COT(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

DEGREES(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

RADIANS(numeric_exp) 

Input:

numeric expression

Output: double numeric value

Description: Convert from degrees to radians.

SELECT RADIANS(90), PI()/2;

   RADIANS(90)    |     (PI) / 2
------------------+------------------
1.5707963267948966|1.5707963267948966

SIN

Synopsis: 

SIN(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

SINH(numeric_exp) 

Input:

numeric expression

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

Synopsis: 

TAN(numeric_exp) 

Input:

numeric expression

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