class documentation

class Over(ColumnElement):

View In Hierarchy

Represent an OVER clause.

This is a special operator against a so-called "window" function, as well as any aggregate function, which produces results relative to the result set itself. Most modern SQL backends now support window functions.

Method __init__ Produce an .Over object against a function.
Method __reduce__ Undocumented
Method ​_interpret​_range Undocumented
Class Variable __visit​_name__ Undocumented
Class Variable ​_traverse​_internals Undocumented
Instance Variable element The underlying expression object to which this .Over object refers towards.
Instance Variable order​_by Undocumented
Instance Variable partition​_by Undocumented
Instance Variable range​_ Undocumented
Property ​_from​_objects Undocumented
Property type Undocumented

Inherited from ColumnElement:

Method cast Produce a type cast, i.e. CAST(<expression> AS <type>).
Method label Produce a column label, i.e. <columnname> AS <name>.
Method operate Operate on an argument.
Method reverse​_operate Reverse operate on an argument.
Method self​_group Apply a 'grouping' to this _expression.ClauseElement.
Method shares​_lineage Return True if the given _expression.ColumnElement has a common ancestor to this _expression.ColumnElement.
Class Variable foreign​_keys Undocumented
Class Variable key The 'key' that in some circumstances refers to this object in a Python namespace.
Class Variable primary​_key Undocumented
Method __getattr__ Undocumented
Method ​_anon​_label Undocumented
Method ​_bind​_param Undocumented
Method ​_compare​_name​_for​_result Return True if the given column element compares to this one when targeting within a result row.
Method ​_dedupe​_anon​_label​_idx label to apply to a column that is anon labeled, but repeated in the SELECT, so that we have to make an "extra anon" label that disambiguates it from the previous appearance.
Method ​_dedupe​_anon​_tq​_label​_idx Undocumented
Method ​_expression​_label a suggested label to use in the case that the column has no name, which should be used if possible as the explicit 'AS <label>' where this expression would normally have an anon label.
Method ​_make​_proxy Create a new _expression.ColumnElement representing this _expression.ColumnElement as it appears in the select list of a descending selectable.
Method ​_negate Undocumented
Method ​_proxy​_key Undocumented
Method ​_tq​_key​_label A label-based version of 'key' that in some circumstances refers to this object in a Python namespace.
Method ​_uncached​_proxy​_set An 'uncached' version of proxy set.
Method comparator Undocumented
Class Variable ​_allow​_label​_resolve A flag that can be flipped to prevent a column from being resolvable by string label name.
Class Variable ​_alt​_names Undocumented
Class Variable ​_is​_implicitly​_boolean Undocumented
Class Variable ​_proxies Undocumented
Class Variable ​_render​_label​_in​_columns​_clause No summary
Class Variable ​_tq​_label The named label that can be used to target this column in a result set in a "table qualified" context.
Property ​_anon​_key​_label Provides a constant 'anonymous key label' for this ColumnElement.
Property ​_anon​_name​_label Provides a constant 'anonymous label' for this ColumnElement.
Property ​_anon​_tq​_key​_label Undocumented
Property ​_anon​_tq​_label Undocumented
Property ​_key​_label legacy; renamed to _tq_key_label
Property ​_label legacy; renamed to _tq_label
Property ​_non​_anon​_label the 'name' that naturally applies this element when rendered in SQL.
Property ​_select​_iterable Undocumented
Property anon​_key​_label Undocumented
Property anon​_label Undocumented
Property base​_columns Undocumented
Property expression Return a column expression.
Property proxy​_set Undocumented

Inherited from ColumnArgumentOrKeyRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, ColumnArgumentOrKeyRole, ColumnArgumentRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from StatementOptionRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, StatementOptionRole, StructuralRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from WhereHavingRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via ColumnElement, WhereHavingRole, OnClauseRole):

Class Variable allows​_lambda Undocumented

Inherited from SQLRole (via ColumnElement, WhereHavingRole, OnClauseRole, StructuralRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from BinaryElementRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, BinaryElementRole, ExpressionElementRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from OrderByRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via ColumnElement, OrderByRole):

Class Variable allows​_lambda Undocumented

Inherited from SQLRole (via ColumnElement, OrderByRole, ByOfRole, ColumnListRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from ColumnsClauseRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from AllowsLambdaRole (via ColumnElement, ColumnsClauseRole):

Class Variable allows​_lambda Undocumented

Inherited from UsesInspection (via ColumnElement, ColumnsClauseRole):

Class Variable ​_post​_inspect Undocumented
Class Variable uses​_inspection Undocumented

Inherited from SQLRole (via ColumnElement, ColumnsClauseRole, ColumnListRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from LimitOffsetRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, LimitOffsetRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DMLColumnRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, DMLColumnRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DDLConstraintColumnRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, DDLConstraintColumnRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from DDLExpressionRole (via ColumnElement):

Class Variable ​_role​_name Undocumented

Inherited from SQLRole (via ColumnElement, DDLExpressionRole, StructuralRole):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from ColumnOperators (via ColumnElement):

Method __add__ Implement the + operator.
Method __contains__ Undocumented
Method __div__ Implement the / operator.
Method __eq__ Implement the == operator.
Method __ge__ Implement the >= operator.
Method __getitem__ Implement the [] operator.
Method __gt__ Implement the > operator.
Method __le__ Implement the <= operator.
Method __lshift__ implement the << operator.
Method __lt__ Implement the < operator.
Method __mod__ Implement the % operator.
Method __mul__ Implement the * operator.
Method __ne__ Implement the != operator.
Method __neg__ Implement the - operator.
Method __radd__ Implement the + operator in reverse.
Method __rdiv__ Implement the / operator in reverse.
Method __rmod__ Implement the % operator in reverse.
Method __rmul__ Implement the * operator in reverse.
Method __rshift__ implement the >> operator.
Method __rsub__ Implement the - operator in reverse.
Method __rtruediv__ Implement the // operator in reverse.
Method __sub__ Implement the - operator.
Method __truediv__ Implement the // operator.
Method all​_ Produce an _expression.all_ clause against the parent object.
Method any​_ Produce an _expression.any_ clause against the parent object.
Method asc Produce a _expression.asc clause against the parent object.
Method between Produce a _expression.between clause against the parent object, given the lower and upper range.
Method collate Produce a _expression.collate clause against the parent object, given the collation string.
Method concat Implement the 'concat' operator.
Method contains Implement the 'contains' operator.
Method desc Produce a _expression.desc clause against the parent object.
Method distinct Produce a _expression.distinct clause against the parent object.
Method endswith Implement the 'endswith' operator.
Method ilike Implement the ilike operator, e.g. case insensitive LIKE.
Method in​_ Implement the in operator.
Method is​_ Implement the IS operator.
Method is​_distinct​_from Implement the IS DISTINCT FROM operator.
Method is​_not Implement the IS NOT operator.
Method is​_not​_distinct​_from Implement the IS NOT DISTINCT FROM operator.
Method like Implement the like operator.
Method match Implements a database-specific 'match' operator.
Method not​_ilike implement the NOT ILIKE operator.
Method not​_in implement the NOT IN operator.
Method not​_like implement the NOT LIKE operator.
Method nulls​_first Produce a _expression.nulls_first clause against the parent object.
Method nulls​_last Produce a _expression.nulls_last clause against the parent object.
Method regexp​_match Implements a database-specific 'regexp match' operator.
Method regexp​_replace Implements a database-specific 'regexp replace' operator.
Method startswith Implement the startswith operator.
Class Variable __slots__ Undocumented
Class Variable timetuple Hack, allows datetime objects to be compared on the LHS.

Inherited from Operators (via ColumnElement, ColumnOperators):

Method __and__ Implement the & operator.
Method __invert__ Implement the ~ operator.
Method __or__ Implement the | operator.
Method bool​_op Return a custom boolean operator.
Method op Produce a generic operator function.

Inherited from ClauseElement (via ColumnElement):

Method compare Compare this _expression.ClauseElement to the given _expression.ClauseElement.
Method compile Compile this SQL expression.
Method params Return a copy with _expression.bindparam elements replaced.
Method unique​_params Return a copy with _expression.bindparam elements replaced.
Method __bool__ Undocumented
Method __getstate__ Undocumented
Method __invert__ Undocumented
Method __repr__ Undocumented
Method __str__ Undocumented
Method ​_clone Create a shallow copy of this ClauseElement.
Method ​_cloned​_set Return the set consisting all cloned ancestors of this ClauseElement.
Method ​_compile​_w​_cache Undocumented
Method ​_compiler Return a compiler appropriate for this ClauseElement, given a Dialect.
Method ​_execute​_on​_connection Undocumented
Method ​_negate​_in​_binary a hook to allow the right side of a binary expression to respond to a negation of the binary expression.
Method ​_replace​_params Undocumented
Method ​_set​_propagate​_attrs Undocumented
Method ​_ungroup Return this _expression.ClauseElement without any groupings.
Method ​_with​_binary​_element​_type in the context of binary expression, convert the type of this object to the one given.
Class Variable ​_cache​_key​_traversal Undocumented
Class Variable ​_is​_bind​_parameter Undocumented
Class Variable ​_is​_clause​_list Undocumented
Class Variable ​_is​_clone​_of Undocumented
Class Variable ​_is​_from​_clause Undocumented
Class Variable ​_is​_from​_container Undocumented
Class Variable ​_is​_immutable Undocumented
Class Variable ​_is​_lambda​_element Undocumented
Class Variable ​_is​_returns​_rows Undocumented
Class Variable ​_is​_select​_container Undocumented
Class Variable ​_is​_select​_statement Undocumented
Class Variable ​_is​_singleton​_constant Undocumented
Class Variable ​_is​_text​_clause Undocumented
Class Variable ​_is​_textual Undocumented
Class Variable ​_order​_by​_label​_element Undocumented
Class Variable bind Undocumented
Class Variable description Undocumented
Class Variable is​_clause​_element Undocumented
Class Variable is​_selectable Undocumented
Class Variable stringify​_dialect Undocumented
Class Variable supports​_execution Undocumented
Instance Variable ​_propagate​_attrs like annotations, however these propagate outwards liberally as SQL constructs are built, and are set up at construction time.
Property ​_constructor return the 'constructor' for this ClauseElement.
Property entity​_namespace Undocumented

Inherited from SQLRole (via ColumnElement, ClauseElement):

Class Variable allows​_lambda Undocumented
Class Variable uses​_inspection Undocumented

Inherited from SupportsWrappingAnnotations (via ColumnElement, ClauseElement):

Method ​_annotate return a copy of this ClauseElement with annotations updated by the given dictionary.
Method ​_deannotate return a copy of this _expression.ClauseElement with annotations removed.
Method ​_with​_annotations return a copy of this ClauseElement with annotations replaced by the given dictionary.

Inherited from SupportsAnnotations (via ColumnElement, ClauseElement, SupportsWrappingAnnotations):

Property ​_annotations​_cache​_key Undocumented

Inherited from MemoizedHasCacheKey (via ColumnElement, ClauseElement):

Method ​_generate​_cache​_key return a cache key.

Inherited from HasCacheKey (via ColumnElement, ClauseElement, MemoizedHasCacheKey):

Class Variable inherit​_cache Indicate if this .HasCacheKey instance should make use of the cache key generation scheme used by its immediate superclass.
Class Method ​_generate​_cache​_attrs generate cache key dispatcher for a new class.
Class Method ​_generate​_cache​_key​_for​_object Undocumented
Method ​_gen​_cache​_key return an optional cache key.
Class Variable __slots__ Undocumented
Class Variable ​_hierarchy​_supports​_caching private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses.
Class Variable ​_is​_has​_cache​_key Undocumented

Inherited from HasCopyInternals (via ColumnElement, ClauseElement):

Method ​_copy​_internals Reassign internal elements to be clones of themselves.

Inherited from Traversible (via ColumnElement, ClauseElement):

Method get​_children Return immediate child .visitors.Traversible elements of this .visitors.Traversible.
Method __class​_getitem__ Undocumented
def __init__(self, element, partition_by=None, order_by=None, range_=None, rows=None):

Produce an .Over object against a function.

Used against aggregate or so-called "window" functions, for database backends that support window functions.

_expression.over is usually called using the .FunctionElement.over method, e.g.:

func.row_number().over(order_by=mytable.c.some_column)

Would produce:

ROW_NUMBER() OVER(ORDER BY some_column)

Ranges are also possible using the :paramref:`.expression.over.range_` and :paramref:`.expression.over.rows` parameters. These mutually-exclusive parameters each accept a 2-tuple, which contains a combination of integers and None:

func.row_number().over(
    order_by=my_table.c.some_column, range_=(None, 0))

The above would produce:

ROW_NUMBER() OVER(ORDER BY some_column
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

A value of None indicates "unbounded", a value of zero indicates "current row", and negative / positive integers indicate "preceding" and "following":

  • RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:

    func.row_number().over(order_by='x', range_=(-5, 10))
    
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:

    func.row_number().over(order_by='x', rows=(None, 0))
    
  • RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:

    func.row_number().over(order_by='x', range_=(-2, None))
    
  • RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:

    func.row_number().over(order_by='x', range_=(1, 3))
    
New in version 1.1: support for RANGE / ROWS within a window

This function is also available from the ~.expression.func construct itself via the .FunctionElement.over method.

See Also

:ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`

.expression.func

_expression.within_group

Parameters
elementa .FunctionElement, .WithinGroup, or other compatible construct.
partition​_bya column element or string, or a list of such, that will be used as the PARTITION BY clause of the OVER construct.
order​_bya column element or string, or a list of such, that will be used as the ORDER BY clause of the OVER construct.
range​_

optional range clause for the window. This is a tuple value which can contain integer values or None, and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.

New in version 1.1.
rows

optional rows clause for the window. This is a tuple value which can contain integer values or None, and will render a ROWS BETWEEN PRECEDING / FOLLOWING clause.

New in version 1.1.
def __reduce__(self):

Undocumented

def _interpret_range(self, range_):

Undocumented

__visit_name__: str =
_traverse_internals =

Undocumented

element =
The underlying expression object to which this .Over object refers towards.
order_by =

Undocumented

partition_by =

Undocumented

range_ =

Undocumented

@property
_from_objects =