Mathematical

acos

Returns the arc cosine of the argument.

Syntax

acos(x)

Arguments

• x — The value for which to find arc cosine of. (U)Int* or Float* or Decimal*

Returned value

Returns the arc cosine of x Float*

Examples

Usage example

SELECT acos(0.5);

1.0471975511965979

Introduced in version 1.1.0.

acosh

Returns the inverse hyperbolic cosine.

Syntax

acosh(x)

Arguments

• x — Hyperbolic cosine of angle. Values from the interval: 1 ≤ x < +∞. (U)Int* or Float* or Decimal*

Returned value

Returns the angle, in radians. Values from the interval: 0 ≤ acosh(x) < +∞. Float64

Examples

Usage example

SELECT acosh(1)

0

Introduced in version 20.12.0.

asin

Calculates the arcsine of the provided argument. For arguments in the range [-1, 1] it returns the value in the range of [-pi() / 2, pi() / 2].

Syntax

asin(x)

Arguments

• x — Argument for which to calculate arcsine of. (U)Int* or Float* or Decimal

Returned value

Returns the arcsine value of the provided argument x Float64

Examples

inverse

SELECT asin(1.0) = pi() / 2, sin(asin(1)), asin(sin(1))

1 1 1

float32

SELECT toTypeName(asin(1.0::Float32))

Float64

nan

SELECT asin(1.1), asin(-2), asin(inf), asin(nan)

nan nan nan nan

Introduced in version 1.1.0.

asinh

Returns the inverse hyperbolic sine.

Syntax

asinh(x)

Arguments

• x — Hyperbolic sine of angle. Values from the interval: -∞ < x < +∞. (U)Int* or Float* or Decimal*

Returned value

Returns the angle, in radians. Values from the interval: -∞ < asinh(x) < +∞. Float64

Examples

Basic usage

SELECT asinh(0)

0

Introduced in version 20.12.0.

atan

Returns the arc tangent of the argument.

Syntax

atan(x)

Arguments

• x — The value for which to find arc tangent of. (U)Int* or Float* or Decimal*

Returned value

Returns the arc tangent of x. Float*

Examples

Usage example

SELECT atan(1);

0.7853981633974483

Introduced in version 1.1.0.

atan2

Returns the atan2 as the angle in the Euclidean plane, given in radians, between the positive x axis and the ray to the point (x, y) ≠ (0, 0).

Syntax

atan2(y, x)

Arguments

• y — y-coordinate of the point through which the ray passes. (U)Int* or Float* or BFloat16 or Decimal*
• x — x-coordinate of the point through which the ray passes. (U)Int* or Float* or BFloat16 or Decimal*

Returned value

Returns the angle θ such that -π < θ ≤ π, in radians Float64

Examples

Usage example

SELECT atan2(1, 1)

0.7853981633974483

Introduced in version 20.12.0.

atanh

Returns the inverse hyperbolic tangent.

Syntax

atanh(x)

Arguments

• x — Hyperbolic tangent of angle. Values from the interval: -1 < x < 1. (U)Int*, Float* or Decimal*. (U)Int* or Float* or Decimal*

Returned value

Returns the angle, in radians. Values from the interval: -∞ < atanh(x) < +∞ Float64

Examples

Usage example

SELECT atanh(0)

0

Introduced in version 20.12.0.

cbrt

Returns the cubic root of the argument.

Syntax

cbrt(x)

Arguments

• x — The value for which to find the cubic root of. (U)Int* or Float* or Decimal*

Returned value

Returns the cubic root of x. Float*

Examples

Usage example

SELECT cbrt(8);

2

Introduced in version 1.1.0.

cos

Returns the cosine of the argument.

Syntax

cos(x)

Arguments

• x — The angle in radians. (U)Int* or Float* or Decimal*

Returned value

Returns the cosine of x. Float*

Examples

Usage example

SELECT cos(0);

1

Introduced in version 1.1.0.

cosh

Returns the hyperbolic cosine of the argument.

Syntax

cosh(x)

Arguments

• x — The angle, in radians. Values from the interval: -∞ < x < +∞. (U)Int* or Float* or Decimal*

Returned value

Returns values from the interval: 1 ≤ cosh(x) < +∞ Float64

Examples

Basic usage

SELECT cosh(0)

1

Introduced in version 20.12.0.

degrees

Converts radians to degrees.

Syntax

degrees(x)

Arguments

• x — Input in radians. (U)Int* or Float* or Decimal*

Returned value

Returns the value of x in degrees. Float64

Examples

Basic usage

SELECT degrees(3.141592653589793)

180

Introduced in version 22.2.0.

e

Returns Euler's constant (e).

Syntax

e()

Returned value

Returns Euler's constant Float64

Examples

Usage example

SELECT e();

2.718281828459045

Introduced in version 1.1.0.

erf

If x is non-negative, then erf(x/(σ√2)) is the probability that a random variable having a normal distribution with standard deviation σ takes the value that is separated from the expected value by more than x.

Syntax

erf(x)

Arguments

• x — The value for which to compute the error function value. (U)Int* or Float* or Decimal*

Returned value

Returns the error function value Float*

Examples

Three sigma rule

SELECT erf(3 / sqrt(2))

┌─erf(divide(3, sqrt(2)))─┐
│      0.9973002039367398 │
└─────────────────────────┘

Introduced in version 1.1.0.

erfc

Returns a number close to 1-erf(x) without loss of precision for large x values.

Syntax

erfc(x)

Arguments

• x — The value for which to find the error function value. (U)Int* or Float* or Decimal*

Returned value

Returns the complementary error function value Float*

Examples

Usage example

SELECT erfc(0);

1

Introduced in version 1.1.0.

exp

Returns e raised to the power of x, where x is the given argument to the function.

Syntax

exp(x)

Arguments

• x — The exponent. (U)Int* or Float* or Decimal*

Returned value

Returns e^x Float*

Examples

Basic usage

SELECT round(exp(-1), 4)

┌─round(exp(-1), 4)─┐
│            0.3679 │
└───────────────────┘

Introduced in version 1.1.0.

exp10

Returns 10 to the power of the given argument.

Syntax

exp10(x)

Arguments

• x — The exponent. (U)Int* or Float* or Decimal*

Returned value

Returns 10^x Float*

Examples

Usage example

SELECT exp10(2);

100

Introduced in version 1.1.0.

exp2

Returns 2 to the power of the given argument.

Syntax

exp2(x)

Arguments

• x — The exponent. (U)Int* or Float* or Decimal*

Returned value

Returns 2^x Float*

Examples

Usage example

SELECT exp2(3);

8

Introduced in version 1.1.0.

factorial

Computes the factorial of an integer value. The factorial of 0 is 1. Likewise, the factorial() function returns 1 for any negative value. The maximum positive value for the input argument is 20, a value of 21 or greater will cause an exception.

Syntax

factorial(n)

Arguments

• n — Integer value for which to calculate the factorial. Maximum value is 20. (U)Int8/16/32/64

Returned value

Returns the factorial of the input as UInt64. Returns 1 for input 0 or any negative value. UInt64

Examples

Usage example

factorial(10)

3628800

Introduced in version 22.11.0.

hypot

Returns the length of the hypotenuse of a right-angle triangle. Hypot avoids problems that occur when squaring very large or very small numbers.

Syntax

hypot(x, y)

Arguments

• x — The first cathetus of a right-angle triangle. (U)Int* or Float* or BFloat16 or Decimal*
• y — The second cathetus of a right-angle triangle. (U)Int* or Float* or BFloat16 or Decimal*

Returned value

Returns the length of the hypotenuse of a right-angle triangle. Float64

Examples

Basic usage

SELECT hypot(1, 1)

1.4142135623730951

Introduced in version 20.12.0.

intExp10

Like exp10 but returns a UInt64 number.

Syntax

intExp10(x)

Arguments

• x — The exponent. Int* or UInt* or Float*

Returned value

Returns 10^x. UInt64

Examples

Usage example

SELECT intExp10(2);

100

Introduced in version 1.1.0.

intExp2

Like exp2 but returns a UInt64 number.

Syntax

intExp2(x)

Arguments

• x — The exponent. Int* or UInt* or Float*

Returned value

Returns 2^x. UInt64

Examples

Usage example

SELECT intExp2(3);

8

Introduced in version 1.1.0.

isPrime

Returns 1 if the argument is a prime number, otherwise 0.

Uses an exact lookup bitmap for small values and a deterministic Miller-Rabin test for larger values. The result is exact for every supported input type.

For wider unsigned integer types (UInt128, UInt256), use isProbablePrime instead.

Syntax

isPrime(n)

Arguments

• n — Unsigned integer to test for primality. UInt8 or UInt16 or UInt32 or UInt64

Returned value

Returns 1 if n is prime, 0 otherwise. UInt8

Examples

Prime number

SELECT isPrime(17)

1

Composite number

SELECT isPrime(18)

0

Large UInt64 prime

SELECT isPrime(18446744073709551557)

1

Maximum UInt64 value

SELECT isPrime(18446744073709551615)

0

Introduced in version 26.5.0.

isProbablePrime

Returns 1 if the argument is probably prime, 0 if it is definitely composite.

For UInt8, UInt16, UInt32, and UInt64, the result is exact and matches isPrime. The rounds argument is ignored.

For UInt128 and UInt256, a return value of 1 is probabilistic. The optional rounds argument controls how many Miller-Rabin rounds are used: more rounds reduce the chance of a false positive and increase the running time. With uniformly random witnesses, the false-positive rate for a fixed composite is bounded by 4^(-rounds); the default of 25 keeps this bound below 10^-15, and the maximum of 256 keeps it below 10^-154.

The function is deterministic: witnesses are seeded from n, so the same (n, rounds) pair always produces the same result. The 4^(-rounds) bound is the per-input probability under uniformly random witnesses; with our deterministic seeding it instead describes a fraction over inputs — a composite that fools its witness sequence will reproducibly return 1.

Syntax

isProbablePrime(n[, rounds])

Arguments

• n — Unsigned integer to test for primality. UInt8 or UInt16 or UInt32 or UInt64 or UInt128 or UInt256
• rounds — Optional positive integer constant in [1, 256]. Number of Miller-Rabin rounds for UInt128/UInt256 (ignored for narrower types). Default 25. UInt8 or UInt16 or UInt32 or UInt64

Returned value

Returns 1 if n is probably prime, 0 if it is definitely composite. UInt8

Examples

Small prime

SELECT isProbablePrime(17)

1

Small composite

SELECT isProbablePrime(18)

0

Largest UInt64 prime (exact result)

SELECT isProbablePrime(18446744073709551557)

1

Mersenne prime M_127 (UInt128)

SELECT isProbablePrime(toUInt128('170141183460469231731687303715884105727'))

1

Curve25519 base field prime 2^255 - 19 (UInt256)

SELECT isProbablePrime(toUInt256('57896044618658097711785492504343953926634992332820282019728792003956564819949'))

1

Faster, lower-confidence check: 5 rounds

SELECT isProbablePrime(toUInt256('57896044618658097711785492504343953926634992332820282019728792003956564819949'), 5)

1

Introduced in version 26.5.0.

lgamma

Returns the logarithm of the gamma function.

Syntax

lgamma(x)

Arguments

• x — The number for which to compute the logarithm of the gamma function. (U)Int* or Float* or Decimal*

Returned value

Returns the logarithm of the gamma function of x. Float*

Examples

Usage example

SELECT lgamma(5);

3.1780538303479458

Introduced in version 1.1.0.

log

Returns the natural logarithm of the argument.

Syntax

log(x)

Arguments

• x — The number for which to compute the natural logarithm of. (U)Int* or Float* or Decimal*

Returned value

Returns the natural logarithm of x. Float*

Examples

Usage example

SELECT log(10);

2.302585092994046

Introduced in version 1.1.0.

log10

Returns the decimal logarithm of the argument.

Syntax

log10(x)

Arguments

• x — Number for which to compute the decimal logarithm of. (U)Int* or Float* or Decimal*

Returned value

Returns the decimal logarithm of x. Float*

Examples

Usage example

SELECT log10(100);

2

Introduced in version 1.1.0.

log1p

Calculates log(1+x). The calculation log1p(x) is more accurate than log(1+x) for small values of x.

Syntax

log1p(x)

Arguments

• x — Values from the interval: -1 < x < +∞. (U)Int* or Float* or Decimal*

Returned value

Returns values from the interval: -∞ < log1p(x) < +∞ Float64

Examples

Usage example

SELECT log1p(0)

0

Introduced in version 20.12.0.

log2

Returns the binary logarithm of the argument.

Syntax

log2(x)

Arguments

• x — The number for which to compute the binary logarithm of. (U)Int* or Float* or Decimal*

Returned value

Returns the binary logarithm of x. Float*

Examples

Usage example

SELECT log2(8);

3

Introduced in version 1.1.0.

pi

Returns pi (π).

Syntax

pi()

Returned value

Returns pi Float64

Examples

Usage example

SELECT pi();

3.141592653589793

Introduced in version 1.1.0.

pow

Returns x raised to the power of y.

Syntax

pow(x, y)

Arguments

• x — The base. (U)Int8/16/32/64 or Float* or BFloat16 or Decimal*
• y — The exponent. (U)Int8/16/32/64 or Float* or BFloat16 or Decimal*

Returned value

Returns x^y Float64

Examples

Usage example

SELECT pow(2, 3);

8

Introduced in version 1.1.0.

proportionsZTest

Returns test statistics for the two proportion Z-test - a statistical test for comparing the proportions from two populations x and y. The function supports both pooled and unpooled estimation methods for the standard error. In the pooled version, the two proportions are averaged and only one proportion is used to estimate the standard error. In the unpooled version, the two proportions are used separately.

Syntax

proportionsZTest(successes_x, successes_y, trials_x, trials_y, conf_level, pool_type)

Arguments

• successes_x — Number of successes in population x. UInt64
• successes_y — Number of successes in population y. UInt64
• trials_x — Number of trials in population x. UInt64
• trials_y — Number of trials in population y. UInt64
• conf_level — Confidence level for the test. Float64
• pool_type — Selection of pooling method for standard error estimation. Can be either 'unpooled' or 'pooled'. String

Returned value

Returns a tuple containing: z_stat (Z statistic), p_val (P value), ci_low (lower confidence interval), ci_high (upper confidence interval). Tuple(Float64, Float64, Float64, Float64)

Examples

Usage example

SELECT proportionsZTest(10, 11, 100, 101, 0.95, 'unpooled');

┌─proportionsZTest(10, 11, 100, 101, 0.95, 'unpooled')───────────────────────────────┐
│ (-0.20656724435948853,0.8363478437079654,-0.09345975390115283,0.07563797172293502) │
└────────────────────────────────────────────────────────────────────────────────────┘

Introduced in version 22.3.0.

radians

Converts degrees to radians.

Syntax

radians(x)

Arguments

• x — Input in degrees. (U)Int* or Float* or Decimal*

Returned value

Returns value in radians Float64

Examples

Usage example

SELECT radians(180)

3.141592653589793

Introduced in version 22.2.0.

sigmoid

Calculates the sigmoid function: 1 / (1 + exp(-x)). The sigmoid function maps any real number to the range (0, 1) and is commonly used in machine learning.

Syntax

sigmoid(x)

Arguments

• x — The input value. (U)Int* or Float*

Returned value

Returns the sigmoid of the input value, in the range (0, 1). Float64

Examples

Basic usage

SELECT sigmoid(0)

0.5

Introduced in version 20.1.0.

sign

Returns the sign of a real number.

Syntax

sign(x)

Arguments

• x — Values from -∞ to +∞. (U)Int* or Decimal* or Float*

Returned value

Returns -1 for x < 0, 0 for x = 0, 1 for x > 0. Int8

Examples

Sign for zero

SELECT sign(0)

0

Sign for positive

SELECT sign(1)

1

Sign for negative

SELECT sign(-1)

-1

Introduced in version 21.2.0.

sin

Returns the sine of the argument.

Syntax

sin(x)

Arguments

• x — The number whose sine will be returned. (U)Int* or Float* or Decimal*

Returned value

Returns the sine of x.

Examples

simple

SELECT sin(1.23)

0.9424888019316975

Introduced in version 1.1.0.

sinh

Returns the hyperbolic sine.

Syntax

sinh(x)

Arguments

• x — The angle, in radians. Values from the interval: -∞ < x < +∞. (U)Int* or Float* or Decimal*

Returned value

Returns values from the interval: -∞ < sinh(x) < +∞ Float64

Examples

Usage example

SELECT sinh(0)

0

Introduced in version 20.12.0.

sqrt

Returns the square root of the argument.

Syntax

sqrt(x)

Arguments

• x — The number for which to find the square root of. (U)Int* or Float* or Decimal*

Returned value

Returns the square root of x Float*

Examples

Usage example

SELECT sqrt(16);

4

Introduced in version 1.1.0.

tan

Returns the tangent of the argument.

Syntax

tan(x)

Arguments

• x — The angle in radians. (U)Int* or Float* or Decimal*

Returned value

Returns the tangent of x. Float*

Examples

Usage example

SELECT tan(0);

0

Introduced in version 1.1.0.

tanh

Returns the hyperbolic tangent.

Syntax

tanh(x)

Arguments

• x — The angle in radians. Values from the interval: -∞ < x < +∞. (U)Int* or Float* or Decimal*

Returned value

Returns values from the interval: -1 < tanh(x) < 1 Float*

Examples

Usage example

SELECT tanh(0)

0

Introduced in version 20.1.0.

tgamma

Returns the gamma function.

Syntax

tgamma(x)

Arguments

• x — The number for which to compute the gamma function of. (U)Int* or Float* or Decimal*

Returned value

Returns the gamma function value Float*

Examples

Usage example

SELECT tgamma(5);

24

Introduced in version 1.1.0.

widthBucket

Returns the number of the bucket in which parameter operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 if operand is less than low, and returns count+1 if operand is greater than or equal to high. There is also a case insensitive alias called WIDTH_BUCKET to provide compatibility with other databases.

Syntax

widthBucket(operand, low, high, count)

Arguments

• operand — The value for which to determine the bucket. (U)Int8/16/32/64
• low — The lower bound of the histogram range. (U)Int8/16/32/64
• high — The upper bound of the histogram range. (U)Int8/16/32/64
• count — The number of equal-width buckets. Cannot be zero. UInt8/16/32/64

Returned value

Returns the bucket number as an integer. Returns 0 if operand < low, returns count+1 if operand >= high. UInt8/16/32/64

Examples

Usage example

widthBucket(10.15, -8.6, 23, 18)

11

Introduced in version 23.3.0.