floating-point - 为什么不鼓励在 ClickHouse 表中使用浮点表示？

Question

documentation并没有真正解释 Float32 的行为和 Float64以及为什么他们感到气馁。

我问这个问题是因为在将论文与控制台 cli 请求或 Rest 请求一起使用时，我看到了奇怪的行为。无论精度如何，发送到 clickhouse 的浮点值都会在最后一位稍作修改。

示例:1258.021545变成了1258.0215453 .

每次我插入这些值时，最后一位数字都会改变。
我不认为问题来自太高的精度值，因为这些值来自 Java doubles。

Answer 1

浮点数问题
通常，使用浮点数进行计算可能会产生舍入误差。
java
请考虑学习 double 之间的区别( float 二进制 点类型)和 BigDecimal (浮点 十进制 点类型)在 Java 编程语言中。
比如有一个相关的问题:java - Double vs. BigDecimal? - Stack Overflow .
点击之家
来自 Float32, Float64 | ClickHouse Documentation :

Using Floating-point Numbers

• Computations with floating-point numbers might produce a rounding error.

  SELECT 1 - 0.9
  
  ┌───────minus(1, 0.9)─┐
  │ 0.09999999999999998 │
  └─────────────────────┘
  

• The result of the calculation depends on the calculation method (the processor type and architecture of the computer system).

• Floating-point calculations might result in numbers such as infinity (Inf) and “not-a-number” (NaN). This should be taken into account when processing the results of calculations.

• When parsing floating-point numbers from text, the result might not be the nearest machine-representable number.

一些可能的解决方案
使用整数(推荐)
来自 Float32, Float64 | ClickHouse Documentation :

We recommend that you store data in integer form whenever possible. For example, convert fixed precision numbers to integer values, such as monetary amounts or page load times in milliseconds.

使用十进制数
来自 Decimal | ClickHouse Documentation :

Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S), Decimal256(S)

Signed fixed-point numbers that keep precision during add, subtract and multiply operations. For division least significant digits are discarded (not rounded).

Parameters

• P - precision. Valid range: [ 1 : 76 ]. Determines how many decimal digits number can have (including fraction).
• S - scale. Valid range: [ 0 : P ]. Determines how many decimal digits fraction can have.

<…>

Decimal Value Ranges

• Decimal32(S) - ( -1 * 10^(9 - S), 1 * 10^(9 - S) )
• Decimal64(S) - ( -1 * 10^(18 - S), 1 * 10^(18 - S) )
• Decimal128(S) - ( -1 * 10^(38 - S), 1 * 10^(38 - S) )
• Decimal256(S) - ( -1 * 10^(76 - S), 1 * 10^(76 - S) )

For example, Decimal32(4) can contain numbers from -99999.9999 to 99999.9999 with 0.0001 step.