postgresql - timescaledb 是否支持窗口函数？

Question

我正在尝试使用 TimescaleDB 扩展来计算一些连续聚合。我有这个工作正常的查询:

SELECT distinct time_bucket('1 hour', entry_ts) as date_hour,
                type_id,
                entry_id,
                exit_id,
                count(*) OVER (partition by time_bucket('1 hour', entry_ts), entry_id, exit_id, type_id) AS total,
                ((count(*) over (partition by time_bucket('1 hour', entry_ts), entry_id, exit_id, type_id)) * 100)::numeric /
                (count(*) over (partition by time_bucket('1 hour', entry_ts), entry_id)) percentage_of_entry
FROM transits

当我尝试将其放入连续聚合物化 View 中时，出现错误:

CREATE MATERIALIZED VIEW transits_hourly
            WITH (timescaledb.continuous) AS
SELECT distinct time_bucket('1 hour', entry_ts) as date_hour,
                type_id,
                entry_id,
                exit_id,
                count(*) OVER (partition by time_bucket('1 hour', entry_ts), entry_id, exit_id, type_id) AS total,
                ((count(*) over (partition by time_bucket('1 hour', entry_ts), entry_id, exit_id, type_id)) * 100)::numeric /
                (count(*) over (partition by time_bucket('1 hour', entry_ts), entry_id)) percentage_of_entry
FROM transits
WITH NO DATA

我得到的错误是:

ERROR:  invalid continuous aggregate view
SQL state: 0A000

TimescaleDB 是否允许在按时间窗口分区上进行连续聚合？
我在 PostgreSQL 12.5 上使用 TimescaleDB 2.1。

Answer 1

TimescaleDB 是一个 PostgreSQL 扩展，允许使用 PostgreSQL 的大部分功能。 hypertables 上的 SELECT 语句没有已知的限制。
然而，连续聚合支持有限的查询，因此它可以增量地维护物化而不是刷新整个物化，这将是昂贵的。基本上查询应该允许独立于其他组处理每个聚合组，因此 DISTINCT和窗口函数是不允许的。
documentation创建连续聚合包含一个注释小节，其中包含对 SELECT 语句的限制列表。特别是:

Aggregates with ORDER BY, DISTINCT and FILTER clauses are not permitted.

Window functions cannot be used in conjunction with continuous aggregates.

解决限制的可能方法:

使用允许的 SELECT 语句创建一个连续聚合，然后在其上定义一个 View ，它将计算最终结果。这可以减少最终 View 要处理的数据量，但执行起来仍然很昂贵。

创建一个 materialized view并创建自动刷新它，例如，在 custom jobs 的帮助下.然而，refresh将重新计算整个物化。

如果您对如何计算部分数据的查询有一个很好的想法，您可以将插入脚本写入另一个表，该脚本将专门用于存储物化。然后可以使用例如 custom jobs 自动实现物化.