Funções Aritméticas

SELECT abs(-0.5)

0.5

SELECT avg2(toUInt8(3), 1.0) AS result, toTypeName(result) AS type;
-- O tipo retornado é Float64 pois o UInt8 deve ser promovido para 64 bits para a comparação.

┌─result─┬─type────┐
│      2 │ Float64 │
└────────┴─────────┘

SELECT avg2(toDecimal32(1, 2), 2) AS result, toTypeName(result) AS type;

┌─result─┬─type──────────┐
│    1.5 │ Decimal(9, 2) │
└────────┴───────────────┘

SELECT avg2(toDate('2025-01-01'), toDate('2025-01-05')) AS result, toTypeName(result) AS type;

┌─────result─┬─type─┐
│ 2025-01-03 │ Date │
└────────────┴──────┘

SELECT avg2(toDateTime('2025-01-01 00:00:00'), toDateTime('2025-01-03 12:00:00')) AS result, toTypeName(result) AS type;

┌──────────────result─┬─type─────┐
│ 2025-01-02 06:00:00 │ DateTime │
└─────────────────────┴──────────┘

SELECT avg2(toTime64('12:00:00', 0), toTime64('14:00:00', 0)) AS result, toTypeName(result) AS type;

┌───result─┬─type──────┐
│ 13:00:00 │ Time64(0) │
└──────────┴───────────┘

SELECT byteSwap(3351772109)

3455829959

SELECT byteSwap(54)

54

SELECT byteSwap(4135)

10000

SELECT byteSwap(3351772109)

3455829959

SELECT byteSwap(123294967295)

18439412204227788800

SELECT divide(25,5) AS quotient, toTypeName(quotient)

5	Float64

SELECT divide(25,0)

inf

SELECT divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)

┌─divideDecimal(toDecimal256(-12, 0), toDecimal32(2.1, 1), 10)─┐
│                                                -5.7142857142 │
└──────────────────────────────────────────────────────────────┘

SELECT toDecimal64(-12, 1) / toDecimal32(2.1, 1);
SELECT toDecimal64(-12, 1) as a, toDecimal32(2.1, 1) as b, divideDecimal(a, b, 1), divideDecimal(a, b, 5);

┌─divide(toDecimal64(-12, 1), toDecimal32(2.1, 1))─┐
│                                             -5.7 │
└──────────────────────────────────────────────────┘
┌───a─┬───b─┬─divideDecimal(a, b, 1)─┬─divideDecimal(a, b, 5)─┐
│ -12 │ 2.1 │                   -5.7 │               -5.71428 │
└─────┴─────┴────────────────────────┴────────────────────────┘

SELECT divideOrNull(25, 0)

\N

SELECT gcd(12, 18)

6

SELECT 1/0 AS infimum, ifNotFinite(infimum,42)

inf	42

SELECT intDiv(toFloat64(1), 0.001) AS res, toTypeName(res)

┌──res─┬─toTypeName(res)─┐
│ 1000 │ Int64           │
└──────┴─────────────────┘

SELECT
intDiv(1, 0.001) AS res,
toTypeName(res)

Received exception from server (version 23.2.1):
Code: 153. DB::Exception: Received from localhost:9000. DB::Exception:
Cannot perform integer division, because it will produce infinite or too
large number: While processing intDiv(1, 0.001) AS res, toTypeName(res).
(ILLEGAL_DIVISION)

SELECT intDivOrNull(1, 0)

\N

SELECT intDivOrNull(-9223372036854775808, -1)

\N

SELECT intDivOrZero(1, 0)

0

SELECT intDivOrZero(0.05, -1)

0

SELECT isFinite(inf)

0

SELECT isInfinite(inf), isInfinite(NaN), isInfinite(10)

1	0	0

SELECT isNaN(NaN)

1

SELECT kqlBin(4.5, 1)

4

SELECT kqlBin(toIntervalNanosecond(16 * 86400000000000), toIntervalNanosecond(7 * 86400000000000))

1209600000000000

SELECT kqlBin(toDateTime64('2026-08-01 12:34:56', 7, 'UTC'), toIntervalHour(1))

2026-08-01 12:00:00.0000000

SELECT kqlBinAt(6.5, 2.5, -0.5)

4.5

SELECT kqlBinAt(toDateTime64('2026-08-01 12:34:56', 7, 'UTC'), toIntervalHour(1), toDateTime64('2026-08-01 00:30:00', 7, 'UTC'))

2026-08-01 12:30:00.0000000

SELECT kqlDivide(7, 2)

3

SELECT kqlDivide(7.0, 2)

3.5

SELECT kqlDivide(toIntervalNanosecond(15000000), toIntervalNanosecond(10000000))

1.5

SELECT kqlMultiply(2, toIntervalNanosecond(3600000000000))

7200000000000

SELECT kqlMultiply(6, 7)

42

SELECT kqlRangeCount(1, 7, 2)

4

SELECT kqlRangeCount(toDateTime64('2026-08-01 00:00:00', 7, 'UTC'), toDateTime64('2026-08-01 12:00:00', 7, 'UTC'), toIntervalHour(5))

3

SELECT lcm(6, 8)

24

SELECT max2(-1, 2)

2

SELECT midpoint(1, toUInt8(3), 0.5) AS result, toTypeName(result) AS type;
-- O tipo retornado é Float64 pois o UInt8 deve ser promovido para 64 bits para a comparação.

┌─result─┬─type────┐
│    1.5 │ Float64 │
└────────┴─────────┘

SELECT midpoint(toDecimal32(1.5, 2), toDecimal32(1, 1), 2) AS result, toTypeName(result) AS type;

┌─result─┬─type──────────┐
│    1.5 │ Decimal(9, 2) │
└────────┴───────────────┘

SELECT midpoint(toDate('2025-01-01'), toDate('2025-01-05')) AS result, toTypeName(result) AS type;

┌─────result─┬─type─┐
│ 2025-01-03 │ Date │
└────────────┴──────┘

SELECT midpoint(toDateTime('2025-01-01 00:00:00'), toDateTime('2025-01-03 12:00:00')) AS result, toTypeName(result) AS type;

┌──────────────result─┬─type─────┐
│ 2025-01-02 06:00:00 │ DateTime │
└─────────────────────┴──────────┘

SELECT midpoint(toTime64('12:00:00', 0), toTime64('14:00:00', 0)) AS result, toTypeName(result) AS type;

┌───result─┬─type──────┐
│ 13:00:00 │ Time64(0) │
└──────────┴───────────┘

SELECT min2(-1, 2)

-1

SELECT minus(10, 5)

5

SELECT minus(toDate('2025-01-01'),5)

2024-12-27

SELECT modulo(5, 2)

1

SELECT moduloLegacy(10, 3)

1

SELECT moduloOrNull(5, 0)

\N

SELECT moduloOrNull(toInt8(-128), toInt8(-1))

\N

SELECT moduloOrZero(5, 0)

0

SELECT moduloOrZero(toFloat64(5), toFloat64(0))

nan

SELECT multiply(5,5)

25

SELECT multiplyDecimal(toDecimal256(-12, 0), toDecimal32(-2.1, 1), 1)

25.2

SELECT multiply(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4));
SELECT multiplyDecimal(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4));

┌─multiply(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│                                               -26.8609633 │
└───────────────────────────────────────────────────────────┘
┌─multiplyDecimal(toDecimal64(-12.647, 3), toDecimal32(2.1239, 4))─┐
│                                                         -26.8609 │
└──────────────────────────────────────────────────────────────────┘

SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    multiplyDecimal(a, b);

┌─────────────a─┬─────────────b─┬─multiplyDecimal(a, b)─┐
│ -12.647987876 │ 123.967645643 │       -1567.941279108 │
└───────────────┴───────────────┴───────────────────────┘

SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    a * b;

Received exception:
Code: 407. DB::Exception: Decimal math overflow. (DECIMAL_OVERFLOW)

SELECT negate(10)

-10

SELECT plus(5,5)

10

SELECT plus(toDate('2025-01-01'),5)

2025-01-06

SELECT toDate('2025-01-01') + CAST('14:30:25', 'Time')

2025-01-01 14:30:25

SELECT positiveModulo(-1, 10)

9

SELECT positiveModuloOrNull(5, 0)

\N

SELECT positiveModuloOrNull(toInt8(-128), toInt8(-1))

\N

SELECT sqr(5)

25