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@xxxxxxxxxx> 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@xxxxxxxxxx> 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
