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 [7.5] ›SQL access ›Functions and Operators

Mathematical Functions

edit

Mathematical Functions

edit

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)  |CEILING(-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 »