我的问题是,我想检索两个测量列表以及这些测量的移动平均值。我可以用这个SQL语句(postgresql interval语法)来实现这一点:
SELECT time, value,
(
SELECT AVG(t2.value)
FROM measurements t2
WHERE t2.time BETWEEN t1.time - interval '5 days' AND t1.time
) moving_average
FROM measurements t1
ORDER BY t1.time;
我希望使用SQLAlchemy代码生成类似的语句。我现在有这个Python代码:
moving_average_days = # configureable value, defaulting to 5
t1 = Measurements.alias('t1')
t2 = Measurements.alias('t2')
query = select([t1.c.time, t1.c.value, select([func.avg(t2.c.value)], t2.c.time.between(t1.c.time - datetime.timedelta(moving_average_days), t1.c.time))],
t1.c.time > (datetime.datetime.utcnow() - datetime.timedelta(ndays))). \
order_by(Measurements.c.time)
但是,这会生成以下SQL:
SELECT t1.time, t1.value, avg_1
FROM measurements AS t1,
(
SELECT avg(t2.value) AS avg_1
FROM measurements AS t2
WHERE t2.time BETWEEN t1.time - %(time_1)s AND t1.time
)
WHERE t1.time > %(time_2)s
ORDER BY t1.time;
该SQL将子查询作为FROM子句的一部分,在该子句中,它不能对顶级值的列值进行标量访问,即它会导致PostgreSQL弹出此错误:
ERROR: subquery in FROM cannot refer to other relations of same query level
LINE 6: WHERE t2.time BETWEEN t1.time - interval '5 days' AN...
因此我想知道的是:如何让SQLAlchemy将子查询移动到SELECT子句?
或者,另一种获取移动平均值的方法(不对每个(时间、值)对执行查询)将是一个选项。
对,显然我需要的是使用所谓的scalar select。通过使用这些代码,我得到了这个python代码,它实际上可以按我的意愿工作(生成与我的问题中的第一个相同的SQL,这是我的目标):
这给出了这个SQL:
相关问题 更多 >
编程相关推荐