python - SQLAlchemy 最近的日期时间

Question

他们是否有任何简单(快速)的方法来获取表并找到与 SQLAlchemy 中给定日期时间最接近的日期时间？在大多数情况下，增量将是给定日期时间与表中日期时间之间的秒数。

日期列是主键

编辑:我正在使用 SQLite

Answer 1

由于它是主键，因此简单的“按差异升序排序，获取第一行”可能不是最快但简单的解决方案。一种快速而肮脏的方法可能是获取大于和小于给定日期时间的并集，按升序和降序排序并限制为第一行，然后选择与第二行相比较小的一个。

下面是一个使用 Postgresql 作为后端的示例，以及一个测试表，其中包含一年的时间戳(分辨率为 12 秒):

sopython=> create table testi (key timestamp without time zone primary key);
CREATE TABLE
sopython=> insert into testi
select *
from generate_series(now() at time zone 'utc' - '1 year'::interval,
                     now() at time zone 'utc',
                     '12 seconds'::interval);
INSERT 0 2628001

和Python:

In [29]: from sqlalchemy import union_all, case

In [30]: from sqlalchemy.orm import aliased

In [31]: the_time = datetime(2016, 5, 5, 10, 45, 55)

创建获取最接近值的联合，将子查询包装在 SELECT 语句中，以便它也可以在 SQLite 中工作。

In [32]: greater = session.query(Testi).filter(Testi.key > the_time).\
    ...:     order_by(Testi.key.asc()).limit(1).subquery().select()

In [33]: lesser = session.query(Testi).filter(Testi.key <= the_time).\
    ...:     order_by(Testi.key.desc()).limit(1).subquery().select()

In [34]: the_union = union_all(lesser, greater).alias()

将模型命名为并集结果

In [35]: testi_alias = aliased(Testi, the_union)

计算与给定日期时间的差异

In [36]: the_diff = testi_alias.key - the_time

或者在 SQLite 中

In [36]: the_diff = func.julianday(testi_alias.key) - func.julianday(the_time)

取 2 中更接近的一个。这个怪物的箱子是 getting the absolute value Postgresql 中的间隔。其他 DB 需要不同的解决方案来进行差值计算和取绝对值。使用 SQLite 只需 func.abs(the_diff)。

In [37]: session.query(testi_alias).\
    ...:     order_by(case([(the_diff < timedelta(0), -the_diff)],
    ...:                   else_=the_diff)).\
    ...:     first()
Out[37]: <sqlalchemy.ext.automap.testi at 0x7f096f837828>

In [38]: _.key
Out[38]: datetime.datetime(2016, 5, 5, 10, 45, 54, 855799)

虽然仅按 diff 排序和限制的简单解决方案在这台计算机上运行了约 800 毫秒，但上述查询在大约 70-100 毫秒内完成。如果数据加倍，那么简单的解决方案(依靠序列扫描)也会加倍。

联合从表中找到这两个值:

In [14]: session.query(testi_alias.key).all()
Out[14]: 
[(datetime.datetime(2016, 5, 5, 10, 45, 54, 855799)),
 (datetime.datetime(2016, 5, 5, 10, 46, 6, 855799))]

最后，您可以将其全部包装在一个通用函数中:

def get_closest(session, cls, col, the_time):
    greater = session.query(cls).filter(col > the_time).\
        order_by(col.asc()).limit(1).subquery().select()

    lesser = session.query(cls).filter(col <= the_time).\
        order_by(col.desc()).limit(1).subquery().select()

    the_union = union_all(lesser, greater).alias()
    the_alias = aliased(cls, the_union)
    the_diff = getattr(the_alias, col.name) - the_time
    abs_diff = case([(the_diff < timedelta(0), -the_diff)],
                    else_=the_diff)

    return session.query(the_alias).\
        order_by(abs_diff.asc()).\
        first()

get_closest(session, Testi, Testi.key, the_time)