算术函数

请注意:
下文中的一些示例引用自 ClickHouse 社区文档并经过一定修改确保可以在 ByteHouse 中正常使用。

abs

Calculates the absolute value of the number (a). That is, if a < 0, it returns -a. For unsigned types it does not do anything. For signed integer types, it returns an unsigned number.

Syntax

abs(x)

Arguments

x – The number.

Returned value

The absolute value of the number.

Example

SELECT abs(-2);

Result:

┌─abs(-2)─┐
│ 2       │
└─────────┘

divide

Calculates the quotient of the numbers. The result type is always a floating-point type.

It is not integer division. For integer division, use the ‘intDiv’ function.

When dividing by zero you get ‘inf’, ‘-inf’, or ‘nan’.

Syntax


divide(a, b) # a / b operator

Arguments

a – The number.
b – The number.

Returned value

Value in floating-point type

Example

SELECT divide(50, 2);

Result:

┌─divide(50, 2)─┐
│ 2.5e+01       │
└───────────────┘

gcd

Returns the greatest common divisor of the numbers.

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

gcd(a, b)

Arguments

a – The number.
b – The number.

Returned value

The greatest common divisor of the numbers

Example

SELECT gcd(27,18);

Result:

┌─gcd(27, 18)─┐
│ 9           │
└─────────────┘

intDiv

Calculates the quotient of the numbers. Divides into integers, rounding down (by the absolute value).

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

intDiv(a, b)

Arguments

a – The number.
b – The number.

Returned value

Quotient of the numbers in integer

Example

SELECT intDiv(10, 2);

Result:

┌─intDiv(10, 2)─┐
│ 5             │
└───────────────┘

intDivOrZero

Differs from ‘intDiv’ in that it returns zero when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

intDivOrZero(a, b)

Arguments

a – The number.
b – The number.

Returned value

Quotient of the numbers in integer

Example

SELECT intDivOrZero(10, -2);

Result:

┌─intDivOrZero(10, -2)─┐
│ -5                   │
└──────────────────────┘

lcm

Returns the least common multiple of the numbers.

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

lcm(a, b)

Arguments

a – The number.
b – The number.

Returned value

The least greatest common divisor of the numbers

Example

SELECT lcm(27,18);

Result:

┌─lcm(27, 18)─┐
│ 54          │
└─────────────┘

min

Aggregate function that calculates the minimum across a group of values.

Syntax

min(column)

Arguments

column – The column name.

Returned value

The minimum number in group of values

Example

CREATE TABLE test.test_min(id Int32) ENGINE = CnchMergeTree ORDER BY id;
INSERT INTO test.test_min(id) VALUES(1),(2),(3),(4),(5); -- insert 1,2,3,4,5 to table
SELECT min(id) FROM test.test_min;

Result:

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

minus

Calculates the difference. The result is always signed.

You can also calculate integer numbers from a date or date with time. The idea is the same – see above for ‘plus’.

Syntax

minus(a, b), a - b operator

Arguments

a – The number.
b – The number.

Returned value

The difference between a and b.

Example

SELECT minus(10, 3);

Result:

┌─minus(10, 3)─┐
│ 7            │
└──────────────┘

modulo

Calculates the remainder after division.

If arguments are floating-point numbers, they are pre-converted to integers by dropping the decimal portion.

The remainder is taken in the same sense as in C++. Truncated division is used for negative numbers.

An exception is thrown when dividing by zero or when dividing a minimal negative number by minus one.

Syntax

modulo(a, b), a % b operator

Arguments

a – The number.
b – The number.

Returned value

The remainder from a divide by b.

Example

SELECT modulo(10, 3);

Result:

┌─modulo(10, 3)─┐
│ 1             │
└───────────────┘

multiply

Calculates the product of the numbers.

Syntax

multiply(a, b) # a * b operator

Arguments

a – The number.
b – The number.

Returned value

Product value of the numbers.

Example

SELECT multiply(3,12);

Result:

┌─multiply(3, 12)─┐
│ 36              │
└─────────────────┘

negate

Calculates a number with the reverse sign. The result is always signed.

Syntax

negate(a) # -a operator

Arguments

a – The number.

Returned value

The number with the reverse sign.

Example

SELECT negate(20);

Result:

┌─negate(20)─┐
│ -20        │
└────────────┘

plus

Calculates the sum of the numbers.

You can also add integer numbers with a date or date and time. In the case of a date, adding an integer means adding the corresponding number of days. For a date with time, it means adding the corresponding number of seconds.

Syntax

select plus(a, b) # a + b operator

Arguments

a – The number.
b – The number.

Returned value

The sum of the numbers.

Example

select plus(1,2);

Result:

┌─plus(1, 2)─┐
│ 3          │
└────────────┘