- Added support for "set-aggregate" functions of the formticket_3516

``<function> WITHIN GROUP (ORDER BY <criteria>)``, using the
method :class:`.FunctionElement.within_group`.  A series of common
set-aggregate functions with return types derived from the set have
been added. This includes functions like :class:`.percentile_cont`,
:class:`.dense_rank` and others.
fixes #1370
- make sure we use func.name for all _literal_as_binds in functions.py
so we get consistent naming behavior for parameters.

1 files changed, 129 insertions, 12 deletions

diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index d5d364c77..618b987e1 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2970,21 +2970,21 @@ class Over(ColumnElement):
     order_by = None
     partition_by = None
 
-    def __init__(self, func, partition_by=None, order_by=None):
+    def __init__(self, element, partition_by=None, order_by=None):
         """Produce an :class:`.Over` object against a function.
 
         Used against aggregate or so-called "window" functions,
         for database backends that support window functions.
 
-        E.g.::
+        :func:`~.expression.over` is usually called using
+        the :meth:`.FunctionElement.over` method, e.g.::
 
-            from sqlalchemy import over
-            over(func.row_number(), order_by='x')
+            func.row_number().over(order_by='x')
 
-        Would produce "ROW_NUMBER() OVER(ORDER BY x)".
+        Would produce ``ROW_NUMBER() OVER(ORDER BY x)``.
 
-        :param func: a :class:`.FunctionElement` construct, typically
-         generated by :data:`~.expression.func`.
+        :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
+         or other compatible construct.
         :param partition_by: a column element or string, or a list
          of such, that will be used as the PARTITION BY clause
          of the OVER construct.
@@ -2997,8 +2997,14 @@ class Over(ColumnElement):
 
         .. versionadded:: 0.7
 
+        .. seealso::
+
+            :data:`.expression.func`
+
+            :func:`.expression.within_group`
+
         """
-        self.func = func
+        self.element = element
         if order_by is not None:
             self.order_by = ClauseList(
                 *util.to_list(order_by),
@@ -3008,17 +3014,29 @@ class Over(ColumnElement):
                 *util.to_list(partition_by),
                 _literal_as_text=_literal_as_label_reference)
 
+    @property
+    def func(self):
+        """the element referred to by this :class:`.Over`
+        clause.
+
+        .. deprecated:: 1.1 the ``func`` element has been renamed to
+           ``.element``.  The two attributes are synonymous though
+           ``.func`` is read-only.
+
+        """
+        return self.element
+
     @util.memoized_property
     def type(self):
-        return self.func.type
+        return self.element.type
 
     def get_children(self, **kwargs):
         return [c for c in
-                (self.func, self.partition_by, self.order_by)
+                (self.element, self.partition_by, self.order_by)
                 if c is not None]
 
     def _copy_internals(self, clone=_clone, **kw):
-        self.func = clone(self.func, **kw)
+        self.element = clone(self.element, **kw)
         if self.partition_by is not None:
             self.partition_by = clone(self.partition_by, **kw)
         if self.order_by is not None:
@@ -3028,7 +3046,106 @@ class Over(ColumnElement):
     def _from_objects(self):
         return list(itertools.chain(
             *[c._from_objects for c in
-                (self.func, self.partition_by, self.order_by)
+                (self.element, self.partition_by, self.order_by)
+              if c is not None]
+        ))
+
+
+class WithinGroup(ColumnElement):
+    """Represent a WITHIN GROUP (ORDER BY) clause.
+
+    This is a special operator against so-called
+    so-called "ordered set aggregate" and "hypothetical
+    set aggregate" functions, including ``percentile_cont()``,
+    ``rank()``, ``dense_rank()``, etc.
+
+    It's supported only by certain database backends, such as PostgreSQL,
+    Oracle and MS SQL Server.
+
+    The :class:`.WithinGroup` consturct extracts its type from the
+    method :meth:`.FunctionElement.within_group_type`.  If this returns
+    ``None``, the function's ``.type`` is used.
+
+    """
+    __visit_name__ = 'withingroup'
+
+    order_by = None
+
+    def __init__(self, element, *order_by):
+        """Produce a :class:`.WithinGroup` object against a function.
+
+        Used against so-called "ordered set aggregate" and "hypothetical
+        set aggregate" functions, including :class:`.percentile_cont`,
+        :class:`.rank`, :class:`.dense_rank`, etc.
+
+        :func:`~.expression.within_group` is usually called using
+        the :meth:`.FunctionElement.within_group` method, e.g.::
+
+            from sqlalchemy import within_group
+            stmt = select([
+                department.c.id,
+                func.percentile_cont(0.5).within_group(
+                    department.c.salary.desc()
+                )
+            ])
+
+        The above statement would produce SQL similar to
+        ``SELECT department.id, percentile_cont(0.5)
+        WITHIN GROUP (ORDER BY department.salary DESC)``.
+
+        :param element: a :class:`.FunctionElement` construct, typically
+         generated by :data:`~.expression.func`.
+        :param \*order_by: one or more column elements that will be used
+         as the ORDER BY clause of the WITHIN GROUP construct.
+
+        .. versionadded:: 1.1
+
+        .. seealso::
+
+            :data:`.expression.func`
+
+            :func:`.expression.over`
+
+        """
+        self.element = element
+        if order_by is not None:
+            self.order_by = ClauseList(
+                *util.to_list(order_by),
+                _literal_as_text=_literal_as_label_reference)
+
+    def over(self, partition_by=None, order_by=None):
+        """Produce an OVER clause against this :class:`.WithinGroup`
+        construct.
+
+        This function has the same signature as that of
+        :meth:`.FunctionElement.over`.
+
+        """
+        return Over(self, partition_by=partition_by, order_by=order_by)
+
+    @util.memoized_property
+    def type(self):
+        wgt = self.element.within_group_type(self)
+        if wgt is not None:
+            return wgt
+        else:
+            return self.element.type
+
+    def get_children(self, **kwargs):
+        return [c for c in
+                (self.func, self.order_by)
+                if c is not None]
+
+    def _copy_internals(self, clone=_clone, **kw):
+        self.element = clone(self.element, **kw)
+        if self.order_by is not None:
+            self.order_by = clone(self.order_by, **kw)
+
+    @property
+    def _from_objects(self):
+        return list(itertools.chain(
+            *[c._from_objects for c in
+                (self.element, self.order_by)
               if c is not None]
         ))