You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
In standard deviation calculation there is the Bessel's correction this mean that there are 'two ways' of calculating a deviation: population standard deviation and sample standard deviation, they are very similar but the formula and result varies a little bit.
population stddev
$\sqrt{Σxi - u}\over{N}$
sample stddev
$\sqrt{Σxi - u}\over{N-1}$
The problem.
Our stddev is a population stddev but postgres's is sample stddev so crate and postgres cannot equate stddev results.
crate: select stddev(a) from unnest([1.0, 2.0, 3.0, 4.0, 5.0]) as a; -> 1.4142135623730951
postgres: select stddev(a) from unnest(array[1.0, 2.0, 3.0, 4.0, 5.0]) as a; -> 1.5811388300841897
Possible Solutions
Change the implementation of stddev to match postgres' or keep it the same and add stddev_pop and stddev_samp? so we can at least polyfill/overload stddev calls.
Most databases seem to implement both functions in one way or another:
postgres -> stddev (default sample), stddev_pop and stddev_samp
mongodb -> stdDevPop and stdDevSamp
mysql -> sstdev (default pop), stddev_pop, stddev_samp
influxdb -> stddev(..., mode: 'sample' or 'population' = 'sample')
Considered Alternatives
No response
The text was updated successfully, but these errors were encountered:
surister
changed the title
stddev missmatch issue, stddev missmatches postgres'
Feb 29, 2024
Context
In standard deviation calculation there is the Bessel's correction this mean that there are 'two ways' of calculating a deviation:
population standard deviation
andsample standard deviation
, they are very similar but the formula and result varies a little bit.population stddev
sample stddev
The problem.
Our
stddev
is apopulation stddev
but postgres's issample stddev
so crate and postgres cannot equatestddev
results.you can quickly check this in here and running
crate:
select stddev(a) from unnest([1.0, 2.0, 3.0, 4.0, 5.0]) as a; -> 1.4142135623730951
postgres:
select stddev(a) from unnest(array[1.0, 2.0, 3.0, 4.0, 5.0]) as a; -> 1.5811388300841897
Possible Solutions
Change the implementation of stddev to match postgres' or keep it the same and add
stddev_pop
andstddev_samp
? so we can at least polyfill/overload stddev calls.Most databases seem to implement both functions in one way or another:
postgres ->
stddev
(default sample),stddev_pop
andstddev_samp
mongodb ->
stdDevPop
andstdDevSamp
mysql ->
sstdev
(default pop),stddev_pop
,stddev_samp
influxdb ->
stddev(..., mode: 'sample' or 'population' = 'sample')
Considered Alternatives
No response
The text was updated successfully, but these errors were encountered: