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

当我试图将它放在一个连续的聚合物化视图中时，我得到了一个错误：

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的大部分功能。超级表上的SELECT语句没有已知的限制。

然而，连续聚合支持有限的查询，因此它可以增量地维护物化，而不是刷新整个物化，这将是昂贵的。基本上，查询应该允许独立于其他组处理每个聚合组，因此不允许使用DISTINCT和窗口函数。

创建连续聚合的documentation包含一个备注小节，其中列出了SELECT语句的限制。特别是：

不允许使用ORDER BY、DISTINCT和FILTER子句进行

聚合。

窗口函数不能与连续聚合一起使用。

解决限制的可能方法：

• 创建一个连续的aggregate with permitted语句，然后在其上定义一个视图，该视图将计算最终结果。这可以允许减少要由最终视图处理的数据量，但是例如在custom jobs的帮助下，execute.
• Create materialized view和创建刷新它的自动化仍然可能是昂贵的。但是，refresh将重新计算整个materialisation.
• If。如果您对如何计算部分数据的查询有很好的了解，您可以编写一个插入脚本到另一个表中，该表将专门为存储物化而创建。然后，物化可以通过例如custom jobs.

自动实现