Thread: variance aggregates per SQL:2003
This patch implements some new aggregate functions defined by SQL2003: stddev_pop(), stddev_samp(), var_pop(), and var_samp(). stddev_samp() and var_samp() are identical to the existing stddev() and variance() aggregates, so I've made the latter aliases for the former. I noticed that SQL2003 does not allow DISTINCT to be specified for these aggregate functions. I can't really see the rationale for this restriction, and it would be fairly ugly to implement as far as I can tell. Thoughts? -Neil
Attachment
On Tue, Mar 07, 2006 at 05:54:00PM -0500, Neil Conway wrote:
> This patch implements some new aggregate functions defined by SQL2003:
> stddev_pop(), stddev_samp(), var_pop(), and var_samp(). stddev_samp()
> and var_samp() are identical to the existing stddev() and variance()
> aggregates, so I've made the latter aliases for the former.
>
> I noticed that SQL2003 does not allow DISTINCT to be specified for these
> aggregate functions. I can't really see the rationale for this
> restriction, and it would be fairly ugly to implement as far as I can
> tell. Thoughts?
>
The rationale is kinda mathematical. A measure of deviation from
central tendency (i.e. variance or stddev) is something where you
probably don't want to normalize the weights.
For example, the standard deviation of {0,1,1,1,2} is about 0.707, but
the standard deviation of {0,1,2} is 1.
Cheers,
D (still hoping for some way to extend stddev, etc. to intervals)
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
On Tue, 2006-03-07 at 16:36 -0800, David Fetter wrote:
> The rationale is kinda mathematical. A measure of deviation from
> central tendency (i.e. variance or stddev) is something where you
> probably don't want to normalize the weights.
>
> For example, the standard deviation of {0,1,1,1,2} is about 0.707, but
> the standard deviation of {0,1,2} is 1.
Well, I realize that stddev(DISTINCT x) != stddev(x) and that most
people are going to be interested in stddev(x), but I don't think it's
inconceivable for someone to be interested in stddev(DISTINCT x).
Explicitly checking for and rejecting it doesn't serve any useful
purpose that I can see, beyond compliance with the letter of the
standard -- if the user asks for stddev(DISTINCT x), are we really
providing useful behavior if we refuse to calculate it?
-Neil
On Tue, Mar 07, 2006 at 07:56:06PM -0500, Neil Conway wrote:
> On Tue, 2006-03-07 at 16:36 -0800, David Fetter wrote:
> > The rationale is kinda mathematical. A measure of deviation from
> > central tendency (i.e. variance or stddev) is something where you
> > probably don't want to normalize the weights.
> >
> > For example, the standard deviation of {0,1,1,1,2} is about 0.707,
> > but the standard deviation of {0,1,2} is 1.
>
> Well, I realize that stddev(DISTINCT x) != stddev(x) and that most
> people are going to be interested in stddev(x), but I don't think
> it's inconceivable for someone to be interested in stddev(DISTINCT
> x).
Not inconceivable. Just really hard to justify unless you're trying
to fudge a number ;)
> Explicitly checking for and rejecting it doesn't serve any useful
> purpose that I can see, beyond compliance with the letter of the
> standard -- if the user asks for stddev(DISTINCT x), are we really
> providing useful behavior if we refuse to calculate it?
Nope. I was just coming up for a rationale for why the standard
disallows it :)
Cheers,
D
--
David Fetter david@fetter.org http://fetter.org/
phone: +1 415 235 3778
Remember to vote!
Neil Conway <neilc@samurai.com> writes:
> Well, I realize that stddev(DISTINCT x) != stddev(x) and that most
> people are going to be interested in stddev(x), but I don't think it's
> inconceivable for someone to be interested in stddev(DISTINCT x).
> Explicitly checking for and rejecting it doesn't serve any useful
> purpose that I can see, beyond compliance with the letter of the
> standard -- if the user asks for stddev(DISTINCT x), are we really
> providing useful behavior if we refuse to calculate it?
Agreed, refusing this is not something we should waste code on.
regards, tom lane
On Tue, 2006-03-07 at 17:54 -0500, Neil Conway wrote: > This patch implements some new aggregate functions defined by SQL2003: > stddev_pop(), stddev_samp(), var_pop(), and var_samp(). Applied. -Neil