Thread: Window function with valued based range frames?

Window function with valued based range frames?

From
David G Johnston
Date:
ex.  avg(value) over (order by date range interval '6' day preceding)

Source:

http://wwwlgis.informatik.uni-kl.de/cms/fileadmin/courses/SS2008/NEDM/RDDM.Chapter.06.Windows_and_Query_Functions_in_SQL.pdf

Page 9, slide 17

The SELECT SQL command page mentions standard aspects of window frame
clauses not being supported but is not specific (supposedly in order to
having people ask for these things).

Just looking for recollection regarding why these were omitted initially and
if anyone has concerned adding them in follow-up.  With the recent
hypothetical work being done maybe these can be re-evaluated in a fresh
light?  They (whatever they is) are standard and do seem generally useful.

I don't personally have an immediate need but have been pondering moving
average related window queries and how performant they are in PostgreSQL
version possible alternative calculation means and came across this
presentation.

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Window-function-with-valued-based-range-frames-tp5820757.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


Re: Window function with valued based range frames?

From
Tom Lane
Date:
David G Johnston <david.g.johnston@gmail.com> writes:
> Just looking for recollection regarding why these were omitted initially and
> if anyone has concerned adding them in follow-up.

My recollection is that RANGE requires some data-type-specific behavior
that we don't have any provision for in PG's datatype extension framework
(something about increment/decrement I think, but too lazy to consult the
archives for details).  The original window-function patch had some klugy
hard-wired behavior for a small set of datatypes, which we quite properly
rejected as not being up to project standards: datatype extensibility is
one of PG's defining features, and we're not going to give it up lightly.
Nobody's yet done the work to get something that would pass muster.

> With the recent
> hypothetical work being done maybe these can be re-evaluated in a fresh
> light?

AFAIK those functions are unrelated to this problem.

            regards, tom lane