Scalar Functions
ABS(expr)
ABS(expr)
Calculates the absolute value of
exprACOS(expr)
ACOS(expr)
Returns the inverse cosine (arccosine) of
expr as a doubleACOSF(expr)
ACOSF(expr)
Returns the inverse cosine (arccosine) of
expr as a floatACOSH(expr)
ACOSH(expr)
Returns the inverse hyperbolic cosine of
expr as a doubleACOSHF(expr)
ACOSHF(expr)
Returns the inverse hyperbolic cosine of
expr as a floatASIN(expr)
ASIN(expr)
Returns the inverse sine (arcsine) of
expr as a doubleASINF(expr)
ASINF(expr)
Returns the inverse sine (arcsine) of
expr as a floatASINH(expr)
ASINH(expr)
Returns the inverse hyperbolic sine of
expr as a doubleASINHF(expr)
ASINHF(expr)
Returns the inverse hyperbolic sine of
expr as a floatATAN(expr)
ATAN(expr)
Returns the inverse tangent (arctangent) of
expr as a doubleATANF(expr)
ATANF(expr)
Returns the inverse tangent (arctangent) of
expr as a floatATANH(expr)
ATANH(expr)
Returns the inverse hyperbolic tangent of
expr as a doubleATANHF(expr)
ATANHF(expr)
Returns the inverse hyperbolic tangent of
expr as a floatATAN2(x, y)
ATAN2(x, y)
Returns the inverse tangent (arctangent) using two arguments as a double
ATAN2F(x, y)
ATAN2F(x, y)
Returns the inverse tangent (arctangent) using two arguments as a float
ATN2(x, y)
ATN2(x, y)
Alias for
ATAN2ATN2F(x, y)
ATN2F(x, y)
Alias for
ATAN2FCBRT(expr)
CBRT(expr)
Returns the cube root of
expr as a doubleCBRTF(expr)
CBRTF(expr)
Returns the cube root of
expr as a floatCEIL(expr)
CEIL(expr)
Alias for
CEILINGCEILING(expr)
CEILING(expr)
Rounds
expr up to the next highest integerCOS(expr)
COS(expr)
Returns the cosine of
expr as a doubleCOSF(expr)
COSF(expr)
Returns the cosine of
expr as a floatCOSH(expr)
COSH(expr)
Returns the hyperbolic cosine of
expr as a doubleCOSHF(expr)
COSHF(expr)
Returns the hyperbolic cosine of
expr as a floatCOT(expr)
COT(expr)
Returns the cotangent of
expr as a doubleCOTF(expr)
COTF(expr)
Returns the cotangent of
expr as a floatDEGREES(expr)
DEGREES(expr)
Returns the conversion of
expr (in radians) to degrees as a doubleDEGREESF(expr)
DEGREESF(expr)
Returns the conversion of
expr (in radians) to degrees as a floatDIVZ(a, b, c)
DIVZ(a, b, c)
Returns the quotient
a / b unless b == 0, in which case it returns cEXP(expr)
EXP(expr)
Returns e to the power of
expr as a doubleEXPF(expr)
EXPF(expr)
Returns e to the power of
expr as a floatFLOOR(expr)
FLOOR(expr)
Rounds
expr down to the next lowest integerGREATER(expr_a, expr_b)
GREATER(expr_a, expr_b)
Returns whichever of
expr_a and expr_b has the larger value, based on typed
comparisonHYPOT(x, y)
HYPOT(x, y)
Returns the hypotenuse of
x and y as a doubleHYPOTF(x, y)
HYPOTF(x, y)
Returns the hypotenuse of
x and y as a floatIFERROR(expr, val)
IFERROR(expr, val)
Alias for
IF_ERROR(expr, val)IFINF(expr, val)
IFINF(expr, val)
Alias for
IF_INF(expr, val)IFINFINITY(expr, val)
IFINFINITY(expr, val)
Alias for
IF_INF(expr, val)IFNAN(expr, val)
IFNAN(expr, val)
Alias for
IF_NAN(expr, val)IF_ERROR(expr, val)
IF_ERROR(expr, val)
Evaluates the given double or float
expr, and if it resolves to infinity or
NaN, return valExample:| Function Call | Result |
|---|---|
IF_ERROR((double(10)/0), 1) | 1.0 |
IF_ERROR(log(-1), 1) | 1.0 |
IF_INF(expr, val)
IF_INF(expr, val)
Evaluates the given double or float
expr, and if it resolves to infinity,
return valExample:| Function Call | Result |
|---|---|
IF_INF((double(10)/0), 999) | 999.0 |
IF_INF(log(-1), 999) | NaN |
IF_INFINITY(expr, val)
IF_INFINITY(expr, val)
Alias for
IF_INF(expr, val)IF_NAN(expr, val)
IF_NAN(expr, val)
Evaluates the given double or float
expr, and if it resolves to NaN,
return valExample:| Function Call | Result |
|---|---|
IF_NAN((double(10)/0), -1) | Infinity |
IF_NAN(log(-1), -1) | -1.0 |
ISINFINITY(expr)
ISINFINITY(expr)
Returns
1 (true) if expr is infinity by IEEE standard; otherwise, returns
0 (false)IS_INFINITY(expr)
IS_INFINITY(expr)
Alias for
ISINFINITYISNAN(expr)
ISNAN(expr)
Returns
1 (true) if expr is not a number by IEEE standard; otherwise, returns
0 (false)IS_NAN(expr)
IS_NAN(expr)
Alias for
ISNANISNUMERIC(expr)
ISNUMERIC(expr)
Returns
1 (true) if expr is a number by IEEE standard; otherwise, returns
0 (false)IS_NUMERIC(expr)
IS_NUMERIC(expr)
Alias for
ISNUMERICLDEXP(x, exp)
LDEXP(x, exp)
Returns the value of
x * 2exp as a doubleLDEXPF(x, exp)
LDEXPF(x, exp)
Returns the value of
x * 2exp as a floatLESSER(expr_a, expr_b)
LESSER(expr_a, expr_b)
Returns whichever of
expr_a and expr_b has the smaller value, based on typed
comparisonLN(expr)
LN(expr)
Returns the natural logarithm of
expr as a doubleLNF(expr)
LNF(expr)
Returns the natural logarithm of
expr as a floatLOG(expr)
LOG(expr)
Alias for
LNLOG10(expr)
LOG10(expr)
Returns the base-10 logarithm of
expr as a doubleLOG10F(expr)
LOG10F(expr)
Returns the base-10 logarithm of
expr as a floatLOG1P(expr)
LOG1P(expr)
Returns the natural logarithm of one plus
expr as a doubleLOG1PF(expr)
LOG1PF(expr)
Returns the natural logarithm of one plus
expr as a floatLOG2(expr)
LOG2(expr)
Returns the binary (base-2) logarithm of
expr as a doubleLOG2F(expr)
LOG2F(expr)
Returns the binary (base-2) logarithm of
expr as a floatLOGF(expr)
LOGF(expr)
Alias for
LNFMAX_CONSECUTIVE_BITS(expr)
MAX_CONSECUTIVE_BITS(expr)
Calculates the length of the longest series of consecutive
1 bits in the integer
exprMOD(dividend, divisor)
MOD(dividend, divisor)
Calculates the remainder after integer division of
dividend by divisorNEXT_AFTER(expr[, target])
NEXT_AFTER(expr[, target])
Returns the next representable floating-point value adjacent to
expr in the direction of target, incrementing expr
if target is greater, decrementing if lesser. The default
value of target is MAX_DOUBLE (always increments when
omitted). The return type matches the input (float or double).NEXT_BEFORE(expr)
NEXT_BEFORE(expr)
Returns the next representable floating-point value less than
expr. The return type matches the input (float or double).PI()
PI()
Returns the value of pi
POW(base, exponent)
POW(base, exponent)
Alias for
POWERPOWF(base, exponent)
POWF(base, exponent)
Alias for
POWERFPOWER(base, exponent)
POWER(base, exponent)
Returns
base raised to the power of exponent as a doublePOWERF(base, exponent)
POWERF(base, exponent)
Returns
base raised to the power of exponent as a floatRADIANS(expr)
RADIANS(expr)
Returns the conversion of
expr (in degrees) to radians as a doubleRADIANSF(expr)
RADIANSF(expr)
Returns the conversion of
expr (in degrees) to radians as a floatRAND([seed])
RAND([seed])
Returns a random floating-point value, with an optional
seedREGR_VALX(y, x)
REGR_VALX(y, x)
Returns NULL if
y is NULL; otherwise, returns xREGR_VALY(y, x)
REGR_VALY(y, x)
Returns NULL if
x is NULL; otherwise, returns yROUND(expr[, scale])
ROUND(expr[, scale])
Rounds
expr to the nearest decimal number with scale
decimal places when scale is a positive number; rounds to
the nearest number such that the result has -(scale) zeros
to the left of the decimal point when scale is negative; use
scale of 0 to round to the nearest integer.The default value of scale is 0.Examples:| Function Call | Result |
|---|---|
ROUND(12345.678) | 12346 |
ROUND(12345.678, 2) | 12345.68 |
ROUND(12345.678, 0) | 12346 |
ROUND(12345.678, -2) | 12300 |
SAFE_DIVIDE(a, b)
SAFE_DIVIDE(a, b)
Returns the quotient
a / b unless b == 0, in which case it returns NULLSIGN(expr)
SIGN(expr)
Determines whether a number is positive, negative, or zero;
returns one of the following three values:
| Expression Value | Result |
|---|---|
| positive | 1 |
| zero | 0 |
| negative | -1 |
SIN(expr)
SIN(expr)
Returns the sine of
expr as a doubleSINF(expr)
SINF(expr)
Returns the sine of
expr as a floatSINH(expr)
SINH(expr)
Returns the hyperbolic sine of
expr as a doubleSINHF(expr)
SINHF(expr)
Returns the hyperbolic sine of
expr as a floatSQRT(expr)
SQRT(expr)
Returns the square root of
expr as a doubleSQRTF(expr)
SQRTF(expr)
Returns the square root of
expr as a floatTAN(expr)
TAN(expr)
Returns the tangent of
expr as a doubleTANF(expr)
TANF(expr)
Returns the tangent of
expr as a floatTANH(expr)
TANH(expr)
Returns the hyperbolic tangent of
expr as a doubleTANHF(expr)
TANHF(expr)
Returns the hyperbolic tangent of
expr as a floatTRUNCATE(expr[, scale])
TRUNCATE(expr[, scale])
Rounds
expr down to the nearest decimal number with
scale decimal places when scale is a positive number;
rounds down to the nearest number such that the result has
-(scale) zeros to the left of the decimal point when
scale is negative; use scale of 0 to round down to
the nearest integer.The default value of scale is 0.Examples:| Function Call | Result |
|---|---|
TRUNCATE(12345.678) | 12345 |
TRUNCATE(12345.678, 2) | 12345.67 |
TRUNCATE(12345.678, 0) | 12345 |
TRUNCATE(12345.678, -2) | 12300 |
WIDTH_BUCKET(expr, min, max, count)
WIDTH_BUCKET(expr, min, max, count)
Defines a set of
count equal intervals (buckets) within the range of min &
max, and puts the value of expr into one of those buckets, where the value is
greater than or equal to the minimum value of the bucket and less than the maximum
value of the bucket. Returns the 1-based number of the bucket into which the value
of expr fell. For values smaller than min, 0 is returned; for values
greater than or equal to max, count + 1 is returned. Examples:In the following examples, a set of 5 equal buckets are defined between 0 and
10 (0-2, 2-4, 4-6, 6-8, & 8-10), and various values are bucketed using
that set.| Function Call | Result |
|---|---|
WIDTH_BUCKET(-1, 0, 10, 5) | 0 |
WIDTH_BUCKET(0, 0, 10, 5) | 1 |
WIDTH_BUCKET(5, 0, 10, 5) | 3 |
WIDTH_BUCKET(10, 0, 10, 5) | 6 |
WIDTH_BUCKET(11, 0, 10, 5) | 6 |
Aggregate Functions
APPROX_COUNT_DISTINCT(expr)
APPROX_COUNT_DISTINCT(expr)
The approximate number of distinct values of
expr; this is faster to calculate than COUNT_DISTINCT but is only an approximationAPPROX_MEDIAN(expr)
APPROX_MEDIAN(expr)
The approximate median of
expr; the result should be within about 2% of the true median value. This is equivalent to issuing
APPROX_PERCENTILE(expr, 50).APPROX_PERCENTILE(expr, p)
APPROX_PERCENTILE(expr, p)
The approximate pth percentile of
expr; p should be a value between 0.0 and 100.0. APPROX_PERCENTILE(expr, 50) will return
the approximate median of expr.AVG(expr)
AVG(expr)
Average of the values of
exprCORR(expr1, expr2)
CORR(expr1, expr2)
Correlation coefficient of the values of
expr1 and expr2CORRELATION(expr1, expr2)
CORRELATION(expr1, expr2)
Alias for
CORRCORRCOEF(expr1, expr2)
CORRCOEF(expr1, expr2)
Alias for
CORRCOUNT(expr)
COUNT(expr)
Count of non-null values of
expr; use * to count all values within an aggregation group or over an entire tableCOUNT_DISTINCT(expr)
COUNT_DISTINCT(expr)
Count of the distinct values of
exprCOV(expr1, expr2)
COV(expr1, expr2)
Alias for
COVAR_POPCOVAR(expr1, expr2)
COVAR(expr1, expr2)
Alias for
COVAR_POPCOVARIANCE(expr1, expr2)
COVARIANCE(expr1, expr2)
Alias for
COVAR_POPCOVAR_POP(expr1, expr2)
COVAR_POP(expr1, expr2)
Population covariance of the values of
expr1 and expr2COVAR_SAMP(expr1, expr2)
COVAR_SAMP(expr1, expr2)
Sample covariance of the values of
expr1 and expr2KURT(expr)
KURT(expr)
Alias for
KURTOSIS_POPKURTOSIS(expr)
KURTOSIS(expr)
Alias for
KURTOSIS_POPKURTOSIS_POP(expr)
KURTOSIS_POP(expr)
Population kurtosis of the values of
exprKURTOSIS_SAMP(expr)
KURTOSIS_SAMP(expr)
Sample kurtosis of the values of
exprKURT_POP(expr)
KURT_POP(expr)
Alias for
KURTOSIS_POPKURT_SAMP(expr)
KURT_SAMP(expr)
Alias for
KURTOSIS_SAMPMAX(expr)
MAX(expr)
Maximum of the values of
exprMEAN(expr)
MEAN(expr)
Alias for
AVGMIN(expr)
MIN(expr)
Minimum of the values of
exprPRODUCT(expr)
PRODUCT(expr)
Product of the values of
exprREGR_AVGX(y, x)
REGR_AVGX(y, x)
Average of the independent variable (
SUM(x)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y)
pairsREGR_AVGY(y, x)
REGR_AVGY(y, x)
Average of the dependent variable (
SUM(y)/N) of the line determined by computing a least-squares-fit linear regression over the given (X, Y)
pairsREGR_COUNT(y, x)
REGR_COUNT(y, x)
Number of input rows used in computing a linear regression, where both expressions are non-null
REGR_INTERCEPT(y, x)
REGR_INTERCEPT(y, x)
Y-intercept of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs
REGR_R2(y, x)
REGR_R2(y, x)
Square of the correlation coefficient, marking how well the least-squares-fit linear regression fit the data set
REGR_SLOPE(y, x)
REGR_SLOPE(y, x)
Slope of the line determined by computing a least-squares-fit linear regression over the given (X, Y) pairs
REGR_SXX(y, x)
REGR_SXX(y, x)
“Sum of squares” of the independent variable (
SUM(x^2) - SUM(x)^2/N) of the line determined by computing a least-squares-fit linear regression
over the given (X, Y) pairsREGR_SXY(y, x)
REGR_SXY(y, x)
“Sum of Products” of independent variable times dependent variable (
SUM(x * y) - SUM(x) * SUM(y)/N) of the line determined by computing a
least-squares-fit linear regression over the given (X, Y) pairsREGR_SYY(y, x)
REGR_SYY(y, x)
“Sum of squares” of the dependent variable (
SUM(y^2) - SUM(y)^2/N) of the line determined by computing a least-squares-fit linear regression
over the given (X, Y) pairsSKEW(expr)
SKEW(expr)
Alias for
SKEWNESS_POPSKEWNESS(expr)
SKEWNESS(expr)
Alias for
SKEWNESS_POPSKEWNESS_POP(expr)
SKEWNESS_POP(expr)
Population skew of the values of
exprSKEWNESS_SAMP(expr)
SKEWNESS_SAMP(expr)
Sample skew of the values of
exprSKEW_POP(expr)
SKEW_POP(expr)
Alias for
SKEWNESS_POPSKEW_SAMP(expr)
SKEW_SAMP(expr)
Alias for
SKEWNESS_SAMPSTDDEV(expr)
STDDEV(expr)
Population standard deviation over values of
expr (i.e. the denominator is N)STDDEV_POP(expr)
STDDEV_POP(expr)
Population standard deviation over values of
expr (i.e. the denominator is N)STDDEV_SAMP(expr)
STDDEV_SAMP(expr)
Sample standard deviation over values of
expr (i.e. the denominator is N-1)SUM(expr)
SUM(expr)
Sum of the values of
exprVAR(expr)
VAR(expr)
Population variance over values of
expr (i.e. the denominator is N)VAR_POP(expr)
VAR_POP(expr)
Population variance over values of
expr (i.e. the denominator is N)VAR_SAMP(expr)
VAR_SAMP(expr)
Sample variance over values of
expr (i.e. the denominator is N-1)VARIANCE(expr)
VARIANCE(expr)
Alias for
VARVARIANCE_POP(expr)
VARIANCE_POP(expr)
Alias for
VAR_POPVARIANCE_SAMP(expr)
VARIANCE_SAMP(expr)
Alias for
VAR_SAMP