dble
dble copied to clipboard
`STD()`/`STDDEV()`/`STDDEV_POP()`/`STDDEV_SAMP()`/`VAR_POP()`/`VAR_SAMP()`/`VARIANCE()` result precision is not correct
Raised by: @FlyingMao Steps:
- Dble:
- MySQL:
By @ditdb
Analysis
- MySQL uses recurrence formula, while Dble use the sum of squares, which causes the precision misbehave on two similar number subtraction (refer to <数值分析,第8页,清华大学出版社,冯有前>).
In MySQL, refer to
sql/item_sum.h
Computing standard deviation refers to https://www.johndcook.com/blog/2008/09/26/comparing-three-methods-of-computing-standard-deviation/
- MySQL returns data in string format, which causes precision lose too.
- Floating calculation implementation differs between MySQL and Java.
Cannot figures out a better way.
BTW: The method mentions in http://www.cnblogs.com/yoyaprogrammer/p/delta_variance.html also causes precision lose.