All mathematical functions have the following behaviors: Show
ABSABS(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.
Return Data Type
SIGNSIGN(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.
Return Data Type
IS_INFIS_INF(X)Description Returns TRUE if the value is positive or negative infinity.
IS_NANIS_NAN(X)Description Returns TRUE if the value is a NaN value.
IEEE_DIVIDEIEEE_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.
RANDRAND()Description Generates a pseudo-random value of type FLOAT64 in the range of [0, 1), inclusive of 0 and exclusive of 1. SQRTSQRT(X)Description Computes the square root of X. Generates an error if X is less than 0.
Return Data Type
POWPOW(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.
Return Data Type The return data type is determined by the argument types with the following table.
POWERPOWER(X, Y)Description Synonym of POW(X, Y). EXPEXP(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
LNLN(X)Description Computes the natural logarithm of X. Generates an error if X is less than or equal to zero.
Return Data Type
LOGLOG(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
LOG10LOG10(X)Description Similar to LOG, but computes logarithm to base 10.
Return Data Type
GREATESTGREATEST(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. LEASTLEAST(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. DIVDIV(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.
SAFE_DIVIDESAFE_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_MULTIPLYSAFE_MULTIPLY(X, Y)Description Equivalent to the multiplication operator (*), but returns NULL if overflow occurs.
Return Data Type
SAFE_NEGATESAFE_NEGATE(X)Description Equivalent to the unary minus operator (-), but returns NULL if overflow occurs.
Return Data Type
SAFE_ADDSAFE_ADD(X, Y)Description Equivalent to the addition operator (+), but returns NULL if overflow occurs.
Return Data Type
SAFE_SUBTRACTSAFE_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
MODMOD(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.
ROUNDROUND(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.
Return Data Type
TRUNCTRUNC(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.
Return Data Type
CEILCEIL(X)Description Returns the smallest integral value that is not less than X.
Return Data Type
CEILINGCEILING(X)Description Synonym of CEIL(X) FLOORFLOOR(X)Description Returns the largest integral value that is not greater than X.
Return Data Type
COSCOS(X)Description Computes the cosine of X where X is specified in radians. Never fails.
COSHCOSH(X)Description Computes the hyperbolic cosine of X where X is specified in radians. Generates an error if overflow occurs.
ACOSACOS(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].
ACOSHACOSH(X)Description Computes the inverse hyperbolic cosine of X. Generates an error if X is a value less than 1.
COTCOT(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 | +---------------------+------+COTHCOTH(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 | +----------------+------+CSCCSC(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 | +----------------+-----------------+------+CSCHCSCH(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 | +----------------+----------------+------+SECSEC(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 | +----------------+---------------+SECHSECH(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 | +----------------+----------------+---------------------+SINSIN(X)Description Computes the sine of X where X is specified in radians. Never fails.
SINHSINH(X)Description Computes the hyperbolic sine of X where X is specified in radians. Generates an error if overflow occurs.
ASINASIN(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].
ASINHASINH(X)Description Computes the inverse hyperbolic sine of X. Does not fail.
TANTAN(X)Description Computes the tangent of X where X is specified in radians. Generates an error if overflow occurs.
TANHTANH(X)Description Computes the hyperbolic tangent of X where X is specified in radians. Does not fail.
ATANATAN(X)Description Computes the principal value of the inverse tangent of X. The return value is in the range [-π/2,π/2]. Does not fail.
ATANHATANH(X)Description Computes the inverse hyperbolic tangent of X. Generates an error if X is outside of the range [-1, 1].
ATAN2ATAN2(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 [-π,π].
CBRTCBRT(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 | +--------------------+RANGE_BUCKETRANGE_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:
Execution failure occurs when:
Parameters
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:
How do you write not equal to in BigQuery?NOT EQUAL TO (!=) and EXISTS... EQUAL TO Giving Different Results.
What is mod in BigQuery?MOD 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.
How do you subtract in BigQuery?Though there is no MINUS function in BigQuery, you can use a LEFT OUTER JOIN as an alternative.
How do you find the percentile in BigQuery?To get percentiles, simply ask for 100 quantiles. select percentiles[offset(10)] as p10, percentiles[offset(25)] as p25, percentiles[offset(50)] as p50, percentiles[offset(75)] as p75, percentiles[offset(90)] as p90, from ( select approx_quantiles(char_length(text), 100) percentiles from `bigquery-public-data.
|