timescaledb 是否支持窗口函数? [英] Does timescaledb support window functions?

问题描述

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

I am trying to use the TimescaleDB extension to compute some continuous aggregates. I have this query which works fine:

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

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

When I try to put this inside a continuous aggregate materialized view, I get an error:

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 是否允许按时间窗口进行连续聚合?

Does TimescaleDB allow Continuous Aggregates over Partition By time windows?

我在 PostgreSQL 12.5 上使用 TimescaleDB 2.1.

I am using TimescaleDB 2.1 on PostgreSQL 12.5.

推荐答案

TimescaleDB 是一个 PostgreSQL 扩展，并允许 PostgreSQL 的大部分功能.超表上的 SELECT 语句没有已知的限制.

TimescaleDB is a PostgreSQL extension and allows the most of PostgreSQL's functionality. There is no known limitations on SELECT statements on hypertables.

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

However, continuous aggregates support limited queries, so it can maintain materialisation incrementally instead of refreshing entire materialisation, which will be expensive. Basically queries should allow to process each aggregate group independently from other groups, thus DISTINCT and window functions are not allowed.

创建连续聚合的文档包含一个带有列表的注释小节SELECT 语句的限制.特别是:

The documentation of creating a continuous aggregate contains a note subsection with list of limitations on the SELECT statement. In particular:

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

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

窗口函数不能与连续聚合结合使用.

Window functions cannot be used in conjunction with continuous aggregates.

解决限制的可能方法:

• 使用允许的 SELECT 语句创建一个连续聚合，然后在其上定义一个视图，该视图将计算最终结果.这可以减少最终视图要处理的数据量，但执行起来仍然很昂贵.
• 创建一个实体化视图并创建自动刷新它，例如，在自定义作业的帮助下.但是，refresh 将重新计算整个物化.
• 如果您对如何计算部分数据的查询有一个好主意，您可以将插入脚本写入另一个表，该脚本将专门创建用于存储物化.然后可以使用例如自定义作业自动实现具体化.

• Create a continuous aggregate with permitted SELECT statement and then define a view on top of it, which will calculate the final result. This can allow to reduce the amount of data to process by the final view, but still can be expensive to execute.
• Create a materialized view and create automation of refreshing it, e.g., with help of custom jobs. However, the refresh will recalculate entire materialisation.
• If you have a good idea how calculate the query on a portion of data, you can write an insert script into another table, which will be specially created for storing the materialization. Then materialisation can be automated with, e.g., custom jobs.

这篇关于timescaledb 是否支持窗口函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！