Mathematical functions¶

The following functions perform mathematical operations.

e¶

Returns e (Euler's constant).

Syntax¶

e()

Returned value¶

Type: Float64.

pi¶

Returns π (Pi).

Syntax¶

pi()

Returned value¶

Type: Float64.

exp¶

Returns 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 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 - (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 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 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: -∞ < x < +∞. (U)Int*, Float* or Decimal*.

Returned value¶

• Values from the interval: 1 <= cosh(x) < +∞.

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

Returned value¶

• The angle, in radians. Values from the interval: 0 <= acosh(x) < +∞.

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

Returned value¶

• Values from the interval: -∞ < sinh(x) < +∞.

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

Returned value¶

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

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

Returned value¶

• Values from the interval: -1 < tanh(x) < 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. (U)Int*, Float* or Decimal*.

Returned value¶

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

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 -π < θ <= π, 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: -1 < x < +∞. (U)Int*, Float* or Decimal*.

Returned value¶

• Values from the interval: -∞ < log1p(x) < +∞.

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 -∞ to +∞. Supports all numeric types in Tinybird.

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 < +∞. (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 can't 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.

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