Google Standard SQL for BigQuery supports mathematical functions. All mathematical functions have the following behaviors:
- They return
NULL
if any of the input parameters isNULL
. - They return
NaN
if any of the arguments isNaN
.
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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.
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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 | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | 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 | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
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 | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | 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 |
This function supports specifying collation.
Return Data Types
Data type of the input values.
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 |
This function supports specifying collation.
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 |
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 | BIGNUMERIC |
---|---|---|---|
INT64 | INT64 | NUMERIC | BIGNUMERIC |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC |
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.
X | Y | SAFE_DIVIDE(X, Y) |
---|---|---|
20 | 4 | 5 |
0 | 20 | 0 |
20 | 0 | NULL |
Return Data Type
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_MULTIPLY
SAFE_MULTIPLY(X, Y)
Description
Equivalent to the multiplication operator (*
), but returns
NULL
if overflow occurs.
X | Y | SAFE_MULTIPLY(X, Y) |
---|---|---|
20 | 4 | 80 |
Return Data Type
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
SAFE_NEGATE
SAFE_NEGATE(X)
Description
Equivalent to the unary minus operator (-
), but returns
NULL
if overflow occurs.
X | SAFE_NEGATE(X) |
---|---|
+1 | -1 |
-1 | +1 |
0 | 0 |
Return Data Type
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
SAFE_ADD
SAFE_ADD(X, Y)
Description
Equivalent to the addition operator (+
), but returns
NULL
if overflow occurs.
X | Y | SAFE_ADD(X, Y) |
---|---|---|
5 | 4 | 9 |
Return Data Type
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 |
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.
X | Y | SAFE_SUBTRACT(X, Y) |
---|---|---|
5 | 4 | 1 |
Return Data Type
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
INT64 | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC | FLOAT64 |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | FLOAT64 |
FLOAT64 | FLOAT64 | FLOAT64 | FLOAT64 | 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 | BIGNUMERIC |
---|---|---|---|
INT64 | INT64 | NUMERIC | BIGNUMERIC |
NUMERIC | NUMERIC | NUMERIC | BIGNUMERIC |
BIGNUMERIC | BIGNUMERIC | BIGNUMERIC | BIGNUMERIC |
ROUND
ROUND(X [, N] [, rounding_mode])
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.
If X is a NUMERIC
or BIGNUMERIC
type, then you can
explicitly set rounding_mode
([Preview][preview])
to one of the following:
- [
"ROUND_HALF_AWAY_FROM_ZERO"
][round-half-away-from-zero]: (Default) Rounds halfway cases away from zero. - [
"ROUND_HALF_EVEN"
][round-half-even]: Rounds halfway cases towards the nearest even digit.
If you set the rounding_mode
and X is not a NUMERIC
or BIGNUMERIC
type,
then the function generates an error.
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 |
ROUND(NUMERIC "2.25", 1, "ROUND_HALF_EVEN") |
2.2 |
ROUND(NUMERIC "2.35", 1, "ROUND_HALF_EVEN") |
2.4 |
ROUND(NUMERIC "2.251", 1, "ROUND_HALF_EVEN") |
2.3 |
ROUND(NUMERIC "-2.5", 0, "ROUND_HALF_EVEN") |
-2 |
ROUND(NUMERIC "2.5", 0, "ROUND_HALF_AWAY_FROM_ZERO") |
3 |
ROUND(NUMERIC "-2.5", 0, "ROUND_HALF_AWAY_FROM_ZERO") |
-3 |
Return Data Type
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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
INPUT | INT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
---|---|---|---|---|
OUTPUT | FLOAT64 | NUMERIC | BIGNUMERIC | FLOAT64 |
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 |
COT
COT(X)
Description
Computes the cotangent for the angle of X
, where X
is specified in radians.
X
can be any data type
that coerces to FLOAT64
.
Supports the SAFE.
prefix.
X | COT(X) |
---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
0 |
Error |
NULL |
NULL |
Return Data Type
FLOAT64
Example
SELECT COT(1) AS a, SAFE.COT(0) AS b;
+---------------------+------+
| a | b |
+---------------------+------+
| 0.64209261593433065 | NULL |
+---------------------+------+
COTH
COTH(X)
Description
Computes the hyperbolic cotangent for the angle of X
, where X
is specified
in radians. X
can be any data type
that coerces to FLOAT64
.
Supports the SAFE.
prefix.
X | COTH(X) |
---|---|
+inf |
1 |
-inf |
-1 |
NaN |
NaN |
0 |
Error |
NULL |
NULL |
Return Data Type
FLOAT64
Example
SELECT COTH(1) AS a, SAFE.COTH(0) AS b;
+----------------+------+
| a | b |
+----------------+------+
| 1.313035285499 | NULL |
+----------------+------+
CSC
CSC(X)
Description
Computes the cosecant of the input angle, which is in radians.
X
can be any data type
that coerces to FLOAT64
.
Supports the SAFE.
prefix.
X | CSC(X) |
---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
0 |
Error |
NULL |
NULL |
Return Data Type
FLOAT64
Example
SELECT CSC(100) AS a, CSC(-1) AS b, SAFE.CSC(0) AS c;
+----------------+-----------------+------+
| a | b | c |
+----------------+-----------------+------+
| -1.97485753142 | -1.188395105778 | NULL |
+----------------+-----------------+------+
CSCH
CSCH(X)
Description
Computes the hyperbolic cosecant of the input angle, which is in radians.
X
can be any data type
that coerces to FLOAT64
.
Supports the SAFE.
prefix.
X | CSCH(X) |
---|---|
+inf |
0 |
-inf |
0 |
NaN |
NaN |
0 |
Error |
NULL |
NULL |
Return Data Type
FLOAT64
Example
SELECT CSCH(0.5) AS a, CSCH(-2) AS b, SAFE.CSCH(0) AS c;
+----------------+----------------+------+
| a | b | c |
+----------------+----------------+------+
| 1.919034751334 | -0.27572056477 | NULL |
+----------------+----------------+------+
SEC
SEC(X)
Description
Computes the secant for the angle of X
, where X
is specified in radians.
X
can be any data type
that coerces to FLOAT64
.
X | SEC(X) |
---|---|
+inf |
NaN |
-inf |
NaN |
NaN |
NaN |
NULL |
NULL |
Return Data Type
FLOAT64
Example
SELECT SEC(100) AS a, SEC(-1) AS b;
+----------------+---------------+
| a | b |
+----------------+---------------+
| 1.159663822905 | 1.85081571768 |
+----------------+---------------+
SECH
SECH(X)
Description
Computes the hyperbolic secant for the angle of X
, where X
is specified
in radians. X
can be any data type
that coerces to FLOAT64
.
Never produces an error.
X | SECH(X) |
---|---|
+inf |
0 |
-inf |
0 |
NaN |
NaN |
NULL |
NULL |
Return Data Type
FLOAT64
Example
SELECT SECH(0.5) AS a, SECH(-2) AS b, SECH(100) AS c;
+----------------+----------------+---------------------+
| a | b | c |
+----------------+----------------+---------------------+
| 0.88681888397 | 0.265802228834 | 7.4401519520417E-44 |
+----------------+----------------+---------------------+
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 |
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 |
+--------------------+
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
orNaN
, returnsNULL
.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
orNULL
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 |
+--------------+-------+