Mathematical functions in Google 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.

X ABS(X)
25 25
-25 25
+inf +inf
-inf +inf

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

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.

X SIGN(X)
25 +1
0 0
-25 -1
NaN NaN

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

IS_INF

IS_INF(X)

Description

Returns TRUE if the value is positive or negative infinity.

Returns FALSE for NUMERIC inputs since NUMERIC cannot be INF.

X IS_INF(X)
+inf TRUE
-inf TRUE
25 FALSE

IS_NAN

IS_NAN(X)

Description

Returns TRUE if the value is a NaN value.

Returns FALSE for NUMERIC inputs since NUMERIC cannot be NaN.

X IS_NAN(X)
NaN TRUE
25 FALSE

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.

X Y IEEE_DIVIDE(X, Y)
20.0 4.0 5.0
0.0 25.0 0.0
25.0 0.0 +inf
-25.0 0.0 -inf
0.0 0.0 NaN
0.0 NaN NaN
NaN 0.0 NaN
+inf +inf NaN
-inf -inf NaN

SQRT

SQRT(X)

Description

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

X SQRT(X)
25.0 5.0
+inf +inf
X < 0 Error

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

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.

X Y POW(X, Y)
2.0 3.0 8.0
1.0 Any value including NaN 1.0
Any value including NaN 0 1.0
-1.0 +inf 1.0
-1.0 -inf 1.0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0.0
ABS(X) < 1 +inf 0.0
ABS(X) > 1 +inf +inf
-inf Y < 0 0.0
-inf Y > 0 -inf if Y is an odd integer, +inf otherwise
+inf Y < 0 0
+inf Y > 0 +inf
Finite value < 0 Non-integer Error
0 Finite value < 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

POWER

POWER(X, Y)

Description

Synonym of POW(X, Y).

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.

X EXP(X)
0.0 1.0
+inf +inf
-inf 0.0

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

LN

LN(X)

Description

Computes the natural logarithm of X. Generates an error if X is less than or equal to zero.

X LN(X)
1.0 0.0
+inf +inf
X < 0 Error

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

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.

X Y LOG(X, Y)
100.0 10.0 2.0
-inf Any value NaN
Any value +inf NaN
+inf 0.0 < Y < 1.0 -inf
+inf Y > 1.0 +inf
X <= 0 Any value Error
Any value Y <= 0 Error
Any value 1.0 Error

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

LOG10

LOG10(X)

Description

Similar to LOG, but computes logarithm to base 10.

X LOG10(X)
100.0 2.0
-inf NaN
+inf NaN
X <= 0 Error

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

GREATEST

GREATEST(X1,...,XN)

Description

Returns the largest value among X1,...,XN according to the < comparison. If any parts of X1,...,XN are NULL, the return value is NULL.

X1,...,XN GREATEST(X1,...,XN)
3,5,1 5

Return Data Types

Data type of the input values.

LEAST

LEAST(X1,...,XN)

Description

Returns the smallest value among X1,...,XN according to the > comparison. If any parts of X1,...,XN are NULL, the return value is NULL.

X1,...,XN LEAST(X1,...,XN)
3,5,1 1

Return Data Types

Data type of the input values.

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. If both inputs are NUMERIC and the result is overflow, then it returns a numeric overflow error.

X Y DIV(X, Y)
20 4 5
0 20 0
20 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

Description

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

XYSAFE_DIVIDE(X, Y)
2045
0200
200NULL

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

Description

Equivalent to the multiplication operator (*), but returns NULL if overflow occurs.

XYSAFE_MULTIPLY(X, Y)
20480

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_NEGATE

SAFE_NEGATE(X)

Description

Equivalent to the unary minus operator (-), but returns NULL if overflow occurs.

XSAFE_NEGATE(X)
+1-1
-1+1
00

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTINT64NUMERICFLOAT64

Description

Equivalent to the addition operator (+), but returns NULL if overflow occurs.

549

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

Description

Returns the result of Y subtracted from X. Equivalent to the subtraction operator (-), but returns NULL if overflow occurs.

XYSAFE_SUBTRACT(X, Y)
541

Return Data Type

INPUTINT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

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.

X Y MOD(X, Y)
25 12 1
25 0 Error

Return Data Type

The return data type is determined by the argument types with the following table.

INPUTINT64NUMERIC
INT64INT64NUMERIC
NUMERICNUMERICNUMERIC

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.

X ROUND(X)
2.0 2.0
2.3 2.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -3.0
-2.5 -3.0
0 0
+inf +inf
-inf -inf
NaN NaN

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

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.

X TRUNC(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
+inf +inf
-inf -inf
NaN NaN

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

CEIL

CEIL(X)

Description

Returns the smallest integral value that is not less than X.

X CEIL(X)
2.0 2.0
2.3 3.0
2.8 3.0
2.5 3.0
-2.3 -2.0
-2.8 -2.0
-2.5 -2.0
0 0
+inf +inf
-inf -inf
NaN NaN

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

CEILING

CEILING(X)

Description

Synonym of CEIL(X)

FLOOR

FLOOR(X)

Description

Returns the largest integral value that is not greater than X.

X FLOOR(X)
2.0 2.0
2.3 2.0
2.8 2.0
2.5 2.0
-2.3 -3.0
-2.8 -3.0
-2.5 -3.0
0 0
+inf +inf
-inf -inf
NaN NaN

Return Data Type

INPUTINT64NUMERICFLOAT64
OUTPUTFLOAT64NUMERICFLOAT64

COS

COS(X)

Description

Computes the cosine of X where X is specified in radians. Never fails.

X COS(X)
+inf NaN
-inf NaN
NaN NaN

COSH

COSH(X)

Description

Computes the hyperbolic cosine of X where X is specified in radians. Generates an error if overflow occurs.

If X is NUMERIC then, the output is FLOAT64.

X COSH(X)
+inf +inf
-inf +inf
NaN NaN

ACOS

ACOS(X)

Description

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

If X is NUMERIC then, the output is FLOAT64.

X ACOS(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Error
X > 1 Error

ACOSH

ACOSH(X)

Description

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

If X is NUMERIC then, the output is FLOAT64.

X ACOSH(X)
+inf +inf
-inf NaN
NaN NaN
X < 1 Error

SIN

SIN(X)

Description

Computes the sine of X where X is specified in radians. Never fails.

X SIN(X)
+inf NaN
-inf NaN
NaN NaN

SINH

SINH(X)

Description

Computes the hyperbolic sine of X where X is specified in radians. Generates an error if overflow occurs.

If X is NUMERIC then, the output is FLOAT64.

X SINH(X)
+inf +inf
-inf -inf
NaN NaN

ASIN

ASIN(X)

Description

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

If X is NUMERIC then, the output is FLOAT64.

X ASIN(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Error
X > 1 Error

ASINH

ASINH(X)

Description

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

If X is NUMERIC then, the output is FLOAT64.

X ASINH(X)
+inf +inf
-inf -inf
NaN NaN

TAN

TAN(X)

Description

Computes the tangent of X where X is specified in radians. Generates an error if overflow occurs.

X TAN(X)
+inf NaN
-inf NaN
NaN NaN

TANH

TANH(X)

Description

Computes the hyperbolic tangent of X where X is specified in radians. Does not fail.

If X is NUMERIC then, the output is FLOAT64.

X TANH(X)
+inf 1.0
-inf -1.0
NaN NaN

ATAN

ATAN(X)

Description

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

If X is NUMERIC then, the output is FLOAT64.

X ATAN(X)
+inf π/2
-inf -π/2
NaN NaN

ATANH

ATANH(X)

Description

Computes the inverse hyperbolic tangent of X. Generates an error if X is outside of the range [-1, 1].

If X is NUMERIC then, the output is FLOAT64.

X ATANH(X)
+inf NaN
-inf NaN
NaN NaN
X < -1 Error
X > 1 Error

ATAN2

ATAN2(X, Y)

Description

Calculates the principal value of the inverse tangent of X/Y using the signs of the two arguments to determine the quadrant. The return value is in the range [-π,π].

If Y is NUMERIC then, the output is FLOAT64.

X Y ATAN2(X, Y)
NaN Any value NaN
Any value NaN NaN
0.0 0.0 0.0
Positive Finite value -inf π
Negative Finite value -inf
Finite value +inf 0.0
+inf Finite value π/2
-inf Finite value -π/2
+inf -inf ¾π
-inf -inf -¾π
+inf +inf π/4
-inf +inf -π/4
[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]