elasticsearch/docs/reference/sql/functions/math.asciidoc

[role="xpack"]
[[sql-functions-math]]
=== Mathematical Functions

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

[[sql-functions-math-generic]]
[discrete]
=== Generic

[[sql-functions-math-abs]]
==== `ABS`

.Synopsis:
[source, sql]
--------------------------------------------------
ABS(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: numeric

*Description*: Returns the {wikipedia}/Absolute_value[absolute value] of `numeric_exp`. The return type is the same as the input type.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[abs]
--------------------------------------------------

[[sql-functions-math-cbrt]]
==== `CBRT`

.Synopsis:
[source, sql]
--------------------------------------------------
CBRT(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Cube_root[cube root] of `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineCbrtWithNegativeValue]
--------------------------------------------------

[[sql-functions-math-ceil]]
==== `CEIL/CEILING`

.Synopsis:
[source, sql]
--------------------------------------------------
CEIL(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: integer or long numeric value

*Description*: Returns the smallest integer greater than or equal to `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineCeiling]
--------------------------------------------------

[[sql-functions-math-e]]
==== `E`

.Synopsis:
[source, sql]
--------------------------------------------------
E()
--------------------------------------------------

*Input*: _none_

*Output*: `2.718281828459045`

*Description*: Returns {wikipedia}/E_%28mathematical_constant%29[Euler's number].

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathEulersNumber]
--------------------------------------------------

[[sql-functions-math-exp]]
==== `EXP`

.Synopsis:
[source, sql]
--------------------------------------------------
EXP(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> float numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns {wikipedia}/Exponential_function[Euler's number at the power] of `numeric_exp` e^numeric_exp^.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathExpInline]
--------------------------------------------------

[[sql-functions-math-expm1]]
==== `EXPM1`

.Synopsis:
[source, sql]
--------------------------------------------------
EXPM1(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> float numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#expm1-double-[Euler's number at the power] of `numeric_exp` minus 1 (e^numeric_exp^ - 1).

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathExpm1Inline]
--------------------------------------------------

[[sql-functions-math-floor]]
==== `FLOOR`

.Synopsis:
[source, sql]
--------------------------------------------------
FLOOR(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: integer or long numeric value

*Description*: Returns the largest integer less than or equal to `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineFloor]
--------------------------------------------------

[[sql-functions-math-log]]
==== `LOG`

.Synopsis:
[source, sql]
--------------------------------------------------
LOG(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Natural_logarithm[natural logarithm] of `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineLog]
--------------------------------------------------

[[sql-functions-math-log10]]
==== `LOG10`

.Synopsis:
[source, sql]
--------------------------------------------------
LOG10(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Common_logarithm[base 10 logarithm] of `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineLog10]
--------------------------------------------------

[[sql-functions-math-pi]]
==== `PI`

.Synopsis:
[source, sql]
--------------------------------------------------
PI()
--------------------------------------------------

*Input*: _none_

*Output*: `3.141592653589793`

*Description*: Returns {wikipedia}/Pi[PI number].

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathPINumber]
--------------------------------------------------

[[sql-functions-math-power]]
==== `POWER`

.Synopsis:
[source, sql]
--------------------------------------------------
POWER(
    numeric_exp, <1>
    integer_exp) <2>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.
<2> integer expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the value of `numeric_exp` to the power of `integer_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlinePowerPositive]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlinePowerNegative]
--------------------------------------------------

[[sql-functions-math-random]]
==== `RANDOM/RAND`

.Synopsis:
[source, sql]
--------------------------------------------------
RANDOM(seed) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns a random double using the given seed.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathRandom]
--------------------------------------------------

[[sql-functions-math-round]]
==== `ROUND`

.Synopsis:
[source, sql]
----
ROUND(
    numeric_exp      <1>
    [, integer_exp]) <2>
----
*Input*:

<1> numeric expression. If `null`, the function returns `null`.
<2> integer expression; optional. If `null`, the function returns `null`.

*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`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathRoundWithPositiveParameter]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathRoundWithNegativeParameter]
--------------------------------------------------

[[sql-functions-math-sign]]
==== `SIGN/SIGNUM`

.Synopsis:
[source, sql]
--------------------------------------------------
SIGN(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*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.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineSign]
--------------------------------------------------


[[sql-functions-math-sqrt]]
==== `SQRT`

.Synopsis:
[source, sql]
--------------------------------------------------
SQRT(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns {wikipedia}/Square_root[square root] of `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineSqrt]
--------------------------------------------------

[[sql-functions-math-truncate]]
==== `TRUNCATE/TRUNC`

.Synopsis:
[source, sql]
----
TRUNCATE(
    numeric_exp      <1>
    [, integer_exp]) <2>
----
*Input*:

<1> numeric expression. If `null`, the function returns `null`.
<2> integer expression; optional. If `null`, the function returns `null`.

*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`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathTruncateWithPositiveParameter]
--------------------------------------------------

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathTruncateWithNegativeParameter]
--------------------------------------------------

[[sql-functions-math-trigonometric]]
[discrete]
=== Trigonometric

[[sql-functions-math-acos]]
==== `ACOS`

.Synopsis:
[source, sql]
--------------------------------------------------
ACOS(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Inverse_trigonometric_functions[arccosine] of `numeric_exp` as an angle, expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineAcos]
--------------------------------------------------

[[sql-functions-math-asin]]
==== `ASIN`

.Synopsis:
[source, sql]
--------------------------------------------------
ASIN(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Inverse_trigonometric_functions[arcsine] of `numeric_exp` as an angle, expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineAsin]
--------------------------------------------------

[[sql-functions-math-atan]]
==== `ATAN`

.Synopsis:
[source, sql]
--------------------------------------------------
ATAN(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Inverse_trigonometric_functions[arctangent] of `numeric_exp` as an angle, expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineAtan]
--------------------------------------------------

[[sql-functions-math-atan2]]
==== `ATAN2`

.Synopsis:
[source, sql]
--------------------------------------------------
ATAN2(
    ordinate, <1>
    abscisa)  <2>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.
<2> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Atan2[arctangent of the `ordinate` and `abscisa` coordinates] specified as an angle, expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineAtan2]
--------------------------------------------------

[[sql-functions-math-cos]]
==== `COS`

.Synopsis:
[source, sql]
--------------------------------------------------
COS(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Trigonometric_functions#cosine[cosine] of `numeric_exp`, where `numeric_exp` is an angle expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineCosine]
--------------------------------------------------

[[sql-functions-math-cosh]]
==== `COSH`

.Synopsis:
[source, sql]
--------------------------------------------------
COSH(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Hyperbolic_function[hyperbolic cosine] of `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineCosh]
--------------------------------------------------

[[sql-functions-math-cot]]
==== `COT`

.Synopsis:
[source, sql]
--------------------------------------------------
COT(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Trigonometric_functions#Cosecant,_secant,_and_cotangent[cotangent] of `numeric_exp`, where `numeric_exp` is an angle expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineCotangent]
--------------------------------------------------

[[sql-functions-math-degrees]]
==== `DEGREES`

.Synopsis:
[source, sql]
--------------------------------------------------
DEGREES(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Convert from {wikipedia}/Radian[radians]
to {wikipedia}/Degree_(angle)[degrees].

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineDegrees]
--------------------------------------------------

[[sql-functions-math-radians]]
==== `RADIANS`

.Synopsis:
[source, sql]
--------------------------------------------------
RADIANS(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Convert from {wikipedia}/Degree_(angle)[degrees]
to {wikipedia}/Radian[radians].

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineRadians]
--------------------------------------------------

[[sql-functions-math-sin]]
==== `SIN`

.Synopsis:
[source, sql]
--------------------------------------------------
SIN(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Trigonometric_functions#sine[sine] of `numeric_exp`, where `numeric_exp` is an angle expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineSine]
--------------------------------------------------

[[sql-functions-math-sinh]]
==== `SINH`

.Synopsis:
[source, sql]
--------------------------------------------------
SINH(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Hyperbolic_function[hyperbolic sine] of `numeric_exp`.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineSinh]
--------------------------------------------------

[[sql-functions-math-tan]]
==== `TAN`

.Synopsis:
[source, sql]
--------------------------------------------------
TAN(numeric_exp) <1>
--------------------------------------------------

*Input*:

<1> numeric expression. If `null`, the function returns `null`.

*Output*: double numeric value

*Description*: Returns the {wikipedia}/Trigonometric_functions#tangent[tangent] of `numeric_exp`, where `numeric_exp` is an angle expressed in radians.

["source","sql",subs="attributes,macros"]
--------------------------------------------------
include-tagged::{sql-specs}/docs/docs.csv-spec[mathInlineTanget]
--------------------------------------------------