Precision and Accuracy in DECIMAL Calculations

1. Characteristics of DECIMAL Calculations

1.1. Calculation Logic

In DolphinDB, a DECIMAL value is divided into two parts for storage: a raw integer containing the raw data, and a scale denoting the number of decimal places. For example, the decimal value 1.23 with a precision of 2 decimal places would be stored as the raw integer 123 and the scale: 2. This storage approach allows calculations to be performed directly on the integer data to avoid precision loss.

When calculating DECIMAL values with functions that return Floating results, calculations are first performed on the integers. The eventual result is converted to Floating type to avoid precision loss during calculation. For example, when calculating the average of DECIMAL32(2) values [1.11, 2.22, 3.33], DolphinDB first computes the sum of the integers 111 + 222 + 333 = 666, and then converts the result to DOUBLE type and divides by the count double(666) / 10^2 / 3 = 2.22.

For the rules of DECIMAL arithmetic operations, see DECIMAL Data Type.

1.2. Data Types of Results

This section discusses the return types when a computational function takes DECIMAL values as inputs.

Only the following DDB functions take DECIMAL inputs and also return DECIMAL results to retain the full decimal precision throughout: sum, max, min, first, last, firstNot, lastNot, and their corresponding cum, m, tm and TopN functions, as well as cumPositiveStreak.

a = decimal64(rand(10,100),4)

typestr(sum(a))
// output: DECIMAL128

typestr(cummax(a))
// output: FAST DECIMAL64 VECTOR

typestr(mmin(a,5))
// output: FAST DECIMAL64 VECTOR

T = 2023.03.23..2023.06.30
typestr(tmsum(T, a, 3))
// output: FAST DECIMAL128 VECTOR

typestr(cumPositiveStreak(a))
// output: FAST DECIMAL128 VECTOR

Note that DECIMAL128 is introduced since version 2.00.10, and the input and output of sum-related functions (including sum/cumsum/msum/tmsum/msumTopN/cumsumTopN/tmsumTopN/cumPositiveStreak) are as follows:

Input TypeOutput Type
DECIMAL32DECIMAL64
DECIMAL64DECIMAL128
DECIMAL128DECIMAL128

Except the functions listed above, functions such as avg, std, var, and skew, and their corresponding cum, m, tm, TopN functions, take DECIMAL inputs and return DOUBLE results.

a = decimal64(rand(10,100),4)

typestr(avg(a))
// output: DOUBLE

typestr(cumstd(a))
// output: FAST DOUBLE VECTOR

typestr(mvar(a,5))
// output: FAST DOUBLE VECTOR

T = 2023.03.23..2023.06.30
typestr(tmskew(T, a, 3))
// output: FAST DOUBLE VECTOR

The following table lists the output data types when calculating DECIMAL64 data:

FunctionInput TypeOutput Type
sum/cumsum/msum/tmsumDECIMAL64DECIMAL128
msumTopN/cumsumTopN/tmsumTopNDECIMAL64DECIMAL128
max/cummax/mmax/tmmaxDECIMAL64DECIMAL64
min/cummin/mmin/tmminDECIMAL64DECIMAL64
first/mfirst/tmfirstDECIMAL64DECIMAL64
last/mlast/tmlastDECIMAL64DECIMAL64
firstNot/cumfirstNotDECIMAL64DECIMAL64
lastNot/cumlastNotDECIMAL64DECIMAL64
cumPositiveStreakDECIMAL64DECIMAL128
avg/cumavg/mavg/tmavg/mavgTopN/cumavgTopN/tmavgTopNDECIMAL64DOUBLE
sum2/cumsum2/msum2/tmsum2DECIMAL64DOUBLE
prod/cumprod/mprod/tmprodDECIMAL64DOUBLE
med/cummed/mmed/tmmedDECIMAL64DOUBLE
std/cumstd/mstd/tmstd/mstdTopN/cumstdTopN/tmstdTopNDECIMAL64DOUBLE
skew/mskew/tmskew/mskewTopN/cumskewTopN/tmskewTopNDECIMAL64DOUBLE
kurtosis/mkurtosis/tmkurtosis/mkurtosisTopN/cumkurtosisTopN/tmkurtosisTopNDECIMAL64DOUBLE
corr/cumcorr/mcorr/tmcorr/mcorrTopN/cumcorrTopN/tmcorrTopNDECIMAL64DOUBLE
covar/cumcovar/mcovar/tmcovar/mcovarTopN/cumcovarTopN/tmcovarTopNDECIMAL64DOUBLE
beta/cumbeta/mbeta/tmbeta/mbetaTopN/cumbetaTopN/tmbetaTopNDECIMAL64DOUBLE

2. Performance and Accuracy Tradeoffs

2.1. Advantages of DECIMAL

Floating numbers have inherent limitations in accurately representing real values in computer systems. This stems from two main reasons. First, binary floating-point formats can only approximate decimal values that have finite representations, such as 0.1. The binary representation cannot precisely encode such decimal fractions. Second, Floating types have a finite range and precision. If a number exceeds the maximum or minimum representable value, the computer system will round or truncate it, introducing further imprecision. In contrast, the DECIMAL data type is capable of precisely representing and computing with data that has an exact decimal representation.

For example, the Floating type is unable to precisely represent the value 123.0001, whereas the DECIMAL type can accurately represent it.

a =123.0001
print(a)
// output: 123.000100000000003

b = decimal64(`123.0001,15)
print(b)
// output: 123.000100000000000

Calculating avg of Floating numbers may yield precision errors, whereas DECIMAL returns an accurate result of DOUBLE type.

a = array(DOUBLE,0)
for (i in 1..100){
	a.append!(123.0000+0.0003*i)
}
avg(a)
// output: 123.015149999999
avg(a) == 123.01515
// output: false
eqFloat(avg(a),123.01515)
// output: true

b= array(DECIMAL64(4),0)
for (i in 1..100){
	b.append!(123.0000+0.0003*i)
}
avg(b)
// output: 123.015150000000
typestr(avg(b))
// output: DOUBLE
avg(b) == 123.01515
// output: true

2.2. Disadvantages of DECIMAL

Overflow Issues

The numeric ranges for the DECIMAL32/DECIMAL64/DECIMAL128 types are as shown in the table below, where S designates the preserved decimal digits.

Underlying Storage Data TypesByte SizeScale RangeValid Numeric RangeMaximum Number of Digits
DECIMAL32int32_t4[0,9](-1 * 10 (9 - S), 1 * 10 (9 - S))9
DECIMAL64int64_t8[0,18](-1 * 10 (18 - S), 1 * 10 (18 - S))18
DECIMAL128int128_t16[0,38](-1 * 10 (38 - S), 1 * 10 (38 - S))38

DECIMAL values may encounter overflow issues constrained by the valid numeric range and maximum number of digits. As of version 2.00.10, DolphinDB supports automatic conversion to a higher precision DECIMAL type to avoid overflow in arithmetic operations:

version 2.00.9.6: 
a = decimal32(4.0000,4)
b = decimal32(8.0000,4)
c = a*b
// Server response: 'c = a * b => Decimal math overflow'

version 2.00.10: 
a = decimal32(4.0000,4)
b = decimal32(8.0000,4)
c = a*b
print(c)
// output: 32.00000000
typestr(c)
// output: DECIMAL64

However, there still remains overflow risks with DECIMAL128:

a = decimal128(36.00000000,8)
b = a*a*a*a
// Server response: 'b = a * a * a * a => Decimal math overflow'

As shown in the above example, when multiplying two DECIMAL values in DolphinDB, the result scale is determined by summing the operand scales. This can lead to an overflow error if the number of digits exceeds the DECIMAL precision limit. In the case of variable "b", the expected result is 1679616.00000000000000000000000000000000, which contains 39 digits. This exceeds the maximum representation of 38 digits for DECIMAL128 and leads to an overflow.

As of version 2.00.10, the decimalMultiply function is introduced for DECIMAL multiplication. Compared with the multiply function (and * operator), this function allows specifying the precision of result. In cases where multiplying DECIMAL values could lead to overflow due to accumulated scales, decimalMultiply can be used to set the result precision explicitly and avoid overflow.

a = decimal64(36.00000000,8) decimalMultiply(a, a, 8)
// output: 1296.00000000

Conversion Errors

If we generate DECIMAL values from constants, errors might occur due to the conversion of floating-point numbers:

a = 0.5599
decimal64(a, 4) 
// output: 0.5598

To mitigate such errors, using strings to generate DECIMAL types is recommended:

a = "0.5599"
decimal64(a, 4)
// output: 0.5599

Memory Usage

DECIMAL32, DECIMAL64, and DECIMAL128 types occupy 4 bytes, 8 bytes, and 16 bytes of memory. FLOAT and DOUBLE types use 4 bytes and 8 bytes. DECIMAL128 could take up twice the memory of DOUBLE to represent the same value. Additionally, arithmetic overflows in DECIMAL32 and DECIMAL 64 that trigger type promotion may double the memory usage. This can pose a potential memory risk when working with large datasets of DECIMAL type.

Computing Performance

Compared to FLOAT and DOUBLE types, calculations of DECIMAL are slower due to the more complex internal representation. Specific performance comparisons will be presented in the next chapter.

Limitations

Currently, the DECIMAL type in DolphinDB has fewer functions and less compatibility than FLOAT/DOUBLE:

  • Some computational functions do not yet support DECIMAL data types.
  • Most statistical functions will generate Floating results even when taking DECIMAL numbers as inputs.
  • Matrices and sets do not support DECIMAL data types.
  • For data type conversion, the DolphinDB system currently does not support the conversion between DECIMAL types and BOOL/CHAR/SYMBOL/UUID/IPADDR/INT128 types or temporal types. To convert STRING or BLOB type to DECIMAL, the string must be convertible to a numeric type.

3. Floating v.s. DECIMAL: Performance Comparison

To evaluate the computational performance of Floating and DECIMAL types, the following example uses several commonly-used computational functions and measures the computation time for each data type using the same dataset.

First, execute the following script to simulate data:

n = 1000000
data1 = rand(float(100.0), n)
data2 = double(data1)
data3 = decimal32(data1, 4)
data4 = decimal64(data1, 4)
data5 = decimal128(data1, 4)

The timer statement is used to measure the computation time:

timer(100) { sum(data1) }  // Executing 100 times to lower single computation errors and magnify differences in time consumption between different types
timer(100) { sum(data2) }
timer(100) { sum(data3) }
timer(100) { sum(data4) }
timer(100) { sum(data5) }
...

Below is the computation time (in ms) for each function and data type:

FunctionFLOATDOUBLEDECIMAL32DECIMAL64DECIMAL128
sum52.98564.33128.72157.234107.126
sum2173.278211.772176.261180.051603.422
prod63.65055.641171.718174.38918850.009
avg178.086210.49526.094106.869258.508
std362.388345.758278.933319.177968.276
kurtosis547.751407.561585.859769.8791013.591
skew546.705439.348622.758842.9941074.128

The performance statistics reveal the performance differences when calculating Floating and DECIMAL values in DolphinDB:

(1) For most of the computational functions, computing DECIMAL is slower than FLOAT/DOUBLE types.

(2) During computations, DECIMAL128 is converted into LONG DOUBLE (and DECIMAL32/DECIMAL64 into DOUBLE). A LONG DOUBLE can take up 12 or 16 bytes, depending on the compiler and CPU. Multiplying LONG DOUBLE values can be highly time-consuming when dealing with huge volume of data. In the given example, the values of the vectors are relatively large, resulting in large products and slower calculations. However, when working with smaller value ranges like [0.5, 1.0], the calculation speed of DECIMAL128 is more comparable to that of DECIMAL32/64.

(3) When conducting calculations with DECIMAL types, certain functions exhibit minimal performance differences compared to Floating numbers. DECIMAL32/64 can even achieve performance comparable to or even better than FLOAT/DOUBLE. This is because DECIMAL computations essentially operate on raw integers, allowing results to be returned directly as DECIMAL or converted to Floating types. Additionally, operations on Floating data (such as sum) may involve loop unrolling and consequently take more time during calculations.

4. Best Practice for Calculating DECIMAL: mavg

In this chapter, the precision of calculations in DECIMAL and Floating types is measured, with a specific focus on the precision loss that occurs during calculations such as moving average.

DolphinDB provides two methods for calculating moving average: mavg and moving(avg, ...). However, the calculation logic differs. The mavg algorithm involves adding new values to the window and subtracting unneeded values while calculating the average. This process introduces precision issues due to the addition and subtraction operations. On the other hand, moving(avg, ...) calculates the average of all the elements every time when a window is determined.

First, import test data from tick.csv (with data of DOUBLE type), and calculate mavg and moving(avg, ...):

data = loadText("<yourDirectory>/tick.csv")

t = select
    MDTime,
    ((LastPx - prev(LastPx)) / (prev(LastPx) + 1E-10) * 1000) as val,
    mavg(((LastPx - prev(LastPx)) / (prev(LastPx) + 1E-10) * 1000), 20, 1),
    moving(avg, ((LastPx - prev(LastPx)) / (prev(LastPx) + 1E-10) * 1000), 20, 1)
    from data

The result is as shown below. Calculation errors start to appear between the two methods from 09:31:53.000 onwards.



To mitigate these calculation errors, the following script converts the intermediate calculation results to DECIMAL128 type before invoking mavg and moving(avg, ...):

data = loadText("<yourDirectory>/tick.csv")

t = select
    MDTime,
    ((LastPx - prev(LastPx)) / (prev(LastPx) + 1E-10) * 1000) as val,
    mavg(decimal128(((LastPx - prev(LastPx)) / (prev(LastPx) + 1E-10) * 1000), 4), 20, 1),
    moving(avg, decimal128(((LastPx - prev(LastPx)) / (prev(LastPx) + 1E-10) * 1000), 4), 20, 1)
from data

The result is as shown below:



As observed, the results of mavg and moving(avg, ...) are identical.

Functions like avg and std, and their corresponding cum, tm, m, TopN functions, may encounter precision errors in computations due to the precision issues in floating-point calculations. In situations where precision is important, it is recommended to use DECIMAL for calculations.

5. Conclusion

Floating-point representations can introduce small errors and imprecise values due to their internal binary storage. The DECIMAL data types in DolphinDB provide accurate representations to avoid these precision issues. However, using DECIMAL comes with drawbacks such as potential overflow, higher memory usage, and relatively lower performance. Despite these limitations, in certain scenarios, using the DECIMAL types can effectively prevent discrepancies between expected and computed outcomes. In summary, DECIMAL types provide accurate representations at the potential expense of performance and scalability.