Арифметические функции

SELECT abs(-0.5)

0.5

SELECT avg2(toUInt8(3), 1.0) AS result, toTypeName(result) AS type;
-- Возвращаемый тип — Float64, так как UInt8 необходимо расширить до 64 бит для выполнения сравнения.

┌─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

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(toDecimal64(-12, 1), toDecimal32(2.1, 1), 1)─┬─divideDecimal(toDecimal64(-12, 1), toDecimal32(2.1, 1), 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(intDiv(toFloat64(1), 0.001))─┐
│ 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 lcm(6, 8)

24

SELECT max2(-1, 2)

2

SELECT midpoint(1, toUInt8(3), 0.5) AS result, toTypeName(result) AS type;
-- Возвращаемый тип — Float64, так как UInt8 необходимо расширить до 64 бит для выполнения сравнения.

┌─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 moduloOrZero(5, 0)

0

SELECT multiply(5,5)

25

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

25.2

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

┌─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);
SELECT
    toDecimal64(-12.647987876, 9) AS a,
    toDecimal64(123.967645643, 9) AS b,
    a * b;

┌─────────────a─┬─────────────b─┬─multiplyDecimal(toDecimal64(-12.647987876, 9), toDecimal64(123.967645643, 9))─┐
│ -12.647987876 │ 123.967645643 │                                                               -1567.941279108 │
└───────────────┴───────────────┴───────────────────────────────────────────────────────────────────────────────┘
Получено исключение от сервера (версия 22.11.1):
Code: 407. DB::Exception: Received from localhost:9000. DB::Exception: Decimal math overflow:
While processing toDecimal64(-12.647987876, 9) AS a, toDecimal64(123.967645643, 9) AS b, a * b. (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