在连接中使用别名时列名不明确

Question

为什么这个查询产生一个"不明确的列名";错误，当我包括"as时间"别名吗?如果我去掉' as time '或者"SAMPLE BY "，它就消失了。

SELECT a.ts as time, sum(a.to_grid), sum(a.from_grid), sum(b.hourly_production)
FROM 'eloverblik' as a, 'ap_systems' as b
WHERE a.ts = b.ts
SAMPLE BY 1d;

编辑:模式如下:

ap_systems
ts timestamp
hourly_production double
eloverblik
ts timestamp
to_grid double
from_grid double

Edit2:下面使用另一个别名和连接语法的查询也有同样的问题

SELECT e.ts as timecol, sum(e.to_grid), sum(e.from_grid), sum(a.hourly_production)
FROM 'eloverblik' as e INNER JOIN 'ap_systems' as a ON
(e.ts = a.ts)
SAMPLE BY 1d;

Answer 1

time为保留关键字，请使用其他名称，如timestamp。
将别名重命名为更具描述性但更短的名称。
使用JOIN语法。
这些解决方案基本上是umadik和Honeybadger在评论中推荐的。

SELECT e.ts as timestamp, sum(e.to_grid) as sum_to_grid, sum(e.from_grid) as sum_from_grid, sum(a.hourly_production) as sum_hourly_production
FROM 'eloverblik' as e INNER JOIN 'ap_systems' as a ON
(e.ts = a.ts)
SAMPLE BY 1d;