Math Functionsedit

Warning

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.

Genericedit

ABSedit

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

CBRTedit

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

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

Synopsis: 

E()

Input: none

Output: 2.718281828459045

Description: Returns Euler’s number.

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

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

EXPedit

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

EXPM1edit

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

FLOORedit

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

LOGedit

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

LOG10edit

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

Synopsis: 

PI()

Input: none

Output: 3.141592653589793

Description: Returns PI number.

SELECT PI();

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

POWERedit

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

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

ROUNDedit

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

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

SQRTedit

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

TRUNCATEedit

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

ACOSedit

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

ASINedit

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

ATANedit

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

ATAN2edit

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

COSedit

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

COSHedit

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

COTedit

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

DEGREESedit

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

RADIANSedit

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

SINedit

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

SINHedit

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

TANedit

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