diff options
Diffstat (limited to 'lib/sqlalchemy/sql/elements.py')
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 141 |
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] )) |