GoogleSQL for BigQuery supports mathematical functions.All mathematical functions have the following behaviors:

• They returnNULL if any of the input parameters isNULL.
• They returnNaN if any of the arguments isNaN.

Categories

Function list

ABS

ABS(X)

Description

Computes absolute value. Returns an error if the argument is an integer and theoutput value can't be represented as the same type; this happens only for thelargest negative input value, which has no positive representation.

Return Data Type

ACOS

ACOS(X)

Description

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

ACOSH

ACOSH(X)

Description

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

ASIN

ASIN(X)

Description

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

ASINH

ASINH(X)

Description

Computes the inverse hyperbolic sine of X. Doesn't fail.

ATAN

ATAN(X)

Description

Computes the principal value of the inverse tangent of X. The return value isin the range [-π/2,π/2]. Doesn't fail.

ATAN2

ATAN2(X,Y)

Description

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

ATANH

ATANH(X)

Description

Computes the inverse hyperbolic tangent of X. Generates an error if X is outsideof the range (-1, 1).

CBRT

CBRT(X)

Description

Computes the cube root ofX.X can be any data typethatcoerces toFLOAT64.Supports theSAFE. prefix.

Return Data Type

FLOAT64

Example

SELECTCBRT(27)AScube_root;/*--------------------* | cube_root          | +--------------------+ | 3.0000000000000004 | *--------------------*/

CEIL

CEIL(X)

Description

Returns the smallest integral value that isn't less than X.

Return Data Type

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.

COSH

COSH(X)

Description

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

COSINE_DISTANCE

COSINE_DISTANCE(vector1,vector2)

Description

Computes thecosine distance between two vectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value or a sparse vector that isrepresented by anARRAY<STRUCT<dimension,magnitude>> value.
• vector2: A vector that's represented by anARRAY<T> value or a sparse vector that isrepresented by anARRAY<STRUCT<dimension,magnitude>> value.

Details

• ARRAY<T> can be used to represent a vector. Each zero-based index in thisarray represents a dimension. The value for each element in this arrayrepresents a magnitude.

  T can represent the following and must be the same for bothvectors:

  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• ARRAY<STRUCT<dimension,magnitude>> can be used to represent asparse vector. With a sparse vector, you only need to includedimension-magnitude pairs for non-zero magnitudes. If a magnitude isn'tpresent in the sparse vector, the magnitude is implicitly understood to bezero.

  For example, if you have a vector with 10,000 dimensions, but only 10dimensions have non-zero magnitudes, then the vector is a sparse vector.As a result, it's more efficient to describe a sparse vector by onlymentioning its non-zero magnitudes.

  InARRAY<STRUCT<dimension,magnitude>>,STRUCT<dimension,magnitude>represents a dimension-magnitude pair for each non-zero magnitude in asparse vector. These parts need to be included for each dimension-magnitudepair:

  • dimension: ASTRING orINT64 value that represents adimension in a vector.

  • magnitude: AFLOAT64 value that represents anon-zero magnitude for a specific dimension in a vector.

  You don't need to include empty dimension-magnitude pairs in asparse vector. For example, the following sparse vector andnon-sparse vector are equivalent:

  -- sparse vector ARRAY<STRUCT<INT64, FLOAT64>>[(1,10.0),(2,30.0),(5,40.0)]

  -- vector ARRAY<FLOAT64>[0.0,10.0,30.0,0.0,0.0,40.0]

  In a sparse vector, dimension-magnitude pairs don't need to be in anyparticular order. The following sparse vectors are equivalent:

  [('a',10.0),('b',30.0),('d',40.0)]

  [('d',40.0),('a',10.0),('b',30.0)]

• Both non-sparse vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can't be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, such as[] or[0.0, 0.0]. If a zero vector is encountered, an error is produced.

• An error is produced if a magnitude in a vector isNULL.

• If a vector isNULL,NULL is returned.

Return type

FLOAT64

Examples

In the following example, non-sparsevectorsare used to compute the cosine distance:

SELECTCOSINE_DISTANCE([1.0,2.0],[3.0,4.0])ASresults;/*----------* | results  | +----------+ | 0.016130 | *----------*/

In the following example, sparse vectors are used to compute thecosine distance:

SELECTCOSINE_DISTANCE([(1,1.0),(2,2.0)],[(2,4.0),(1,3.0)])ASresults;/*----------*  | results  |  +----------+  | 0.016130 |  *----------*/

The ordering of numeric values in a vector doesn't impact the resultsproduced by this function. For example these queries produce the same resultseven though the numeric values in each vector is in a different order:

SELECTCOSINE_DISTANCE([1.0,2.0],[3.0,4.0])ASresults;

SELECTCOSINE_DISTANCE([2.0,1.0],[4.0,3.0])ASresults;

SELECTCOSINE_DISTANCE([(1,1.0),(2,2.0)],[(1,3.0),(2,4.0)])ASresults;

/*----------*  | results  |  +----------+  | 0.016130 |  *----------*/

In the following example, the function can't compute cosine distance againstthe first vector, which is a zero vector:

-- ERRORSELECTCOSINE_DISTANCE([0.0,0.0],[3.0,4.0])ASresults;

-- ERRORSELECTCOSINE_DISTANCE([(1,0.0),(2,0.0)],[(1,3.0),(2,4.0)])ASresults;

Both non-sparse vectors must have the samedimensions. If not, an error is produced. In the following example, thefirst vector has two dimensions and the second vector has three:

-- ERRORSELECTCOSINE_DISTANCE([9.0,7.0],[8.0,4.0,5.0])ASresults;

If you use sparse vectors and you repeat a dimension, an error isproduced:

-- ERRORSELECTCOSINE_DISTANCE([(1,9.0),(2,7.0),(2,8.0)],[(1,8.0),(2,4.0),(3,5.0)])ASresults;

COT

COT(X)

Description

Computes the cotangent for the angle ofX, whereX is specified in radians.X can be any data typethatcoerces toFLOAT64.Supports theSAFE. prefix.

Return Data Type

FLOAT64

Example

SELECTCOT(1)ASa,SAFE.COT(0)ASb;/*---------------------+------* | a                   | b    | +---------------------+------+ | 0.64209261593433065 | NULL | *---------------------+------*/

COTH

COTH(X)

Description

Computes the hyperbolic cotangent for the angle ofX, whereX is specifiedin radians.X can be any data typethatcoerces toFLOAT64.Supports theSAFE. prefix.

Return Data Type

FLOAT64

Example

SELECTCOTH(1)ASa,SAFE.COTH(0)ASb;/*----------------+------* | 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 typethatcoerces toFLOAT64.Supports theSAFE. prefix.

Return Data Type

FLOAT64

Example

SELECTCSC(100)ASa,CSC(-1)ASb,SAFE.CSC(0)ASc;/*----------------+-----------------+------* | 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 typethatcoerces toFLOAT64.Supports theSAFE. prefix.

Return Data Type

FLOAT64

Example

SELECTCSCH(0.5)ASa,CSCH(-2)ASb,SAFE.CSCH(0)ASc;/*----------------+----------------+------* | a              | b              | c    | +----------------+----------------+------+ | 1.919034751334 | -0.27572056477 | NULL | *----------------+----------------+------*/

DIV

DIV(X,Y)

Description

Returns the result of integer division of X by Y. Division by zero returnsan error. Division by -1 may overflow.

Return Data Type

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

EXP

EXP(X)

Description

Computese to the power of X, also called the natural exponential function. Ifthe result underflows, this function returns a zero. Generates an error if theresult overflows.

Return Data Type

EUCLIDEAN_DISTANCE

EUCLIDEAN_DISTANCE(vector1,vector2)

Description

Computes theEuclidean distance between two vectors.

Definitions

• vector1: A vector that's represented by anARRAY<T> value or a sparse vector that isrepresented by anARRAY<STRUCT<dimension,magnitude>> value.
• vector2: A vector that's represented by anARRAY<T> value or a sparse vector that isrepresented by anARRAY<STRUCT<dimension,magnitude>> value.

Details

• ARRAY<T> can be used to represent a vector. Each zero-based index in thisarray represents a dimension. The value for each element in this arrayrepresents a magnitude.

  T can represent the following and must be the same for bothvectors:

  • FLOAT64

  In the following example vector, there are four dimensions. The magnitudeis10.0 for dimension0,55.0 for dimension1,40.0 fordimension2, and34.0 for dimension3:

  [10.0,55.0,40.0,34.0]

• ARRAY<STRUCT<dimension,magnitude>> can be used to represent asparse vector. With a sparse vector, you only need to includedimension-magnitude pairs for non-zero magnitudes. If a magnitude isn'tpresent in the sparse vector, the magnitude is implicitly understood to bezero.

  For example, if you have a vector with 10,000 dimensions, but only 10dimensions have non-zero magnitudes, then the vector is a sparse vector.As a result, it's more efficient to describe a sparse vector by onlymentioning its non-zero magnitudes.

  InARRAY<STRUCT<dimension,magnitude>>,STRUCT<dimension,magnitude>represents a dimension-magnitude pair for each non-zero magnitude in asparse vector. These parts need to be included for each dimension-magnitudepair:

  • dimension: ASTRING orINT64 value that represents adimension in a vector.

  • magnitude: AFLOAT64 value that represents anon-zero magnitude for a specific dimension in a vector.

  You don't need to include empty dimension-magnitude pairs in asparse vector. For example, the following sparse vector andnon-sparse vector are equivalent:

  -- sparse vector ARRAY<STRUCT<INT64, FLOAT64>>[(1,10.0),(2,30.0),(5,40.0)]

  -- vector ARRAY<FLOAT64>[0.0,10.0,30.0,0.0,0.0,40.0]

  In a sparse vector, dimension-magnitude pairs don't need to be in anyparticular order. The following sparse vectors are equivalent:

  [('a',10.0),('b',30.0),('d',40.0)]

  [('d',40.0),('a',10.0),('b',30.0)]

• Both non-sparse vectorsin this function must share the same dimensions, and if they don't, an erroris produced.

• A vector can be a zero vector. A vector is a zero vector if it hasno dimensions or all dimensions have a magnitude of0, such as[] or[0.0, 0.0].

• An error is produced if a magnitude in a vector isNULL.

• If a vector isNULL,NULL is returned.

Return type

FLOAT64

Examples

In the following example, non-sparse vectorsare used to compute the Euclidean distance:

SELECTEUCLIDEAN_DISTANCE([1.0,2.0],[3.0,4.0])ASresults;/*----------* | results  | +----------+ | 2.828    | *----------*/

In the following example, sparse vectors are used to compute theEuclidean distance:

SELECTEUCLIDEAN_DISTANCE([(1,1.0),(2,2.0)],[(2,4.0),(1,3.0)])ASresults;/*----------*  | results  |  +----------+  | 2.828    |  *----------*/

The ordering of magnitudes in a vector doesn't impact the resultsproduced by this function. For example these queries produce the same resultseven though the magnitudes in each vector is in a different order:

SELECTEUCLIDEAN_DISTANCE([1.0,2.0],[3.0,4.0]);

SELECTEUCLIDEAN_DISTANCE([2.0,1.0],[4.0,3.0]);

SELECTEUCLIDEAN_DISTANCE([(1,1.0),(2,2.0)],[(1,3.0),(2,4.0)])ASresults;

/*----------*  | results  |  +----------+  | 2.828    |  *----------*/

Both non-sparse vectors must have the samedimensions. If not, an error is produced. In the following example, the firstvector has two dimensions and the second vector has three:

-- ERRORSELECTEUCLIDEAN_DISTANCE([9.0,7.0],[8.0,4.0,5.0])ASresults;

If you use sparse vectors and you repeat a dimension, an error isproduced:

-- ERRORSELECTEUCLIDEAN_DISTANCE([(1,9.0),(2,7.0),(2,8.0)],[(1,8.0),(2,4.0),(3,5.0)])ASresults;

FLOOR

FLOOR(X)

Description

Returns the largest integral value that isn't greater than X.

Return Data Type

GREATEST

GREATEST(X1,...,XN)

Description

Returns the greatest value amongX1,...,XN. If any argument isNULL, returnsNULL. Otherwise, in the case of floating-point arguments, if any argument isNaN, returnsNaN. In all other cases, returns the value amongX1,...,XNthat has the greatest value according to the ordering used by theORDER BYclause. The argumentsX1, ..., XN must be coercible to a common supertype, andthe supertype must support ordering.

This function supports specifyingcollation.

Return Data Types

Data type of the input values.

IEEE_DIVIDE

IEEE_DIVIDE(X,Y)

Description

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

IS_INF

IS_INF(X)

Description

ReturnsTRUE if the value is positive or negative infinity.

IS_NAN

IS_NAN(X)

Description

ReturnsTRUE if the value is aNaN value.

LEAST

LEAST(X1,...,XN)

Description

Returns the least value amongX1,...,XN. If any argument isNULL, returnsNULL. Otherwise, in the case of floating-point arguments, if any argument isNaN, returnsNaN. In all other cases, returns the value amongX1,...,XNthat has the least value according to the ordering used by theORDER BYclause. The argumentsX1, ..., XN must be coercible to a common supertype, andthe supertype must support ordering.

This function supports specifyingcollation.

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 orequal to zero.

Return Data Type

LOG

LOG(X[,Y])

Description

If only X is present,LOG is a synonym ofLN. If Y is also present,LOG computes the logarithm of X to base Y.

Return Data Type

LOG10

LOG10(X)

Description

Similar toLOG, but computes logarithm to base 10.

Return Data Type

MOD

MOD(X,Y)

Description

Modulo function: returns the remainder of the division of X by Y. Returnedvalue has the same sign as X. An error is generated if Y is 0.

Return Data Type

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

POW

POW(X,Y)

Description

Returns the value of X raised to the power of Y. If the result underflows andisn't representable, then the function returns a value of zero.

Return Data Type

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

POWER

POWER(X,Y)

Description

Synonym ofPOW(X, Y).

RAND

RAND()

Description

Generates a pseudo-random value of typeFLOAT64 inthe 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 positionof the point's upper bound. This can be useful if you need to group your data tobuild 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 valueRANGE_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 isNULL 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 valueRANGE_BUCKET(1.2,[1,1.2,1.4,1.6])-- 2 is return valueRANGE_BUCKET(1.2,[1,2,4,6])-- execution failure

Execution failure occurs when:

• The array has aNaN orNULL 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 calledstudents, check to see how many records wouldexist in eachage_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)

WITHstudentsAS(SELECT9ASageUNIONALLSELECT20ASageUNIONALLSELECT25ASageUNIONALLSELECT31ASageUNIONALLSELECT32ASageUNIONALLSELECT33ASage)SELECTRANGE_BUCKET(age,[10,20,30])ASage_group,COUNT(*)AScountFROMstudentsGROUPBY1/*--------------+-------* | age_group    | count | +--------------+-------+ | 0            | 1     | | 2            | 2     | | 3            | 3     | *--------------+-------*/

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 casesaway from zero. Generates an error if overflow occurs.

If X is aNUMERIC orBIGNUMERIC type, then you canexplicitly setrounding_modeto one of the following:

• "ROUND_HALF_AWAY_FROM_ZERO": (Default) Roundshalfway cases away from zero.
• "ROUND_HALF_EVEN": Rounds halfway casestowards the nearest even digit.

If you set therounding_mode and X isn't aNUMERIC orBIGNUMERIC type,then the function generates an error.

Return Data Type

SAFE_ADD

SAFE_ADD(X,Y)

Description

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

Return Data Type

SAFE_DIVIDE

SAFE_DIVIDE(X,Y)

Description

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

Return Data Type

SAFE_MULTIPLY

SAFE_MULTIPLY(X,Y)

Description

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

Return Data Type

SAFE_NEGATE

SAFE_NEGATE(X)

Description

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

Return Data Type

SAFE_SUBTRACT

SAFE_SUBTRACT(X,Y)

Description

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

Return Data Type

SEC

SEC(X)

Description

Computes the secant for the angle ofX, whereX is specified in radians.X can be any data typethatcoerces toFLOAT64.

Return Data Type

FLOAT64

Example

SELECTSEC(100)ASa,SEC(-1)ASb;/*----------------+---------------* | a              | b             | +----------------+---------------+ | 1.159663822905 | 1.85081571768 | *----------------+---------------*/

SECH

SECH(X)

Description

Computes the hyperbolic secant for the angle ofX, whereX is specifiedin radians.X can be any data typethatcoerces toFLOAT64.Never produces an error.

Return Data Type

FLOAT64

Example

SELECTSECH(0.5)ASa,SECH(-2)ASb,SECH(100)ASc;/*----------------+----------------+---------------------* | a              | b              | c                   | +----------------+----------------+---------------------+ | 0.88681888397  | 0.265802228834 | 7.4401519520417E-44 | *----------------+----------------+---------------------*/

SIGN

SIGN(X)

Description

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

Return Data Type

SIN

SIN(X)

Description

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

SINH

SINH(X)

Description

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

SQRT

SQRT(X)

Description

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

Return Data Type

TAN

TAN(X)

Description

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

TANH

TANH(X)

Description

Computes the hyperbolic tangent of X where X is specified in radians. Doesn'tfail.

TRUNC

TRUNC(X[,N])

Description

If only X is present,TRUNC rounds X to the nearest integer whose absolutevalue isn't greater than the absolute value of X. If N is also present,TRUNCbehaves likeROUND(X, N), but always rounds towards zero and never overflows.

Return Data Type