We've reorganized our documentation navigation structure to align directly with your operational workflows. See the release notes and the walkthrough video for more information.

Mathematical functions

GoogleSQL for SecOps supports mathematical functions. 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.

Category	Functions
Trigonometric	`ACOS` `ACOSH` `ASIN` `ASINH` `ATAN` `ATAN2` `ATANH` `COS` `COSH` `SIN` `SINH` `TAN` `TANH`
Exponential and logarithmic	`EXP` `LN` `LOG` `LOG10`
Rounding and truncation	`CEIL` `CEILING` `FLOOR` `ROUND` `TRUNC`
Power and root	`CBRT` `POW` `POWER` `SQRT`
Sign	`ABS` `SIGN`
Comparison	`GREATEST` `LEAST`
Random number generator	`RAND`
Arithmetic and error handling	`DIV` `IEEE_DIVIDE` `IS_INF` `IS_NAN` `MOD`
Bucket	`RANGE_BUCKET`

Function list

Name	Summary
`ABS`	Computes the absolute value of `X`.
`ACOS`	Computes the inverse cosine of `X`.
`ACOSH`	Computes the inverse hyperbolic cosine of `X`.
`ASIN`	Computes the inverse sine of `X`.
`ASINH`	Computes the inverse hyperbolic sine of `X`.
`ATAN`	Computes the inverse tangent of `X`.
`ATAN2`	Computes the inverse tangent of `X/Y`, using the signs of `X` and `Y` to determine the quadrant.
`ATANH`	Computes the inverse hyperbolic tangent of `X`.
`AVG`	Gets the average of non-`NULL` values. For more information, see Aggregate functions.
`CBRT`	Computes the cube root of `X`.
`CEIL`	Gets the smallest integral value that isn't less than `X`.
`CEILING`	Synonym of `CEIL`.
`COS`	Computes the cosine of `X`.
`COSH`	Computes the hyperbolic cosine of `X`.
`DIV`	Divides integer `X` by integer `Y`.
`EXP`	Computes `e` to the power of `X`.
`FLOOR`	Gets the largest integral value that isn't greater than `X`.
`GREATEST`	Gets the greatest value among `X1,...,XN`.
`IEEE_DIVIDE`	Divides `X` by `Y`, but doesn't generate errors for division by zero or overflow.
`IS_INF`	Checks if `X` is positive or negative infinity.
`IS_NAN`	Checks if `X` is a `NaN` value.
`LEAST`	Gets the least value among `X1,...,XN`.
`LN`	Computes the natural logarithm of `X`.
`LOG`	Computes the natural logarithm of `X` or the logarithm of `X` to base `Y`.
`LOG10`	Computes the natural logarithm of `X` to base 10.
`MAX`	Gets the maximum non-`NULL` value. For more information, see Aggregate functions.
`MOD`	Gets the remainder of the division of `X` by `Y`.
`POW`	Produces the value of `X` raised to the power of `Y`.
`POWER`	Synonym of `POW`.
`RAND`	Generates a pseudo-random value of type `FLOAT64` in the range of `[0, 1)`.
`RANGE_BUCKET`	Scans through a sorted array and returns the 0-based position of a point's upper bound.
`ROUND`	Rounds `X` to the nearest integer or rounds `X` to `N` decimal places after the decimal point.
`SIGN`	Produces -1 , 0, or +1 for negative, zero, and positive arguments respectively.
`SIN`	Computes the sine of `X`.
`SINH`	Computes the hyperbolic sine of `X`.
`SQRT`	Computes the square root of `X`.
`SUM`	Gets the sum of non-`NULL` values. For more information, see Aggregate functions.
`TAN`	Computes the tangent of `X`.
`TANH`	Computes the hyperbolic tangent of `X`.
`TRUNC`	Rounds a number like `ROUND(X)` or `ROUND(X, N)`, but always rounds towards zero and never overflows.

`ABS`

ABS(X)

Description

Computes absolute value. Returns an error if the argument is an integer and the output value can't 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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`INT64`	`NUMERIC`	`FLOAT64`

`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

`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. Doesn't fail.

X	ASINH(X)
`+inf`	`+inf`
`-inf`	`-inf`
`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]. Doesn't fail.

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

`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

`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

`CBRT`

CBRT(X)

Description

Computes the cube root of X. X can be any data type that coerces to FLOAT64. Supports the SAFE. prefix.

X	CBRT(X)
`+inf`	`inf`
`-inf`	`-inf`
`NaN`	`NaN`
`0`	`0`
`NULL`	`NULL`

Return Data Type

FLOAT64

Example

SELECT CBRT(27) AS cube_root;

/*--------------------+
 | cube_root          |
 +--------------------+
 | 3.0000000000000004 |
 +--------------------*/

`CEIL`

CEIL(X)

Description

Returns the smallest integral value that isn't 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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

`CEILING`

CEILING(X)

Description

Synonym of CEIL(X)

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

`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
12	-7	-1
20	3	6
0	20	0
20	0	Error

Return Data Type

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

INPUT	`INT64`	`NUMERIC`
`INT64`	`INT64`	`NUMERIC`
`NUMERIC`	`NUMERIC`	`NUMERIC`

`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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

`FLOOR`

FLOOR(X)

Description

Returns the largest integral value that isn't 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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

`GREATEST`

GREATEST(X1,...,XN)

Description

Returns the greatest value among X1,...,XN. If any argument is NULL, returns NULL. Otherwise, in the case of floating-point arguments, if any argument is NaN, returns NaN. In all other cases, returns the value among X1,...,XN that has the greatest value according to the ordering used by the ORDER BY clause. The arguments X1, ..., XN must be coercible to a common supertype, and the supertype must support ordering.

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

Return Data Types

Data type of the input values.

`IEEE_DIVIDE`

IEEE_DIVIDE(X, Y)

Description

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

X	Y	IEEE_DIVIDE(X, Y)
20.0	4.0	5.0
20.0	6.0	3.3333333333333335
0.0	25.0	0.0
25.0	0.0	`+inf`
-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`

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

`LEAST`

LEAST(X1,...,XN)

Description

Returns the least value among X1,...,XN. If any argument is NULL, returns NULL. Otherwise, in the case of floating-point arguments, if any argument is NaN, returns NaN. In all other cases, returns the value among X1,...,XN that has the least value according to the ordering used by the ORDER BY clause. The arguments X1, ..., XN must be coercible to a common supertype, and the supertype must support ordering.

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

Return Data Types

Data type of the input values.

`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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

`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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
`INT64`	`FLOAT64`	`NUMERIC`	`FLOAT64`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`FLOAT64`
`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`

`LOG10`

LOG10(X)

Description

Similar to LOG, but computes logarithm to base 10.

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

Return Data Type

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

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

INPUT	`INT64`	`NUMERIC`
`INT64`	`INT64`	`NUMERIC`
`NUMERIC`	`NUMERIC`	`NUMERIC`

`POW`

POW(X, Y)

Description

Returns the value of X raised to the power of Y. If the result underflows and isn't 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.

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
`INT64`	`FLOAT64`	`NUMERIC`	`FLOAT64`
`NUMERIC`	`NUMERIC`	`NUMERIC`	`FLOAT64`
`FLOAT64`	`FLOAT64`	`FLOAT64`	`FLOAT64`

`POWER`

POWER(X, Y)

Description

Synonym of POW(X, Y).

`RAND`

RAND()

Description

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

`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 doesn't 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 isn't 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     |
 +--------------+-------*/

`ROUND`

ROUND(X [, N])

Description

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

Expression	Return Value
`ROUND(2.0)`	2.0
`ROUND(2.3)`	2.0
`ROUND(2.8)`	3.0
`ROUND(2.5)`	3.0
`ROUND(-2.3)`	-2.0
`ROUND(-2.8)`	-3.0
`ROUND(-2.5)`	-3.0
`ROUND(0)`	0
`ROUND(+inf)`	`+inf`
`ROUND(-inf)`	`-inf`
`ROUND(NaN)`	`NaN`
`ROUND(123.7, -1)`	120.0
`ROUND(1.235, 2)`	1.24

Return Data Type

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

`SIGN`

SIGN(X)

Description

Returns -1, 0, or +1 for negative, zero and positive arguments respectively. For floating point arguments, this function doesn't distinguish between positive and negative zero.

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

Return Data Type

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`INT64`	`NUMERIC`	`FLOAT64`

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

`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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

`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. Doesn't fail.

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

`TRUNC`

TRUNC(X [, N])

Description

If only X is present, TRUNC rounds X to the nearest integer whose absolute value isn't 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

INPUT	`INT64`	`NUMERIC`	`FLOAT64`
OUTPUT	`FLOAT64`	`NUMERIC`	`FLOAT64`

Mathematical functions Stay organized with collections Save and categorize content based on your preferences.

Categories

Function list

ABS

ACOS

ACOSH

ASIN

ASINH

ATAN

ATAN2

ATANH

CBRT

CEIL

CEILING

COS

COSH

DIV

EXP

FLOOR

GREATEST

IEEE_DIVIDE

IS_INF

IS_NAN

LEAST

LN

LOG

LOG10

MOD

POW

POWER

RAND

RANGE_BUCKET

ROUND

SIGN

SIN

SINH

SQRT

TAN

TANH

TRUNC

Mathematical functions

`ABS`

`ACOS`

`ACOSH`

`ASIN`

`ASINH`

`ATAN`

`ATAN2`

`ATANH`

`CBRT`

`CEIL`

`CEILING`

`COS`

`COSH`

`DIV`

`EXP`

`FLOOR`

`GREATEST`

`IEEE_DIVIDE`

`IS_INF`

`IS_NAN`

`LEAST`

`LN`

`LOG`

`LOG10`

`MOD`

`POW`

`POWER`

`RAND`

`RANGE_BUCKET`

`ROUND`

`SIGN`

`SIN`

`SINH`

`SQRT`

`TAN`

`TANH`

`TRUNC`