Joe Conway <mail@joeconway.com> writes:
> So I'd take it that PostgreSQL's STDDEV implements STDDEV_POP.
No, we implement the sample standard deviation, as stated in the docs:
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-aggregate.html
The code is pretty straightforward, at least in the float8 case:
/* We define STDDEV of no values to be NULL, of 1 value to be 0 */
if (N == 0.0)
PG_RETURN_NULL();
if (N <= 1.0)
PG_RETURN_FLOAT8(0.0);
numerator = N * sumX2 - sumX * sumX;
/* Watch out for roundoff error producing a negative numerator */
if (numerator <= 0.0)
PG_RETURN_FLOAT8(0.0);
PG_RETURN_FLOAT8(sqrt(numerator / (N * (N - 1.0))));
I don't have a real strong feeling about whether we should change the
behavior at N=1 or not. Does the SQL200x spec provide any guidance?
regards, tom lane