python - SQLAlchemy 窗口框架作为时间间隔

Question

在 Sqlalchemy 中是否有任何方法可以将窗口框架指定为这样的时间间隔？

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

在他们的文档中有一个方法 sqlalchemy.sql.functions.FunctionElement.over(partition_by=None, order_by=None, rows=None, range_=None) 。通过它只需要数字数据作为范围_。

Answer 1

从 SQLAlchemy 1.4.25 开始，没有内置支持。
解决方法

实现 str覆盖 __abs__ 的子类和 __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)

补丁 Over._interpret_range处理 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

用法:

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

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