any #

Select the first encountered value.
The query can be executed in any order, and may even be executed in a different order each time; therefore, the result of this function is not deterministic.
To get a deterministic result, you can use the "min" or "max" function instead of "any".
In some cases, you can rely on the execution order. This applies to SELECT queries from subqueries that use ORDER BY.
When a SELECT query contains a GROUP BY clause or at least one aggregate function, ByteHouse (unlike MySQL) requires that all expressions in the clause be calculated from the SELECT GROUP BY key or aggregate function. In other words, every column selected from the table must be used in the GROUP BY key or aggregate function. To get behavior similar to MySQL, you can place other columns in aggregate functions.
Syntax

any(column)

Parameter

• column– Column name.

Return value

• The first encountered value. The type is the same as the input column.

Example

SELECT any(number) FROM numbers(10);

┌─any(number)─┐ 
│ 0           │ 
└─────────────┘

anyHeavy #

The heavy hitters algorithm selects a frequently occurring value. If a value appears in more than half of the cases in each execution thread of the query, that value is returned. Usually, the result is not deterministic.
Syntax

anyHeavy(column)

Parameter

• column– Column name.

Return value

• The most common value. The type is the same as the input column.

Example

CREATE TABLE IF NOT EXISTS test.functionAnyHeavy(id Int) ENGINE=CnchMergeTree() ORDER BY id; 
INSERT INTO test.functionAnyHeavy values (1),(1),(1),(2),(3); 
SELECT anyHeavy(id) FROM test.functionAnyHeavy;

┌─anyHeavy(id)─┐ 
│ 1            │ 
└──────────────┘

anyLast #

Select the last encountered value.
The result is also not deterministic.
Syntax

anyLast(column) 
sql

Parameter

• column– Column name.

Return value

• The last encountered value. The type is the same as the input column.

Example

SELECT anyLast(number) FROM numbers(10);

┌─anyLast(number)─┐ 
│ 9               │ 
└─────────────────┘

argMax #

Returns the value of arg for which val is maximized. If there are multiple different values for the maximum value of val, the first encountered value is returned. argval
Syntax

argMax(arg, val) 
sql

Parameter

• arg– Argument.
• val– Value.

Return value

• The value of arg corresponding to the maximum value of val. The type is the same as the arg type.

Example

CREATE TABLE IF NOT EXISTS test.functionArgMax (user String, salary Int) Engine=CnchMergeTree() ORDER BY user; 
INSERT INTO test.functionArgMax VALUES ('director',5000),('manager',3000),('worker',1000); 
SELECT argMax(user, salary) FROM test.functionArgMax;

┌─argMax(user, salary)─┐ 
│ director             │ 
└──────────────────────┘

argMin #

Returns the value of arg for which val is minimized. If there are multiple different values for the minimum value of val, the first encountered value is returned. argval
Syntax

argMin(arg, val)

Parameter

• arg– Argument.
• val– Value.

Return value

• The value of arg corresponding to the minimum value of val. The type is the same as the arg type.

Example

CREATE TABLE IF NOT EXISTS test.functionArgMin (user String, salary Int) Engine=CnchMergeTree() ORDER BY user; 
INSERT INTO test.functionArgMin VALUES ('director',5000),('manager',3000),('worker',1000); 
SELECT argMin(user, salary) FROM test.functionArgMin;

┌─argMin(user, salary)─┐ 
│ worker               │ 
└──────────────────────┘

avg #

Calculates the arithmetic average value.
Syntax

avg(x)

Parameter

• x— Input value, must be an integer, floating-point number, or decimal.

Return value

• The arithmetic average value, always Float64.
• NaN if the input parameter x is empty.

Example

CREATE TABLE IF NOT EXISTS test.functionAvg (x Int8) ENGINE=CnchMergeTree() ORDER BY x; 
INSERT INTO test.functionAvg SELECT * FROM numbers(6); 
SELECT avg(x) FROM test.functionAvg;

┌─avg(x)──┐ 
│ 2.5e+00 │ 
└─────────┘

CREATE TABLE IF NOT EXISTS test.functionAvg (x Int8) ENGINE=CnchMergeTree() ORDER BY x; 
SELECT avg(x) FROM test.functionAvg;

┌─avg(x)─┐ 
│ NaN    │ 
└────────┘

corr #

Calculates the Pearson correlation coefficient: Σ((x - x̅)(y - y̅)) / sqrt(Σ((x - x̅)^2) * Σ((y - y̅)^2)).
This function uses a numerically unstable algorithm. To ensure numerical stability in calculation, use corrStable. It runs slower, but the calculation error is lower.
Syntax

corr(x, y)

Parameters

• x— Input value, must be an integer or a floating-point number.
• y— Input value, must be an integer or a floating-point number.

Return value

• The Pearson correlation coefficient. Returns a value of type Float64.

Example

CREATE TABLE IF NOT EXISTS test.functionCorr (x Int8, y Int8) ENGINE=CnchMergeTree() ORDER BY x; 
INSERT INTO test.functionCorr VALUES (1,5),(2,4),(3,3),(4,2),(5,1); 
SELECT corr(x,y) FROM test.functionCorr;

┌─corr(x, y)─┐ 
│ -1e+00     │ 
└────────────┘

covarPop #

Calculates the value Σ((x - x̅)(y - y̅)) / n.
This function uses a numerically unstable algorithm. To ensure numerical stability in calculation, use covarPopStable. It runs slower, but the calculation error is lower.
Syntax

covarPop(x, y)

Parameters

• x— Input value, must be an integer or a floating-point number.
• y— Input value, must be an integer or a floating-point number.

Return value

• Σ((x - x̅)(y - y̅)) / n. Returns a value of type Float64.

Example

CREATE TABLE IF NOT EXISTS test.functionCovarPop (x Int8, y Int8) ENGINE=CnchMergeTree() ORDER BY x; 
INSERT INTO test.functionCovarPop VALUES (1,5),(2,4),(3,3),(4,2),(5,1); 
SELECT covarPop(x,y) FROM test.functionCovarPop;

┌─covarPop(x, y)─┐ 
│ -2e+00         │ 
└────────────────┘

covarSamp #

Calculates the value Σ((x - x̅)(y - y̅)) / (n - 1).
This function uses a numerically unstable algorithm. To ensure numerical stability in calculation, use covarSampStable. It runs slower, but the calculation error is lower.
Syntax

covarSamp(x, y)

Parameters

• x— Input value, must be an integer or a floating-point number.
• y— Input value, must be an integer or a floating-point number.

Return value

• Σ((x - x̅)(y - y̅)) / (n - 1). Returns a value of type Float64.
• When n <= 1, returns NaN.

Example

CREATE TABLE IF NOT EXISTS test.functionCovarSamp (x Int8, y Int8) ENGINE=CnchMergeTree() ORDER BY x; 
INSERT INTO test.functionCovarSamp VALUES (1,5),(2,4),(3,3),(4,2),(5,1); 
SELECT covarSamp(x,y) FROM test.functionCovarSamp;

┌─covarSamp(x, y)─┐ 
│ -2.5e+00        │ 
└─────────────────┘

CREATE TABLE IF NOT EXISTS test.functionCovarSamp (x Int8, y Int8) ENGINE=CnchMergeTree() ORDER BY x; 
INSERT INTO test.functionCovarSamp VALUES (1,5); 
SELECT covarSamp(x,y) FROM test.functionCovarSamp;

┌─covarSamp(x, y)─┐ 
│ NaN             │ 
└─────────────────┘

groupArray #

Creates an array of parameter values.
Values can be added to the array in any (unspecified) order.
The second version (with the max_size parameter) limits the size of the resulting array to max_size elements. For example, groupArray(1)(x) is equivalent to [any (x)].
In some cases, you can still rely on the execution order. This applies to subqueries in SELECT statements that use ORDER BY.
Syntax

groupArray(x) 
groupArray(max_size)(x)

Parameters

• x— Input value.

Return value

• An array of values. The type is array.

Example

select groupArray(number) from numbers(10);

┌─groupArray(number)─────────────┐ 
│ [0, 1, 2, 3, 4, 5, 6, 7, 8, 9] │ 
└────────────────────────────────┘

select groupArray(5)(number) from numbers(10);

┌─groupArray(5)(number)─┐ 
│ [0, 1, 2, 3, 4]       │ 
└───────────────────────┘

groupBitAnd #

Applies AND bitwise to a series of numbers.
Syntax

groupBitAnd(expr)

Parameters

• expr– Expression that produces type UInt*.

Return value

• Value of type UInt*.

Example

CREATE TABLE IF NOT EXISTS test.groupBitAnd (num UInt8) ENGINE=CnchMergeTree() ORDER BY num; 
INSERT INTO test.groupBitAnd VALUES (44),(28),(13),(85); 
SELECT groupBitAnd(num) FROM test.groupBitAnd;

┌─groupBitAnd(num)─┐ 
│ 4                │ 
└──────────────────┘

The bit representations of 44, 28, 13, and 85 are 00101100, 00011100, 00001101, and 01010101, respectively. The bitwise AND of these four numbers is 00000100, which equals to 4.

groupBitOr #

Applies OR bitwise to a series of numbers.
Syntax

groupBitOr(expr)

Parameters

• expr– Expression that produces type UInt*.

Return value

• Value of type UInt*.

Example

CREATE TABLE IF NOT EXISTS test.functionGroupBitOr (num UInt8) ENGINE=CnchMergeTree ORDER BY num; 
INSERT INTO test.functionGroupBitOr VALUES (44),(28),(13),(85); 
SELECT groupBitOr(num) FROM test.functionGroupBitOr;

┌─groupBitOr(num)─┐ 
│ 125             │ 
└─────────────────┘

The bit representations of 44, 28, 13, and 85 are 00101100, 00011100, 00001101, and 01010101, respectively. The bitwise OR of these four numbers is 01111101, which equals to 125.

groupBitXor #

Applies XOR bitwise to a series of numbers.
Syntax

groupBitXor(expr)

Parameters

• expr– Expression that produces type UInt*.

Return value

• Value of type UInt*.

Example

CREATE TABLE IF NOT EXISTS test.functionGroupBitXor (num UInt8) ENGINE=CnchMergeTree ORDER BY num; 
INSERT INTO test.functionGroupBitXor VALUES (44),(28),(13),(85); 
SELECT groupBitXor(num) FROM test.functionGroupBitXor;

┌─groupBitXor(num)─┐ 
│ 104              │ 
└──────────────────┘

The bit representations of 44, 28, 13, and 85 are 00101100, 00011100, 00001101, and 01010101, respectively. The bitwise XOR result of these four numbers is 01101000, which equals 104.

groupBitmap #

Performs bitmap or aggregate calculation on a column of unsigned integers and returns the cardinality as type UInt64. If the suffix -State is added, returns the bitmap object.
Syntax

groupBitmap(expr)

Parameters

• expr – Expression that produces a value of type UInt*.

Return value

• Value of type UInt64.

Example

SELECT groupBitmap(number) FROM numbers(4);

┌─groupBitmap(number)─┐ 
│ 4                   │ 
└─────────────────────┘

groupBitmapAnd #

Calculates the AND of bitmap columns and returns the cardinality as type UInt64. If the suffix -State is added, returns the bitmap object.
Syntax

groupBitmapAnd(expr)

Parameters

• expr – Expression that produces a value of type AggregateFunction(groupBitmap, UInt*).

Return value

• Value of type UInt64.

Example

DROP TABLE IF EXISTS bitmap_column_expr_test2; 

CREATE TABLE bitmap_column_expr_test2(tag_id String,z AggregateFunction(groupBitmap, UInt32)) ENGINE = CnchMergeTree ORDER BY tag_id; 

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] as Array(UInt32)))); 
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] as Array(UInt32)))); 
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] as Array(UInt32)))); 

SELECT groupBitmapAnd(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%'); 
┌─groupBitmapAnd(z)─┐ 
│               3   │ 
└───────────────────┘ 

SELECT arraySort(bitmapToArray(groupBitmapAndState(z))) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%'); 
┌─arraySort(bitmapToArray(groupBitmapAndState(z)))─┐ 
│ [6,8,10]                                         │ 
└──────────────────────────────────────────────────┘

groupBitmapOr #

Calculates the OR of bitmap columns and returns the cardinality as type UInt64. If the suffix -State is added, returns the bitmap object. This is equivalent to groupBitmapMerge.
Syntax

groupBitmapOr(expr)

Parameters

• expr – Expression that produces a value of type AggregateFunction(groupBitmap, UInt*).

Return value

• Value of type UInt64.

Example

DROP TABLE IF EXISTS bitmap_column_expr_test2; 
CREATE TABLE bitmap_column_expr_test2(tag_id String,z AggregateFunction(groupBitmap, UInt32)) ENGINE = CnchMergeTree ORDER BY tag_id; 

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] as Array(UInt32)))); 
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] as Array(UInt32)))); 
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] as Array(UInt32)))); 

SELECT groupBitmapOr(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%'); 
┌─groupBitmapOr(z)─┐ 
│             15   │ 
└──────────────────┘ 

SELECT arraySort(bitmapToArray(groupBitmapOrState(z))) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%'); 
┌─arraySort(bitmapToArray(groupBitmapOrState(z)))─┐ 
│ [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]           │ 
└─────────────────────────────────────────────────┘

groupBitmapXor #

Calculates the XOR of bitmap columns and returns the cardinality as type UInt64. If the suffix -State is added, returns the bitmap object.
Syntax

groupBitmapOr(expr)

Parameters

• expr – Expression that produces a value of type AggregateFunction(groupBitmap, UInt*).

Return value

• Value of type UInt64.

Example

DROP TABLE IF EXISTS bitmap_column_expr_test2; 
CREATE TABLE bitmap_column_expr_test2(tag_id String, z AggregateFunction(groupBitmap, UInt32)) ENGINE = CnchMergeTree ORDER BY tag_id; 

INSERT INTO bitmap_column_expr_test2 VALUES ('tag1', bitmapBuild(cast([1,2,3,4,5,6,7,8,9,10] as Array(UInt32)))); 
INSERT INTO bitmap_column_expr_test2 VALUES ('tag2', bitmapBuild(cast([6,7,8,9,10,11,12,13,14,15] as Array(UInt32)))); 
INSERT INTO bitmap_column_expr_test2 VALUES ('tag3', bitmapBuild(cast([2,4,6,8,10,12] as Array(UInt32)))); 

SELECT groupBitmapXor(z) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%'); 
┌─groupBitmapXor(z)─┐ 
│              10   │ 
└───────────────────┘ 

SELECT arraySort(bitmapToArray(groupBitmapXorState(z))) FROM bitmap_column_expr_test2 WHERE like(tag_id, 'tag%'); 
┌─arraySort(bitmapToArray(groupBitmapXorState(z)))─┐ 
│ [1,3,5,6,8,10,11,13,14,15]                       │ 
└──────────────────────────────────────────────────┘

groupConcat #

Concatenates strings from a group, optionally separated by a delimiter, and optionally limits the maximum number of elements included.
Syntax

groupConcat[(delimiter [, limit])](expression);

Alias: group_concat
Parameters

• expression — Expression or column name for the string to be concatenated.
• delimiter — String used to separate concatenated valuesString. This parameter is optional. If not specified, the default is ','.

Parameter list

• delimiter — String used to separate concatenated valuesString. This parameter is optional. If not specified, the default is ','.
• limit — Positive integer specifying the maximum number of elements to concatenateInteger. If there are more elements, the excess elements will be ignored. This parameter is optional.

Return value

• Returns a String concatenated from the values of the column or expression. If there are no elements in the group or only null elements, and the function does not specify how to handle only null values, the result is a Nullable String with a null value.

Example
Input table:

create database if not exists test; 
create table if not exists test.employees (id UInt64, name String) engine=CnchMergeTree order by id; 
insert into test.employees values(1, 'Joe'), (2, 'Jane'), (3, 'Doe');

1. Basic usage:

   SELECT groupConcat(name) FROM test.employees;
   

   ┌─groupConcat(name)─┐ 
   │ Joe, Jane, Doe    │ 
   └───────────────────┘
   

2. Using a comma as the separator:

   SELECT groupConcat(', ')(name) FROM test.employees;
   

   ┌─groupConcat(', ')(name)─┐ 
   │ Joe, Jane, Doe          │ 
   └─────────────────────────┘
   

3. Limit the number of concatenated elements

   SELECT groupConcat(', ', 2)(name) FROM test.employees;
   

   ┌─groupConcat(', ', 2)(name)─┐ 
   │ Joe, Jane                  │ 
   └────────────────────────────┘
   

groupUniqArray #

Create an array based on different parameter values. Memory consumption is the same as the uniqExact function.
The second version (with the max_size parameter) limits the size of the result array to max_size elements.
Syntax

groupUniqArray(x) 
groupUniqArray(max_size)(x)

Param

• x— Input value.

Return value

• An array of values. Type: array.

Example

select groupUniqArray(number) from numbers(10);

┌─groupUniqArray(number)─────────┐ 
│ [0, 8, 3, 9, 7, 2, 1, 6, 4, 5] │ 
└────────────────────────────────┘

select groupUniqArray(5)(number) from numbers(10);

┌─groupArray(5)(number)─┐ 
│ [0, 1, 2, 3, 4]       │ 
└───────────────────────┘

histogram #

Calculate an adaptive histogram. It does not guarantee accurate results.
This function uses the streaming parallel decision tree algorithm. When new data enters the function, the boundaries of the histogram bins are adjusted. In most cases, the bin widths are not equal.
Syntax

histogram(number_of_bins)(values)

Param

• number_of_bins— The upper limit of the number of bins in the histogram. The function automatically calculates the number of bins. It attempts to reach the specified number of bins, but if it fails, it uses fewer bins.
• values— Expression that generates input values.

Return value

• A tuple array in the following format:
• [(lower_1, upper_1, height_1), ... (lower_N, upper_N, height_N)]

lower
— Lower bound of the bin.
upper
— Upper bound of the bin.
height — Calculated height of the bin.
Example

SELECT histogram(5)(number + 1) FROM numbers(20);

┌─histogram(5)(plus(number, 1))───────────────────────────────────────────┐ 
│ [(1,4.5,4),(4.5,8.5,4),(8.5,12.75,4.125),(12.75,17,4.625),(17,20,3.25)] │ 
└─────────────────────────────────────────────────────────────────────────┘

You can use the bar function to visualize the histogram, for example:

WITH histogram(5)(rand() % 100) AS hist 
SELECT 
    arrayJoin(hist).3 AS height, 
    bar(height, 0, 6, 5) AS bar 
FROM numbers(20);

┌─height─┬─bar───┐ 
│  2.125 │ █▋    │ 
│   3.25 │ ██▌   │ 
│  5.625 │ ████▏ │ 
│  5.625 │ ████▏ │ 
│  3.375 │ ██▌   │ 
└────────┴───────┘

In this case, you should keep in mind that you do not know the boundaries of the histogram bins.

max #

An aggregate function that calculates the maximum value of a set of values.
Syntax

max(values)

Param

• values— Input value.

Return value

• Maximum. Type: same as value

Example

SELECT max(number) FROM numbers(20);

┌─max(number)─┐ 
│ 19          │ 
└─────────────┘

To select up to two values using a non-aggregate function, see greatest:

SELECT greatest(a, b) FROM table;

median #

These median* functions are aliases for the corresponding quantile* functions. They calculate the median of a numerical data sample.
Function:

• median— Alias for quantile.
• medianDeterministic— Alias for quantileDeterministic.
• medianExact— Alias for quantileExact.
• medianExactWeighted— Alias for quantileExactWeighted.
• medianTiming— Alias for quantileTiming.
• medianTimingWeighted— Alias for quantileTimingWeighted.
• medianTDigest— Alias for quantileTDigest.
• medianTDigestWeighted— Alias for quantileTDigestWeighted.
• medianBFloat16— Alias for quantileBFloat16.

medianDeterministic #

Alias for quantileDeterministic.

medianExact #

Alias for quantileExact.

medianExactWeighted #

Alias for quantileExactWeighted.

medianTDigest #

Alias for quantileTDigest.

medianTDigestWeighted #

Alias for quantileTDigestWeighted.

medianTiming #

Alias for quantileTiming.

medianTimingWeighted #

Alias for quantileTimingWeighted.

quantile #

Calculate the approximate quantile of a sequence of numerical data values.
This function applies reservoir sampling, with a maximum reservoir size of 8192, and uses a random number generator for sampling. The result is not deterministic. To acquire an accurate quantile, use the quantileExact function.
When using multiple quantile* functions with different levels in a query, internal states are not merged (that is, the query is less efficient than it should be). In this case, use the quantile function.
Syntax

quantile(level)(expr)

Alias: median.
Parameters

• level— quantile level. Optional parameter. A constant floating-point number between 0 and 1. We recommend using values in the range level [0.01, 0.99]. Default value: 0.5. When level=0.5, the function calculates the median.
• expr— Expression applied to column values, resulting in a number, date, or datetime.

Return value

• Approximate quantile at the specified level.

Type:

• Float64 for numeric data type input.
• If the input value has Date type, returns Date.
• If the input value has DateTime type, returns DateTime.

Example

SELECT quantile(number) FROM numbers(10);

┌─quantile(number)─┐ 
│ 4.5e+00          │ 
└──────────────────┘

quantileDeterministic #

Calculates the approximate quantile of a sequence of numeric values.
This function uses reservoir sampling with a reservoir size up to 8192, and the sampling algorithm is deterministic. As a result, the output is deterministic. To obtain exact quantiles, use the quantileExact function.
When using multiple quantile* functions with different levels in a query, internal states are not merged (that is, the query is less efficient than it should be). In this case, use the quantile function.
Syntax

quantileDeterministic(level)(expr, determinator) 
sql

Alias: medianDeterministic.
Parameters

• level— quantile level. Optional parameter. A constant floating-point number between 0 and 1. We recommend using values in the range level [0.01, 0.99]. Default value: 0.5. When level=0.5, the function calculates the median.
• expr— Expression applied to column values, resulting in a numeric data type, date, or datetime.
• determinator— In the reservoir sampling algorithm, a hash value is used instead of a random number generator to ensure deterministic sampling results. As a determinator, you can use any deterministic positive number, such as a user ID or event ID. If the same determinator value appears too frequently, the function will not work properly.

Return value

• Approximate quantile at the specified level.

Type:

• Float64 for numeric data type input.
• If the input value has Date type, returns Date.
• If the input value has DateTime type, returns DateTime.

Example

SELECT quantileDeterministic(number, 1) FROM numbers(10); 
sql

┌─quantileDeterministic(number, 1)─┐ 
│ 4.5e+00                          │ 
└──────────────────────────────────┘ 
Plain text

quantileExact #

Accurately calculate the quantile of a numerical data sequence.
To acquire an accurate value, all passed Value are combined into an array and then partially sorted. Therefore, this function consumes O(n) memory, where n is the number of Value passed. However, for a small number of Value, this function is highly efficient.
When using multiple functions with different levels quantile* in a Search, the internal state is not merged (that is, the efficiency of the Search is less than optimal). In this case, use the quantile function.
Syntax

quantileExact(level)(expr)

Alias: medianExact.
Parameters

• level— quantile level. Optional parameter. A constant floating-point number between 0 and 1. We suggest using values of level in the range [0.01, 0.99]. Default value: 0.5. level=0.5 The function calculates the Median.
• expr— Expression on column values, resulting in a numerical data type, that is, Date or DateTime.

Return value

• The quantile at the specified level.

Type:

• Float64 for numerical data type input.
• If the enter a value is of Date type, it is Date.
• If the enter a value is of DateTime type, it is DateTime.

Example

SELECT quantileExact(number) FROM numbers(10);

┌─quantileExact(number)─┐ 
│                     5 │ 
└───────────────────────┘

quantileExactWeighted #

Accurately calculate the quantile of a numerical data sequence, while considering the weight of each element.
To acquire an accurate value, all passed Value are combined into an array and then partially sorted. Each Value is counted with its weight, as if it is the current value weight. A hash table is used in the algorithm. Therefore, if Value passed are frequently repeated, this function consumes less RAM than quantileExact. You can use this function instead of quantileExact and specify weight 1.
When using multiple functions with different levels quantile* in a Search, the internal state is not merged (that is, the efficiency of the Search is less than optimal). In this case, use the quantile function.
Syntax

quantileExactWeighted(level)(expr, weight)

Alias: medianExactWeighted.
Parameters

• level— quantile level. Optional parameter. A constant floating-point number between 0 and 1. We suggest using values of level in the range [0.01, 0.99]. Default value: 0.5. level=0.5 The function calculates the Median.
• expr— Expression on column values, resulting in a number, date, or datetime.
• weight— Sequence member weight column. The weight is the number of times the value appears.

Return value

• The quantile at the specified level.

Type:

• Float64 for numerical data type input.
• If the input value has the Date type, the result is a date.
• If the input value has the DateTime type, the result is DateTime.

Example

CREATE TABLE IF NOT EXISTS test.functionQuantileExactWeighted (n UInt8, val UInt8) ENGINE=CnchMergeTree ORDER BY n; 
INSERT INTO test.functionQuantileExactWeighted VALUES (0,3),(1,2),(2,1),(5,4); 
SELECT quantileExactWeighted(n, val) FROM test.functionQuantileExactWeighted;

┌─quantileExactWeighted(n, val)─┐ 
│                             1 │ 
└───────────────────────────────┘

quantileTDigest #

Use the t-digest algorithm to calculate the approximate quantile of a numerical data sequence.
Memory consumption is log(n), where n is the number of values. The result depends on the order in which the query is run and is not deterministic.
The function's performance is lower than that of quantile or quantileTiming. In terms of the ratio of state size to accuracy, this function is much better than quantile.
When using multiple quantile* functions with different levels in a query, the internal state is not merged (that is, the query is less efficient than it could be). In this case, use the quantile function.
Syntax

quantileTDigest(level)(expr)

Alias: medianTDigest.
Parameters

• level— Quantile level. Optional parameter. A constant floating-point number between 0 and 1. We suggest using values in the level range [0.01, 0.99]. Default value: 0.5. The level=0.5 function calculates the median.
• expr — an expression on column values, resulting in a numerical data type, that is, date or datetime.

Return value

• The approximate quantile at the specified level.

Type:

• Float64 for numerical data type input.
• If the input value has the Date type, the result is a date.
• If the input value has the DateTime type, the result is DateTime.

Example

SELECT quantileTDigest(number) FROM numbers(10)

┌─quantileTDigest(number)─┐ 
│ 4.5e+00                 │ 
└─────────────────────────┘

quantileTDigestWeighted #

Use the t-digest algorithm to calculate the approximate quantile of a numerical data sequence. This function takes into account the weight of each sequence member. The maximum error is 1%. Memory consumption is log(n), where n is the number of values. log(n)n
The function's performance is lower than that of quantile or quantileTiming. In terms of the ratio of state size to accuracy, this function is much better than quantile.
The result depends on the order in which the query is run and is not deterministic.
When using multiple quantile* functions with different levels in a query, the internal state is not merged (that is, the query is less efficient than it could be). In this case, use the quantile function.

sum #

Calculate the sum. Only applicable to numbers.
Syntax

sum(num)

Parameters

• num: numerical value column. (U)Int*, Float*, Decimal*。

Return value

• The sum of values. (U)Int*, Float*, Decimal*。

Example

1. Create a table employees and insert some fictitious employee data.

   CREATE TABLE employees 
   ( 
       `id` UInt32, 
       `name` String, 
       `salary` UInt32 
   ) 
   ENGINE = Log
   

   INSERT INTO employees VALUES 
       (87432, 'John Smith', 45680), 
       (59018, 'Jane Smith', 72350), 
       (20376, 'Ivan Ivanovich', 58900), 
       (71245, 'Anastasia Ivanovna', 89210);
   

2. Use the sum function to query the total amount of employee salaries.

   SELECT sum(salary) FROM employees;
   

   As a result:

   ┌─sum(salary)─┐ 
   1. │      266140 │ 
      └─────────────┘