Arithmetic

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

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          │
└────────────┘