Mathematical functions

GoogleSQL for BigQuery 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 .

Categories

Function list

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.

Return Data Type

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

ACOSH

  ACOSH 
 ( 
 X 
 ) 
 

Description

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

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

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 is in 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 of the 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 outside of the range (-1, 1).

CBRT

  CBRT 
 ( 
 X 
 ) 
 

Description

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

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.

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 the cosine distance between two vectors.

Definitions

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

Details

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

  T can represent the following and must be the same for both vectors:

  • FLOAT64

  In the following example vector, there are four dimensions. The magnitude is 10.0 for dimension 0 , 55.0 for dimension 1 , 40.0 for dimension 2 , and 34.0 for dimension 3 :

    [ 
   10.0 
   , 
    
   55.0 
   , 
    
   40.0 
   , 
    
   34.0 
   ] 
   
  

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

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

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

  • dimension : A STRING or INT64 value that represents a dimension in a vector.

  • magnitude : A FLOAT64 value that represents a non-zero magnitude for a specific dimension in a vector.

  You don't need to include empty dimension-magnitude pairs in a sparse vector. For example, the following sparse vector and non-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 any particular 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 vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can't be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of 0 , 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 is NULL .

• If a vector is NULL , NULL is returned.

Return type

FLOAT64

Examples

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

  SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 1.0 
 , 
  
 2.0 
 ] 
 , 
  
 [ 
 3.0 
 , 
  
 4.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 /*----------* 
 | results  | 
 +----------+ 
 | 0.016130 | 
 *----------*/ 
 

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

  SELECT 
  
 COSINE_DISTANCE 
 ( 
  
 [ 
 ( 
 1 
 , 
  
 1.0 
 ), 
  
 ( 
 2 
 , 
  
 2.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 2 
 , 
  
 4.0 
 ), 
  
 ( 
 1 
 , 
  
 3.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
  
 /*----------* 
 | results  | 
 +----------+ 
 | 0.016130 | 
 *----------*/ 
 

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

  SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 1.0 
 , 
  
 2.0 
 ] 
 , 
  
 [ 
 3.0 
 , 
  
 4.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

  SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 2.0 
 , 
  
 1.0 
 ] 
 , 
  
 [ 
 4.0 
 , 
  
 3.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

  SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 ( 
 1 
 , 
  
 1.0 
 ), 
  
 ( 
 2 
 , 
  
 2.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 1 
 , 
  
 3.0 
 ), 
  
 ( 
 2 
 , 
  
 4.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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

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

  -- ERROR 
 SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 0.0 
 , 
  
 0.0 
 ] 
 , 
  
 [ 
 3.0 
 , 
  
 4.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

  -- ERROR 
 SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 ( 
 1 
 , 
  
 0.0 
 ), 
  
 ( 
 2 
 , 
  
 0.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 1 
 , 
  
 3.0 
 ), 
  
 ( 
 2 
 , 
  
 4.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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

  -- ERROR 
 SELECT 
  
 COSINE_DISTANCE 
 ( 
 [ 
 9.0 
 , 
  
 7.0 
 ] 
 , 
  
 [ 
 8.0 
 , 
  
 4.0 
 , 
  
 5.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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

  -- ERROR 
 SELECT 
  
 COSINE_DISTANCE 
 ( 
  
 [ 
 ( 
 1 
 , 
  
 9.0 
 ), 
  
 ( 
 2 
 , 
  
 7.0 
 ), 
  
 ( 
 2 
 , 
  
 8.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 1 
 , 
  
 8.0 
 ), 
  
 ( 
 2 
 , 
  
 4.0 
 ), 
  
 ( 
 3 
 , 
  
 5.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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.

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.

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.

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.

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 | 
 *----------------+----------------+------*/ 
 

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.

Return Data Type

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

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.

Return Data Type

EUCLIDEAN_DISTANCE

  EUCLIDEAN_DISTANCE 
 ( 
 vector1 
 , 
  
 vector2 
 ) 
 

Description

Computes the Euclidean distance between two vectors.

Definitions

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

Details

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

  T can represent the following and must be the same for both vectors:

  • FLOAT64

  In the following example vector, there are four dimensions. The magnitude is 10.0 for dimension 0 , 55.0 for dimension 1 , 40.0 for dimension 2 , and 34.0 for dimension 3 :

    [ 
   10.0 
   , 
    
   55.0 
   , 
    
   40.0 
   , 
    
   34.0 
   ] 
   
  

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

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

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

  • dimension : A STRING or INT64 value that represents a dimension in a vector.

  • magnitude : A FLOAT64 value that represents a non-zero magnitude for a specific dimension in a vector.

  You don't need to include empty dimension-magnitude pairs in a sparse vector. For example, the following sparse vector and non-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 any particular 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 vectors in this function must share the same dimensions, and if they don't, an error is produced.

• A vector can be a zero vector. A vector is a zero vector if it has no dimensions or all dimensions have a magnitude of 0 , such as [] or [0.0, 0.0] .

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

• If a vector is NULL , NULL is returned.

Return type

FLOAT64

Examples

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

  SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
 [ 
 1.0 
 , 
  
 2.0 
 ] 
 , 
  
 [ 
 3.0 
 , 
  
 4.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 /*----------* 
 | results  | 
 +----------+ 
 | 2.828    | 
 *----------*/ 
 

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

  SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
  
 [ 
 ( 
 1 
 , 
  
 1.0 
 ), 
  
 ( 
 2 
 , 
  
 2.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 2 
 , 
  
 4.0 
 ), 
  
 ( 
 1 
 , 
  
 3.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
  
 /*----------* 
 | results  | 
 +----------+ 
 | 2.828    | 
 *----------*/ 
 

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

  SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
 [ 
 1.0 
 , 
  
 2.0 
 ] 
 , 
  
 [ 
 3.0 
 , 
  
 4.0 
 ] 
 ); 
 

  SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
 [ 
 2.0 
 , 
  
 1.0 
 ] 
 , 
  
 [ 
 4.0 
 , 
  
 3.0 
 ] 
 ); 
 

  SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
 [ 
 ( 
 1 
 , 
  
 1.0 
 ), 
  
 ( 
 2 
 , 
  
 2.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 1 
 , 
  
 3.0 
 ), 
  
 ( 
 2 
 , 
  
 4.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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

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

  -- ERROR 
 SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
 [ 
 9.0 
 , 
  
 7.0 
 ] 
 , 
  
 [ 
 8.0 
 , 
  
 4.0 
 , 
  
 5.0 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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

  -- ERROR 
 SELECT 
  
 EUCLIDEAN_DISTANCE 
 ( 
  
 [ 
 ( 
 1 
 , 
  
 9.0 
 ), 
  
 ( 
 2 
 , 
  
 7.0 
 ), 
  
 ( 
 2 
 , 
  
 8.0 
 ) 
 ] 
 , 
  
 [ 
 ( 
 1 
 , 
  
 8.0 
 ), 
  
 ( 
 2 
 , 
  
 4.0 
 ), 
  
 ( 
 3 
 , 
  
 5.0 
 ) 
 ] 
 ) 
  
 AS 
  
 results 
 ; 
 

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

This function supports specifying collation .

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.

IS_INF

  IS_INF 
 ( 
 X 
 ) 
 

Description

Returns TRUE if the value is positive or negative infinity.

IS_NAN

  IS_NAN 
 ( 
 X 
 ) 
 

Description

Returns TRUE if the value is a NaN value.

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.

This function supports specifying collation .

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.

Return Data Type

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.

Return Data Type

LOG10

  LOG10 
 ( 
 X 
 ) 
 

Description

Similar to LOG , 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. Returned value 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 following table.

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.

Return Data Type

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

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 
  
 [ 
 , 
  
 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 to one of the following:

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

If you set the rounding_mode and X isn't a NUMERIC or BIGNUMERIC type, then the function generates an error.

Return Data Type

SAFE_ADD

  SAFE_ADD 
 ( 
 X 
 , 
  
 Y 
 ) 
 

Description

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

Return Data Type

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.

Return Data Type

SAFE_MULTIPLY

  SAFE_MULTIPLY 
 ( 
 X 
 , 
  
 Y 
 ) 
 

Description

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

Return Data Type

SAFE_NEGATE

  SAFE_NEGATE 
 ( 
 X 
 ) 
 

Description

Equivalent to the unary minus operator ( - ), but returns NULL 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 returns NULL if overflow occurs.

Return Data Type

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 .

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.

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 | 
 *----------------+----------------+---------------------*/ 
 

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.

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. Generates an 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 if overflow occurs.

TANH

  TANH 
 ( 
 X 
 ) 
 

Description

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

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.

Return Data Type