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: Optional[_ByArgument] = None,
order_by: Optional[_ByArgument] = None,
range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
)
| 1515 | |
| 1516 | |
| 1517 | def over( |
| 1518 | element: FunctionElement[_T], |
| 1519 | partition_by: Optional[_ByArgument] = None, |
| 1520 | order_by: Optional[_ByArgument] = None, |
| 1521 | range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, |
| 1522 | rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, |
| 1523 | groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None, |
| 1524 | ) -> Over[_T]: |
| 1525 | r"""Produce an :class:`.Over` object against a function. |
| 1526 | |
| 1527 | Used against aggregate or so-called "window" functions, |
| 1528 | for database backends that support window functions. |
| 1529 | |
| 1530 | :func:`_expression.over` is usually called using |
| 1531 | the :meth:`.FunctionElement.over` method, e.g.:: |
| 1532 | |
| 1533 | func.row_number().over(order_by=mytable.c.some_column) |
| 1534 | |
| 1535 | Would produce: |
| 1536 | |
| 1537 | .. sourcecode:: sql |
| 1538 | |
| 1539 | ROW_NUMBER() OVER(ORDER BY some_column) |
| 1540 | |
| 1541 | Ranges are also possible using the :paramref:`.expression.over.range_`, |
| 1542 | :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups` |
| 1543 | parameters. These |
| 1544 | mutually-exclusive parameters each accept a 2-tuple, which contains |
| 1545 | a combination of integers and None:: |
| 1546 | |
| 1547 | func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0)) |
| 1548 | |
| 1549 | The above would produce: |
| 1550 | |
| 1551 | .. sourcecode:: sql |
| 1552 | |
| 1553 | ROW_NUMBER() OVER(ORDER BY some_column |
| 1554 | RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) |
| 1555 | |
| 1556 | A value of ``None`` indicates "unbounded", a |
| 1557 | value of zero indicates "current row", and negative / positive |
| 1558 | integers indicate "preceding" and "following": |
| 1559 | |
| 1560 | * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:: |
| 1561 | |
| 1562 | func.row_number().over(order_by="x", range_=(-5, 10)) |
| 1563 | |
| 1564 | * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:: |
| 1565 | |
| 1566 | func.row_number().over(order_by="x", rows=(None, 0)) |
| 1567 | |
| 1568 | * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:: |
| 1569 | |
| 1570 | func.row_number().over(order_by="x", range_=(-2, None)) |
| 1571 | |
| 1572 | * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:: |
| 1573 | |
| 1574 | func.row_number().over(order_by="x", range_=(1, 3)) |