On 14 Mar 2005, Greg Stark wrote:
> > SELECT ROW_NUMBER() OVER bar AS num,
> > x,
> > avg(a) OVER bar,
> > sum (a) OVER bar
> > FROM foo
> > WINDOW bar AS PARTITION BY x ORDER BY x, y, z;
>
> Note that as you said, this returns just as many records as are in the
> original table. The OLAP functions here are just regular functions, not
> aggregate functions
They are aggregate functions, the avg() is a window aggregate function
according to the standard. It runs over all values in the same partition.
> -- albeit functions that use data from other records other
> than the one being output.
Yes, and not just one other record, but a number of them. Isn't that what
aggregate functions are?
Anyway, I just wanted to point to this area in the standard to tell others
what are possible using standard constructs. It doesn't really help
anything in this specific case. Pg will not have any of this implemented
in the nearest future (I guess). There is always a chance that someone see
the mail, get interested, learn about it and then implements it :-)
--
/Dennis Björklund