New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

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

Open

actiontech-bot opened this issue Aug 25, 2017 · 1 comment

Assignees

Labels

complex-query Function Migrated

Member

actiontech-bot commented Aug 25, 2017 •

edited

Loading

Raised by: @FlyingMao
Steps:

Dble:
MySQL:

actiontech-bot added the Migrated label

actiontech-bot self-assigned this

zhunina added complex-query Function labels

Member Author

actiontech-bot commented Aug 25, 2017

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment