Skip to main content
Skip to main content
Edit this page

Mathematical functions

e​

Returns ee (Euler's constant).

Syntax

e()

Returned value

Type: Float64.

pi​

Returns π\pi (Pi).

Syntax

pi()

Returned value

Type: Float64.

exp​

Returns exe^{x}, where x is the given argument to the function.

Syntax

exp(x)

Arguments

Example

Query:

SELECT round(exp(-1), 4);

Result:

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

Returned value

Type: Float*.

log​

Returns the natural logarithm of the argument.

Syntax

log(x)

Alias: ln(x)

Arguments

Returned value

Type: Float*.

exp2​

Returns 2 to the power of the given argument

Syntax

exp2(x)

Arguments

Returned value

Type: Float*.

intExp2​

Like exp but returns a UInt64.

Syntax

intExp2(x)

log2​

Returns the binary logarithm of the argument.

Syntax

log2(x)

Arguments

Returned value

Type: Float*.

exp10​

Returns 10 to the power of the given argument.

Syntax

exp10(x)

Arguments

Returned value

Type: Float*.

intExp10​

Like exp10 but returns a UInt64.

Syntax

intExp10(x)

log10​

Returns the decimal logarithm of the argument.

Syntax

log10(x)

Arguments

Returned value

Type: Float*.

sqrt​

Returns the square root of the argument.

sqrt(x)

Arguments

Returned value

Type: Float*.

cbrt​

Returns the cubic root of the argument.

cbrt(x)

Arguments

Returned value

Type: Float*.

erf​

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

Syntax

erf(x)

Arguments

Returned value

Type: Float*.

Example

(three sigma rule)

SELECT erf(3 / sqrt(2));

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

erfc​

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

Syntax

erfc(x)

Arguments

Returned value

Type: Float*.

lgamma​

Returns the logarithm of the gamma function.

Syntax

lgamma(x)

Arguments

Returned value

Type: Float*.

tgamma​

Returns the gamma function.

Syntax

gamma(x)

Arguments

Returned value

Type: Float*.

sin​

Returns the sine of the argument

Syntax

sin(x)

Arguments

Returned value

Type: Float*.

Example

Query:

SELECT sin(1.23);

0.9424888019316975

cos​

Returns the cosine of the argument.

Syntax

cos(x)

Arguments

Returned value

Type: Float*.

tan​

Returns the tangent of the argument.

Syntax

tan(x)

Arguments

Returned value

Type: Float*.

asin​

Returns the arc sine of the argument.

Syntax

asin(x)

Arguments

Returned value

Type: Float*.

acos​

Returns the arc cosine of the argument.

Syntax

acos(x)

Arguments

Returned value

Type: Float*.

atan​

Returns the arc tangent of the argument.

Syntax

atan(x)

Arguments

Returned value

Type: Float*.

pow​

Returns xyx^y.

Syntax

pow(x, y)

Alias: power(x, y)

Arguments

Returned value

Type: Float64.

cosh​

Returns the hyperbolic cosine of the argument.

Syntax

cosh(x)

Arguments

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

Returned value

  • Values from the interval: 1≤cosh(x)<+∞1 \le cosh(x) \lt +\infty.

Type: Float64.

Example

SELECT cosh(0);

Result:

┌─cosh(0)──┐
│        1 │
└──────────┘

acosh​

Returns the inverse hyperbolic cosine.

Syntax

acosh(x)

Arguments

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

Returned value

  • The angle, in radians. Values from the interval: 0≤acosh(x)<+∞0 \le acosh(x) \lt +\infty.

Type: Float64.

Example

SELECT acosh(1);

Result:

┌─acosh(1)─┐
│        0 │
└──────────┘

sinh​

Returns the hyperbolic sine.

Syntax

sinh(x)

Arguments

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

Returned value

  • Values from the interval: −∞<sinh(x)<+∞-\infty \lt sinh(x) \lt +\infty.

Type: Float64.

Example

SELECT sinh(0);

Result:

┌─sinh(0)──┐
│        0 │
└──────────┘

asinh​

Returns the inverse hyperbolic sine.

Syntax

asinh(x)

Arguments

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

Returned value

  • The angle, in radians. Values from the interval: −∞<asinh(x)<+∞-\infty \lt asinh(x) \lt +\infty.

Type: Float64.

Example

SELECT asinh(0);

Result:

┌─asinh(0)─┐
│        0 │
└──────────┘

tanh​

Returns the hyperbolic tangent.

Syntax

tanh(x)

Arguments

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

Returned value

  • Values from the interval: −1<tanh(x)<1-1 \lt tanh(x) \lt 1.

Type: Float*.

Example

SELECT tanh(0);

Result:

0

atanh​

Returns the inverse hyperbolic tangent.

Syntax

atanh(x)

Arguments

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

Returned value

  • The angle, in radians. Values from the interval: −∞<atanh(x)<+∞-\infty \lt atanh(x) \lt +\infty.

Type: Float64.

Example

SELECT atanh(0);

Result:

┌─atanh(0)─┐
│        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

Returned value

  • The angle θ such that −π<0≤π-\pi \lt 0 \le \pi, in radians.

Type: Float64.

Example

SELECT atan2(1, 1);

Result:

┌────────atan2(1, 1)─┐
│ 0.7853981633974483 │
└────────────────────┘

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

Returned value

  • The length of the hypotenuse of a right-angle triangle.

Type: Float64.

Example

SELECT hypot(1, 1);

Result:

┌────────hypot(1, 1)─┐
│ 1.4142135623730951 │
└────────────────────┘

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<+∞-1 \lt x \lt +\infty. (U)Int*, Float* or Decimal*.

Returned value

  • Values from the interval: −∞<log1p(x)<+∞-\infty < log1p(x) \lt +\infty.

Type: Float64.

Example

SELECT log1p(0);

Result:

┌─log1p(0)─┐
│        0 │
└──────────┘

sign​

Returns the sign of a real number.

Syntax

sign(x)

Arguments

  • x — Values from −∞-\infty to +∞+\infty. Supports all numeric types in ClickHouse.

Returned value

  • -1 for x < 0
  • 0 for x = 0
  • 1 for x > 0

Type: Int8.

Examples

Sign for the zero value:

SELECT sign(0);

Result:

┌─sign(0)─┐
│       0 │
└─────────┘

Sign for the positive value:

SELECT sign(1);

Result:

┌─sign(1)─┐
│       1 │
└─────────┘

Sign for the negative value:

SELECT sign(-1);

Result:

┌─sign(-1)─┐
│       -1 │
└──────────┘

sigmoid​

Returns the sigmoid function.

Syntax

sigmoid(x)

Parameters

  • x — input value. Values from the interval: −∞<x<+∞-\infty \lt x \lt +\infty. (U)Int*, Float* or Decimal*.

Returned value

  • Corresponding value along the sigmoid curve between 0 and 1. Float64.

Example

Query:

SELECT round(sigmoid(x), 5) FROM (SELECT arrayJoin([-1, 0, 1]) AS x);

Result:

0.26894
0.5
0.73106

degrees​

Converts radians to degrees.

Syntax

degrees(x)

Arguments

Returned value

Example

SELECT degrees(3.141592653589793);

Result:

┌─degrees(3.141592653589793)─┐
│                        180 │
└────────────────────────────┘

radians​

Converts degrees to radians.

Syntax

radians(x)

Arguments

Returned value

  • Value in radians.

Type: Float64.

Example

SELECT radians(180);

Result:

┌──────radians(180)─┐
│ 3.141592653589793 │
└───────────────────┘

factorial​

Computes the factorial of an integer value. Works with any native integer type including UInt(8|16|32|64) and Int(8|16|32|64). The return type is UInt64.

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 exception throw. Syntax

factorial(n)

Example

SELECT factorial(10);

Result:

┌─factorial(10)─┐
│       3628800 │
└───────────────┘

width_bucket​

Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 if operand < low, and returns count+1 if operand >= high.

operand, low, high can be any native number type. count can only be unsigned native integer and its value cannot be zero.

Syntax

widthBucket(operand, low, high, count)

Alias: WIDTH_BUCKET

Example

SELECT widthBucket(10.15, -8.6, 23, 18);

Result:

┌─widthBucket(10.15, -8.6, 23, 18)─┐
│                               11 │
└──────────────────────────────────┘

proportionsZTest​

Returns test statistics for the two proportion Z-test - a statistical test for comparing the proportions from two populations x and y.

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 (way in which the standard error is estimated). Can be either unpooled or pooled. String.
Note

For argument pool_type: 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.

Returned value

  • z_stat: Z statistic. Float64.
  • p_val: P value. Float64.
  • ci_low: The lower confidence interval. Float64.
  • ci_high: The upper confidence interval. Float64.

Example

Query:

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

Result:

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

acos​

Introduced in: v1.1

Returns the arc cosine of the argument.

Syntax

acos(x)

Arguments

Returned value

Returns the arc cosine of x Float*

Examples

Usage example

SELECT acos(0.5);

1.0471975511965979

acosh​

Introduced in: v20.12

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

asin​

Introduced in: v

Calculates the arcsine of the argument.

Takes arbitrary numeric type, which includes floating point and integer numbers, as well as big integers and decimals and returns Float64.

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

It represents an inverse function to function 'sin' on this range: [example:inverse]

It always returns Float64, even if the argument has Float32 type: [example:float32]

For arguments outside of this range, it returns nan: [example:nan]

Every self-respectful data scientist knows how to apply arcsine to improve ads click-through rate with ClickHouse. For more details, see [https://en.wikipedia.org/wiki/Inverse_trigonometric_functions].

Syntax

Arguments

  • None. Returned value

Examples

inverse

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

float32

SELECT toTypeName(asin(1.0::Float32))

nan

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

asinh​

Introduced in: v20.12

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

atan​

Introduced in: v1.1

Returns the arc tangent of the argument.

Syntax

atan(x)

Arguments

Returned value

Returns the arc tangent of x. Float*

Examples

Usage example

SELECT atan(1);

0.7853981633974483

atan2​

Introduced in: v20.12

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

Returned value

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

Examples

Usage example

SELECT atan2(1, 1)

0.7853981633974483

atanh​

Introduced in: v20.12

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

cbrt​

Introduced in: v1.1

Returns the cubic root of the argument.

Syntax

cbrt(x)

Arguments

Returned value

Returns the cubic root of x. Float*

Examples

Usage example

SELECT cbrt(8);

2

cos​

Introduced in: v1.1

Returns the cosine of the argument.

Syntax

cos(x)

Arguments

Returned value

Returns the cosine of x. Float*

Examples

Usage example

SELECT cos(0);

1

cosh​

Introduced in: v20.12

Returns the hyperbolic cosine of the argument.

Syntax

cosh(x)

Arguments

Returned value

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

Examples

Basic usage

SELECT cosh(0)

1

degrees​

Introduced in: v22.2

Converts radians to degrees.

Syntax

degrees(x)

Arguments

Returned value

Returns the value of x in degrees. Float64

Examples

Basic usage

SELECT degrees(3.141592653589793)

180

e​

Introduced in: v1.1

Returns Euler's constant (e).

Syntax

e()

Arguments

  • None. Returned value

Returns Euler's constant Float64

Examples

Usage example

SELECT e();

2.718281828459045

erf​

Introduced in: v1.1

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

Returned value

Returns the error function value Float*

Examples

Three sigma rule

SELECT erf(3 / sqrt(2))

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

erfc​

Introduced in: v1.1

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

Syntax

erfc(x)

Arguments

Returned value

Returns the complementary error function value Float*

Examples

Usage example

SELECT erfc(0);

1

exp​

Introduced in: v1.1

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

Syntax

exp(x)

Arguments

Returned value

Returns e^x Float*

Examples

Basic usage

SELECT round(exp(-1), 4)

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

exp10​

Introduced in: v1.1

Returns 10 to the power of the given argument.

Syntax

exp10(x)

Arguments

Returned value

Returns 10^x Float*

Examples

Usage example

SELECT exp10(2);

100

exp2​

Introduced in: v1.1

Returns 2 to the power of the given argument.

Syntax

exp2(x)

Arguments

Returned value

Returns 2^x Float*

Examples

Usage example

SELECT exp2(3);

8

factorial​

Introduced in: v

Computes the factorial of an integer value. It works with any native integer type including UInt(8|16|32|64) and Int(8|16|32|64). The return type is UInt64.

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 exception throw.

Syntax

Arguments

  • None. Returned value

Examples

factorial

SELECT factorial(10)

hypot​

Introduced in: v20.12

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

Returned value

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

Examples

Basic usage

SELECT hypot(1, 1)

1.4142135623730951

intExp10​

Introduced in: v1.1

Like exp10 but returns a UInt64 number.

Syntax

intExp10(x)

Arguments

Returned value

Returns 10^x. UInt64

Examples

Usage example

SELECT intExp10(2);

100

intExp2​

Introduced in: v1.1

Like exp2 but returns a UInt64 number.

Syntax

intExp2(x)

Arguments

Returned value

Returns 2^x. UInt64

Examples

Usage example

SELECT intExp2(3);

8

lgamma​

Introduced in: v1.1

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

log​

Introduced in: v1.1

Returns the natural logarithm of the argument.

Syntax

log(x)

Arguments

Returned value

Returns the natural logarithm of x. Float*

Examples

Usage example

SELECT log(10);

2.302585092994046

log10​

Introduced in: v1.1

Returns the decimal logarithm of the argument.

Syntax

log10(x)

Arguments

Returned value

Returns the decimal logarithm of x. Float*

Examples

Usage example

SELECT log10(100);

2

log1p​

Introduced in: v20.12

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

Syntax

log1p(x)

Arguments

Returned value

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

Examples

Usage example

SELECT log1p(0)

0

log2​

Introduced in: v1.1

Returns the binary logarithm of the argument.

Syntax

log2(x)

Arguments

Returned value

Returns the binary logarithm of x. Float*

Examples

Usage example

SELECT log2(8);

3

pi​

Introduced in: v1.1

Returns pi (π).

Syntax

pi()

Arguments

  • None. Returned value

Returns pi Float64

Examples

Usage example

SELECT pi();

3.141592653589793

pow​

Introduced in: v1.1

Returns x raised to the power of y.

Syntax

pow(x, y)

Arguments

Returned value

Returns x^y Float64

Examples

Usage example

SELECT pow(2, 3);

8

radians​

Introduced in: v22.2

Converts degrees to radians.

Syntax

radians(x)

Arguments

Returned value

Returns value in radians Float64

Examples

Usage example

SELECT radians(180)

3.141592653589793

sign​

Introduced in: v21.2

Returns the sign of a real number.

Syntax

sign(x)

Arguments

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

sin​

Introduced in: v

Returns the sine of the argument.

Syntax

sin(x)

Arguments

Returned value

Returns the sine of x.

Examples

simple

SELECT sin(1.23)

0.9424888019316975

sinh​

Introduced in: v20.12

Returns the hyperbolic sine.

Syntax

sinh(x)

Arguments

Returned value

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

Examples

Usage example

SELECT sinh(0)

0

sqrt​

Introduced in: v1.1

Returns the square root of the argument.

Syntax

sqrt(x)

Arguments

Returned value

Returns the square root of x Float*

Examples

Usage example

SELECT sqrt(16);

4

tan​

Introduced in: v1.1

Returns the tangent of the argument.

Syntax

tan(x)

Arguments

Returned value

Returns the tangent of x. Float*

Examples

Usage example

SELECT tan(0);

0

tanh​

Introduced in: v20.1

Returns the hyperbolic tangent.

Syntax

tanh(x)

Arguments

Returned value

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

Examples

Usage example

SELECT tanh(0)

0

tgamma​

Introduced in: v1.1

Returns the gamma function.

Syntax

tgamma(x)

Arguments

Returned value

Returns the gamma function value Float*

Examples

Usage example

SELECT tgamma(5);

24

widthBucket​

Introduced in: v

Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 if operand < low, and returns count+1 if operand >= high.

operand, low, high can be any native number type. count can only be unsigned native integer and its value cannot be zero.

Syntax

widthBucket(operand, low, high, count)

There is also a case insensitive alias called WIDTH_BUCKET to provide compatibility with other databases.

Example

Query: [example:simple]

Result:

┌─widthBucket(10.15, -8.6, 23, 18)─┐
│                               11 │
└──────────────────────────────────┘

Syntax

Arguments

  • None. Returned value

Examples

simple

SELECT widthBucket(10.15, -8.6, 23, 18)