Math Functions | Elasticsearch Guide [6.5]

IMPORTANT: No additional bug fixes or documentation updates will be released for this version. For the latest information, see the current release documentation.

› › ›

« Full-Text Search Functions String Functions »

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:

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`edit

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`edit

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`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:

EXP(numeric_exp)

Input:

float numeric expression

Output: double numeric value

Description:Returns Euler’s number at the power of numeric_exp e^numeric_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:

EXPM1(numeric_exp)

Input:

float numeric expression

Output: double numeric value

Description:Returns Euler’s number at the power of numeric_exp minus 1 (e^numeric_exp - 1).

SELECT E(), EXP(2), EXPM1(2);

        E        |     EXP(2)     |    EXPM1(2)
-----------------+----------------+----------------
2.718281828459045|7.38905609893065|6.38905609893065

`FLOOR`edit

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`edit

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`edit

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`edit

Synopsis:

PI()

Input: none

Output: 3.141592653589793

Description:Returns PI number.

SELECT PI();

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

`POWER`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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

Trigonometricedit

`ACOS`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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`edit

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

« Full-Text Search Functions String Functions »

Math Functionsedit

Genericedit

ABSedit

CBRTedit

CEIL/CEILINGedit

Eedit

EXPedit

EXPM1edit

FLOORedit

LOGedit

LOG10edit

PIedit

POWERedit

RANDOMedit

ROUNDedit

SIGNedit

SQRTedit

TRUNCATEedit

Trigonometricedit

ACOSedit

ASINedit

ATANedit

ATAN2edit

COSedit

COSHedit

COTedit

DEGREESedit

RADIANSedit

SINedit

SINHedit

TANedit

`ABS`edit

`CBRT`edit

`CEIL/CEILING`edit

`E`edit

`EXP`edit

`EXPM1`edit

`FLOOR`edit

`LOG`edit

`LOG10`edit

`PI`edit

`POWER`edit

`RANDOM`edit

`ROUND`edit

`SIGN`edit

`SQRT`edit

`TRUNCATE`edit

`ACOS`edit

`ASIN`edit

`ATAN`edit

`ATAN2`edit

`COS`edit

`COSH`edit

`COT`edit

`DEGREES`edit

`RADIANS`edit

`SIN`edit

`SINH`edit

`TAN`edit