Math Functions

Mathematics

abs

abs returns the absolute value of the argument.

Parameters:

  • number - the number to operate on.

Usage:

  • abs(number)

Returns:

  • If number is positive or zero, returns number.
  • If number is negative, returns -(number)
  • Data type of result is the same as the data type of number

cbrt, cube root

cbrt returns the cube root of the argument.

Parameters:

  • number - the number to operate on.

Usage:

  • cbrt(number)

Returns:

  • Float64

ceil, ceiling

ceil and ceiling return the next highest integer of the argument.

Parameters:

  • number - the number to operate on.
  • OPTIONAL: dec_place - the decimal place to round to.
    - 0 return no decimal places
    - positive = return that many decimal places to the right of the decimal
    - negative = return the ceiling of the number that many decimal places to the left of the decimal

Usage:

  • ceil(number), ceil(number, dec_place)
  • ceiling(number), ceiling(number, dec_place)

Returns:

  • Float64 if number is not an integer, or if dec_place is 1 or greater
  • UInt if number is a positive integer
  • Int if number is a negative integer

divide, /

divide is the functional call for division. Works as an operator as well.

Parameters:

  • dividend - the number to divide into parts. The top half of the fraction.
  • divisor - the number to divide by. The bottom half of the fraction.

Usage:

  • function call: divide(dividend, divisor)
  • operator: dividend/divisor

Returns:

  • Float64 representing the result
  • the return value is a null Float64 if the divisor is 0

e

Returns a double with the approximate value of the constant "e".

  • The constant e is a mathematical constant that is the base of the natural logarithm; the unique number whose natural logarithm is equal to one.
  • e is approximately equal to 2.71828
  • More Information about e

Parameters:

  • none

Usage:

  • e()

Returns:

  • 2.718281828459045 as a Float64

erf

Calculates the error function of an argument. For a random variable Y that is normally distributed with mean 0 and variance 1/2, erf(arg) is the probability of Y falling in the range [−arg, arg].

More information on the Error Function

Parameters:

  • arg - a number, should be positive for meaningful results.

Usage:

  • erf(arg)

Returns:

  • a probability represented as Float64 between 0 and 1.

erfc

Calculates the complementary error function, erfc(arg) = 1 - erf(arg)

More information on the Error Function

Parameters:

  • arg - a number, should be positive for meaningful results.

Usage:

  • erfc(arg)

Returns:

  • a probability represented as Float64 between 0 and 1.

exp

Calculates the value of e raised to the power of the argument.

Parameters:

  • arg - a number

Usage:

  • exp(arg)

Returns:

  • The constant e raised to the power of arg represented as Float64

exp2

Calculates the value of 2 raised to the power of the argument.

Parameters:

  • arg - a number

Usage:

  • exp2(arg)

Returns:

  • 2 raised to the power of arg represented as Float64

exp10

Calculates the value of 10 raised to the power of the argument.

Parameters:

  • arg - a number

Usage:

  • exp10(arg)

Returns:

  • 10 raised to the power of arg represented as Float64

floor

floor rounds down to the closest integer

Parameters:

  • number - the number to operate on.
  • OPTIONAL: dec_place - the decimal place to round to.
    - 0 return no decimal places
    - positive = return that many decimal places to the right of the decimal
    - negative = return the floor of the number that many decimal places to the left of the decimal

Usage:

  • floor(number), floor(number, dec_place)

Returns:

  • Float64 if number is not an integer, or dec_place is positive
  • UInt if number is a positive integer
  • Int if number is a negative integer

gcd

gcd calculates the greatest common divisor of two number arguments.

Parameters:

  • arg1 - the first argument.
  • arg2 - the second argument.

Usage:

  • gcd(arg1,arg2)

Returns:

  • UInt representing the result if both values are UInt
  • Float64 if at least one of the arguments is Float64
  • DB::Exception: Division by zero... if one of the numbers is 0 or very small negative number.

intDiv, intDivOrZero

intDiv Does division on two numbers, and returns an Int64 that is rounded down to the nearest integer. If the result is negative, the result is rounded up. An error is thrown when dividing by 0 or a small negative number.

intDivOrZero returns zero instead of an exception in the case of division by 0 or small negative numbers.

In either case, the arguments do not need to be integers, just a number type.

Parameters:

  • dividend - the number to divide into parts. The top half of the fraction.
  • divisor - the number to divide by. The bottom half of the fraction.

Usage:

  • intDiv(dividend, divisor)
  • intDivOrZero(dividend, divisor)

Returns:

  • Int64 representing the result, rounded to a whole number. If the result is positive, the number is rounded down. If the result is negative, the result is rounded up.
  • When using intDiv, DB::Exception: Division by zero... if one of the numbers is 0 or very small negative number.
  • When using intDivOrZero, 0 is returned with division by zero or a very small negative number.

intExp2

Returns a UInt64 that is close to 2 to the power of the argument.

Parameters:

  • arg - a number

Usage:

  • intExp2(arg)

Returns:

  • 2 raised to the power of arg represented as the closestUInt64 to the result.

intExp10

Returns a UInt64 that is close to 10 to the power of the argument.

Parameters:

  • arg - a number

Usage:

  • intExp10(arg)

Returns:

  • 10 raised to the power of arg represented as the closestUInt64 to the result.

lcm

lcm calculates the least common multiple of two numbers.

Parameters:

  • arg1 - the first argument.
  • arg2 - the second argument.

Usage:

  • lcm(arg1,arg2)

Returns:

  • UInt64 if both arguments are integers
  • Float64 if at least one of the numbers is a Float64
  • DB::Exception: Division by zero... if one of the numbers is 0 or very small negative number.

lgamma

The Gamma function extends the the factorial calculation to real and complex numbers. The lgamma function is used because the gamma function can grow very quickly.

Parameters:

  • arg - a number

Usage:

  • lgamma(arg)

Returns:

  • Float64

ln, log

Returns a double value close to ln(arg). log is a synonym for ln.

Parameters:

  • arg - a number

Usage:

  • ln(arg)
  • log(arg)
  • log(x,y) can be calculated with ln(x)/ln(y)

Returns:

  • Float64 value close to ln(arg).

log2

Calculates a double value that is approximately the binary logarithm of the argument.

Parameters:

  • arg - a number

Usage:

  • log2(arg)

Returns:

  • Float64 value close to log2(arg).

log10

Calculates a double that is close to the decimal logarithm of the argument.

Parameters:

  • arg - a number

Usage:

  • log10(arg)

Returns:

  • Float64 value close to log10(arg).

minus, -

The minus function and the - operator perform subtraction on two numbers.

Parameters:

  • arg1 - the number to subract from, or the minuend.
  • arg2 - the number to subtract, or the subtrahend.

Usage:

  • function call: minus(arg1, arg2)
  • operator: arg1 - arg2

Returns:

  • Int64 if both numbers are integers.
  • Float64 if at least one of the numbers is a Float64.

modulo, moduloOrZero, % operator

The modulo function and the % operator calculate the remainder, or modulus, from division on two numbers. If the divisor is 0 or a very small negative number, an error will be returned.

moduloOrZero behaves like intDivOrZero, returning 0 instead of an error when the divisor is 0 or a very small negative number.

Parameters:

  • dividend - the number to divide into parts. The top half of the fraction.
  • divisor - the number to divide by. The bottom half of the fraction.

In either case, the arguments do not need to be integers, just a number type.

Parameters:

  • dividend - the number to divide into parts. The top half of the fraction.
  • divisor - the number to divide by. The bottom half of the fraction.

Usage:

  • function calls
    - modulo(dividend, divisor)
    - moduloOrZero(dividend,divisor)
  • operator: dividend%divisor

Returns:

  • Int64 representing the result, rounded to a whole number. If the result is positive, the number is rounded down. If the result is negative, the result is rounded up.
  • When using modulo or %, DB::Exception: Division by zero... if one of the numbers is 0 or very small negative number.
  • When using moduloOrZero, 0 is returned with division by zero or a very small negative number.

multiply, *

The multiply function and the * operator perform multiplication on two numbers.

Parameters: (order of arguments does not matter)

  • arg1 - the first argument
  • arg2 - the second argument

Usage:

  • function call: multiply(arg1, arg2)
  • operator: arg1 * arg2

Returns:

  • UInt64 if both numbers are UInt
  • Int64 if both numbers are integers, but at least one is negative
  • Float64 if at least one of the numbers is a Float64

negate, - unary operator

The negate function and the - unary operator return the argument with the sign reversed.

Parameters:

  • arg - the number to negate

Usage:

  • function call: negate(arg)
  • operator: -arg

Returns:

  • Int64 if arg is an integer
  • Float64 if arg is a floating point

plus, +

The plus function and the + operator perform addition on two numbers.

Parameters: (order of arguments does not matter)

  • arg1 - the first argument
  • arg2 - the second argument

Usage:

  • function call: plus(arg1, arg2)
  • operator: arg1 + arg2

Returns:

  • UInt64 if the result is a positive integer
  • Int64 if the result is a negative integer
  • Float64 if at least one of the numbers is a Float64

pow, power

Returns a Float64 that is close to the value of base to the power of pow.

Parameters: (order of arguments does not matter)

  • base - the argument to apply the exponent to
  • pow - the exponent to apply

Usage:

  • power(base, pow)
  • pow(base, pow)

Returns:

  • Float64 close to base raised to the power of pow

round, roundBankers

round rounds a number to dec_place number of decimal places. When a number is half way between two numbers, like 10.5, the number is rounded to the nearest even number. This is called "Bankers Rounding" or "Round to Even".

  • round(10.5) = 10
  • round(11.5) = 12

Parameters:

  • number - the number to operate on.
  • OPTIONAL: dec_place - the decimal place to round to.
    - 0 return no decimal places
    - positive = return that many decimal places to the right of the decimal
    - negative = return dec_place decimal places to the left of the decimal

Usage:

  • round(number), round(number, dec_place)
  • roundBankers(number), roundBankers(number, dec_place)

Returns:

  • Float64 if number is not an integer, or dec_place is positive
  • UInt if number is a positive integer
  • Int if number is a negative integer

roundAge

roundAge rounds down to the closest value in the the set of ages [0, 18, 25, 35, 45, 55] to help categorize by age bracket. This function is specialized for Yandex.Metrica. This is a specialized version of roundDown.

Parameters:

  • number - the number to operate on.

Usage:

  • roundAge(number)

Returns:

  • UInt from the set [0, 18, 25, 35, 45, 55]
  • returns 0 if number is negative

roundDown

roundDown rounds a number down to the lowest closest value in an array_of_values

Parameters:

  • number - the number to round down.
  • array_of_values - the values to choose from when rounding down.

Usage:

  • roundDown(number, array_of_values)

Returns:

  • A value that is the same data type as number

roundDuration

roundDuration rounds down to the closest value in the the set of time relevant values [0, 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000]. See Time-series functions for more operations on time units.

Parameters:

  • number - the number to operate on.

Usage:

  • roundDuration(number)

Returns:

  • UInt from the set [0, 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000]
  • returns 0 if number is negative

roundToExp2

roundToExp2 rounds down to the closest value that is a power of 2. For example, [0, 1, 2, 4, 8, 16, 32 ....]. This is particularly useful when dealing with memory metrics

Parameters:

  • number - the number to operate on.

Usage:

  • roundToExp2(number)

Returns:

  • UInt that is 2 raised to the power of some number.
  • If number is negative, 0 is returned

sqrt

Returns the square root of the argument.

Parameters:

  • number - the number to operate on.

Usage:

  • sqrt(number)

Returns:

  • Float64 if number is positive
  • null if number is negative

tgamma

The tgamma function is the implementation of the Gamma function which extends the the factorial calculation to real and complex numbers.

Parameters:

  • arg - a number

Usage:

  • tgamma(arg)

Returns:

  • Float64

trunc, truncate

truncate removes digits from a number. It does not round in the process. Works like floor except for negative values.

Parameters:

  • number - the number to operate on.
  • OPTIONAL: dec_place - the decimal place to truncate to.
    - 0 returns no decimal places. This is the default.
    - positive = truncate after that many decimal places to the right of the decimal
    - negative = truncate the number that many decimal places to the left of the decimal

Usage:

  • trunc(number), truncate(number, dec_place)

Returns:

  • Float64 if number is not an integer, or dec_place is positive
  • UInt if number is a positive integer
  • Int if number is a negative integer

Trigonometry

acos

The arc cosine of an argument, where the value of the argument is in radians.

Parameters:

  • arg - the argument, valid values are [-1, +1]

Usage:

  • acos(arg)

Returns:

  • Float64 in radians.
  • If arg is outside of the range [-1, +1] null will be returned.

asin

The arc sine of a, where the value of a is in radians.

Parameters:

  • arg - the argument, valid values are [-1, +1]

Usage:

  • asin(arg)

Returns:

  • Float64 in radians.
  • If arg is outside of the range [-1, +1] null will be returned.

atan

The arc tangent of a, where the value of a is in radians.

Parameters:

  • arg - the argument

Usage:

  • atan(arg)

Returns:

  • Float64
  • Range of results is approximately [-1.57, 1.57] radians.

cos

The cosine of a number, where the value of a is in radians.

Parameters:

  • arg - the argument

Usage:

  • cos(arg)

Returns:

  • Float64 in radians

pi

Returns a double with the approximate value of π.

Parameters:

  • none

Usage:

  • pi()

Returns:

  • 3.141592653589793 as a Float64

sin

The sine of a number, where the number is in radians.

Parameters:

  • arg - the argument

Usage:

  • sin(arg)

Returns:

  • Float64

tan

The tangent of a number, where the number is in radians.

Parameters:

  • arg - the argument

Usage:

  • tan(arg)

Returns:

  • Float64

Did this page help you?