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 »
Elastic Docs ›Elasticsearch Guide [6.6] ›SQL Access ›Functions and Operators

Math Functions

edit

Math Functions

edit

This functionality is in beta and is subject to change. The design and code is less mature than official GA features and is being provided as-is with no warranties. Beta 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

edit

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)

Input:

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

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

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/RAND

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/SIGNUM

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

Trigonometric

edit

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 »