NOTE: You are looking at documentation for an older release. For the latest information, see the current release documentation.
Mathematical Functions
editMathematical Functions
editAll math and trigonometric functions require their input (where applicable) to be numeric.
Generic
editABS
editSynopsis:
Input:
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
editSynopsis:
Input:
Output: double numeric value
Description:Returns the cube root of numeric_exp
.
SELECT CBRT(-125.5); CBRT(-125.5) ------------------- -5.0066577974783435
CEIL/CEILING
editSynopsis:
Input:
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
editSynopsis:
E()
Input: none
Output: 2.718281828459045
Description:Returns Euler’s number.
SELECT E(), CEIL(E()); E() | CEIL(E()) -----------------+--------------- 2.718281828459045|3
EXP
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
PI()
Input: none
Output: 3.141592653589793
Description:Returns PI number.
SELECT PI(); PI() ----------------- 3.141592653589793
POWER
editSynopsis:
Input:
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
editSynopsis:
Input:
Output: double numeric value
Description:Returns a random double using the given seed.
SELECT RANDOM(123); RANDOM(123) ------------------ 0.7231742029971469
ROUND
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editACOS
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
Output: double numeric value
Description:Convert from degrees to radians.
SELECT RADIANS(90), PI()/2; RADIANS(90) | PI()/2 ------------------+------------------ 1.5707963267948966|1.5707963267948966
SIN
editSynopsis:
Input:
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
editSynopsis:
Input:
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
editSynopsis:
Input:
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