Module « sqlalchemy »

Fonction over - module sqlalchemy

Signature de la fonction over

def over(element, partition_by=None, order_by=None, range_=None, rows=None) 

Description

over.__doc__

Produce an :class:`.Over` object against a function.

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

        :func:`_expression.over` is usually called using
        the :meth:`.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))

        .. versionadded:: 1.1 support for RANGE / ROWS within a window


        :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.
        :param order_by: a column element or string, or a list
         of such, that will be used as the ORDER BY clause
         of the OVER construct.
        :param 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.

         .. versionadded:: 1.1

        :param 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.

         .. versionadded:: 1.1

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

        .. seealso::

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

            :data:`.expression.func`

            :func:`_expression.within_group`