support for postgresql windowing functions

[Former user]

as per: http://groups.google.com/group/sqlalchemy/browse_thread/thread/21f3ad6d03058deb/bb4b90043159eb01?lnk=gst&q=postgresql+window+function#bb4b90043159eb01 ,

PG8.4 ref: http://www.postgresql.org/docs/8.4/static/tutorial-window.html

It would be nice to have support for window functions in the expression language.

Thanks!

[e-mail]

[Former user]

This seems like part of the way there:

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql.expression import Executable, ClauseElement, ClauseList, _generative
from sqlalchemy import util


class Over(Executable, ClauseList):
    def __init__(self, *column_or_columns):
        ClauseList.__init__(self,*util.to_list(column_or_columns) or [       self.over_clause = ClauseList(*util.to_list(column_or_columns) or [](])
))

@compiles(Over)
def visit_over(grouping, compiler, asfrom=False, **kwargs):
    return "OVER (PARTITION BY " + compiler.process(grouping.over_clause, **kwargs) + ")"

Gregg L.

[Former user]

From where I'm looking at things... it looks like it's very hard to add new methods onto select and friends... I'm wondering if there could be a decorator, similar to '_generative' that would note that a function can be 'chained'.... thus select([some_column_set](some_column_set)).over(some_col1) would have some parts like this:

@chainable
class Over(Executable, ClauseList):

and in select and friends:

def getattr(self,attr):
    if attr in global_chainables_registry:
         return ...

I realize I'm being a bit vague, and this might warrant a larger discussion!

Gregg

[Mike Bayer]

More links regarding this:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/c20a4f380e277af/fb179a515bf48868

http://groups.google.com/group/sqlalchemy/browse_thread/thread/4f7b243c41eb183f/fb79736d4c435537

[Mike Bayer]

sorry, those are WITH queries, not the same thing as windowing functions. windowing functions are not as tough to implement.

[Mike Bayer]

and WITH is over in #1859

[Mike Bayer]

also I just had to use a window the other day, its pretty easy in the usual case, from_self() makes the usual "subquery from self" a snap:

for x, in Session.query(SomeClass.x, literal_column("ROW_NUMBER OVER (ORDER BY foo.bar) AS rownum")).\
                from_self(SomeClass.x).\
                filter("rownum %% %d=1" % modulus)

[Mike Bayer]

putting this on 0.7.0 for the moment, will force me to look at it, see if I can do it with a clause element. I am using window functions right now across PG / SQL Server. Perhaps supporting just the OVER (expression) version to start, with WINDOW coming in later.

select([table.c.b.over(order_by=table.c.c)](table.c.a,))
select([table.c.b.over(partition_by=table.c.c)](table.c.a,))

w = window(partition_by=table.c.a, order_by=table.c.b.desc())
select([table.c.b.over(w)](table.c.a,))

[Mike Bayer]

that syntax above is a little misguided. OVER is always against a window or aggregate function. So it looks like this:

select([func.rank().over(partition_by=table.c.b)](table.c.a,))

020d6ef8f017d4411b403c18d93f913d6b01fd62

[Mike Bayer]

Removing milestone: 0.7.0 (automated comment)