r"""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_
(
element: FunctionElement[_T],
partition_by: _ByArgument | None = None,
order_by: _ByArgument | None = None,
range_: _FrameIntTuple | FrameClause | None = None,
rows: _FrameIntTuple | FrameClause | None = None,
groups: _FrameIntTuple | FrameClause | None = None,
exclude: str | None = None,
)
| 1614 | |
| 1615 | |
| 1616 | def over( |
| 1617 | element: FunctionElement[_T], |
| 1618 | partition_by: _ByArgument | None = None, |
| 1619 | order_by: _ByArgument | None = None, |
| 1620 | range_: _FrameIntTuple | FrameClause | None = None, |
| 1621 | rows: _FrameIntTuple | FrameClause | None = None, |
| 1622 | groups: _FrameIntTuple | FrameClause | None = None, |
| 1623 | exclude: str | None = None, |
| 1624 | ) -> Over[_T]: |
| 1625 | r"""Produce an :class:`.Over` object against a function. |
| 1626 | |
| 1627 | Used against aggregate or so-called "window" functions, |
| 1628 | for database backends that support window functions. |
| 1629 | |
| 1630 | :func:`_expression.over` is usually called using |
| 1631 | the :meth:`.FunctionElement.over` method, e.g.:: |
| 1632 | |
| 1633 | func.row_number().over(order_by=mytable.c.some_column) |
| 1634 | |
| 1635 | Would produce: |
| 1636 | |
| 1637 | .. sourcecode:: sql |
| 1638 | |
| 1639 | ROW_NUMBER() OVER(ORDER BY some_column) |
| 1640 | |
| 1641 | Ranges are also possible using the :paramref:`.expression.over.range_`, |
| 1642 | :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups` |
| 1643 | parameters. These |
| 1644 | mutually-exclusive parameters each accept a 2-tuple, which contains |
| 1645 | a combination of integers and None:: |
| 1646 | |
| 1647 | func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0)) |
| 1648 | |
| 1649 | The above would produce: |
| 1650 | |
| 1651 | .. sourcecode:: sql |
| 1652 | |
| 1653 | ROW_NUMBER() OVER(ORDER BY some_column |
| 1654 | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| 1655 | |
| 1656 | A value of ``None`` indicates "unbounded", a |
| 1657 | value of zero indicates "current row", and negative / positive |
| 1658 | integers indicate "preceding" and "following": |
| 1659 | |
| 1660 | * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:: |
| 1661 | |
| 1662 | func.row_number().over(order_by="x", range_=(-5, 10)) |
| 1663 | |
| 1664 | * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:: |
| 1665 | |
| 1666 | func.row_number().over(order_by="x", rows=(None, 0)) |
| 1667 | |
| 1668 | * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:: |
| 1669 | |
| 1670 | func.row_number().over(order_by="x", range_=(-2, None)) |
| 1671 | |
| 1672 | * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:: |
| 1673 |