Add window frame specificationpr/264

3 files changed, 64 insertions, 8 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 3d2f02006..33de2424b 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -812,12 +812,23 @@ class SQLCompiler(Compiled):
         return "%s OVER (%s)" % (
             over.element._compiler_dispatch(self, **kwargs),
             ' '.join(
-                '%s BY %s' % (word, clause._compiler_dispatch(self, **kwargs))
-                for word, clause in (
-                    ('PARTITION', over.partition_by),
-                    ('ORDER', over.order_by)
+                itertools.chain(
+                    (
+                        '%s BY %s' % (
+                            word, clause._compiler_dispatch(self, **kwargs)
+                        )
+                        for word, clause in (
+                            ('PARTITION', over.partition_by),
+                            ('ORDER', over.order_by)
+                        )
+                        if clause is not None and len(clause)
+                    ),
+                    [
+                        '%s BETWEEN %s AND %s' % (
+                            over.frame_kind, over.preceding, over.following
+                        )
+                    ]
                 )
-                if clause is not None and len(clause)
             )
         )
 
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 00c2c37ba..52f46f295 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -3071,7 +3071,8 @@ class Over(ColumnElement):
     order_by = None
     partition_by = None
 
-    def __init__(self, element, partition_by=None, order_by=None):
+    def __init__(
+        self, element, partition_by=None, order_by=None, range=None, rows=None):
         """Produce an :class:`.Over` object against a function.
 
         Used against aggregate or so-called "window" functions,
@@ -3115,6 +3116,44 @@ class Over(ColumnElement):
                 *util.to_list(partition_by),
                 _literal_as_text=_literal_as_label_reference)
 
+        if range is not None and rows is not None:
+            raise ValueError(
+                'Must provide either RANGE specification or '
+                'ROWS specification, not both'
+            )
+
+        if range is None and rows is None:
+            range = {}
+
+        self.frame_kind = 'ROWS' if rows is not None else 'RANGE'
+        frame = rows if rows is not None else range
+
+        assert frame is not None
+
+        if frozenset(frame) - frozenset(('preceding', 'following')):
+            raise ValueError(
+                'Found additional keys in frame specification dict'
+            )
+        self.preceding = Over._format_frame_clause(
+            frame, 'preceding', 'UNBOUNDED PRECEDING'
+        )
+        self.following = Over._format_frame_clause(
+            frame, 'following', 'CURRENT ROW'
+        )
+
+    @staticmethod
+    def _format_frame_clause(frame, key, default):
+        value = frame.get(key, default)
+
+        if value == default:
+            return default
+        elif value is None:
+            return 'UNBOUNDED %s' % key.upper()
+        elif value == 0:
+            return 'CURRENT ROW'
+        else:
+            return '%d %s' % (value, key.upper())
+
     @property
     def func(self):
         """the element referred to by this :class:`.Over`
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index e369f5a61..a140544f0 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -94,7 +94,7 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         """
         return self.clause_expr.element
 
-    def over(self, partition_by=None, order_by=None):
+    def over(self, partition_by=None, order_by=None, rows=None, range=None):
         """Produce an OVER clause against this function.
 
         Used against aggregate or so-called "window" functions,
@@ -114,7 +114,13 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         .. versionadded:: 0.7
 
         """
-        return Over(self, partition_by=partition_by, order_by=order_by)
+        return Over(
+            self,
+            partition_by=partition_by,
+            order_by=order_by,
+            rows=rows,
+            range=range
+        )
 
     def within_group(self, *order_by):
         """Produce a WITHIN GROUP (ORDER BY expr) clause against this function.