1. lecstor
  2. sqlalchemy

Commits

Mike Bayer  committed 2b8c3a6

- Added over() function, method to FunctionElement
classes, produces the _Over() construct which
in turn generates "window functions", i.e.
"<window function> OVER (PARTITION BY <partition by>,
ORDER BY <order by>)".
[ticket:1844]

  • Participants
  • Parent commits 67f1779
  • Branches default

Comments (0)

Files changed (12)

File CHANGES

View file
  • Ignore whitespace
     as DISTINCT ON (<expr>). [ticket:1069]
 
 - sql
+  - Added over() function, method to FunctionElement
+    classes, produces the _Over() construct which 
+    in turn generates "window functions", i.e.
+    "<window function> OVER (PARTITION BY <partition by>,
+    ORDER BY <order by>)".
+    [ticket:1844]
+
   - LIMIT/OFFSET clauses now use bind parameters
     [ticket:805]
 

File doc/build/core/expression_api.rst

View file
  • Ignore whitespace
 
 .. autofunction:: outerjoin
 
+.. autofunction:: over
+
 .. autofunction:: select
 
 .. autofunction:: subquery
    :members:
    :show-inheritance:
 
+.. autoclass:: ClauseList
+   :members:
+   :show-inheritance:
+
 .. autoclass:: ColumnClause
    :members:
    :show-inheritance:

File doc/build/core/tutorial.rst

View file
  • Ignore whitespace
 Operators
 ==========
 
-
 Since we've stumbled upon SQLAlchemy's operator paradigm, let's go through some of its capabilities.  We've seen how to equate two columns to each other:
 
 .. sourcecode:: pycon+sql
 Functions
 ---------
 
-
-SQL functions are created using the ``func`` keyword, which generates functions using attribute access:
+SQL functions are created using the :attr:`~.expression.func` keyword, which generates functions using attribute access:
 
 .. sourcecode:: pycon+sql
 
 
 See also :attr:`sqlalchemy.sql.expression.func`.
 
+Window Functions
+-----------------
+
+Any :class:`.FunctionElement`, including functions generated by :attr:`~.expression.func`, can be turned into a "window function", that is
+an OVER clause, using the :meth:`~.FunctionElement.over` method:
+
+.. sourcecode:: pycon+sql
+
+    >>> s = select([users.c.id, func.row_number().over(order_by=users.c.name)])
+    >>> print s # doctest: +NORMALIZE_WHITESPACE
+    SELECT users.id, row_number() OVER (ORDER BY users.name) AS anon_1 
+    FROM users
+
 Unions and Other Set Operations
 -------------------------------
 

File lib/sqlalchemy/__init__.py

View file
  • Ignore whitespace
     or_,
     outerjoin,
     outparam,
+    over,
     select,
     subquery,
     text,

File lib/sqlalchemy/engine/base.py

View file
  • Ignore whitespace
         raise NotImplementedError()
 
     def execute(self, object, *multiparams, **params):
+        """Executes the given construct and returns a :class:`.ResultProxy`."""
+        raise NotImplementedError()
+
+    def scalar(self, object, *multiparams, **params):
+        """Executes and returns the first column of the first row.
+
+        The underlying cursor is closed after execution.
+        """
         raise NotImplementedError()
 
     def _execute_clauseelement(self, elem, multiparams=None, params=None):

File lib/sqlalchemy/sql/__init__.py

View file
  • Ignore whitespace
     or_,
     outerjoin,
     outparam,
+    over,
     select,
     subquery,
     table,

File lib/sqlalchemy/sql/compiler.py

View file
  • Ignore whitespace
         if clause.value is not None:
             x += clause.value._compiler_dispatch(self, **kwargs) + " "
         for cond, result in clause.whens:
-            x += "WHEN " + cond._compiler_dispatch(self, **kwargs) + \
-                            " THEN " + \
-                            result._compiler_dispatch(self, **kwargs) + " "
+            x += "WHEN " + cond._compiler_dispatch(
+                            self, **kwargs
+                            ) + " THEN " + result._compiler_dispatch(
+                                            self, **kwargs) + " "
         if clause.else_ is not None:
-            x += "ELSE " + clause.else_._compiler_dispatch(self, **kwargs) + \
-                                        " "
+            x += "ELSE " + clause.else_._compiler_dispatch(
+                                self, **kwargs
+                            ) + " "
         x += "END"
         return x
 
                     (cast.clause._compiler_dispatch(self, **kwargs),
                     cast.typeclause._compiler_dispatch(self, **kwargs))
 
+    def visit_over(self, over, **kwargs):
+        x ="%s OVER (" % over.func._compiler_dispatch(self, **kwargs)
+        if over.partition_by is not None:
+            x += "PARTITION BY %s" % \
+                over.partition_by._compiler_dispatch(self, **kwargs)
+            if over.order_by is not None:
+                x += ", "
+        if over.order_by is not None:
+            x += "ORDER BY %s" % \
+                over.order_by._compiler_dispatch(self, **kwargs)
+        x += ")"
+        return x
+
     def visit_extract(self, extract, **kwargs):
         field = self.extract_map.get(extract.field, extract.field)
         return "EXTRACT(%s FROM %s)" % (field, 

File lib/sqlalchemy/sql/expression.py

View file
  • Ignore whitespace
     'except_', 'except_all', 'exists', 'extract', 'func', 'modifier',
     'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label',
     'literal', 'literal_column', 'not_', 'null', 'nullsfirst', 'nullslast',
-    'or_', 'outparam', 'outerjoin', 'select', 'subquery', 'table', 'text',
+    'or_', 'outparam', 'outerjoin', 'over', 'select', 'subquery', 'table', 'text',
     'tuple_', 'type_coerce', 'union', 'union_all', 'update', ]
 
 PARSE_AUTOCOMMIT = util.symbol('PARSE_AUTOCOMMIT')
     """
     return _TextClause(text, bind=bind, *args, **kwargs)
 
+def over(func, partition_by=None, order_by=None):
+    """Produce an OVER clause against a function.
+    
+    Used against aggregate or so-called "window" functions,
+    for database backends that support window functions.
+    
+    E.g.::
+    
+        from sqlalchemy import over
+        over(func.row_number(), order_by='x')
+        
+    Would produce "ROW_NUMBER() OVER(ORDER BY x)".
+
+    :param func: a :class:`.FunctionElement` construct, typically
+     generated by :attr:`~.expression.func`.
+    :param partition_by: a column element or string, or a list
+     of such, that will be used as the PARTITION BY clause
+     of the OVER construct.
+    :param order_by: a column element or string, or a list
+     of such, that will be used as the ORDER BY clause
+     of the OVER construct.
+     
+    This function is also available from the :attr:`~.expression.func`
+    construct itself via the :meth:`.FunctionElement.over` method.
+    
+    New in 0.7.
+
+    """
+    return _Over(func, partition_by=partition_by, order_by=order_by)
+
 def null():
     """Return a :class:`_Null` object, which compiles to ``NULL`` in a sql
     statement.
         >>> print func.count(1)
         count(:param_1)
 
-   Any name can be given to `func`. If the function name is unknown to
+   Any name can be given to ``func``. If the function name is unknown to
    SQLAlchemy, it will be rendered exactly as is. For common SQL functions
    which SQLAlchemy is aware of, the name may be interpreted as a *generic
    function* which will be compiled appropriately to the target database::
 
         >>> print func.stats.yield_curve(5, 10)
         stats.yield_curve(:yield_curve_1, :yield_curve_2)
-
+    
    SQLAlchemy can be made aware of the return type of functions to enable
    type-specific lexical and result-based behavior. For example, to ensure
    that a string-based function returns a Unicode value and is similarly
         ... func.my_string(u'there', type_=Unicode)
         my_string(:my_string_1) || :my_string_2 || my_string(:my_string_3)
 
+   The object returned by a ``func`` call is an instance of :class:`.Function`.
+   This object meets the "column" interface, including comparison and labeling
+   functions.  The object can also be passed the :meth:`~.Connectable.execute`
+   method of a :class:`.Connection` or :class:`.Engine`, where it will be
+   wrapped inside of a SELECT statement first.
+
    Functions which are interpreted as "generic" functions know how to
    calculate their return type automatically. For a listing of known generic
    functions, see :ref:`generic_functions`.
     """Base for SQL function-oriented constructs."""
 
     def __init__(self, *clauses, **kwargs):
+        """Construct a :class:`.FunctionElement`.
+        """
         args = [_literal_as_binds(c, self.name) for c in clauses]
         self.clause_expr = ClauseList(
                                 operator=operators.comma_op,
 
     @property
     def columns(self):
+        """Fulfill the 'columns' contrct of :class:`.ColumnElement`.
+        
+        Returns a single-element list consisting of this object.
+        
+        """
         return [self]
 
     @util.memoized_property
     def clauses(self):
+        """Return the underlying :class:`.ClauseList` which contains
+        the arguments for this :class:`.FunctionElement`.
+        
+        """
         return self.clause_expr.element
 
+    def over(self, partition_by=None, order_by=None):
+        """Produce an OVER clause against this function.
+        
+        Used against aggregate or so-called "window" functions,
+        for database backends that support window functions.
+        
+        The expression::
+        
+            func.row_number().over(order_by='x')
+            
+        is shorthand for::
+        
+            from sqlalchemy import over
+            over(func.row_number(), order_by='x')
+
+        See :func:`~.expression.over` for a full description.
+        
+        New in 0.7.
+        
+        """
+        return over(self, partition_by=partition_by, order_by=order_by)
+
     @property
     def _from_objects(self):
         return self.clauses._from_objects
         util.reset_memoized(self, 'clauses')
 
     def select(self):
+        """Produce a :func:`~.expression.select` construct 
+        against this :class:`FunctionElement`.
+        
+        This is shorthand for::
+        
+            s = select([function_element])
+            
+        """
         s = select([self])
         if self._execution_options:
             s = s.execution_options(**self._execution_options)
         return s
 
     def scalar(self):
+        """Execute this :class:`.FunctionElement` against an embedded
+        'bind' and return a scalar value.
+        
+        This first calls :meth:`~.FunctionElement.select` to 
+        produce a SELECT construct.
+        
+        Note that :class:`.FunctionElement` can be passed to 
+        the :meth:`.Connectable.scalar` method of :class:`.Connection`
+        or :class:`.Engine`.
+        
+        """
         return self.select().execute().scalar()
 
     def execute(self):
+        """Execute this :class:`.FunctionElement` against an embedded
+        'bind'.
+        
+        This first calls :meth:`~.FunctionElement.select` to 
+        produce a SELECT construct.
+        
+        Note that :class:`.FunctionElement` can be passed to 
+        the :meth:`.Connectable.execute` method of :class:`.Connection`
+        or :class:`.Engine`.
+        
+        """
         return self.select().execute()
 
     def _bind_param(self, operator, obj):
 
 
 class Function(FunctionElement):
-    """Describe a named SQL function."""
+    """Describe a named SQL function.
+    
+    See the superclass :class:`.FunctionElement` for a description
+    of public methods.
+    
+    """
 
     __visit_name__ = 'function'
 
     def __init__(self, name, *clauses, **kw):
+        """Construct a :class:`.Function`.
+        
+        The :attr:`.func` construct is normally used to construct 
+        new :class:`.Function` instances.
+        
+        """
         self.packagenames = kw.pop('packagenames', None) or []
         self.name = name
         self._bind = kw.get('bind', None)
     def __setstate__(self, state):
         self.element = state['element']
 
+class _Over(ColumnElement):
+    """Represent an OVER clause.
+    
+    This is a special operator against a so-called 
+    "window" function, as well as any aggregate function,
+    which produces results relative to the result set
+    itself.  It's supported only by certain database
+    backends.
+    
+    """
+    __visit_name__ = 'over'
+
+    order_by = None
+    partition_by = None
+
+    def __init__(self, func, partition_by=None, order_by=None):
+        self.func = func
+        if order_by is not None:
+            self.order_by = ClauseList(*util.to_list(order_by))
+        if partition_by is not None:
+            self.partition_by = ClauseList(*util.to_list(partition_by))
+
+    @util.memoized_property
+    def type(self):
+        return self.func.type
+
+    def get_children(self, **kwargs):
+        return [c for c in 
+                (self.func, self.partition_by, self.order_by) 
+                if c is not None]
+
+    def _copy_internals(self, clone=_clone):
+        self.func = clone(self.func)
+        if self.partition_by is not None:
+            self.partition_by = clone(self.partition_by)
+        if self.order_by is not None:
+            self.order_by = clone(self.order_by)
+
+    @property
+    def _from_objects(self):
+        return itertools.chain(
+            *[c._from_objects for c in 
+                (self.func, self.partition_by, self.order_by) 
+            if c is not None]
+        )
+
 class _Label(ColumnElement):
     """Represents a column label (AS).
 

File test/lib/requires.py

View file
  • Ignore whitespace
         fails_on('sybase', 'no support for OFFSET or equivalent'),
     )
 
+def window_functions(fn):
+    return _chain_decorators_on(
+        fn,
+        only_on(('postgresql', 'mssql', 'oracle'), 
+                "Backend does not support window functions"),
+    )
+
 def returning(fn):
     return _chain_decorators_on(
         fn,

File test/sql/test_compiler.py

View file
  • Ignore whitespace
                             'CAST(NULL AS INTEGER)',
                             dialect=sqlite.dialect())
 
+    def test_over(self):
+        self.assert_compile(
+            func.row_number().over(
+                order_by=[table1.c.name, table1.c.description]
+            ),
+            "row_number() OVER (ORDER BY mytable.name, mytable.description)"
+        )
+        self.assert_compile(
+            func.row_number().over(
+                partition_by=[table1.c.name, table1.c.description]
+            ),
+            "row_number() OVER (PARTITION BY mytable.name, "
+            "mytable.description)"
+        )
+        self.assert_compile(
+            func.row_number().over(
+                partition_by=[table1.c.name],
+                order_by=[table1.c.description]
+            ),
+            "row_number() OVER (PARTITION BY mytable.name, "
+            "ORDER BY mytable.description)"
+        )
+        self.assert_compile(
+            func.row_number().over(
+                partition_by=table1.c.name,
+                order_by=table1.c.description
+            ),
+            "row_number() OVER (PARTITION BY mytable.name, "
+            "ORDER BY mytable.description)"
+        )
+
+        self.assert_compile(
+            select([func.row_number().over(
+                order_by=table1.c.description
+            ).label('foo')]),
+            "SELECT row_number() OVER (ORDER BY mytable.description) "
+            "AS foo FROM mytable"
+        )
+
+        # test from_obj generation.
+        # from func:
+        self.assert_compile(
+            select([
+                func.max(table1.c.name).over(
+                    partition_by=['foo']
+                )
+            ]),
+            "SELECT max(mytable.name) OVER (PARTITION BY foo) "
+            "AS anon_1 FROM mytable"
+        )
+        # from partition_by
+        self.assert_compile(
+            select([
+                func.row_number().over(
+                    partition_by=[table1.c.name]
+                )
+            ]),
+            "SELECT row_number() OVER (PARTITION BY mytable.name) "
+            "AS anon_1 FROM mytable"
+        )
+        # from order_by
+        self.assert_compile(
+            select([
+                func.row_number().over(
+                    order_by=table1.c.name
+                )
+            ]),
+            "SELECT row_number() OVER (ORDER BY mytable.name) "
+            "AS anon_1 FROM mytable"
+        )
+
+
+
     def test_date_between(self):
         import datetime
         table = Table('dt', metadata,

File test/sql/test_generative.py

View file
  • Ignore whitespace
         expr2 = CloningVisitor().traverse(expr)
         assert str(expr) == str(expr2)
 
+    def test_over(self):
+        expr = func.row_number().over(order_by=t1.c.col1)
+        expr2 = CloningVisitor().traverse(expr)
+        assert str(expr) == str(expr2)
+
     def test_adapt_union(self):
         u = union(t1.select().where(t1.c.col1==4), t1.select().where(t1.c.col1==5)).alias()
 

File test/sql/test_query.py

View file
  • Ignore whitespace
             [(2,),(1,)]
         )
 
+    @testing.requires.window_functions
+    def test_over(self):
+        eq_(
+            select([
+                flds.c.intcol, func.row_number().over(order_by=flds.c.strcol)
+            ]).execute().fetchall(),
+            [(13, 1L), (5, 2L)]
+        )
 
 
+