Arithmetic functions

Arithmetic functions in ClickHouse perform mathematical operations on numeric data types.

These functions work with operands of types UInt8, UInt16, UInt32, UInt64, Int8, Int16, Int32, Int64, Float32, or Float64.

Type casting and result types

Before performing an operation, both operands are cast to the result type. The result type is determined as follows:

For operands up to 32 bits wide, the result type is the next larger type after the larger of the two operands (integer size promotion).
For operands of 64 bits or more, the result type matches the size of the larger operand.
If one operand is signed, the result type will be signed; otherwise, it will be unsigned.

This approach ensures quick calculations using the maximum native integer width of 64 bits while minimizing the risk of overflows.

ClickHouse function reference

plus

Calculates the sum of two values. It can be used with numeric types, dates, and timestamps.

Syntax:

plus(a, b)

Attributes:

a (numeric, date, or timestamp): The first value to be added.
b (numeric, date, or timestamp): The second value to be added.

Returns:

A numeric value, date, or timestamp, depending on the input types.

Alternatively, you can use the + operator:

a + b

Example:

SELECT
  order_id,
  num_tacos,
  plus(num_tacos, 2) AS tacos_with_extras,
  order_date,
  plus(order_date, 1) AS delivery_date,
  pickup_time,
  plus(pickup_time, 1800) AS latest_pickup_time
FROM
  taco_orders;

Result:

| order_id | num_tacos | tacos_with_extras | order_date | delivery_date | pickup_time | latest_pickup_time |
|----------|-----------|-------------------|------------|---------------|-------------|--------------------|
| 101      | 5         | 7                 | 2023-05-05 | 2023-05-06    | 18:00:00    | 18:30:00           |
| 102      | 7         | 9                 | 2023-05-06 | 2023-05-07    | 18:30:00    | 19:00:00           |
| 103      | 4         | 6                 | 2023-05-07 | 2023-05-08    | 17:15:00    | 17:45:00           |

In this example:

tacos_with_extras adds 2 extra tacos to each order.
delivery_date calculates the delivery date by adding 1 day to the order date.
latest_pickup_time adds 1800 seconds (30 minutes) to the original pickup time.

When adding an integer to a date, it increments the number of days. When adding an integer to a timestamp, it increments the number of seconds.

minus

Calculates the difference between two values. It can be used with numeric types, dates, and timestamps.

Syntax:

minus(a, b)

Attributes:

a (numeric, date, or timestamp): The value to subtract from.
b (numeric, date, or timestamp): The value to subtract.

Returns:

A numeric value, date, or timestamp, depending on the input types. The result is always signed.

Alternatively, you can use the - operator:

a - b

Example:

SELECT
  order_id,
  num_tacos,
  minus(num_tacos, 2) AS tacos_without_extras,
  order_date,
  minus(order_date, 1) AS prep_date,
  pickup_time,
  minus(pickup_time, 900) AS earliest_pickup_time
FROM
  taco_orders;

Result:

| order_id | num_tacos | tacos_without_extras | order_date  | prep_date   | pickup_time      | earliest_pickup_time |
|----------|-----------|----------------------|-------------|-------------|------------------|----------------------|
| 101      | 5         | 3                    | 2023-05-05  | 2023-05-04  | 18:00:00         | 17:45:00             |
| 102      | 7         | 5                    | 2023-05-06  | 2023-05-05  | 18:30:00         | 18:15:00             |
| 103      | 4         | 2                    | 2023-05-07  | 2023-05-06  | 17:15:00         | 17:00:00             |

In this example:

tacos_without_extras subtracts 2 tacos from each order.
prep_date calculates the preparation date by subtracting 1 day from the order date.
earliest_pickup_time subtracts 900 seconds (15 minutes) from the original pickup time.

When subtracting an integer from a date, it decrements the number of days. When subtracting an integer from a timestamp, it decrements the number of seconds.

multiply

Calculates the product of two numeric values.

Syntax:

multiply(a, b)

Alternatively, you can use the * operator:

a * b

Arguments:

a (numeric): The first value to be multiplied.
b (numeric): The second value to be multiplied.

Returns:

A numeric value representing the product of a and b.

Example:

SELECT
  order_id,
  num_tacos,
  price_per_taco,
  multiply(num_tacos, price_per_taco) AS total_price
FROM
  taco_orders;

Result:

| order_id | num_tacos | price_per_taco | total_price |
|----------|-----------|----------------|-------------|
| 1        | 3         | 2.50           | 7.50        |
| 2        | 5         | 2.75           | 13.75       |
| 3        | 2         | 3.00           | 6.00        |

In this example, multiply calculates the total price for each taco order by multiplying the number of tacos by the price per taco.

divide

Calculates the quotient of two numeric values. The result is always of type Float64.

Syntax:

divide(a, b)

Alternatively, you can use the / operator:

a / b

Arguments:

a (numeric): The dividend.
b (numeric): The divisor.

Returns:

A Float64 value representing the quotient.

Division by 0 returns inf, -inf, or nan.
For integer division, use the intDiv function instead.

Example:

SELECT
  order_id,
  total_price,
  num_tacos,
  divide(total_price, num_tacos) AS price_per_taco
FROM
  taco_orders;

Result:

| order_id | total_price | num_tacos | price_per_taco |
|----------|-------------|-----------|----------------|
| 1        | 15.00       | 3         | 5.00           |
| 2        | 25.50       | 5         | 5.10           |
| 3        | 10.00       | 2         | 5.00           |

In this example, divide calculates the price per taco by dividing the total price by the number of tacos for each order.

intDiv

Performs integer division of two numeric values, rounding down to the nearest integer.

Syntax:

intDiv(a, b)

Arguments:

a (numeric): The dividend.
b (numeric): The divisor.

Returns:

An integer result of the division, rounded down.

Example:

SELECT
  order_id,
  total_tacos,
  total_price,
  intDiv(total_price, total_tacos) AS price_per_taco
FROM
  taco_orders;

Result:

| order_id | total_tacos | total_price | price_per_taco |
|----------|-------------|-------------|----------------|
| 1        | 5           | 25          | 5              |
| 2        | 3           | 20          | 6              |
| 3        | 7           | 35          | 5              |

In this example, intDiv calculates the price per taco by dividing the total price by the number of tacos, rounding down to the nearest integer.

Note that for order 2, the actual price per taco would be 6.67, but intDiv rounds it down to 6.

intDivOrZero

Performs integer division of two values, returning zero when dividing by zero or when dividing the minimum negative number by -1.

Syntax:

intDivOrZero(a, b)

Arguments:

a (Integer): The dividend.
b (Integer): The divisor.

Returns:

The integer quotient of a divided by b, or zero if b is zero or if dividing the minimum negative number by -1. [Integer]

Example:

SELECT
  order_id,
  total_tacos,
  available_tacos,
  intDivOrZero(total_tacos, available_tacos) AS tacos_per_person
FROM
  taco_orders;

Result:

| order_id | total_tacos | available_tacos | tacos_per_person |
|----------|-------------|-----------------|------------------|
| 1        | 10          | 2               | 5                |
| 2        | 15          | 3               | 5                |
| 3        | 8           | 0               | 0                |

In this example, intDivOrZero calculates how many tacos each person can have based on the total number of tacos and available people. For order 3, where there are no available people (division by zero), the result is 0 instead of throwing an error.

isFinite

Checks whether a floating-point value is finite (not infinite and not NaN).

Syntax:

isFinite(x)

Arguments:

x (Float32 or Float64): The value to check.

Returns:

1 if the value is finite, 0 otherwise. [ UInt8 ]

Example:

SELECT
  taco_price,
  isFinite(taco_price) AS is_valid_price
FROM
  taco_menu;

Result:

| taco_price | is_valid_price |
|------------|----------------|
| 5.99       | 1              |
| inf        | 0              |
| 3.50       | 1              |
| NaN        | 0              |

In this example, isFinite checks if each taco_price is a valid finite number. The result 1 indicates a finite price, while 0 indicates an infinite or NaN price.

isInfinite

Checks if a floating-point value is infinite.

Syntax:

isInfinite(x)

Arguments:

x (Float32 or Float64): The value to check.

Returns:

1 if x is infinite, 0 otherwise. Note that 0 is returned for NaN values. [ UInt8 ]

Example:

SELECT
  taco_price,
  isInfinite(taco_price) AS is_infinite_price
FROM
  taco_menu;

Result:

| taco_price | is_infinite_price |
|------------|-------------------|
| 5.99       | 0                 |
| inf        | 1                 |
| 7.50       | 0                 |

In this example, isInfinite checks if each taco_price is infinite. The result 1 indicates an infinite price, while 0 indicates a finite price or NaN.

ifNotFinite

Checks whether a floating-point value is finite and returns either the original value or a fallback value.

Syntax:

ifNotFinite(x, y)

Arguments:

x (Float32 or Float64): Value to check for finiteness.
y (Float32 or Float64): Fallback value to return if x is not finite.

Returns:

x if x is finite
y if x is not finite (infinity or NaN)

Example:

SELECT
  taco_order_id,
  taco_price,
  ifNotFinite(taco_price / 0, 9.99) AS safe_price
FROM
  taco_orders;

Result:

| taco_order_id | taco_price | safe_price |
|---------------|------------|------------|
| 1             | 5.99       | 5.99       |
| 2             | 7.50       | 7.50       |
| 3             | 0.00       | 9.99       |

In this example, ifNotFinite is used to handle potential division by zero when calculating the taco price. If the division results in infinity (due to division by zero), the function returns the fallback price of 9.99.

isNaN

Checks if a floating-point value is NaN (Not a Number).

Syntax:

isNaN(x)

Arguments:

x (Float32 or Float64): The value to check.

Returns:

1 if the argument is NaN, 0 otherwise. [ UInt8]

Example:

SELECT
  taco_id,
  taco_price,
  isNaN(taco_price) AS is_price_nan
FROM
  taco_orders;

Result:

| taco_id | taco_price | is_price_nan |
|---------|------------|--------------|
| 1       | 5.99       | 0            |
| 2       | nan        | 1            |
| 3       | 4.50       | 0            |

In this example, isNaN checks if each taco_price is NaN. The result 1 indicates that the price is NaN, while 0 indicates a valid numeric price.

modulo

Calculates the remainder of the division of two values.

Syntax:

modulo(a, b)

Alternatively, you can use the % operator:

a % b

Arguments:

a (numeric): The dividend.
b (numeric): The divisor.

Returns:

The remainder of a divided by b. The result type is:

Integer if both inputs are integers
Float64 if one of the inputs is a floating-point number

Uses truncated division for negative numbers (like C++). Throws an exception when dividing by zero or when dividing the minimum negative number by -1.

Example:

SELECT
  order_id,
  num_tacos,
  modulo(num_tacos, 3) AS tacos_leftover
FROM
  taco_orders;

Result:

| order_id | num_tacos | tacos_leftover |
|----------|-----------|----------------|
| 101      | 5         | 2              |
| 102      | 7         | 1              |
| 103      | 4         | 1              |

In this example, modulo calculates how many tacos are left over when dividing the order quantity by 3 (e.g. for packaging in boxes of 3).

moduloOrZero

Calculates the remainder of the division of two values, returning zero if the divisor is zero.

Syntax:

moduloOrZero(a, b)

Arguments:

a (numeric): The dividend.
b (numeric): The divisor.

Returns:

The remainder of a divided by b, or zero if b is zero. [Numeric]

Example:

SELECT
  taco_order_id,
  num_tacos,
  moduloOrZero(num_tacos, 3) AS tacos_leftover
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | tacos_leftover |
|---------------|-----------|----------------|
| 1             | 5         | 2              |
| 2             | 3         | 0              |
| 3             | 7         | 1              |
| 4             | 2         | 2              |

In this example, moduloOrZero calculates how many tacos are left over when dividing the number of tacos by 3 (e.g., for grouping into sets of 3). If num_tacos were 0, the result would be 0 instead of throwing an error.

positiveModulo

Calculates the positive modulo of two values. It always returns a non-negative number.

Syntax:

positiveModulo(a, b)

Arguments:

a (numeric): The dividend.
b (numeric): The divisor.

Returns:

The positive remainder of a divided by b. [Numeric]

Aliases:

positive_modulo(a, b)
pmod(a, b)

Example:

SELECT
  taco_order_id,
  num_tacos,
  positiveModulo(num_tacos, 3) AS tacos_leftover
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | tacos_leftover |
|----------------|-----------|----------------|
| 1              | 5         | 2              |
| 2              | 7         | 1              |
| 3              | 3         | 0              |

In this example, positiveModulo calculates how many tacos are left over when dividing the order into groups of 3. The result is always non-negative, even for negative inputs.

This function is 4-5 times slower than the regular modulo function.

negate

Negates a numeric value. The result is always signed.

Syntax:

negate(a)

Alternatively, you can use the unary minus operator:

-a

Arguments:

a (numeric): The numeric value to negate.

Returns:

The negated value of a. [Numeric]

Example:

SELECT
  taco_price,
  negate(taco_price) AS negative_price,
  -taco_price AS negative_price_alt
FROM
  taco_menu;

Result:

| taco_price | negative_price | negative_price_alt |
|------------|----------------|---------------------|
| 5.99       | -5.99          | -5.99               |
| 7.50       | -7.50          | -7.50               |
| 4.25       | -4.25          | -4.25               |

In this example, negate and the unary minus operator are used to calculate the negative value of each taco price.

abs

Calculates the absolute value of a numeric input.

Syntax:

abs(a)

Arguments:

a (numeric): The numeric value to calculate the absolute value of.

Returns:

The absolute value of the input. [Numeric]

Example:

SELECT
  order_id,
  taco_price,
  abs(taco_price) AS absolute_price
FROM taco_orders;

Result:

| order_id | taco_price | absolute_price |
|----------|------------|-----------------|
| 1        | -5.99      | 5.99            |
| 2        | 4.50       | 4.50            |
| 3        | -3.25      | 3.25            |

In this example, abs calculates the absolute value of taco_price, removing the negative sign for orders 1 and 3 while leaving the positive value unchanged for order 2.

gcd

Calculates the greatest common divisor (GCD) of two numeric values.

Syntax:

gcd(a, b)

Arguments:

a (numeric): First numeric value.
b (numeric): Second numeric value.

Returns:

The greatest common divisor of a and b. [Numeric]

Example:

SELECT
  taco_order_id,
  num_tacos,
  num_sides,
  gcd(num_tacos, num_sides) AS common_divisor
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | num_sides | common_divisor |
|---------------|-----------|-----------|----------------|
| 1             | 6         | 4         | 2              |
| 2             | 9         | 3         | 3              |
| 3             | 5         | 7         | 1              |

In this example, gcd calculates the greatest common divisor between the number of tacos and sides for each order. A common divisor of 1 indicates the numbers are coprime.

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

lcm

Calculates the least common multiple of two numeric values.

Syntax:

lcm(a, b)

Arguments:

a (numeric): First numeric value.
b (numeric): Second numeric value.

Returns:

The least common multiple of a and b. [Numeric]

Example:

SELECT
  taco_order_id,
  num_tacos,
  num_sides,
  lcm(num_tacos, num_sides) AS lcm_tacos_sides
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | num_sides | lcm_tacos_sides |
|---------------|-----------|-----------|-----------------|
| 1             | 3         | 2         | 6               |
| 2             | 5         | 3         | 15              |
| 3             | 2         | 4         | 4               |

In this example, lcm calculates the least common multiple of the number of tacos and sides for each order. This could be useful for determining how many complete “sets” of the order can be made without leftover items.

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

max2

Returns the larger of two numeric values.

Syntax:

max2(a, b)

Arguments:

a (numeric): First numeric value.
b (numeric): Second numeric value.

Returns:

The larger of a and b. [Float64]

Example:

SELECT
  taco_order_id,
  num_tacos,
  extra_tacos,
  max2(num_tacos, extra_tacos) AS max_tacos
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | extra_tacos | max_tacos |
|---------------|-----------|-------------|-----------|
| 1             | 3         | 2           | 3         |
| 2             | 1         | 5           | 5         |
| 3             | 4         | 0           | 4         |

In this example, max2 compares num_tacos and extra_tacos for each order, returning the larger value as max_tacos.

min2

Returns the smaller of two numeric values.

Syntax:

min2(a, b)

Arguments:

a (numeric): First numeric value.
b (numeric): Second numeric value.

Returns:

The smaller of the two input values. [Float64]

Example:

SELECT
  taco_order_id,
  num_tacos,
  extra_tacos,
  min2(num_tacos, extra_tacos) AS min_tacos
FROM
  taco_orders;

Result:

| taco_order_id | num_tacos | extra_tacos | min_tacos |
|---------------|-----------|-------------|-----------|
| 1             | 3         | 2           | 2         |
| 2             | 5         | 1           | 1         |
| 3             | 2         | 4           | 2         |

In this example, min2 returns the smaller value between num_tacos and extra_tacos for each order. The result is stored in the min_tacos column.

multiplyDecimal

Multiplies two decimal values with precise control over the result scale.

Syntax:

multiplyDecimal(a, b [, result_scale])

Arguments:

a (Decimal): First decimal value.
b (Decimal): Second decimal value.
result_scale (Int/UInt): (Optional) Scale of the result.

Returns:

The product of a and b as a Decimal256 value with the specified scale.

Example:

SELECT
  taco_price,
  quantity,
  multiplyDecimal(taco_price, quantity, 2) AS total_price
FROM
  taco_orders;

Result:

| taco_price | quantity | total_price |
|------------|----------|-------------|
| 2.50       | 3        | 7.50        |
| 3.75       | 2        | 7.50        |
| 2.25       | 4        | 9.00        |

In this example, multiplyDecimal calculates the total price by multiplying the taco price by the quantity, with the result rounded to 2 decimal places.

This function is slower than regular multiplication but provides precise control over the result’s scale. Use regular multiplication when speed is more important than exact decimal precision.

divideDecimal

Divides two decimal values with precise control over the result scale.

Syntax:

divideDecimal(a, b [, result_scale])

Arguments:

a (Decimal): The dividend (numerator).
b (Decimal): The divisor (denominator).
result_scale (Int/UInt): (Optional) The scale of the result.

Returns:

The result of division with the specified scale. [ Decimal256 ]

Example:

SELECT
  taco_order_id,
  total_price,
  num_tacos,
  divideDecimal(total_price, num_tacos, 2) AS price_per_taco
FROM
  taco_orders;

Result:

| taco_order_id | total_price | num_tacos | price_per_taco |
|---------------|-------------|-----------|----------------|
| 1             | 15.00       | 3         | 5.00           |
| 2             | 25.50       | 5         | 5.10           |
| 3             | 10.99       | 2         | 5.49           |

In this example, divideDecimal calculates the price per taco by dividing the total price by the number of tacos, with the result rounded to 2 decimal places.

This function is significantly slower than regular division. Use the standard divide function if precise control over the result scale is not required or if faster computation is needed.

byteSwap

Reverses the bytes of an integer, changing its endianness.

Syntax:

byteSwap(a)

Arguments:

a (Integer): The integer value to reverse.

Returns:

The integer with reversed byte order. [Integer]

Example:

SELECT
  taco_order_id,
  byteSwap(taco_order_id) AS reversed_id
FROM
  taco_orders
WHERE
  taco_order_id = 3351772109;

Result:

| taco_order_id | reversed_id |
|---------------|-------------|
| 3351772109    | 3455829959  |

In this example, byteSwap reverses the bytes of the taco_order_id. The process can be understood as:

Convert 3351772109 to hexadecimal: C7 C7 FB CD
Reverse the bytes: CD FB C7 C7
Convert back to decimal: 3455829959

This function can be useful for tasks like reversing IPv4 addresses stored as integers

Introduction

Statements

Functions

Type casting and result types

ClickHouse function reference

plus

minus

multiply

divide

intDiv

intDivOrZero

isFinite

isInfinite

ifNotFinite

isNaN

modulo

moduloOrZero

positiveModulo

negate

abs

gcd

lcm

max2

min2

multiplyDecimal

divideDecimal

byteSwap

Introduction

Statements

Functions

​Type casting and result types

​ClickHouse function reference

​plus

​minus

​multiply

​divide

​intDiv

​intDivOrZero

​isFinite

​isInfinite

​ifNotFinite

​isNaN

​modulo

​moduloOrZero

​positiveModulo

​negate

​abs

​gcd

​lcm

​max2

​min2

​multiplyDecimal

​divideDecimal

​byteSwap

Type casting and result types

ClickHouse function reference

plus

minus

multiply

divide

intDiv

intDivOrZero

isFinite

isInfinite

ifNotFinite

isNaN

modulo

moduloOrZero

positiveModulo

negate

abs

gcd

lcm

max2

min2

multiplyDecimal

divideDecimal

byteSwap