Re: stddev returns 0 when there is one row

nolan(at)celery(dot)tssi(dot)com wrote:
>>I'm not volunteering, no. (For starters, I think you'd need to upgrade
>>the aggregate code to allow multiple-input aggregates. Which is worth
>>doing, but I do not expect to have time to think about such things for
>>7.4.)
>
> Having multiple-input aggregates opens up possibilites for a large number
> of multivariate statistical measures, either built in or as user-defined
> functions. (He says as he works on formatting output from an Oracle DBMS
> for time-series analysis via SPSS or SAS.)
>

The SQL200x standard is pretty explicit, as I posted earlier. Assuming
it doesn't change between the draft I'm looking at and the final
version, we should in fact have each of the following as unary (single
argument) aggregates: VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP.
Interestingly section 4.16.3 covers quite a bit more including a number
of binary (two argument) aggregate functions (e.g. COVAR_POP and
COVAR_SAMP). It also talks about aggregation by either groups (similar
to current) and "windows":

4.16.3 Aggregate functions
An aggregate function is a function whose result is derived from an
aggregation of rows defined by one of:
— The grouping of a grouped table, in which case the aggregate
function is a group aggregate function, or set function, and for
each group there is one aggregation, which includes every row
in the group.
— The window frame of a row R of a windowed table relative to a
particular window structure descriptor, in which case the aggregate
function is a window aggregate function, and the aggregation
consists of every row in the window frame of R, as defined by the
window structure descriptor.

All of this is very interesting, but it isn't relevant to PostgreSQL
7.4. In the meantime, if you are interested in multivariate statistics,
see PL/R here: http://www.joeconway.com/

Joe