Mathematical functions - ClickHouse Documentation

acos

Introduced in: v1.1.0 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

Query

SELECT acos(0.5);

Response

1.0471975511965979

acosh

Introduced in: v20.12.0 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

Query

SELECT acosh(1)

Response

asin

Introduced in: v1.1.0 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

Query

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

Response

1 1 1

float32

Query

SELECT toTypeName(asin(1.0::Float32))

Response

Float64

nan

Query

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

Response

nan nan nan nan

asinh

Introduced in: v20.12.0 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

Query

SELECT asinh(0)

Response

atan

Introduced in: v1.1.0 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

Query

SELECT atan(1);

Response

0.7853981633974483

atan2

Introduced in: v20.12.0 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 Decimal*
x — x-coordinate of the point through which the ray passes. (U)Int* or Float* or Decimal*

Returned value Returns the angle θ such that -π < θ ≤ π, in radians Float64 Examples Usage example

Query

SELECT atan2(1, 1)

Response

0.7853981633974483

atanh

Introduced in: v20.12.0 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

Query

SELECT atanh(0)

Response

cbrt

Introduced in: v1.1.0 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

Query

SELECT cbrt(8);

Response

cos

Introduced in: v1.1.0 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

Query

SELECT cos(0);

Response

cosh

Introduced in: v20.12.0 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

Query

SELECT cosh(0)

Response

degrees

Introduced in: v22.2.0 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

Query

SELECT degrees(3.141592653589793)

Response

e

Introduced in: v1.1.0 Returns Euler’s constant (e). Syntax

e()

Arguments

None.

Returned value Returns Euler’s constant Float64 Examples Usage example

Query

SELECT e();

Response

2.718281828459045

erf

Introduced in: v1.1.0 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

Query

SELECT erf(3 / sqrt(2))

Response

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

erfc

Introduced in: v1.1.0 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

Query

SELECT erfc(0);

Response

exp

Introduced in: v1.1.0 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

Query

SELECT round(exp(-1), 4)

Response

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

exp10

Introduced in: v1.1.0 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

Query

SELECT exp10(2);

Response

exp2

Introduced in: v1.1.0 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

Query

SELECT exp2(3);

Response

factorial

Introduced in: v22.11.0 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

Query

factorial(10)

Response

hypot

Introduced in: v20.12.0 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 Decimal*
y — The second cathetus of a right-angle triangle. (U)Int* or Float* or Decimal*

Returned value Returns the length of the hypotenuse of a right-angle triangle. Float64 Examples Basic usage

Query

SELECT hypot(1, 1)

Response

1.4142135623730951

intExp10

Introduced in: v1.1.0 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

Query

SELECT intExp10(2);

Response

intExp2

Introduced in: v1.1.0 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

Query

SELECT intExp2(3);

Response

isPrime

Introduced in: v26.5.0 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

Query

SELECT isPrime(17)

Response

Composite number

Query

SELECT isPrime(18)

Response

Large UInt64 prime

Query

SELECT isPrime(18446744073709551557)

Response

Maximum UInt64 value

Query

SELECT isPrime(18446744073709551615)

Response

isProbablePrime

Introduced in: v26.5.0 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 makes this bound smaller than 10^-15. Values above 256 are rejected as BAD_ARGUMENTS, since they offer no meaningful improvement and only waste time. The function is deterministic: witnesses are derived from a fixed seed computed from n, so the same (n, rounds) pair always produces the same result. As a consequence, a composite that happens to pass this particular witness sequence will reproducibly return 1, rather than failing the test independently on each call. The 4^(-rounds) bound should therefore be read as a guide to typical accuracy across inputs, not as a per-call probability for a fixed input. 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

Query

SELECT isProbablePrime(17)

Response

Small composite

Query

SELECT isProbablePrime(18)

Response

Largest UInt64 prime (exact result)

Query

SELECT isProbablePrime(18446744073709551557)

Response

Mersenne prime M_127 (UInt128)

Query

SELECT isProbablePrime(toUInt128('170141183460469231731687303715884105727'))

Response

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

Query

SELECT isProbablePrime(toUInt256('57896044618658097711785492504343953926634992332820282019728792003956564819949'))

Response

Faster, lower-confidence check: 5 rounds

Query

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

Response

lgamma

Introduced in: v1.1.0 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

Query

SELECT lgamma(5);

Response

3.1780538303479458

log

Introduced in: v1.1.0 Returns the natural logarithm of the argument. Syntax

log(x)

Aliases: ln 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

Query

SELECT log(10);

Response

2.302585092994046

log10

Introduced in: v1.1.0 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

Query

SELECT log10(100);

Response

log1p

Introduced in: v20.12.0 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

Query

SELECT log1p(0)

Response

log2

Introduced in: v1.1.0 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

Query

SELECT log2(8);

Response

pi

Introduced in: v1.1.0 Returns pi (π). Syntax

pi()

Arguments

None.

Returned value Returns pi Float64 Examples Usage example

Query

SELECT pi();

Response

3.141592653589793

pow

Introduced in: v1.1.0 Returns x raised to the power of y. Syntax

pow(x, y)

Aliases: power Arguments

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

Returned value Returns x^y Float64 Examples Usage example

Query

SELECT pow(2, 3);

Response

proportionsZTest

Introduced in: v22.3.0 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

Query

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

Response

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

radians

Introduced in: v22.2.0 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

Query

SELECT radians(180)

Response

3.141592653589793

sigmoid

Introduced in: v20.1.0 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

Query

SELECT sigmoid(0)

Response

0.5

sign

Introduced in: v21.2.0 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

Query

SELECT sign(0)

Response

Sign for positive

Query

SELECT sign(1)

Response

Sign for negative

Query

SELECT sign(-1)

Response

-1

sin

Introduced in: v1.1.0 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

Query

SELECT sin(1.23)

Response

0.9424888019316975

sinh

Introduced in: v20.12.0 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

Query

SELECT sinh(0)

Response

sqrt

Introduced in: v1.1.0 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

Query

SELECT sqrt(16);

Response

tan

Introduced in: v1.1.0 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

Query

SELECT tan(0);

Response

tanh

Introduced in: v20.1.0 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

Query

SELECT tanh(0)

Response

tgamma

Introduced in: v1.1.0 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

Query

SELECT tgamma(5);

Response

widthBucket

Introduced in: v23.3.0 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)

Aliases: width_bucket 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

Query

widthBucket(10.15, -8.6, 23, 18)

Response

SQL Reference

Documentation Index

​acos

​acosh

​asin

​asinh

​atan

​atan2

​atanh

​cbrt

​cos

​cosh

​degrees

​e

​erf

​erfc

​exp

​exp10

​exp2

​factorial

​hypot

​intExp10

​intExp2

​isPrime

​isProbablePrime

​lgamma

​log

​log10

​log1p

​log2

​pi

​pow

​proportionsZTest

​radians

​sigmoid

​sign

​sin

​sinh

​sqrt

​tan

​tanh

​tgamma

​widthBucket

acos

acosh

asin

asinh

atan

atan2

atanh

cbrt

cos

cosh

degrees

e

erf

erfc

exp

exp10

exp2

factorial

hypot

intExp10

intExp2

isPrime

isProbablePrime

lgamma

log

log10

log1p

log2

pi

pow

proportionsZTest

radians

sigmoid

sign

sin

sinh

sqrt

tan

tanh

tgamma

widthBucket