Repository: kayak/pypika (python)

Issue:
Support analytic queries for Vertica and Oracle
https://my.vertica.com/docs/7.1.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/AnalyticFunctions.htm

Add support for analytical functions and queries using PARTITION OVER

Pull request:
Fixes #47 Added support and documentation for analytic functions. 
Added wrappers for NTILE, RANK, FIRST_VALUE, LAST_VALUE, AVG, MEDIAN, STDDEV

Patch:
diff --git a/README.rst b/README.rst
index 2bd955d..f6f22bc 100644
--- a/README.rst
+++ b/README.rst
@@ -52,7 +52,7 @@ Selecting Data
 ^^^^^^^^^^^^^^
 
 The entry point for building queries is ``pypika.Query``.  In order to select columns from a table, the table must
-first be added to the query.  For simple queries with only one table, tables and columns can be references using
+first be added to the query.  For simple queries with only one table, tables and and columns can be references using
 strings.  For more sophisticated queries a ``pypika.Table`` must be used.
 
 .. code-block:: python
@@ -340,7 +340,6 @@ Example of a join using `ON`
     SELECT "history".* FROM "history" JOIN "customers" ON "history"."customer_id"="customers"."customer_id" AND "history"."group"="customers"."group" WHERE "customers"."group"='A'
 
 
-
 Example of a join using `USING`
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
@@ -535,7 +534,6 @@ using the ``when`` method and to set the default value using ``else_``.
     SELECT "id",CASE WHEN "fname"='Tom' THEN 'It was Tom' WHEN "fname"='John' THEN 'It was John' ELSE 'It was someone else.' END "who_was_it" FROM "customers"
 
 
-
 Inserting Data
 ^^^^^^^^^^^^^^
 
diff --git a/docs/3_advanced.rst b/docs/3_advanced.rst
index 5f12559..ecacd27 100644
--- a/docs/3_advanced.rst
+++ b/docs/3_advanced.rst
@@ -7,46 +7,110 @@ Advanced Query Features
 
 This section covers the range of functions that are not widely standardized across all SQL databases or meet special
 needs.  |Brand| intends to support as many features across different platforms as possible.  If there are any features
-specific to a certain platform that PyPika does not support, please create a GitHub issue requesting that it be added.
+specific to a certain platform that PyPika does not support, please create a github issue requesting that it be added.
 
+GROUP BY Modifiers
+------------------
+
+The `ROLLUP` modifier allows for aggregating to higher levels that the given groups, called super-aggregates.
 
-Handling different database platforms
--------------------------------------
+.. code-block:: python
 
-There can sometimes be differences between how database vendors implement SQL in their platform, for example
-which quote characters are used. To ensure that the correct SQL standard is used for your platform,
-the platform-specific Query classes can be used.
+    from pypika import Rollup, functions as fn
+
+    products = Table('products')
+
+    query = Query.from_(products) \
+        .select(products.id, products.category, fn.Sum(products.price)) \
+        .rollup(products.id, products.category)
+
+.. code-block:: sql
+
+    SELECT "id","category",SUM("price") FROM "products" GROUP BY ROLLUP("id","category")
+
+
+Analytic Queries
+----------------
+
+The package ``pypika.analytic`` contains analytic function wrappers.  These can be used in ``SELECT`` clauses when
+building queries for databases that support them. Different functions have different arguments but all require some sort
+of partitioning.
+
+``NTILE`` and ``RANK``
+~~~~~~~~~~~~~~~~~~~~~~
+
+The ``NTILE`` function requires a constant integer argument while the ``RANK`` function takes no arguments.
+clause.
 
 .. code-block:: python
 
-    from pypika import MySQLQuery, MSSQLQuery, PostgreSQLQuery, OracleQuery, VerticaQuery
+    from pypika import Query, Table, analytics as an, functions as fn
 
-You can use these query classes as a drop in replacement for the default ``Query`` class shown in the other examples.
-Again, if you encounter any issues specific to a platform, please create a GitHub issue on this repository.
+    store_sales_fact, date_dimension = Table('store_sales_fact', schema='store'), Table('date_dimension')
 
-GROUP BY Modifiers
-------------------
+    total_sales = fn.Sum(store_sales_fact.sales_quantity).as_('TOTAL_SALES')
+    calendar_month_name = date_dimension.calendar_month_name.as_('MONTH')
+    ntile = an.NTile(4).order_by(total_sales).as_('NTILE')
 
-The `ROLLUP` modifier allows for aggregating to higher levels that the given groups, called super-aggregates.
+    query = Query.from_(store_sales_fact) \
+        .join(date_dimension).using('date_key') \
+        .select(calendar_month_name, total_sales, ntile) \
+        .groupby(calendar_month_name) \
+        .orderby(ntile)
+
+.. code-block:: sql
+
+    SELECT "date_dimension"."calendar_month_name" "MONTH",SUM("store_sales_fact"."sales_quantity") "TOTAL_SALES",NTILE(4) OVER(PARTITION BY  ORDER BY SUM("store_sales_fact"."sales_quantity")) "NTILE" FROM "store"."store_sales_fact" JOIN "date_dimension" USING ("date_key") GROUP BY "date_dimension"."calendar_month_name" ORDER BY NTILE(4) OVER(PARTITION BY  ORDER BY SUM("store_sales_fact"."sales_quantity"))
+
+``FIRST_VALUE`` and ``LAST_VALUE``
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+``FIRST_VALUE`` and ``LAST_VALUE`` both expect a single argument.  They also support an additional ``IGNORE NULLS``
+clause.
 
 .. code-block:: python
 
-    from pypika import Rollup, functions as fn
+    from pypika import Query, Table, analytics as an
 
-    products = Table('products')
+    t_month = Table('t_month')
+
+    first_month = an.FirstValue(t_month.month) \
+        .over(t_month.season) \
+        .orderby(t_month.id)
 
-    query = Query.from_(products).select(
-        products.id,
-        products.category,
-        fn.Sum(products.price)
-    ).rollup(
-        products.id,
-        products.category
-    )
+    last_month = an.LastValue(t_month.month) \
+        .over(t_month.season) \
+        .orderby(t_month.id) \
+        .ignore_nulls()
+
+    query = Query.from_(t_month) \
+        .select(first_month, last_month)
 
-    print(query)
 
 .. code-block:: sql
 
-    SELECT "id","category",SUM("price") FROM "products" GROUP BY ROLLUP("id","category")
+    SELECT FIRST_VALUE("month") OVER(PARTITION BY "season" ORDER BY "id"),LAST_VALUE("month" IGNORE NULLS) OVER(PARTITION BY "season" ORDER BY "id") FROM "t_month"
+
+``MEDIAN``, ``AVG`` and ``STDDEV``
+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+
+These functions take one or more arguments
+
+.. code-block:: python
+
+    from pypika import Query, Table, analytics as an
+
+    customer_dimension = Table('customer_dimension')
+
+    median_income = an.Median(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('MEDIAN')
+    avg_income = an.Avg(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('AVG')
+    stddev_income = an.StdDev(customer_dimension.annual_income).over(customer_dimension.customer_state).as_('STDDEV')
+
+    query = Query.from_(customer_dimension) \
+        .select(median_income, avg_income, stddev_income) \
+        .where(customer_dimension.customer_state.isin(['DC', 'WI'])) \
+        .orderby(customer_dimension.customer_state)
+
+.. code-block:: sql
 
+    SELECT MEDIAN("annual_income") OVER(PARTITION BY "customer_state") "MEDIAN",AVG("annual_income") OVER(PARTITION BY "customer_state") "AVG",STDDEV("annual_income") OVER(PARTITION BY "customer_state") "STDDEV" FROM "customer_dimension" WHERE "customer_state" IN ('DC','WI') ORDER BY "customer_state"
\ No newline at end of file
diff --git a/pypika/analytics.py b/pypika/analytics.py
new file mode 100644
index 0000000..69affde
--- /dev/null
+++ b/pypika/analytics.py
@@ -0,0 +1,43 @@
+# coding: utf-8
+"""
+Package for SQL analytic functions wrappers
+"""
+from pypika.terms import AnalyticFunction, IgnoreNullsAnalyticFunction
+
+__author__ = "Timothy Heys"
+__email__ = "theys@kayak.com"
+
+
+class Rank(AnalyticFunction):
+    def __init__(self, **kwargs):
+        super(Rank, self).__init__('RANK', **kwargs)
+
+
+class NTile(AnalyticFunction):
+    def __init__(self, term, **kwargs):
+        super(NTile, self).__init__('NTILE', term, **kwargs)
+
+
+class FirstValue(IgnoreNullsAnalyticFunction):
+    def __init__(self, *terms, **kwargs):
+        super(FirstValue, self).__init__('FIRST_VALUE', *terms, **kwargs)
+
+
+class LastValue(IgnoreNullsAnalyticFunction):
+    def __init__(self, *terms, **kwargs):
+        super(LastValue, self).__init__('LAST_VALUE', *terms, **kwargs)
+
+
+class Median(AnalyticFunction):
+    def __init__(self, term, **kwargs):
+        super(Median, self).__init__('MEDIAN', term, **kwargs)
+
+
+class Avg(AnalyticFunction):
+    def __init__(self, term, **kwargs):
+        super(Avg, self).__init__('AVG', term, **kwargs)
+
+
+class StdDev(AnalyticFunction):
+    def __init__(self, term, **kwargs):
+        super(StdDev, self).__init__('STDDEV', term, **kwargs)
diff --git a/pypika/functions.py b/pypika/functions.py
index cdc0bd9..f21a083 100644
--- a/pypika/functions.py
+++ b/pypika/functions.py
@@ -25,7 +25,6 @@ class Count(AggregateFunction):
     @builder
     def distinct(self):
         self._distinct = True
-        return self
 
 
 # Arithmetic Functions
@@ -67,28 +66,20 @@ class Coalesce(Function):
 # Type Functions
 class Cast(Function):
     def __init__(self, term, as_type, alias=None):
-        super(Cast, self).__init__('CAST', term, as_type, alias=alias)
+        super(Cast, self).__init__('CAST', term, alias=alias)
+        self.as_type = as_type
 
-    def get_function_sql(self, **kwargs):
-        # FIXME escape
-        return '{name}({field} AS {type})'.format(
-            name=self.name,
-            field=self.params[0],
-            type=self.params[1],
-        )
+    def get_special_params_sql(self, **kwargs):
+        return 'AS {type}'.format(type=self.as_type.value)
 
 
 class Convert(Function):
     def __init__(self, term, encoding, alias=None):
-        super(Convert, self).__init__('CONVERT', term, encoding, alias=alias)
+        super(Convert, self).__init__('CONVERT', term, alias=alias)
+        self.encoding = encoding
 
-    def get_function_sql(self, **kwargs):
-        # FIXME escape
-        return '{name}({field} USING {type})'.format(
-            name=self.name,
-            field=self.params[0],
-            type=self.params[1],
-        )
+    def get_special_params_sql(self, **kwargs):
+        return 'USING {type}'.format(type=self.encoding.value)
 
 
 class ToChar(Function):
@@ -196,11 +187,10 @@ class CurTime(Function):
 
 class Extract(Function):
     def __init__(self, date_part, field, alias=None):
-        super(Extract, self).__init__('EXTRACT', date_part, field, alias=alias)
+        super(Extract, self).__init__('EXTRACT', date_part, alias=alias)
+        self.field = field
 
-    def get_function_sql(self, **kwargs):
-        return '{name}({part} FROM {field})'.format(
-            name=self.name,
-            part=self.params[0],
-            field=self.params[1],
+    def get_special_params_sql(self, **kwargs):
+        return 'FROM {field}'.format(
+            field=self.field,
         )
diff --git a/pypika/terms.py b/pypika/terms.py
index 30f5fed..fa826fe 100644
--- a/pypika/terms.py
+++ b/pypika/terms.py
@@ -27,7 +27,6 @@ class Term(object):
     @builder
     def as_(self, alias):
         self.alias = alias
-        return self
 
     @property
     def tables_(self):
@@ -229,7 +228,6 @@ class Field(Term):
             A copy of the field with it's table value replaced.
         """
         self.table = table
-        return self
 
     def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
         # Need to add namespace if the table has an alias
@@ -301,7 +299,6 @@ class Criterion(Term):
     @builder
     def negate(self):
         self._is_negated = True
-        return self
 
     def fields(self):
         raise NotImplementedError()
@@ -338,7 +335,6 @@ class BasicCriterion(Criterion):
     def for_(self, table):
         self.left = self.left.for_(table)
         self.right = self.right.for_(table)
-        return self
 
     def fields(self):
         return self.left.fields() + self.right.fields()
@@ -397,7 +393,6 @@ class BetweenCriterion(Criterion):
     @builder
     def for_(self, table):
         self.term = self.term.for_(table)
-        return self
 
     def get_sql(self, **kwargs):
         # FIXME escape
@@ -423,7 +418,6 @@ class NullCriterion(Criterion):
     @builder
     def for_(self, table):
         self.term = self.term.for_(table)
-        return self
 
     def get_sql(self, **kwargs):
         return "{term} IS{not_} NULL".format(
@@ -508,7 +502,6 @@ class ArithmeticExpression(Term):
         """
         self.left = self.left.for_(table)
         self.right = self.right.for_(table)
-        return self
 
     def fields(self):
         return self.left.fields() + self.right.fields()
@@ -546,18 +539,12 @@ class Case(Term):
     @builder
     def when(self, criterion, term):
         self._cases.append((criterion, self._wrap(term)))
-        return self
 
     @builder
     def else_(self, term):
         self._else = self._wrap(term)
         return self
 
-    @builder
-    def as_(self, alias):
-        self.alias = alias
-        return self
-
     def get_sql(self, with_alias=False, **kwargs):
         if not self._cases:
             raise CaseException("At least one 'when' case is required for a CASE statement.")
@@ -590,16 +577,16 @@ class Case(Term):
 
 
 class Function(Term):
-    def __init__(self, name, *params, **kwargs):
+    def __init__(self, name, *args, **kwargs):
         super(Function, self).__init__(kwargs.get('alias'))
         self.name = name
-        self.params = [self._wrap(param)
-                       for param in params]
+        self.args = [self._wrap(param)
+                     for param in args]
 
     @property
     def tables_(self):
         return {table
-                for param in self.params
+                for param in self.args
                 for table in param.tables_}
 
     @builder
@@ -612,18 +599,23 @@ class Function(Term):
         :return:
             A copy of the field with it's table value replaced.
         """
-        self.params = [param.for_(table) if hasattr(param, 'for_')
+        self.args = [param.for_(table) if hasattr(param, 'for_')
                        else param
-                       for param in self.params]
-        return self
+                     for param in self.args]
 
-    def get_function_sql(self, with_namespace=False, quote_char=None):
-        return '{name}({params})'.format(
+    def get_special_params_sql(self, **kwargs):
+        pass
+
+    def get_function_sql(self, **kwargs):
+        special_params_sql = self.get_special_params_sql(**kwargs)
+
+        return '{name}({args}{special})'.format(
             name=self.name,
-            params=','.join(p.get_sql(with_alias=False, with_namespace=with_namespace, quote_char=quote_char)
+            args=','.join(p.get_sql(with_alias=False, **kwargs)
                             if hasattr(p, 'get_sql')
                             else str(p)
-                            for p in self.params),
+                          for p in self.args),
+            special=(' ' + special_params_sql) if special_params_sql else '',
         )
 
     def get_sql(self, with_alias=False, with_namespace=False, quote_char=None, **kwargs):
@@ -638,20 +630,87 @@ class Function(Term):
 
     def fields(self):
         return [field
-                for param in self.params
+                for param in self.args
                 if hasattr(param, 'fields')
                 for field in param.fields()]
 
-    @builder
-    def as_(self, alias):
-        self.alias = alias
-        return self
-
 
 class AggregateFunction(Function):
     is_aggregate = True
 
 
+class AnalyticFunction(Function):
+    is_analytic = True
+
+    def __init__(self, name, *args, **kwargs):
+        super(AnalyticFunction, self).__init__(name, *args, **kwargs)
+        self._partition = []
+        self._order = []
+
+    @builder
+    def over(self, *terms):
+        self._partition += terms
+
+    @builder
+    def orderby(self, *terms):
+        self._order += terms
+
+    def get_partition_sql(self, **kwargs):
+        terms = []
+        if self._partition:
+            terms.append('PARTITION BY {args}'.format(
+                args=','.join(p.get_sql(**kwargs)
+                              if hasattr(p, 'get_sql')
+                              else str(p)
+                              for p in self._partition)))
+
+        if self._order:
+            terms.append('ORDER BY {args}'.format(
+                args=','.join(p.get_sql(**kwargs)
+                              if hasattr(p, 'get_sql')
+                              else str(p)
+                              for p in self._order)))
+
+        return ' '.join(terms)
+
+    def get_function_sql(self, **kwargs):
+        function_sql = super(AnalyticFunction, self).get_function_sql(**kwargs)
+        partition_sql = self.get_partition_sql(**kwargs)
+
+        if not partition_sql:
+            return function_sql
+
+        return '{function_sql} OVER({partition_sql})'.format(
+            function_sql=function_sql,
+            partition_sql=partition_sql
+        )
+
+
+class IgnoreNullsAnalyticFunction(AnalyticFunction):
+    def __init__(self, name, *args, **kwargs):
+        super(IgnoreNullsAnalyticFunction, self).__init__(name, *args, **kwargs)
+        self._ignore_nulls = False
+
+    @builder
+    def ignore_nulls(self):
+        self._ignore_nulls = True
+
+    def get_special_params_sql(self, **kwargs):
+        if self._ignore_nulls:
+            return 'IGNORE NULLS'
+
+        # No special params unless ignoring nulls
+        return None
+
+    def get_function_sql(self, with_namespace=False, quote_char=None):
+        function_sql = super(AnalyticFunction, self).get_function_sql(with_namespace=False, quote_char=quote_char)
+
+        return '{function_sql} OVER({partition_sql})'.format(
+            function_sql=function_sql,
+            partition_sql=self.get_partition_sql(with_alias=False, with_namespace=with_namespace, quote_char=quote_char)
+        )
+
+
 class Interval(object):
     units = ['years', 'months', 'days', 'hours', 'minutes', 'seconds', 'microseconds']
     labels = ['YEAR', 'MONTH', 'DAY', 'HOUR', 'MINUTE', 'SECOND', 'MICROSECOND']


Test patch:
diff --git a/pypika/tests/test_aggregate.py b/pypika/tests/test_aggregate.py
index b37c7bd..e7802aa 100644
--- a/pypika/tests/test_aggregate.py
+++ b/pypika/tests/test_aggregate.py
@@ -1,8 +1,7 @@
 # coding: utf-8
 import unittest
 
-from pypika import Case
-from pypika import Field, functions as fn
+from pypika import Case , Field, functions as fn
 from pypika.terms import ValueWrapper
 
 
diff --git a/pypika/tests/test_analytic_queries.py b/pypika/tests/test_analytic_queries.py
new file mode 100644
index 0000000..4952f34
--- /dev/null
+++ b/pypika/tests/test_analytic_queries.py
@@ -0,0 +1,214 @@
+# coding: utf-8
+import unittest
+
+from pypika import Query, Tables, analytics as an, JoinType
+
+__author__ = "Timothy Heys"
+__email__ = "theys@kayak.com"
+
+
+class RankTests(unittest.TestCase):
+    table_abc, table_efg = Tables('abc', 'efg')
+
+    def test_rank(self):
+        expr = an.Rank() \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'RANK() '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_multiple_partitions(self):
+        expr = an.Rank() \
+            .orderby(self.table_abc.date) \
+            .over(self.table_abc.foo, self.table_abc.bar)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'RANK() '
+                         'OVER(PARTITION BY "foo","bar" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_ntile_no_partition_or_order_invalid_sql(self):
+        expr = an.NTile(5)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'NTILE(5) '
+                         'FROM "abc"', str(q))
+
+    def test_ntile_with_partition(self):
+        expr = an.NTile(5) \
+            .over(self.table_abc.foo)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'NTILE(5) '
+                         'OVER(PARTITION BY "foo") '
+                         'FROM "abc"', str(q))
+
+    def test_ntile_with_order(self):
+        expr = an.NTile(5) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'NTILE(5) '
+                         'OVER(ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_ntile_with_partition_and_order(self):
+        expr = an.NTile(5) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'NTILE(5) '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_first_value(self):
+        expr = an.FirstValue(self.table_abc.fizz) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'FIRST_VALUE("fizz") '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_first_value_ignore_nulls(self):
+        expr = an.FirstValue(self.table_abc.fizz) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date) \
+            .ignore_nulls()
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'FIRST_VALUE("fizz" IGNORE NULLS) '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_first_value_ignore_nulls_first(self):
+        expr = an.FirstValue(self.table_abc.fizz) \
+            .ignore_nulls() \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'FIRST_VALUE("fizz" IGNORE NULLS) '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_first_value_multi_argument(self):
+        expr = an.FirstValue(self.table_abc.fizz, self.table_abc.buzz) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'FIRST_VALUE("fizz","buzz") '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_last_value(self):
+        expr = an.LastValue(self.table_abc.fizz) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'LAST_VALUE("fizz") '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_last_value_multi_argument(self):
+        expr = an.LastValue(self.table_abc.fizz, self.table_abc.buzz) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'LAST_VALUE("fizz","buzz") '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_last_value_ignore_nulls(self):
+        expr = an.LastValue(self.table_abc.fizz) \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_abc.date) \
+            .ignore_nulls()
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'LAST_VALUE("fizz" IGNORE NULLS) '
+                         'OVER(PARTITION BY "foo" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_median(self):
+        expr = an.Median(self.table_abc.fizz) \
+            .over(self.table_abc.foo, self.table_abc.bar) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'MEDIAN("fizz") '
+                         'OVER(PARTITION BY "foo","bar" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_avg(self):
+        expr = an.Avg(self.table_abc.fizz) \
+            .over(self.table_abc.foo, self.table_abc.bar) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'AVG("fizz") '
+                         'OVER(PARTITION BY "foo","bar" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_stddev(self):
+        expr = an.StdDev(self.table_abc.fizz) \
+            .over(self.table_abc.foo, self.table_abc.bar) \
+            .orderby(self.table_abc.date)
+
+        q = Query.from_(self.table_abc).select(expr)
+
+        self.assertEqual('SELECT '
+                         'STDDEV("fizz") '
+                         'OVER(PARTITION BY "foo","bar" ORDER BY "date") '
+                         'FROM "abc"', str(q))
+
+    def test_table_prefixes_used_in_analytic_functions(self):
+        expr = an.Rank() \
+            .over(self.table_abc.foo) \
+            .orderby(self.table_efg.date)
+
+        query = Query.from_(self.table_abc) \
+            .join(self.table_efg, how=JoinType.left) \
+            .on(self.table_abc.foo == self.table_efg.bar) \
+            .select('*', expr)
+
+        self.assertEqual('SELECT *,RANK() OVER(PARTITION BY "abc"."foo" ORDER BY "efg"."date") '
+                         'FROM "abc" LEFT JOIN "efg" ON "abc"."foo"="efg"."bar"', str(query))
diff --git a/pypika/tests/test_tuples.py b/pypika/tests/test_tuples.py
index d9855fd..b08fc5b 100644
--- a/pypika/tests/test_tuples.py
+++ b/pypika/tests/test_tuples.py
@@ -56,13 +56,3 @@ class TupleTests(unittest.TestCase):
 
         self.assertEqual('SELECT "foo","bar" FROM "abc" '
                          'WHERE ("foo","bar") IN ((1,1),(2,2),(3,3))', str(q))
-
-    def test_tuples_in_join(self):
-        query = Query.from_(self.table_abc) \
-            .join(self.table_efg) \
-            .on(self.table_abc.foo == self.table_efg.bar) \
-            .select('*') \
-            .where(Tuple(self.table_abc.foo, self.table_efg.bar).isin([(1, 1), Tuple(2, 2), (3, 3)]))
-
-        self.assertEqual('SELECT * FROM "abc" JOIN "efg" ON "abc"."foo"="efg"."bar" '
-                         'WHERE ("abc"."foo","efg"."bar") IN ((1,1),(2,2),(3,3))', str(query))