Relational Data - Functions - Math Functions

e

Returns ${\textstyle e}$ (Euler’s constant).

Syntax

e()

Returned value

Type: Float64.

pi

Returns ${\textstyle \pi }$ (Pi).

Syntax

pi()

Returned value

Type: Float64.

exp

Returns ${\textstyle e^{x}}$, where x is the given argument to the function.

Syntax

exp(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

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

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

exp2

Returns 2 to the power of the given argument

Syntax

exp2(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

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

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

exp10

Returns 10 to the power of the given argument.

Syntax

exp10(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

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

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

sqrt

Returns the square root of the argument.

sqrt(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

cbrt

Returns the cubic root of the argument.

cbrt(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

erf

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

Syntax

erf(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

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 ${\textstyle 1-erf(x)}$ without loss of precision for large x values.

Syntax

erfc(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

lgamma

Returns the logarithm of the gamma function.

Syntax

lgamma(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

tgamma

Returns the gamma function.

Syntax

gamma(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

sin

Returns the sine of the argument

Syntax

sin(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

Example

Query:

SELECT sin(1.23);

0.9424888019316975

cos

Returns the cosine of the argument.

Syntax

cos(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

tan

Returns the tangent of the argument.

Syntax

tan(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

asin

Returns the arc sine of the argument.

Syntax

asin(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

acos

Returns the arc cosine of the argument.

Syntax

acos(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

atan

Returns the arc tangent of the argument.

Syntax

atan(x)

Arguments

• x - (U)Int*, Float* or Decimal*.

Returned value

Type: Float*.

pow

Returns ${\textstyle x^{y}}$.

Syntax

pow(x, y)

Alias: power(x, y)

Arguments

• x - (U)Int8/16/32/64, Float* or Decimal*
• y - (U)Int8/16/32/64, Float* or Decimal*

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: Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle -\infty \lt x \lt +\infty} . (U)Int*, Float* or Decimal*.

Returned value

• Values from the interval: Failed to parse (unknown function "\lt"): {\textstyle 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: Failed to parse (unknown function "\lt"): {\textstyle 1 \le x \lt +\infty} . (U)Int*, Float* or Decimal*.

Returned value

• The angle, in radians. Values from the interval: Failed to parse (unknown function "\lt"): {\textstyle 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: Failed to parse (unknown function "\lt"): {\textstyle -\infty \lt x \lt +\infty} . (U)Int*, Float* or Decimal*.

Returned value

• Values from the interval: Failed to parse (unknown function "\lt"): {\textstyle -\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: Failed to parse (unknown function "\lt"): {\textstyle -\infty \lt x \lt +\infty} . (U)Int*, Float* or Decimal*.

Returned value

• The angle, in radians. Values from the interval: Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle -\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: Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle -\infty \lt x \lt +\infty} . (U)Int*, Float* or Decimal*.

Returned value

• Values from the interval: Failed to parse (unknown function "\lt"): {\textstyle -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: Failed to parse (unknown function "\lt"): {\textstyle -1 \lt x \lt 1} . (U)Int*, Float* or Decimal*.

Returned value

• The angle, in radians. Values from the interval: Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle -\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

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

Returned value

• The angle θ such that Failed to parse (unknown function "\lt"): {\textstyle -\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

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

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: Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle -1 \lt x \lt +\infty} . (U)Int*, Float* or Decimal*.

Returned value

• Values from the interval: Failed to parse (unknown function "\lt"): {\textstyle -\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 ${\textstyle -\infty }$ to ${\textstyle +\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: Failed to parse (SVG (MathML can be enabled via browser plugin): Invalid response ("Math extension cannot connect to Restbase.") from server "https://wikimedia.org/api/rest_v1/":): {\textstyle -\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

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

Returned value

• Value in degrees. Float64.

Example

SELECT degrees(3.141592653589793);

Result:

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

radians

Converts degrees to radians.

Syntax

radians(x)

Arguments

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

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.

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) │
└────────────────────────────────────────────────────────────────────────────────────┘