First of all, stddev doesn't return square of smth - so, why should we
worry about intermediate results? Furthermore, statistics work with
any 'units' and doesn't worry about physical meaning of variance in
any case (for example, what about variance for the set of lifetime
values of people from town N in XX century? ;-) ).
Second, SQL standard doesn't contain definition for STDDEV function,
but it has STDDEV_POP and STDDEV_SAMP (but it doesn't really matter
for this discussion). As for valid datatypes for these functions, I
cannot find exact definition unfortunately, but I see folliwing:
'Without Feature T621, "Enhanced numeric functions", conforming SQL
language shall not contain a
<computational operation> that immediately contains STDDEV_POP,
STDDEV_SAMP, VAR_POP, or
VAR_SAMP.'
So, authors meant that these functions should accept only numeric values.
Last but not least, ORA doesn't want to accept interval values for
stddev() func:
CREATE TABLE teststddev(id INTEGER PRIMARY KEY, val INTERVAL YEAR TO MONTH);
INSERT INTO teststddev(id, val) VALUES(1, INTERVAL '300' MONTH(3));
INSERT INTO teststddev(id, val) VALUES(2, INTERVAL '2' YEAR(1));
INSERT INTO teststddev(id, val) VALUES(3, INTERVAL '-125' MONTH(3));
SELECT STDDEV(val) FROM teststddev;
ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL YEAR TO MONTH
As for me, I think that it's quite reasonable to expect stddev working
with intervals... Why not?
On 5/21/06, David Fetter <david@fetter.org> wrote:
> On Sun, May 21, 2006 at 01:14:15AM +1000, Brendan Jurd wrote:
> > Hi all,
> >
> > I noticed a peculiarity in the default postgres aggregate functions. min(),
> > max() and avg() support interval as an input type, but stddev() and
> > variance() do not.
> >
> > Is there a rationale behind this, or is it just something that was never
> > implemented?
>
> That's because variance of foo is measured in foo^2 units. What is
> the square of an interval?
>
> Cheers,
> D
> --
> David Fetter <david@fetter.org> http://fetter.org/
> phone: +1 415 235 3778 AIM: dfetter666
> Skype: davidfetter
>
> Remember to vote!
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>
--
Best regards,
Nikolay