0

My environment: DolphinDB server 2.00.8 on Linux

I used a where condition (which compares floating point numbers) to filter table data, but the query result is incorrect.

Here’s what I did:

I have a table containing the result of my factor analysis on level2 market data. See screenshot below:

enter image description here

I want to select data from the table with the condition: factorValue > percentile(factorValue, 80).

The result of percentile(factorValue, 80) is 2679.999999999999545. Therefore, in the screenshot above, there should be 2 records satisfying this condition: the records with factorValue 80329.0602 and 2680.enter image description here

But the query only returned 1 record. The record with factorValue = 2690 is not returned. Where is the problem?

enter image description here

Polly
  • 603
  • 3
  • 13

1 Answers1

0

Execute 2680 > 2679.999999999999545 in DolphinDB GUI. The result is true, which is correct. So the problem is not in the floating point comparison.

The problem may be the precision of numeric values. By default, DolphinDB GUI uses 16 digits of precision. Therefore, if the calculated factorValue is actually 2679.99999, it will be displayed as 2680 in GUI.

enter image description here

Solution:

Adjust the default number of decimal places in GUI. For example, set it to 16. Now let’s check the value of factorValue in higher precision to see if it is actually smaller than 2680. If yes, our assumption is correct.

enter image description here

enter image description here

If you still want to display the factorValue as 2680.0 in the table, use the function round to specify the number of decimal places applied in the comparison. This way you’ll still get the correct query result.

// construct data
HTSCSecurityID = take(`600000.SH, 6)
MDDate = take(2022.04.01, 6)
MDTime = 13:00:00.000 + 1..6 * 3000
factorValue = 0 0 0 80329.0602 751 2679.99999
resultTable = table(HTSCSecurityID, MDDate, MDTime, factorValue)

// filter for records with factorValue greater than 2679.999999999999545
select * from resultTable where round(factorValue, 4) > 2679.999999999999545
Polly
  • 603
  • 3
  • 13