Version: 1.0.x

Mathematical

Notice: Some of the examples below are referenced from ClickHouse Documentation but have been adapted and modified to work in ByConity.

COVAR_POP

Calculates the value of Σ((x - x̅)(y - y̅)) / n .

Note: This function uses a numerically unstable algorithm. If you need numerical stability in calculations, use the covarPopStable function. It works slower but provides a lower computational error.

Syntax

covarPop(x, y)

Arguments

x – The set of number.
y - The set of number.

Returned value

The population covariance.

Type: Float64

Example

CREATE TABLE test.test_covarPop(days_employed Int32, salary Int32) ENGINE = CnchMergeTree ORDER BY days_employed; -- create sample table
INSERT INTO test.test_covarPop(days_employed,salary) VALUES(300,3000),(600,4000),(900,4500),(1200,4800),(1500,5000); -- insert data to table
SELECT covarPop(days_employed,salary) FROM test.test_covarPop; -- find out the population covariance for days employed and salary

Result:

┌─covarPop(days_employed, salary)─┐
│ 2.88e+05                        │
└─────────────────────────────────┘

COVAR_SAMP

Calculates the value of Σ((x - x̅)(y - y̅)) / (n - 1) .

Note:This function uses a numerically unstable algorithm. If you need numerical stability in calculations, use the covarSampStable function. It works slower but provides a lower computational error.

Syntax

covarSamp(x, y)

Arguments

x – The set of number.
y - The set of number.

Returned value

The sample covariance, when n <= 1 , returns +∞.

Type: Float64

Example

CREATE TABLE test.test_covarSamp(days_employed Int32, salary Int32) ENGINE = CnchMergeTree ORDER BY days_employed; -- create sample table
INSERT INTO test.test_covarSamp(days_employed,salary) VALUES(300,3000),(600,4000),(900,4500),(1200,4800),(1500,5000); -- insert data to table
SELECT covarSamp(days_employed,salary) FROM test.test_covarSamp; -- find out the sample covariance for days employed and salary

Result:

┌─covarSamp(days_employed, salary)─┐
│ 3.6e+05                          │
└──────────────────────────────────┘

acos

The arc cosine.

Syntax

acos(x)

Arguments

x – The radians.

Returned value

Return radians.

Type: Float64

Example

SELECT acos(-1);

Result:

┌─ACOS(-1)──────────────┐
│ 3.141592653589793e+00 │
└───────────────────────┘

asin

The arc sine.

Syntax

asin(x)

Arguments

x – The radians.

Returned value

Return radians.

Type: Float64

Example

SELECT asin(-1);

Result:

┌─asin(1)────────────────┐
│ 1.5707963267948966e+00 │
└────────────────────────┘

atan

The arc tangent.

Syntax

atan(x)

Arguments

x – The radians.

Returned value

Return radians.

Type: Float64

Example

SELECT atan(-1);

Result:

┌─atan(-1)───────────────┐
│ -7.853981633974483e-01 │
└────────────────────────┘

cbrt

Accepts a numeric argument and returns a Float64 number close to the cubic root of the argument.

Syntax

cbrt(x)

Arguments

x – The number.

Returned value

The root of the argument.

Type:Float64

Example

SELECT cbrt(8)

Result:

┌─cbrt(8)─┐
│ 2e+00   │
└─────────┘

ceil

Returns the smallest round number that is greater than or equal to x . In every other way, it is the same as the floor function (see above).

Syntax

ceil(x[, N]), ceiling(x[, N])

Arguments

x – The number.
N — decimal-places, An integer value.

Returned value

The round number.

Type: Float64

Example

SELECT ceil(1.99,2);

Result:

┌─ceil(1.99, 2)─┐
│ 1.99e+00      │
└───────────────┘

other example:

SELECT ceil(1.99,1);

Result:

┌─ceil(1.99, 1)─┐
│ 2e+00         │
└───────────────┘

ceiling

Returns the smallest round number that is greater than or equal to x . In every other way, it is the same as the floor function (see above).

Syntax

ceil(x[, N]), ceiling(x[, N])

Arguments

x – The number.
N - The integer of rounding decimal place.

Returned value

The round number.

Type: Float64

Example

SELECT ceiling(1.99,2);

Result:

┌─ceil(1.99, 2)─┐
│ 1.99e+00      │
└───────────────┘

other example:

SELECT ceiling(1.99,1);

Result:

┌─ceil(1.99, 1)─┐
│ 2e+00         │
└───────────────┘

cos

The cosine.

Syntax

cos(x)

Arguments

x – The radians.

Returned value

Return radians.

Type: Float64

Example

SELECT cos(pi())

Result:

┌─cos(pi())─┐
│ -1e+00    │
└───────────┘

erf

The error function erf(x)=2√π∫x0e−t2dt erf(x) .

Note: 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 number.

Returned value

The probability.

Type: Float64

Example

SELECT erf(3 / sqrt(2));

┌─erf(divide(3, sqrt(2)))─┐
│ 9.973002039367398e-01   │
└─────────────────────────┘

Note: three sigma rule

erfc

The complementary error function follows the formula: erfc(x) = 1 − erf(x).

Accepts a numeric argument and returns a Float64 number close to 1 - erf(x), but without loss of precision for large x values.

Syntax

erfc(x)

Arguments

x – The number.

Returned value

The probability.

Type: Float64

Example

SELECT erfc(3 / sqrt(2));

┌─erfc(divide(3, sqrt(2)))─┐
│ 2.6997960632601913e-03   │
└──────────────────────────┘

Note: three sigma rule

exp

Accepts a numeric argument and returns a Float64 number close to the exponent of the argument.

Syntax

exp(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

SELECT exp(1);

Result:

┌─exp(1)────────────────┐
│ 2.718281828459045e+00 │
└───────────────────────┘

exp10

Accepts a numeric argument and returns a Float64 number close to 10 to the power of x.

Syntax

exp10(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

SELECT exp10(3);

Result:

┌─exp10(3)─┐
│ 1e+03    │
└──────────┘

exp2

Accepts a numeric argument and returns a Float64 number close to 2 to the power of x.

Syntax

exp2(x)

Arguments

x – The number.

Returned value

The result of calculation.

calculating Type: Float64

Example

SELECT exp2(3);

Result:

┌─exp2(3)─┐
│ 8e+00   │
└─────────┘

floor

Returns the largest round number that is less than or equal to x . A round number is a multiple of 1/10N, or the nearest number of the appropriate data type if 1 / 10N isn’t exact.

N is an integer constant, optional parameter. By default it is zero, which means to round to an integer.
N may be negative.
x is any numeric type. The result is a number of the same type.

For integer arguments, it makes sense to round with a negative N value (for non-negative N , the function does not do anything).

If rounding causes overflow (for example, floor(-128, -1)), an implementation-specific result is returned.

Syntax

floor(x[, N])

Arguments

x – The number.
N – round to an integer

Returned value

The result of calculation.

Type: Float64

Example

SELECT floor(123.45, 1);

Result:

┌─floor(123.45, 1)─┐
│ 1.234e+02        │
└──────────────────┘

other example:

select floor(123.45, -1);

Result:

┌─floor(123.45, -1)─┐
│ 1.2e+02           │
└───────────────────┘

intExp10

Accepts a numeric argument and returns a UInt64 number close to 10 to the power of x.

Syntax

intExp10(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: UInt64

Example

SELECT intExp10(3);

Result:

┌─intExp10(3)─┐
│ 1000        │
└─────────────┘

intExp2

Accepts a numeric argument and returns a UInt64 number close to 2 to the power of x.

Syntax

intExp2(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: UInt64

Example

SELECT intExp2(3);

Result:

┌─intExp2(3)─┐
│ 8          │
└────────────┘

lgamma

The logarithm of the gamma function.

Syntax

lgamma(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

SELECT lgamma(3);

Result:

┌─lgamma(3)─────────────┐
│ 6.931471805599453e-01 │
└───────────────────────┘

ln

Accepts a numeric argument and returns a Float64 number close to the natural logarithm of the argument.

Syntax

ln(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

select ln(2.7182818)

Result:

┌─ln(2.7182818)─────────┐
│ 9.999999895305024e-01 │
└───────────────────────┘

log10

Accepts a numeric argument and returns a Float64 number close to the decimal logarithm of the argument.

Syntax

log10(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

SELECT log10(3);

Result:

┌─log10(3)───────────────┐
│ 4.7712125471966244e-01 │
└────────────────────────┘

log2

Accepts a numeric argument and returns a Float64 number close to the binary logarithm of the argument.

Syntax

log2(x)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

select log2(3);

Result:

┌─log2(3)───────────────┐
│ 1.584962500721156e+00 │
└───────────────────────┘

pi

Returns a Float64 number that is close to the number π.

Syntax

pi()

Arguments

Returned value

The value of π.

Type: Float64

Example

SELECT pi();

Result:

┌─pi()──────────────────┐
│ 3.141592653589793e+00 │
└───────────────────────┘

pow

Takes two numeric arguments x and y. Returns a Float64 number close to x to the power of y.

Syntax

pow(x, y)

Arguments

x – The number.

Returned value

The result of calculation.

Type: Float64

Example

SELECT pow(2, 3);

Result:

┌─pow(2, 3)─┐
│ 8e+00     │
└───────────┘

power

Takes two numeric arguments x and y. Returns a Float64 number close to x to the power of y.

Alias：pow

round

Rounds a value to a specified number of decimal places.

The function returns the nearest number of the specified order. In case when given number has equal distance to surrounding numbers, the function uses banker’s rounding for float number types and rounds away from zero for the other number types.

Syntax

round(expression [, decimal_places])

Arguments

expression — A number to be rounded. Can be any expression returning the numeric data type .
decimal-places — An integer value.
- If decimal-places > 0 then the function rounds the value to the right of the decimal point.
- If decimal-places < 0 then the function rounds the value to the left of the decimal point.
- If decimal-places = 0 then the function rounds the value to integer. In this case the argument can be omitted.

Returned value:

The rounded number of the same type as the input number.

Type: Float64

Example

SELECT round(1.1234);

Result:

┌─round(1.1234)─┐
│ 1e+00         │
└───────────────┘

other example

SELECT round(1.1234,2);

Result:

┌─round(1.1234, 2)─┐
│ 1.12e+00         │
└──────────────────┘

roundAge

Accepts a number. If the number is less than 17, it returns 17. Otherwise, it rounds the number down to a number from the set: 17, 25, 35, 45, 55. This function is specific to Yandex.Metrica and used for implementing the report on user age.

Syntax

roundAge(num)

Arguments

num – The age.

Returned value

A rounded value..

Type: UInt8

Example

SELECT roundAge(50);

Result:

┌─roundAge(50)─┐
│ 45           │
└──────────────┘

other example

SELECT roundAge(16), roundAge(17),roundAge(18);

Result:

┌─roundAge(16)─┬─roundAge(17)─┬─roundAge(18)─┐
│ 17           │ 17           │ 18           │
└──────────────┴──────────────┴──────────────┘

roundDown

Accepts a number and rounds it down to an element in the specified array. If the value is less or greater than the bound, the lowest or greatest bound is returned.

Syntax

roundDown(number, array)

Arguments

number – The number.
array – The array.

Returned value

The result of roundng.

Example

SELECT roundDown(2, [6, 7, 8]);

Result:

┌─roundDown(2, [6, 7, 8])─┐
│ 6                       │
└─────────────────────────┘

roundDuration

Accepts a number. If the number is less than one, it returns 0. Otherwise, it rounds the number down to numbers from the set: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000. This function is specific to Yandex. Metrica and used for implementing the report on session length.

Syntax

roundDuration(number)

Arguments

x – The number.

Returned value

The result of rounding.

Example

SELECT roundDuration(230);

Result:

┌─roundDuration(230)─┐
│ 180                │
└────────────────────┘

roundToExp2

Accepts a number. If the number is less than one, it returns 0. Otherwise, it rounds the number down to the nearest (whole non-negative) degree of two.

Syntax

roundToExp2(number)

Arguments

number – The number.

Returned value

The result of rounding.

Example

SELECT roundToExp2(31);

Result:

┌─roundToExp2(31)─┐
│ 16              │
└─────────────────┘

sin

The sine.

Syntax

sin(x)

Arguments

x – The radians.

Returned value

Return radians.

Type: Float64

Example

SELECT sin(pi()/2)

Result:

┌─sin(divide(pi(), 2))─┐
│ 1e+00                │
└──────────────────────┘

sqrt

Accepts a numeric argument and returns a Float64 number close to the square root of the argument.

Syntax

sqrt(x)

Arguments

x – The number.

Returned value

The square root.

Type: Float64

Example

SELECT sqrt(4);

Result:

┌─sqrt(4)─┐
│ 2e+00   │
└─────────┘

tan

The tangent.

Syntax

tan(x)

Arguments

x – The radians.

Returned value

Return radians.

Type: Float64

Example

SELECT tan(pi()/4);

Result:

┌─tan(divide(pi(), 4))──┐
│ 9.999999999999999e-01 │
└───────────────────────┘

Note: the result has particular precision, it probably will be fixed with next minor release.## tgamma Computes the gamma function of arg.

Syntax

tgamma(x)

Arguments

x – The number.

Returned value

The result of calculation.

Example

select tgamma(10);

Result:

┌─tgamma(10)─────────────┐
│ 3.6287999999999994e+05 │
└────────────────────────┘

trunc

Returns the round number with largest absolute value that has an absolute value less than or equal to x ‘s. In every other way, it is the same as the ’floor’ function (see above).

Syntax

trunc(x[, N])
truncate(x[, N])

Arguments

x – The number.
N - The integer of rounding decimal place.

Returned value

The rouded number.

Example

SELECT trunc(100.11, 1)

Result:

┌─trunc(100.11, 1)─┐
│ 1.001e+02        │
└──────────────────┘

truncate

Removes all data from a table. When the clause IF EXISTS is omitted, the query returns an error if the table does not exist.

The TRUNCATE query is not supported for View, File, URL, Buffer and Null table engines.

Syntax

TRUNCATE TABLE [IF EXISTS] [db.]name [ON CLUSTER cluster]

Arguments

name – The table name.
[IF EXISTS] - Optional, the query returns an error if the table does not exist.
[db.] - Optional, the database name.
[ON CLUSTER cluster] - Optional, the cluster name.

Returned value

N.A.

Example

CREATE TABLE test.test_truncate (id Int32) ENGINE = CnchMergeTree ORDER BY id;
INSERT INTO test.test_truncate(id) VALUES(1),(2),(3),(4),(5); -- insert 1,2,3,4,5 to table
SELECT * FROM test.test_truncate; -- check the date before truncate

┌─id─┐
│ 1  │
│ 2  │
│ 3  │
│ 4  │
│ 5  │
└────┘

TRUNCATE TABLE test.`test_truncate`

SELECT * FROM test.`test_truncate`

Result:

COVAR_POP​

COVAR_SAMP​

acos​

asin​

atan​

cbrt​

ceil​

ceiling​

cos​

erf​

erfc​

exp​

exp10​

exp2​

floor​

intExp10​

intExp2​

lgamma​

ln​

log10​

log2​

pi​

pow​

power​

round​

roundAge​

roundDown​

roundDuration​

roundToExp2​

sin​

sqrt​

tan​

trunc​

truncate​

COVAR_POP

COVAR_SAMP

acos

asin

atan

cbrt

ceil

ceiling

cos

erf

erfc

exp

exp10

exp2

floor

intExp10

intExp2

lgamma

ln

log10

log2

pi

pow

power

round

roundAge

roundDown

roundDuration

roundToExp2

sin

sqrt

tan

trunc

truncate