他们是否有任何简单(快速)的方法来获取一个表,并在datetime中找到离给定的SQLAlchemy最近的日期时间?在大多数情况下,增量将是给定的日期时间和表中的时间之间的秒。
日期列是主键。
编辑:我在使用SQLite
发布于 2017-03-02 11:02:19
因为它是主键--一个简单的“按差分升序排序,获取第一行”可能不是最快的,尽管是简单的解决方案。一种快速而肮脏的方法可能是获取大于和小于给定日期时间的联合,对升序和降序进行排序,并将其限制在第一行,然后从2中选择差异较小的行。
下面是一个使用Postgresql作为后端的示例和一个具有12s分辨率的具有一年时间戳的测试表:
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中的更接近点。用Postgresql表示间隔的getting the absolute value。其他DBs对于差分计算和绝对值计算需要不同的解决方案。使用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内运行,而上面的查询大约在70-100 70内完成。如果将数据加倍,那么依赖seq扫描的简单解决方案也会加倍。
工会从表中找到这两个值:
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)发布于 2017-03-02 10:48:34
使用普通SQL,您可以执行以下操作:
select * from table where datetime > your_date_time limit 1;
select * from table where datetime < your_date_time limit 1;要获得第一次约会之后和第一次约会时间之前,然后计算差异,并得到最近。
使用SQLAlchemy,您可能可以使用.limit或.filter方法编写类似的东西。
发布于 2020-11-10 16:04:56
真的不喜欢其他的答案。很难阅读或下降到纯SQL,这不是问题所要求的。
简单解决方案:
Event是模型名Event.ts是datetime列名。def closest(ts):
import math
# find the closest events which are greater/less than the timestamp
gt_event = Event.query.filter(Event.ts > ts).order_by(Event.ts.asc()).first()
lt_event = Event.query.filter(Event.ts < ts).order_by(Event.ts.desc()).first()
# find diff between events and the ts, if no event found default to infintiy
gt_diff = (gt_event.ts - ts).total_seconds() if gt_event else math.inf
lt_diff = (ts - lt_event.ts).total_seconds() if lt_event else math.inf
# return the newest event if it is closer to ts else older event
# if an event is None its diff will always be greater as we set it to infinity
return gt_event if gt_diff < lt_diff else lt_eventhttps://stackoverflow.com/questions/42552696
复制相似问题