SQLAlchemy window frames as time interval

user_16292353 0
Viewed 186

Is there any way in SQLAlchemy to specify window frame as time interval like this?

    OVER(
        PARTITION BY some_col 
        ORDER BY other_date_type_col 
        RANGE BETWEEN '30 days'::INTERVAL PRECEDING AND CURRENT ROW
   )

There's a method sqlalchemy.sql.functions.FunctionElement.over(partition_by=None, order_by=None, rows=None, range_=None) in their docs. By it takes only numeric data as range_.

sqlalchemy python
1 Answers

As of SQLAlchemy 1.4.25, there is no built-in support.

Workaround

  1. Implement a str subclass that overrides __abs__ and __lt__.
class RangeDays(str):
    def __new__(cls, x):
        obj = super().__new__(cls, f"{abs(x)} day" if abs(x) == 1 else f"{abs(x)} days")
        obj.x = x
        return obj

    def __abs__(self):
        # abs(range_[0]) called in SQLCompiler._format_frame_clause
        return self

    def __lt__(self, other):
        # range_[0] < 0 called in SQLCompiler._format_frame_clause
        return self.x.__lt__(other)
  1. Patch Over._interpret_range to handle RangeDays.
from sqlalchemy.sql.elements import Over

_old_interpret_range = Over._interpret_range


def _interpret_range(self, range_):
    lower, lower_ = (None, range_[0]) if isinstance(range_[0], RangeDays) else (range_[0], None)
    upper, upper_ = (None, range_[1]) if isinstance(range_[1], RangeDays) else (range_[1], None)
    lower, upper = _old_interpret_range(self, (lower, upper))
    return lower_ or lower, upper_ or upper


Over._interpret_range = _interpret_range

Usage:

# '30 days' PRECEDING AND CURRENT ROW
range_=(RangeDays(-30), 0)

# '1 day' PRECEDING AND '10 days' FOLLOWING
range_=(RangeDays(-1), RangeDays(10))
user_86017600
Related