Mathematical Functions in Standard SQL

All mathematical functions have the following behaviors:

• They return NULL if any of the input parameters is NULL.
• They return NaN if any of the arguments is NaN.

ABS

ABS(X)

Description

Computes absolute value. Returns an error if the argument is an integer and the output value cannot be represented as the same type; this happens only for the largest negative input value, which has no positive representation. Returns +inf for a +/-inf argument.

SIGN

SIGN(X)

Description

Returns -1, 0, or +1 for negative, zero and positive arguments respectively. For floating point arguments, this function does not distinguish between positive and negative zero. Returns NaN for a NaN argument.

IS_INF

IS_INF(X)

Description

Returns TRUE if the value is positive or negative infinity. Returns NULL for NULL inputs.

IS_NAN

IS_NAN(X)

Description

Returns TRUE if the value is a NaN value. Returns NULL for NULL inputs.

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

Description

Divides X by Y; this function never fails. Returns FLOAT64. Unlike the division operator (/), this function does not generate errors for division by zero or overflow.

Special cases:

• If the result overflows, returns +/-inf.
• If Y=0 and X=0, returns NaN.
• If Y=0 and X!=0, returns +/-inf.
• If X = +/-inf and Y = +/-inf, returns NaN.

The behavior of IEEE_DIVIDE is further illustrated in the table below.

Special cases for IEEE_DIVIDE

The following table lists special cases for IEEE_DIVIDE.

Numerator Data Type (X)	Denominator Data Type (Y)	Result Value
Anything except 0	0	+/-inf
0	0	NaN
0	NaN	NaN
NaN	0	NaN
+/-inf	+/-inf	NaN

RAND

RAND()

Description

Generates a pseudo-random value of type FLOAT64 in the range of [0, 1), inclusive of 0 and exclusive of 1.

SQRT

SQRT(X)

Description

Computes the square root of X. Generates an error if X is less than 0. Returns +inf if X is +inf.

POW

POW(X, Y)

Description

Returns the value of X raised to the power of Y. If the result underflows and is not representable, then the function returns a value of zero. Returns an error if one of the following is true:

• X is a finite value less than 0 and Y is a noninteger
• X is 0 and Y is a finite value less than 0

The behavior of POW() is further illustrated in the table below.

POWER

POWER(X, Y)

Description

Synonym of POW().

Special cases for POW(X, Y) and POWER(X, Y)

The following are special cases for POW(X, Y) and POWER(X, Y).

X	Y	POW(X, Y) or POWER(X, Y)
1.0	Any value including NaN	1.0
any including NaN	0	1.0
-1.0	+/-inf	1.0
ABS(X) < 1	-inf	+inf
ABS(X) > 1	-inf	0
ABS(X) < 1	+inf	0
ABS(X) > 1	+inf	+inf
-inf	Y < 0	0
-inf	Y > 0	-inf if Y is an odd integer, +inf otherwise
+inf	Y < 0	0
+inf	Y > 0	+inf

EXP

EXP(X)

Description

Computes e to the power of X, also called the natural exponential function. If the result underflows, this function returns a zero. Generates an error if the result overflows. If X is +/-inf, then this function returns +inf or 0.

LN

LN(X)

Description

Computes the natural logarithm of X. Generates an error if X is less than or equal to zero. If X is +inf, then this function returns +inf.

LOG

LOG(X [, Y])

Description

If only X is present, LOG is a synonym of LN. If Y is also present, LOG computes the logarithm of X to base Y. Generates an error in these cases:

• X is less than or equal to zero
• Y is 1.0
• Y is less than or equal to zero.

The behavior of LOG(X, Y) is further illustrated in the table below.

Special cases for LOG(X, Y)

X	Y	LOG(X, Y)
-inf	Any value	NaN
Any value	+inf	NaN
+inf	0.0 Y < 1.0	-inf
+inf	Y > 1.0	+inf

LOG10

LOG10(X)

Description

Similar to LOG, but computes logarithm to base 10.

GREATEST

GREATEST(X1,...,XN)

Description

Returns NULL if any of the inputs is NULL. Otherwise, returns NaN if any of the inputs is NaN. Otherwise, returns the largest value among X1,...,XN according to the < comparison.

LEAST

LEAST(X1,...,XN)

Description

Returns NULL if any of the inputs is NULL. Returns NaN if any of the inputs is NaN. Otherwise, returns the smallest value among X1,...,XN according to the > comparison.

DIV

DIV(X, Y)

Description

Returns the result of integer division of X by Y. Division by zero returns an error. Division by -1 may overflow. See the table below for possible result types.

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Description

Equivalent to the division operator (/), but returns NULL if an error occurs, such as a division by zero error.

MOD

MOD(X, Y)

Description

Modulo function: returns the remainder of the division of X by Y. Returned value has the same sign as X. An error is generated if Y is 0. See the table below for possible result types.

ROUND

ROUND(X [, N])

Description

If only X is present, ROUND rounds X to the nearest integer. If N is present, ROUND rounds X to N decimal places after the decimal point. If N is negative, ROUND will round off digits to the left of the decimal point. Rounds halfway cases away from zero. Generates an error if overflow occurs.

TRUNC

TRUNC(X [, N])

Description

If only X is present, TRUNC rounds X to the nearest integer whose absolute value is not greater than the absolute value of X. If N is also present, TRUNC behaves like ROUND(X, N), but always rounds towards zero and never overflows.

CEIL

CEIL(X)

Description

Returns the smallest integral value (with FLOAT64 type) that is not less than X.

CEILING

CEILING(X)

Description

Synonym of CEIL(X)

FLOOR

FLOOR(X)

Description

Returns the largest integral value (with FLOAT64 type) that is not greater than X.

Example rounding function behavior

Example behavior of BigQuery rounding functions:

Input "X"	ROUND(X)	TRUNC(X)	CEIL(X)	FLOOR(X)
2.0	2.0	2.0	2.0	2.0
2.3	2.0	2.0	3.0	2.0
2.8	3.0	2.0	3.0	2.0
2.5	3.0	2.0	3.0	2.0
-2.3	-2.0	-2.0	-2.0	-3.0
-2.8	-3.0	-2.0	-2.0	-3.0
-2.5	-3.0	-2.0	-2.0	-3.0
0	0	0	0	0
+/-inf	+/-inf	+/-inf	+/-inf	+/-inf
NaN	NaN	NaN	NaN	NaN

COS

COS(X)

Description

Computes cosine of X. Never fails.

COSH

COSH(X)

Description

Computes the hyperbolic cosine of X. Generates an error if an overflow occurs.

ACOS

ACOS(X)

Description

Computes the principal value of the arc cosine of X. The return value is in the range [0,]. Generates an error if X is a finite value outside of range [-1, 1].

ACOSH

ACOSH(X)

Description

Computes the inverse hyperbolic cosine of X. Generates an error if X is a finite value less than 1.

SIN

SIN(X)

Description

Computes the sine of X. Never fails.

SINH

SINH(X)

Description

Computes the hyperbolic sine of X. Generates an error if an overflow occurs.

ASIN

ASIN(X)

Description

Computes the principal value of the arc sine of X. The return value is in the range [-π/2,π/2]. Generates an error if X is a finite value outside of range [-1, 1].

ASINH

ASINH(X)

Description

Computes the inverse hyperbolic sine of X. Does not fail.

TAN

TAN(X)

Description

Computes tangent of X. Generates an error if an overflow occurs.

TANH

TANH(X)

Description

Computes hyperbolic tangent of X. Does not fail.

ATAN

ATAN(X)

Description

Computes the principal value of the arc tangent of X. The return value is in the range [-π/2,π/2]. Does not fail.

ATANH

ATANH(X)

Description

Computes the inverse hyperbolic tangent of X. Generates an error if the absolute value of X is greater or equal 1.

ATAN2

ATAN2(Y, X)

Description

Calculates the principal value of the arc tangent of Y/X using the signs of the two arguments to determine the quadrant. The return value is in the range [-π,π]. The behavior of this function is further illustrated in the table below.

Special cases for ATAN2()

Y	X	ATAN2(Y, X)
NaN	Any value	NaN
Any value	NaN	NaN
0	0	0, π or -π depending on the sign of X and Y
Finite value	-inf	π or -π depending on the sign of Y
Finite value	+inf	0
+/-inf	Finite value	π/2 or π/2 depending on the sign of Y
+/-inf	-inf	¾π or -¾π depending on the sign of Y
+/-inf	+inf	π/4 or -π/4 depending on the sign of Y

Special cases for trigonometric and hyperbolic rounding functions

X	COS(X)	COSH(X)	ACOS(X)	ACOSH(X)	SIN(X)	SINH(X)	ASIN(X)	ASINH(X)	TAN(X)	TANH(X)	ATAN(X)	ATANH(X)
+/-inf	NaN	=+inf	NaN	=+inf	NaN	=+inf	NaN	=+inf	NaN	=+1.0	π/2	NaN
-inf	NaN	=+inf	NaN	NaN	NaN	-inf	NaN	-inf	NaN	-1.0	-π/2	NaN
NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN