До какой точности происходит округление в запросах

[Владимир К.]

При сравнении результатов запросов с Oracle обнаружил некоторые расхождения, судя по всему связанные с работой округления. Есть таблица с некоторым полем VAL, имеющим тим Float64, затем выполняется запрос, в котором вычисляется сумма записей удовлетворяющих определенному условию и общая сумма записей, затем считается доля, на которую накладываются ограничения, нечто вроде такого:

select ID
from 
(
select 1 as JOIN_COL, ID, sum(VAL) sum
from DB.MY_TABLE
where 
... куча всяких условий
group by ID
) ANY INNER JOIN
(
select 1 as JOIN_COL, sum(VAL) sum_all
from DB.MY_TABLE
) USING JOIN_COL
WHERE sum/sum_all > 0.000001

 Дак вот интересует какого типа будет получаться значение выражения sum/sum_all в условии WHERE? Какая у него будет точность?

(экспериментально установил, что результаты стали совпадать, когда изменил границу до > 0.0001)

[Николай Кочетов]

Если поле VAL имеет тип Float64, то и выражение sum/sum_all имеет тип Float64.
Точность связана с особенностью хранения типов с плавающей точкой. При этом сложно предсказать, какая точность получится у результата. Она зависит от распределения VAL, количества суммируемых чисел, а также порядка суммирования (который в таком запросе может быть произвольным). В частности, при повторных запусках могут получаться разные результаты.

Если точность важна, можно хранить значения в Int64, домноженные на константу.

Кажется, что запрос можно сделать без JOIN секции. Примерно так:

select ID
from
(
        select ID, (select sum(VAL) from DB.MY_TABLE) sum_all, sum(VAL)/sum_all as fraction

from DB.MY_TABLE
where 
... куча всяких условий
group by ID

        having fraction > 0.000001
)

или

with (select sum(VAL) from DB.MY_TABLE) as sum_all
select ID

from DB.MY_TABLE
where 
... куча всяких условий
group by ID

having sum(val) / sum_all > 0.000001


пятница, 13 октября 2017 г., 9:50:14 UTC+3 пользователь Владимир К. написал:

[Владимир К.]

История с плавающей точкой понятна, в оригинале когда экспериментировал ведь это учитывал, а когда стал задавать вопрос необдумав убрал эту часть. В общем перед суммой делаю предварительно floor значения (и соответственно trunc в Oracle), в этом случае какого типа будет выражение  sum/sum_all, если они считаются как sum(floor(VAL))? Уместно ли будет ожидать, что floor(VAL) это Int64, sum(floor(VAL)) соответственно тоже Int64, а затем отношение Int64 к Int64 будет Float64 или же будет как то иначе?

И отдельное спасибо за альтернативный вариант написания запроса, хотя я пока сравниваю результаты с имеющейся системой и запросы сравниваю as is, с минимальной адаптацией.

пятница, 13 октября 2017 г., 20:00:04 UTC+5 пользователь Николай Кочетов написал:

[Mikhail Filimonov]

О типах проще всего спросить у самого Clickhouse:

SELECT toTypeName(floor(toFloat64(100.001)))

┌─toTypeName(floor(toFloat64(100.001)))─┐
│ Float64                               │
└───────────────────────────────────────┘

SELECT toTypeName(toInt64(floor(toFloat64(100.001))))

┌─toTypeName(toInt64(floor(toFloat64(100.001))))─┐
│ Int64                                          │
└────────────────────────────────────────────────┘

SELECT toTypeName(toInt64(100) / toInt64(10))

┌─toTypeName(divide(toInt64(100), toInt64(10)))─┐
│ Float64                                       │
└───────────────────────────────────────────────┘