Repository: kayak/pypika
Pull request #452 (2020-06-23 19:29:46)

Added FILTER statement to aggregate functions

coveralls: 
[![Coverage Status](https://coveralls.io/builds/31641245/badge)](https://coveralls.io/builds/31641245)

Coverage remained the same at ?% when pulling **c3fc3f8512d2e816bc0eb1a54754bf445025df78 on nibrag:agg-func-filter** into **f8799fd9f2e8a61cb059a5a123c44e1174963a84 on kayak:master**.

gl3nn: Can be merged if the conflicts are resolved.

Patch:
diff --git a/pypika/terms.py b/pypika/terms.py
index 2cfb8e1..14d2e5b 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -1183,8 +1183,33 @@ class Function(Criterion):
 class AggregateFunction(Function):
     is_aggregate = True
 
+    def __init__(self, name, *args, **kwargs):
+        super(AggregateFunction, self).__init__(name, *args, **kwargs)
 
-class AnalyticFunction(Function):
+        self._filters = []
+        self._include_filter = False
+
+    @builder
+    def filter(self, *filters: Any) -> "AnalyticFunction":
+        self._include_filter = True
+        self._filters += filters
+
+    def get_filter_sql(self, **kwargs: Any) -> str:
+        if self._include_filter:
+            return "WHERE {criterions}".format(criterions=Criterion.all(self._filters).get_sql(**kwargs))
+
+    def get_function_sql(self, **kwargs: Any):
+        sql = super(AggregateFunction, self).get_function_sql(**kwargs)
+        filter_sql = self.get_filter_sql(**kwargs)
+
+        if self._include_filter:
+            sql += " FILTER({filter_sql})".format(filter_sql=filter_sql)
+
+        return sql
+
+
+class AnalyticFunction(AggregateFunction):
+    is_aggregate = False
     is_analytic = True
 
     def __init__(self, name: str, *args: Any, **kwargs: Any) -> None:
@@ -1195,11 +1220,6 @@ class AnalyticFunction(Function):
         self._include_filter = False
         self._include_over = False
 
-    @builder
-    def filter(self, *filters: Any) -> "AnalyticFunction":
-        self._include_filter = True
-        self._filters += filters
-
     @builder
     def over(self, *terms: Any) -> "AnalyticFunction":
         self._include_over = True
@@ -1219,10 +1239,6 @@ class AnalyticFunction(Function):
             orient=orient.value,
         )
 
-    def get_filter_sql(self) -> str:
-        if self._include_filter:
-            return "WHERE {criterions}".format(criterions=Criterion.all(self._filters).get_sql())
-
     def get_partition_sql(self, **kwargs: Any) -> str:
         terms = []
         if self._partition:
@@ -1242,13 +1258,10 @@ class AnalyticFunction(Function):
         return " ".join(terms)
 
     def get_function_sql(self, **kwargs: Any) -> str:
-        function_sql = super().get_function_sql(**kwargs)
-        filter_sql = self.get_filter_sql()
+        function_sql = super(AnalyticFunction, self).get_function_sql(**kwargs)
         partition_sql = self.get_partition_sql(**kwargs)
 
         sql = function_sql
-        if self._include_filter:
-            sql += " FILTER({filter_sql})".format(filter_sql=filter_sql)
         if self._include_over:
             sql += " OVER({partition_sql})".format(partition_sql=partition_sql)
 


Test patch:
diff --git a/pypika/tests/test_analytic_queries.py b/pypika/tests/test_analytic_queries.py
index ef35d56..6a35ab8 100644
--- a/pypika/tests/test_analytic_queries.py
+++ b/pypika/tests/test_analytic_queries.py
@@ -164,6 +164,31 @@ class RankTests(unittest.TestCase):
             str(q),
         )
 
+    def test_filter_quote_table_in_filter(self):
+        expr = (
+            an.LastValue(self.table_efg.fizz)
+            .filter(self.table_efg.filed.eq('yes'))
+            .over(self.table_efg.foo)
+            .orderby(self.table_efg.date)
+        )
+
+        q = (
+            Query.from_(self.table_abc)
+            .inner_join(self.table_efg)
+            .on(self.table_abc.id.eq(self.table_efg.id))
+            .select(expr)
+        )
+
+        self.assertEqual(
+            'SELECT '
+            'LAST_VALUE("efg"."fizz") '
+            'FILTER(WHERE "efg"."filed"=\'yes\') '
+            'OVER(PARTITION BY "efg"."foo" ORDER BY "efg"."date") '
+            'FROM "abc" '
+            'JOIN "efg" ON "abc"."id"="efg"."id"',
+            str(q),
+        )
+
     def test_orderby_asc(self):
         expr = an.LastValue(self.table_abc.fizz).over(self.table_abc.foo).orderby(self.table_abc.date, order=Order.asc)
 
diff --git a/pypika/tests/test_selects.py b/pypika/tests/test_selects.py
index 6ec10ab..c955199 100644
--- a/pypika/tests/test_selects.py
+++ b/pypika/tests/test_selects.py
@@ -496,6 +496,15 @@ class GroupByTests(unittest.TestCase):
 
         self.assertEqual('SELECT "foo",SUM(DISTINCT "bar") FROM "abc" GROUP BY "foo"', str(q))
 
+    def test_groupby__sum_filter(self):
+        q = (
+            Query.from_(self.t)
+            .groupby(self.t.foo)
+            .select(self.t.foo, fn.Sum(self.t.bar).filter(self.t.id.eq(1) & self.t.cid.gt(2)))
+        )
+
+        self.assertEqual('SELECT "foo",SUM("bar") FILTER(WHERE "id"=1 AND "cid">2) FROM "abc" GROUP BY "foo"', str(q))
+
     def test_groupby__str(self):
         q = Query.from_("abc").groupby("foo").select("foo", fn.Count("*").distinct())