Support range specification in window function

Fixes: #3049
Co-authored-by: Mike Bayer <mike_mp@zzzcomputing.com>
Change-Id: Ie572095c3e25f70a1e72e1af6858e5edd89fd25e
Pull-request: https://github.com/zzzeek/sqlalchemy/pull/264

1 files changed, 27 insertions, 2 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 144f2aa47..6d9ab9039 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -810,16 +810,41 @@ class SQLCompiler(Compiled):
             (cast.clause._compiler_dispatch(self, **kwargs),
              cast.typeclause._compiler_dispatch(self, **kwargs))
 
+    def _format_frame_clause(self, range_, **kw):
+        return '%s AND %s' % (
+            "UNBOUNDED PRECEDING"
+            if range_[0] is elements.RANGE_UNBOUNDED
+            else "CURRENT ROW" if range_[0] is elements.RANGE_CURRENT
+            else "%s PRECEDING" % (self.process(range_[0], **kw), ),
+
+            "UNBOUNDED FOLLOWING"
+            if range_[1] is elements.RANGE_UNBOUNDED
+            else "CURRENT ROW" if range_[1] is elements.RANGE_CURRENT
+            else "%s FOLLOWING" % (self.process(range_[1], **kw), )
+        )
+
     def visit_over(self, over, **kwargs):
+        if over.range_:
+            range_ = "RANGE BETWEEN %s" % self._format_frame_clause(
+                over.range_, **kwargs)
+        elif over.rows:
+            range_ = "ROWS BETWEEN %s" % self._format_frame_clause(
+                over.rows, **kwargs)
+        else:
+            range_ = None
+
         return "%s OVER (%s)" % (
             over.element._compiler_dispatch(self, **kwargs),
-            ' '.join(
-                '%s BY %s' % (word, clause._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)
                 )
                 if clause is not None and len(clause)
+            ] + ([range_] if range_ else [])
             )
         )