请注意:
下文中的一些示例引用自 ClickHouse 社区文档 并经过一定修改确保可以在 ByteHouse 中正常使用。
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
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 │ └─────────────────────────────────┘
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
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 │ └──────────────────────────────────┘
The arc cosine.
Syntax
acos(x)
Arguments
x
– The radians.Returned value
Type: Float64
Example
SELECT acos(-1);
Result:
┌─ACOS(-1)──────────────┐ │ 3.141592653589793e+00 │ └───────────────────────┘
The arc sine.
Syntax
asin(x)
Arguments
x
– The radians.Returned value
Type: Float64
Example
SELECT asin(-1);
Result:
┌─asin(1)────────────────┐ │ 1.5707963267948966e+00 │ └────────────────────────┘
The arc tangent.
Syntax
atan(x)
Arguments
x
– The radians.Returned value
Type: Float64
Example
SELECT atan(-1);
Result:
┌─atan(-1)───────────────┐ │ -7.853981633974483e-01 │ └────────────────────────┘
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
Type:Float64
Example
SELECT cbrt(8)
Result:
┌─cbrt(8)─┐ │ 2e+00 │ └─────────┘
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
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 │ └───────────────┘
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
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 │ └───────────────┘
The cosine.
Syntax
cos(x)
Arguments
x
– The radians.Returned value
Type: Float64
Example
SELECT cos(pi())
Result:
┌─cos(pi())─┐ │ -1e+00 │ └───────────┘
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
Type: Float64
Example
SELECT erf(3 / sqrt(2));
┌─erf(divide(3, sqrt(2)))─┐ │ 9.973002039367398e-01 │ └─────────────────────────┘
Note: three sigma rule
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
Type: Float64
Example
SELECT erfc(3 / sqrt(2));
┌─erfc(divide(3, sqrt(2)))─┐ │ 2.6997960632601913e-03 │ └──────────────────────────┘
Note: three sigma rule
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
Type: Float64
Example
SELECT exp(1);
Result:
┌─exp(1)────────────────┐ │ 2.718281828459045e+00 │ └───────────────────────┘
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
Type: Float64
Example
SELECT exp10(3);
Result:
┌─exp10(3)─┐ │ 1e+03 │ └──────────┘
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
calculating
Type: Float64
Example
SELECT exp2(3);
Result:
┌─exp2(3)─┐ │ 8e+00 │ └─────────┘
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
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 │ └───────────────────┘
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
Type: UInt64
Example
SELECT intExp10(3);
Result:
┌─intExp10(3)─┐ │ 1000 │ └─────────────┘
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
Type: UInt64
Example
SELECT intExp2(3);
Result:
┌─intExp2(3)─┐ │ 8 │ └────────────┘
The logarithm of the gamma function.
Syntax
lgamma(x)
Arguments
x
– The number.Returned value
Type: Float64
Example
SELECT lgamma(3);
Result:
┌─lgamma(3)─────────────┐ │ 6.931471805599453e-01 │ └───────────────────────┘
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
Type: Float64
Example
select ln(2.7182818)
Result:
┌─ln(2.7182818)─────────┐ │ 9.999999895305024e-01 │ └───────────────────────┘
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
Type: Float64
Example
SELECT log10(3);
Result:
┌─log10(3)───────────────┐ │ 4.7712125471966244e-01 │ └────────────────────────┘
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
Type: Float64
Example
select log2(3);
Result:
┌─log2(3)───────────────┐ │ 1.584962500721156e+00 │ └───────────────────────┘
Returns a Float64 number that is close to the number π.
Syntax
pi()
Arguments
Returned value
Type: Float64
Example
SELECT pi();
Result:
┌─pi()──────────────────┐ │ 3.141592653589793e+00 │ └───────────────────────┘
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
Type: Float64
Example
SELECT pow(2, 3);
Result:
┌─pow(2, 3)─┐ │ 8e+00 │ └───────────┘
Takes two numeric arguments x
and y
. Returns a Float64 number close to x
to the power of y
.
Alias:pow
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:
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 │ └──────────────────┘
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
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 │ └──────────────┴──────────────┴──────────────┘
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
Example
SELECT roundDown(2, [6, 7, 8]);
Result:
┌─roundDown(2, [6, 7, 8])─┐ │ 6 │ └─────────────────────────┘
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
Example
SELECT roundDuration(230);
Result:
┌─roundDuration(230)─┐ │ 180 │ └────────────────────┘
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
Example
SELECT roundToExp2(31);
Result:
┌─roundToExp2(31)─┐ │ 16 │ └─────────────────┘
The sine.
Syntax
sin(x)
Arguments
x
– The radians.Returned value
Type: Float64
Example
SELECT sin(pi()/2)
Result:
┌─sin(divide(pi(), 2))─┐ │ 1e+00 │ └──────────────────────┘
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
Type: Float64
Example
SELECT sqrt(4);
Result:
┌─sqrt(4)─┐ │ 2e+00 │ └─────────┘
The tangent.
Syntax
tan(x)
Arguments
x
– The radians.Returned value
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
Example
select tgamma(10);
Result:
┌─tgamma(10)─────────────┐ │ 3.6287999999999994e+05 │ └────────────────────────┘
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
Example
SELECT trunc(100.11, 1)
Result:
┌─trunc(100.11, 1)─┐ │ 1.001e+02 │ └──────────────────┘
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
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: