COVAR_POP #

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

Syntax

covarPop(x, y)

Param

• x — a set of numbers.
• y — a set of numbers.

Return value

• Overall 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 #

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

Syntax

covarSamp(x, y)

Param

• x — a set of numbers.
• y — a set of numbers.

Return value

• When n <= 1, the sample covariance 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 #

Arccosine.
Syntax

acos(x)

Param

• x — radians.

Return value

• Returns radians.

Type: Float64
Example

SELECT acos(-1);

Result:

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

asin #

Arcsine.
Syntax

asin(x)

Param

• x — radians.

Return value

• Returns radians.

Type: Float64
Example

SELECT asin(-1);

Result:

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

atan #

Arctangent.
Syntax

atan(x)

Param

• x — radians.

Return value

• Returns radians.

Type: Float64
Example

SELECT atan(-1);

Result:

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

cbrt #

Takes a number param and returns a Float64 number close to the cube root of that param.
Syntax

cbrt(x)

Param

• x — number.

Return value

• The cube root of the argument.

Type: Float64
Example

SELECT cbrt(8)

Result:

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

ceil #

Returns the smallest integer greater than or equal to x. In other respects, it is the same as the function floor (see above).
Syntax

ceil(x[, N])

Param

• x — number.
• N — decimal-places, an integer value.

Return value

• Integer.

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 integer greater than or equal to x. In other respects, it is the same as the function floor (see above).
Syntax

ceiling(x[, N])

Param

• x — number.
• N — rounds to an integer after the decimal point.

Return value

• Integer.

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 #

Cosine.
Syntax

cos(x)

Param

• x — radians.

Return value

• Returns radians.

Type: Float64
Example

SELECT cos(pi())

Result:

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

erf #

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

Syntax

erf(x)

Param

• x — number.

Return value

• Probability.

Type: Float64
Example

SELECT erf(3 / sqrt(2));

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

Caution: Three sigma rule

erfc #

The complementary error function follows the formula: erfc(x) = 1 − erf(x).
Accepts a numeric parameter and returns a Float64 number close to 1 - erf(x), but does not cause precision loss for large x values.
Syntax

erfc(x) 
sql

Parameter

• x number.

Return value

• Probability.

Type: Float64
Example

SELECT erfc(3 / sqrt(2));

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

Caution: Three sigma rule

exp #

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

exp(x)

Parameter

• x number.

Return value

• Calculated result.

Type: Float64
Example

SELECT exp(1);

Result:

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

exp10 #

Accepts a numeric parameter and returns a Float64 number close to 10 raised to the power of x.
Syntax

exp10(x)

Parameter

• x number.

Return value

• Calculated result.

Type: Float64
Example

SELECT exp10(3);

Result:

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

exp2 #

Accepts a numeric parameter and returns a Float64 number close to 2 raised to the power of x.
Syntax

exp2(x)

Parameter

• x number.

Return value

• Calculated result.

Calculation
Type: Float64
Example

SELECT exp2(3);

Result:

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

floor #

Returns the largest integer less than or equal to x. The integer is a multiple of 1/10^N. If 1/10^N is not exact, the result is the closest number of the corresponding data type.

• N is an integer constant and an optional parameter. By default, N is zero, indicating rounding to an integer.
• N may be negative.
• x can be any numeric type. The result is a number of the same type.

For integer parameters, rounding with a negative N is meaningful (for non-negative N, this function does nothing).
If rounding causes overflow (for example, floor(-128, -1)), the function returns an implementation-specific result.
Syntax

floor(x[, N])

Parameter

• x number.
• N – round to integer

Return value

• Calculated result.

Type: Float64
Example

SELECT floor(123.45, 1);

Result:

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

Other examples:

select floor(123.45, -1);

Result:

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

intExp10 #

Accepts a numeric parameter and returns a UInt64 number close to 10 raised to the power of x.
Syntax

intExp10(x)

Parameter

• x number.

Return value

• Calculated result.

Type: UInt64
Example

SELECT intExp10(3);

Result:

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

intExp2 #

Accepts a numeric parameter and returns a UInt64 number close to 2 raised to the power of x.
Syntax

intExp2(x)

Parameter

• x number.

Return value

• Calculated result.

Type: UInt64
Example

SELECT intExp2(3);

Result:

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

lgamma #

Logarithm of the gamma function.
Syntax

lgamma(x)

Parameter

• x number.

Return value

• Calculated result.

Type: Float64
Example

SELECT lgamma(3);

Result:

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

ln #

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

ln(x)

Parameter

• x number.

Return value

• Calculated result.

Type: Float64
Example

select ln(2.7182818)

Result:

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

log10 #

Accepts a numeric parameter and returns the decimal logarithm of the parameter as a Float64 Number.
Syntax

log10(x) 
sql

Param

• x Number.

Return value

• Calculated result.

Type: Float64
Example

SELECT log10(3);

As a result:

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

log2 #

Accepts a numeric parameter and returns the binary logarithm of the parameter as a Float64 Number.
Syntax

log2(x)

Param

• x Number.

Return value

• Calculated result.

Type: Float64
Example

select log2(3);

As a result:

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

pi #

Returns the value of π as a Float64 Number.
Syntax

pi()

Param

• Not applicable

Return value

• Value of π.

Type: Float64
Example

SELECT pi();

As a result:

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

pow #

Accepts two numerical value parameters, x and y. Returns x raised to the power of y as a Float64 Number.
Syntax

pow(x, y)

Param

• x Number.

Return value

• Calculated result.

Type: Float64
Example

SELECT pow(2, 3);

As a result:

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

power #

Accepts two numerical value parameters, x and y. Returns x raised to the power of y as a Float64 Number.
alias: pow

round #

Rounds the value to the specified decimal places.
This function returns the number closest to the specified value. If the given number is equidistant from surrounding numbers, the function will use banker's rounding for floating-point number types and round away from zero for other number types.
Syntax

round(expression [, decimal_places])

Param

• expression— The number to round. Can be any expression that returns a number data type.
• decimal-places— An integer value.
  • If decimal-places > 0, the function will round the value to the right of the decimal point.
  • If decimal-places < 0, the function will round the value to the left of the decimal point.
  • If decimal-places = 0, the function will round the value to an integer. In this case, the parameter can be omitted.

Return value:

• A rounded number of the same type as the input number.

Type: Float64
Example

SELECT round(1.1234);

As a result:

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

Other examples

SELECT round(1.1234,2);

As a result:

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

roundAge #

Accepts a number. If the number is smaller than 17, return 17. Otherwise, round the number down to one of the following numbers: 17, 25, 35, 45, 55. This function is specific to Yandex.Metrica and is used to implement the user age report.
Syntax

roundAge(num)

Param

• num Age.

Return value

• A rounded value.

Type: UInt8
Example

SELECT roundAge(50);

As a result:

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

Other examples

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

As a 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 smaller than or bigger than the boundaries, then the lowest or highest boundary is returned.
Syntax

roundDown(number, array)

Param

• number Number.
• array – Array.

Return value

• The rounded result.

Example

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

As a result:

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

roundDuration #

Accepts a number. If the Number is smaller than one, then 0 is returned. Otherwise, it rounds the Number down to one of the following set of numbers: 1, 10, 30, 60, 120, 180, 240, 300, 600, 1200, 1800, 3600, 7200, 18000, 36000. This Function is specific to Yandex. Metrica, used to implement session length Report.
Syntax

roundDuration(number)

Param

• x Number.

Return value

• The rounded result.

Example

SELECT roundDuration(230);

As a result:

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

roundToExp2 #

Accepts number. If number is smaller than one, then 0 is returned. Otherwise, rounds down number to the nearest (non-negative integer) power of two.
Syntax

roundToExp2(number)

Param

• number Number.

Return value

• The rounded result.

Example

SELECT roundToExp2(31);

As a result:

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

sin #

Sine.
Syntax

sin(x)

Param

• x – Radian.

Return value

• Returns the radian.

Type: Float64
Example

SELECT sin(pi()/2)

As a result:

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

sqrt #

Accepts a number parameter and returns a Float64 number close to the square root of that parameter.
Syntax

sqrt(x)

Param

• x Number.

Return value

• Square root.

Type: Float64
Example

SELECT sqrt(4);

As a result:

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

tan #

Tangent.
Syntax

tan(x)

Param

• x – Radian.

Return value

• Returns the radian.

Type: Float64
Example

SELECT tan(pi()/4);

As a result:

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

Caution: The result has specific precision

tgamma #

Calculate the gamma function of arg.
Syntax

tgamma(x)

Param

• x Number.

Return value

• The calculated result.

Example

select tgamma(10);

As a result:

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

trunc #

Returns the integer with the largest absolute value whose absolute value is less than or equal to x. In all other respects, it is the same as the "floor" function (see above).
Syntax

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

Param

• x Number.
• N Rounds to integer after the decimal point.

Return value

• Truncated number.

Example

SELECT trunc(100.11, 1)

As a result:

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

truncate #

Delete all data from the table. When the IF EXISTS clause is omitted, if the table does not exist, the query returns an error.
This TRUNCATE query does not support views, files, URL, buffer, and empty table engines.
Syntax

TRUNCATE TABLE [IF EXISTS] [db.]name

Param

• name – Table Name.
• [IF EXISTS] Optional, if the table does not exist, the query returns an error.
• [db.] Optional, database name.
• Optional, cluster name.

Return value

• Not applicable

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