From 52a095ba6675f5f5807a1dc655b4ae32b9999f27 Mon Sep 17 00:00:00 2001
From: =?utf8?q?Ilja=20Everil=C3=A4?= <saarni@gmail.com>
Date: Thu, 11 Sep 2014 15:39:56 +0300
Subject: [PATCH] allow windowing filtered functions

---
 lib/sqlalchemy/sql/elements.py | 20 ++++++++++++++++++++
 test/sql/test_compiler.py      | 27 +++++++++++++++++++++++++++
 2 files changed, 47 insertions(+)

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 5ac16ab7ad..62fe6553a3 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2931,6 +2931,26 @@ class FunctionFilter(ColumnElement):
 
         return self
 
+    def over(self, partition_by=None, order_by=None):
+        """Produce an OVER clause against this filtered function.
+
+        Used against aggregate or so-called "window" functions,
+        for database backends that support window functions.
+
+        The expression::
+
+            func.rank().filter(MyClass.y > 5).over(order_by='x')
+
+        is shorthand for::
+
+            from sqlalchemy import over, funcfilter
+            over(funcfilter(func.rank(), MyClass.y > 5), order_by='x')
+
+        See :func:`~.expression.over` for a full description.
+
+        """
+        return Over(self, partition_by=partition_by, order_by=order_by)
+
     @util.memoized_property
     def type(self):
         return self.func.type
diff --git a/test/sql/test_compiler.py b/test/sql/test_compiler.py
index 7bba295634..fc33db184c 100644
--- a/test/sql/test_compiler.py
+++ b/test/sql/test_compiler.py
@@ -2254,6 +2254,33 @@ class SelectTest(fixtures.TestBase, AssertsCompiledSQL):
             "AS anon_1 FROM mytable"
         )
 
+        # test filtered windowing:
+        self.assert_compile(
+            select([
+                func.rank().filter(
+                    table1.c.name > 'foo'
+                ).over(
+                    order_by=table1.c.name
+                )
+            ]),
+            "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
+            "OVER (ORDER BY mytable.name) AS anon_1 FROM mytable"
+        )
+
+        self.assert_compile(
+            select([
+                func.rank().filter(
+                    table1.c.name > 'foo'
+                ).over(
+                    order_by=table1.c.name,
+                    partition_by=['description']
+                )
+            ]),
+            "SELECT rank() FILTER (WHERE mytable.name > :name_1) "
+            "OVER (PARTITION BY mytable.description ORDER BY mytable.name) "
+            "AS anon_1 FROM mytable"
+        )
+
     def test_date_between(self):
         import datetime
         table = Table('dt', metadata,
-- 
2.47.3