Re: Window functions and relational algebra

Robert James wrote
> I'm just discovering the power of window functions. Is there any
> mathematical formalism for them, similar to relational algebra?
>
> It would seem to me that window functions aren't expressable in pure
> relational algebra, but that a well defined extension to it would be
> possible to express them; at least for those of us so inclinded, being
> able to think in terms of an alegbra would make reasoning about them
> much more powerful.

The underlying relation the widow is placed over is fully defined from the
relational algebra in surrounding query; but given that it can only see a
simple relation, and cannot itself perform union/except/intersect nor joins
and restrictions (i.e., it cannot remove rows for the final output), I'm not
sure how relational algebra would even be a valid concept in this context.

The idea of window functions is that you take the an already existing source
relation and simply add columns/attributes whose underlying formulas are
able to see any or all of the existing rows in the source relation. I guess
you can call this simple projection onto the underlying relation but being
limited to "add only projection" makes calling that operation relational a
stretch.

I find this line of reasoning quite simple and elegant and do not see what
trying to extend relational algebra would provide; but my tendencies in this
area are toward the practical and away from the underlying theory and
foundational math. SQL is not pure relational and so when reasoning about
SQL it is necessary to incorporate different frameworks than just relational
algebra into your thinking. In this case the relational aspects are
initially processed then special functional projections are applied to
construct a final relation. That relation can then be algebraically joined
to other relations or returned to the caller.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Window-functions-and-relational-algebra-tp5775942p5775957.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.