postgres/timescaledb中的连续聚合需要time_bucket-function? &

Question

我有一个SELECT-query，它给了我一些东西的总和(minutes_per_hour_used)。按id、工作日和观测时间分组。

SELECT id,
extract(dow from observed_date) AS weekday, (  --observed_date  is type date
observed_hour,  -- is type timestamp without timezone, every full hour 00:00:00, 01:00:00, ...
sum(minutes_per_hour_used)
FROM base_table
GROUP BY id, weekday, observed_hour
ORDER BY id, weekday, observed_hour;

结果看起来不错，但现在我想将其存储在一个自我维护的视图中，该视图仅考虑/聚合过去8周。我认为连续聚合是正确的方式，但我不能使它工作(https://blog.timescale.com/blog/continuous-aggregates-faster-queries-with-automatically-maintained-materialized-views/)。似乎我需要以某种方式使用time_bucket函数，但实际上我不知道如何使用。有什么想法/提示吗?

我正在使用postgres与timescaledb.

编辑:这给了我想要的输出，但我不能把它放在一个连续的聚合

SELECT id,
extract(dow from observed_date) AS weekday,
observed_hour,
sum(minutes_per_hour_used)
FROM base_table
WHERE observed_date >= now() - interval '8 weeks'
GROUP BY id, weekday, observed_hour
ORDER BY id, weekday, observed_hour;

编辑:加上

CREATE VIEW my_view
WITH (timescaledb.continuous) AS

得到[0A000] ERROR: invalid SELECT query for continuous aggregate

Answer 1

连续聚合需要按time_bucket分组:

SELECT <grouping_exprs>, <aggregate_functions>
FROM <hypertable>
[WHERE ... ]
GROUP BY time_bucket( <const_value>, <partition_col_of_hypertable> ),
[ optional grouping exprs>]
[HAVING ...]

应该应用于分区列，该列通常是创建超表时使用的时间维度列。也不支持ORDER BY

对于问题中的聚合查询，没有时间列用于分组。weekday和observed_hour都不是时间有效列，因为它们不随时间增加，而是它们的值有规律地重复。weekday每7天重复一次，observed_hour每24小时重复一次。这违反了连续聚合的要求。

由于这个用例没有现成的解决方案，一种方法是使用连续聚合来减少目标查询的数据量，例如，按天分组:

CREATE MATERIALIZED VIEW daily
WITH (timescaledb.continuous) AS
SELECT id,
time_bucket('1day', observed_date) AS day,
observed_hour,
sum(minutes_per_hour_used)
FROM base_table
GROUP BY 1, 2, 3;

然后在上面执行目标聚合查询:

SELECT id,
extract(dow from day) AS weekday,
observed_hour,
sum(minutes_per_hour_used)
FROM daily
WHERE day >= now() - interval '8 weeks'
GROUP BY id, weekday, observed_hour
ORDER BY id, weekday, observed_hour;

另一种方法是使用PostgreSQL的物化视图，并在自定义作业的帮助下定期刷新，这是由TimescaleDB的作业调度框架运行的。请注意，刷新将重新计算整个视图，在示例中包含8周的数据。物化视图可以根据原始表base_table编写，也可以根据上面建议的连续聚合编写。