Skip site navigation (1) Skip section navigation (2)

Re: variance aggregates per SQL:2003

From: David Fetter <david(at)fetter(dot)org>
To: Neil Conway <neilc(at)samurai(dot)com>
Cc: pgsql-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: variance aggregates per SQL:2003
Date: 2006-03-08 00:36:04
Message-ID: 20060308003604.GA12612@fetter.org (view raw or flat)
Thread:
Lists: pgsql-patches
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(at)fetter(dot)org http://fetter.org/
phone: +1 415 235 3778

Remember to vote!

In response to

Responses

pgsql-patches by date

Next:From: Neil ConwayDate: 2006-03-08 00:56:06
Subject: Re: variance aggregates per SQL:2003
Previous:From: Alvaro HerreraDate: 2006-03-07 23:49:24
Subject: Re: pg_freespacemap question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group