Re: Calculating a moving average

"Jim C. Nasby" <decibel(at)decibel(dot)org> writes:

> If you're feeling adventurous, you might look at Oracle's documentation
> on their analytic functions and see if you can come up with something
> generic for PostgreSQL.

I think the hard part of doing even a simple implementation is precisely the
point I raised about doing it in Perl or Python. Somehow you have to allocate
a static storage area specific to the call site. It's sort of like an
aggregate function call except of course that you're going to return a datum
for every record.

For a fuller implementation there are a lot more details. If I understand
correctly in Oracle you get to specify an ORDER BY clause and the equivalent
of a GROUP BY clause in the analytic function call. I think each call site can
even have its own order and grouping.

> Even if you only do a moving average function it would be a good start.

Actually my pet one would be a "rank" function. So you could do something like
"return the top 3 scoring players from each team". Currently the suggested way
to do it is by using an aggregate function to gather up the data in an array.

--
greg