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

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

Return Data Type

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

### IS_INF

``````IS_INF(X)
``````

Description

Returns `TRUE` if the value is positive or negative infinity.

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.

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`

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

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

Return Data Type

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

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

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

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

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.

INPUTINT64NUMERICBIGNUMERIC
INT64INT64NUMERICBIGNUMERIC
NUMERICNUMERICNUMERICBIGNUMERIC
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERIC

### 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
020`0`
200`NULL`

Return Data Type

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64FLOAT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTINT64NUMERICBIGNUMERICFLOAT64

``````SAFE_ADD(X, Y)
``````

Description

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

549

Return Data Type

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
INT64INT64NUMERICBIGNUMERICFLOAT64
NUMERICNUMERICNUMERICBIGNUMERICFLOAT64
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64FLOAT64

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

INPUTINT64NUMERICBIGNUMERIC
INT64INT64NUMERICBIGNUMERIC
NUMERICNUMERICNUMERICBIGNUMERIC
BIGNUMERICBIGNUMERICBIGNUMERICBIGNUMERIC

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

### 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

INPUTINT64NUMERICBIGNUMERICFLOAT64
OUTPUTFLOAT64NUMERICBIGNUMERICFLOAT64

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

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

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.

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.

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

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.

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.

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.

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

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 [-π,π].

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

### RANGE_BUCKET

``````RANGE_BUCKET(point, boundaries_array)
``````

Description

`RANGE_BUCKET` scans through a sorted array and returns the 0-based position of the point's upper bound. This can be useful if you need to group your data to build partitions, histograms, business-defined rules, and more.

`RANGE_BUCKET` follows these rules:

• If the point exists in the array, returns the index of the next larger value.

``````RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value
RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
``````
• If the point does not exist in the array, but it falls between two values, returns the index of the larger value.

``````RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
``````
• If the point is smaller than the first value in the array, returns 0.

``````RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
``````
• If the point is greater than or equal to the last value in the array, returns the length of the array.

``````RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
``````
• If the array is empty, returns 0.

``````RANGE_BUCKET(80, []) -- 0 is return value
``````
• If the point is `NULL` or `NaN`, returns `NULL`.

``````RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
``````
• The data type for the point and array must be compatible.

``````RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value
RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value
RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
``````

Execution failure occurs when:

• The array has a `NaN` or `NULL` value in it.

``````RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
``````
• The array is not sorted in ascending order.

``````RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
``````

Parameters

• `point`: A generic value.
• `boundaries_array`: A generic array of values.

Return Value

`INT64`

Examples

In a table called `students`, check to see how many records would exist in each `age_group` bucket, based on a student's age:

• age_group 0 (age < 10)
• age_group 1 (age >= 10, age < 20)
• age_group 2 (age >= 20, age < 30)
• age_group 3 (age >= 30)
``````WITH students AS
(
SELECT 9 AS age UNION ALL
SELECT 20 AS age UNION ALL
SELECT 25 AS age UNION ALL
SELECT 31 AS age UNION ALL
SELECT 32 AS age UNION ALL
SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

+--------------+-------+
| age_group    | count |
+--------------+-------+
| 0            | 1     |
| 2            | 2     |
| 3            | 3     |
+--------------+-------+
``````
[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"難以理解" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"資訊或程式碼範例有誤" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"缺少我需要的資訊/範例" },{ "type": "thumb-down", "id": "otherDown", "label":"其他" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"容易理解" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"確實解決了我的問題" },{ "type": "thumb-up", "id": "otherUp", "label":"其他" }]