dble icon indicating copy to clipboard operation
dble copied to clipboard

`STD()`/`STDDEV()`/`STDDEV_POP()`/`STDDEV_SAMP()`/`VAR_POP()`/`VAR_SAMP()`/`VARIANCE()` result precision is not correct

Open actiontech-bot opened this issue 7 years ago • 1 comments

Raised by: @FlyingMao Steps:

  1. Dble: image
  2. MySQL: image

actiontech-bot avatar Aug 25 '17 07:08 actiontech-bot

By @ditdb

Analysis

  1. 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/

  1. MySQL returns data in string format, which causes precision lose too.
  2. 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.

actiontech-bot avatar Aug 25 '17 07:08 actiontech-bot