Numeric Functions

plus

Calculates the sum of two values a and b.

plus(a, b)

It is possible to add an integer and a date or date with time. The former operation increments the number of days in the date, the latter operation increments the number of seconds in the date with time.

Alias

a + b (operator)

minus

Calculates the difference of two values a and b. The result is always signed.

Similar to plus, it is possible to subtract an integer from a date or date with time.

minus(a, b)

Alias

a - b (operator)

multiply

Calculates the product of two values a and b.

multiply(a, b)

Alias:

a * b (operator)

divide

Calculates the quotient of two values a and b. The result type is always Float64 (opens new window). Integer division is provided by the intDiv function.

divide(a, b)

Division by 0 returns inf, inf, or nan.

Alias

a / b (operator)

exp

Returns e to the power of the given argument.

exp(x)

log2

Returns the binary logarithm of the argument.

log2(x)

log10

Returns the decimal logarithm of the argument.

log10(x)

cos

Returns the cosine of the argument.

cos(x)

tan

Returns the tangent of the argument.

tan(x)

sin

Returns the sine of the argument

sin(x)

sqrt

Returns the square root of the argument.

sqrt(x)

abs

Calculates the absolute value of a. Has no effect if a is of an unsigned type. If a is of a signed type, it returns an unsigned number.

abs(a)

floor

Returns the largest round number that is less than or equal to x. A round number is a multiple of 1 / 10N, or the nearest number of the appropriate data type if 1 / 10N isn’t exact. ‘N’ is an integer constant, optional parameter. By default it is zero, which means to round to an integer. ‘N’ may be negative.

floor(x[, N])

x is any numeric type. The result is a number of the same type. For integer arguments, it makes sense to round with a negative N value (for non-negative N, the function does not do anything). If rounding causes overflow (for example, floor(-128, -1)), an implementation-specific result is returned.

Examples

floor(123.45, 1) = 123.4, floor(123.45, -1) = 120

ceil

Returns the smallest round number that is greater than or equal to x. In every other way, it is the same as the floor function (see above).

round

Rounds a value to a specified number of decimal places. The function returns the nearest number of the specified order. In case when given number has equal distance to surrounding numbers, the function uses banker’s rounding for float number types and rounds away from zero for the other number types (Decimal).

round(expression [, decimal_places])

Arguments

• expression — A number to be rounded. Can be any expression returning the numeric data type.
• decimal-places — An integer value.
  • If decimal-places > 0 then the function rounds the value to the right of the decimal point.
  • If decimal-places < 0 then the function rounds the value to the left of the decimal point.
  • If decimal-places = 0 then the function rounds the value to integer. In this case the argument can be omitted.

Returned value

The rounded number of the same type as the input number.

Examples

• use with Float

  SELECT number / 2 AS x, round(x) FROM system.numbers LIMIT 3
  

  ┌───x─┬─round(divide(number, 2))─┐
  │   0 │                        0 │
  │ 0.5 │                        0 │
  │   1 │                        1 │
  └─────┴──────────────────────────┘
  

• use with Decimal

  SELECT cast(number / 2 AS Decimal(10,4)) AS x, round(x) FROM system.numbers LIMIT 3
  

  ┌───x─┬─round(CAST(divide(number, 2), 'Decimal(10, 4)'))─┐
  │   0 │                                                0 │
  │ 0.5 │                                                1 │
  │   1 │                                                1 │
  └─────┴──────────────────────────────────────────────────┘
  

• Rounding to the nearest number.

  round(3.2, 0) = 3
  round(4.1267, 2) = 4.13
  round(22,-1) = 20
  round(467,-2) = 500
  round(-467,-2) = -500
  

• Banker’s rounding.

  round(3.5) = 4
  round(4.5) = 4
  round(3.55, 1) = 3.6
  round(3.65, 1) = 3.6
  

rand32

Returns a random UInt32 number, evenly distributed across the range of all possible UInt32 numbers. Uses a linear congruential generator.

randNormal

Returns a random Float64 drawn from a normal distribution (opens new window).

randNormal(mean, variance)

Arguments

• mean - Float64 - mean value of distribution,
• variance - Float64 - variance of the distribution.

Returned value

• Random number.
• Type: Float64.

Example

SELECT randNormal(10, 2) FROM numbers(5)

┌──randNormal(10, 2)─┐
│ 13.389228911709653 │
│ 8.622949707401295  │
│ 10.801887062682981 │
│ 4.5220192605895315 │
│ 10.901239123982567 │
└────────────────────┘