Support for the WITHIN GROUP (ORDER BY) clauses (within_group/WithinGroup).pr/156

6 files changed, 97 insertions, 2 deletions

diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py
index d184e1fbf..62492faad 100644
--- a/lib/sqlalchemy/__init__.py
+++ b/lib/sqlalchemy/__init__.py
@@ -49,6 +49,7 @@ from .sql import (
     union,
     union_all,
     update,
+    within_group,
     )
 
 from .types import (
diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py
index 351e08d0b..d7eb37a8b 100644
--- a/lib/sqlalchemy/sql/__init__.py
+++ b/lib/sqlalchemy/sql/__init__.py
@@ -64,6 +64,7 @@ from .expression import (
     union,
     union_all,
     update,
+    within_group,
 )
 
 from .visitors import ClauseVisitor
diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index ca14c9371..26d35a207 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -747,6 +747,12 @@ class SQLCompiler(Compiled):
             )
         )
 
+    def visit_within_group(self, within_group, **kwargs):
+        return "%s WITHIN GROUP (ORDER BY %s)" % (
+            within_group.func._compiler_dispatch(self, **kwargs),
+            within_group.order_by._compiler_dispatch(self, **kwargs)
+        )
+
     def visit_funcfilter(self, funcfilter, **kwargs):
         return "%s FILTER (WHERE %s)" % (
             funcfilter.func._compiler_dispatch(self, **kwargs),
diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py
index 8df22b7a6..91d081ca0 100644
--- a/lib/sqlalchemy/sql/elements.py
+++ b/lib/sqlalchemy/sql/elements.py
@@ -2892,6 +2892,68 @@ class Over(ColumnElement):
         ))
 
 
+class WithinGroup(ColumnElement):
+    """Represent an WITHIN GROUP clause.
+
+    This is a special operator against so-called ordered-set
+    and hypothetical-set aggregate functions in the SQL:2008 standard.
+    """
+    __visit_name__ = 'within_group'
+
+    order_by = None
+
+    def __init__(self, func, order_by):
+        """Produce an :class:`.WithinGroup` object against a function.
+
+        Used against ordered-set aggregates for databases that support
+        ordered-set aggregate functions
+
+        E.g.::
+
+            from sqlalchemy import within_group
+            within_group(func.percentile_disc(0.25), order_by='x')
+
+        Would produce "PERCENTILE_DISC(0.25) WITHIN GROUP (ORDER BY x)".
+
+        :param func: a :class:`.FunctionElement` construct, typically
+         generated by :data:`~.expression.func`.
+        :param order_by: a column element or string, or a list
+         of such, that will be used as the ORDER BY clause
+         of the WITHIN GROUP construct.
+
+        This function is also available from the :data:`~.expression.func`
+        construct itself via the :meth:`.FunctionElement.within_group` method.
+
+        .. versionadded:: 1.0
+
+        """
+        self.func = func
+        self.order_by = ClauseList(
+            *util.to_list(order_by),
+            _literal_as_text=_literal_as_label_reference)
+
+    @util.memoized_property
+    def type(self):
+        return self.func.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.func = clone(self.func, **kw)
+        self.order_by = clone(self.order_by, **kw)
+
+    @property
+    def _from_objects(self):
+        return list(itertools.chain(
+            *[c._from_objects for c in
+                (self.func, self.order_by)
+              if c is not None]
+        ))
+
+
 class FunctionFilter(ColumnElement):
     """Represent a function FILTER clause.
 
diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py
index 2218bd660..970fa15dc 100644
--- a/lib/sqlalchemy/sql/expression.py
+++ b/lib/sqlalchemy/sql/expression.py
@@ -36,7 +36,8 @@ from .elements import ClauseElement, ColumnElement,\
     True_, False_, BinaryExpression, Tuple, TypeClause, Extract, \
     Grouping, not_, \
     collate, literal_column, between,\
-    literal, outparam, type_coerce, ClauseList, FunctionFilter
+    literal, outparam, type_coerce, ClauseList, FunctionFilter, \
+    WithinGroup
 
 from .elements import SavepointClause, RollbackToSavepointClause, \
     ReleaseSavepointClause
@@ -99,6 +100,8 @@ update = public_factory(Update, ".expression.update")
 delete = public_factory(Delete, ".expression.delete")
 funcfilter = public_factory(
     FunctionFilter, ".expression.funcfilter")
+within_group = public_factory(
+    WithinGroup, ".expression.within_group")
 
 
 # internal functions still being called from tests and the ORM,
diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py
index 9280c7d60..f61ebfcd0 100644
--- a/lib/sqlalchemy/sql/functions.py
+++ b/lib/sqlalchemy/sql/functions.py
@@ -12,7 +12,7 @@ from . import sqltypes, schema
 from .base import Executable, ColumnCollection
 from .elements import ClauseList, Cast, Extract, _literal_as_binds, \
     literal_column, _type_from_args, ColumnElement, _clone,\
-    Over, BindParameter, FunctionFilter
+    Over, BindParameter, FunctionFilter, WithinGroup
 from .selectable import FromClause, Select, Alias
 
 from . import operators
@@ -116,6 +116,28 @@ class FunctionElement(Executable, ColumnElement, FromClause):
         """
         return Over(self, partition_by=partition_by, order_by=order_by)
 
+    def within_group(self, order_by=None):
+        """Produce a WITHIN GROUP clause against this function.
+
+        Used against ordered-set and hypothetical-set aggregates
+        within groups.
+
+        The expression::
+
+            func.percentile_disc(0.25).within_group(order_by='x')
+
+        is shorthand for::
+
+            from sqlalchemy import within_group
+            within_group(func.percentile_disc(0.25), order_by='x')
+
+        See :func:`~.expression.within_group` for a full description.
+
+        .. versionadded:: 1.0
+
+        """
+        return WithinGroup(self, order_by=order_by)
+
     def filter(self, *criterion):
         """Produce a FILTER clause against this function.