Do aggregate function (sum, average) support precision arithmetic


#1

Do aggregate function (sum, average) support precision arithmetic?
I have impression that aggregate functions like SUM and AVG convert the number to some imprecise floating point representation - something like 64-bit IEEE 754.

/* 10,000 Accounts */
select tostring(sum(tonumber(“0.15”))) as impreciseSum
from documents doc where doc.entityType = ‘Account’;

[
{
“impreciseSum”: “1500.0000000001792”
}
]

/* 10,000 Accounts */
select tostring(avg(tonumber(“0.15”))) as impreciseAverage
from documents doc where doc.entityType = ‘Account’;

[
{
“impreciseAverage”: “0.15000000000001792”
}
]

Is there a way to get a precise result?


#2

You can use ROUND on results which ever precision you want
tostring(ROUND(avg(tonumber(“0.15”)),3))


#3

I am not sure this helps me - my use cases are things like summation of account balances - what I would like to get is high precision arithmetic. One way around it would be to store the values (for example account amounts) as strings and do the calculations in the java library (https://docs.oracle.com/javase/6/docs/api/java/math/BigDecimal.html). But this is awkward as I would have to fetch all the data and do the sum/avg myself. Is there a better way how to do it?


#4

N1QL JSON uses float64. BigDecimal is not suported