问题
我有一个使用PostgreSQL+TimescaleDB的表:
create table heatingElectricityMeter(
time timestamptz(0) not null,
import real not null
);
select create_hypertable('heatingElectricityMeter', 'time', chunk_time_interval => interval '1 month');
"import" 是自能量计生产以来消耗的总能量。因此,“import”是单调递增的。大约每10秒钟,我会向该表插入一个值(但当前在数据库中每10秒钟不到一个值)。
现在,我想创建一个条形图,显示过去X天每个本地日消耗的能量。比如说...最近90天。
我已经尝试过的方法:
这是我的第一次尝试:
select
time_bucket(interval '1 day', time::timestamp) "day",
first(import, "time"),
last(import, "time")
from heatingelectricitymeter
where time between '2021-07-28T02:00:00Z' and '2021-10-28T02:00:00Z'
group by "day"
order by 1
这个查询在我的树莓派2上,在查询时间范围内有大约85K个数据点,需要大约2.5秒的时间。虽然不算是“问题”,但速度可以更快。我下一步的尝试:
select
time_bucket(interval '1 day', time::timestamp) "day",
max(import) - min(import)
from heatingelectricitymeter
where time between '2021-07-28T02:00:00Z' and '2021-10-28T02:00:00Z'
group by "day"
order by 1
效果应该是一样的,我也期望它表现出相同的效果。然而,令我惊讶的是,它要快得多:只需要大约1.5秒。
(旁注:不将其转换为
::timestamp
可以将查询加速到1.7秒和1.0秒。但是,我最终想在本地时间内使用午夜到午夜,如果我正确理解手册,那么我将需要进行此转换)现在有一件事是文档说:
这并不能真正回答为什么last和first命令不使用索引,而是通过它们的组执行顺序扫描。
min
和max
更快的原因。它们也不能使用索引。同时,使用索引似乎是有道理的。我们只需要每个组的第一个和最后一个值。我的下一个尝试是直接收集所需的值,而不使用分组。
select "day", "import" - lag("import") over (order by "day") from (
select ('2021-10-28'::date - v) "day"
from generate_series(0, 90) s(v)
)x
join lateral (
select time, import from heatingElectricityMeter where time >= day order by time limit 1
)y on true
哇!只用了0.7秒!
结果并不完全与min/max/first/last相同,因为在此查询中,我将始终将每天的第一个值与前一天的第一个值进行比较。但在我看来,这实际上更好,因为数据可能会落在错误的日期,但不会丢失。(在完美的世界中,我会在每天凌晨准确获得插值值,但出于简单起见,我将推迟这一点。对于我的用例,我可以接受轻微的不准确性)。
然而,感觉我以一种过于复杂的方式做到了这一点。在我看来,这似乎是使用时间数据库完成的完全正常的任务。是否有更简单有效的方法来执行此查询?
first
/last
和min
/max
在做不同的事情。 - TmTron