创建连续聚合

创建连续聚合是一个两步过程。您需要首先创建视图，然后启用策略以保持视图刷新。您可以在超表上或在另一个连续聚合之上创建视图。每个源表或视图上可以有多个连续聚合。

连续聚合要求在超表的时间分区列上使用 time_bucket。

默认情况下，视图会自动刷新。您可以通过设置 WITH NO DATA 选项来调整此设置。此外，该视图不能是 安全屏障视图。

连续聚合在后台使用超表，这意味着它们也使用块时间间隔。默认情况下，连续聚合的块时间间隔是原始超表块时间间隔的 10 倍。例如，如果原始超表的块时间间隔为 7 天，则在其之上的连续聚合的块时间间隔为 70 天。

在此示例中，我们使用名为 conditions 的超表，并为每日天气数据创建一个连续聚合视图。GROUP BY 子句必须包含一个使用超表时间维度列的 time_bucket 表达式。此外，SELECT、GROUP BY 和 HAVING 子句中包含的所有函数及其参数都必须是 不可变 的。

您可以在连续聚合中使用大多数 PostgreSQL 聚合函数。要查看支持哪些 PostgreSQL 功能，请查看函数支持表。

连续聚合要求在超表的时间分区列上使用 time_bucket。时间桶允许您定义一个时间间隔，而不是必须使用特定的时间戳。例如，您可以将时间桶定义为五分钟或一天。

您不能直接在连续聚合中使用 time_bucket_gapfill。这是因为您需要访问先前的数据来确定填充内容，而这些内容在您创建连续聚合时尚未可用。您可以通过使用 time_bucket 创建连续聚合，然后使用 time_bucket_gapfill 查询连续聚合来解决此问题。

默认情况下，当您首次创建视图时，它会填充数据。这样做是为了可以在整个超表上计算聚合。如果您不希望发生这种情况，例如如果表非常大，或者新数据正在连续添加，您可以控制数据刷新的顺序。您可以通过使用 WITH NO DATA 选项，为连续聚合策略添加手动刷新来实现。

WITH NO DATA 选项允许立即创建连续聚合，因此您无需等待数据聚合。数据仅在策略开始运行时才开始填充。这意味着只有比 start_offset 时间新的数据才开始填充连续聚合。如果您有早于 start_offset 间隔的历史数据，您需要手动刷新历史数据直到当前的 start_offset，以使实时查询高效运行。

在 Timescale 2.10 及更高版本，以及 PostgreSQL 12 及更高版本中，您可以创建一个包含 JOIN 的连续聚合查询。例如

CREATE MATERIALIZED VIEW conditions_summary_daily_3
WITH (timescaledb.continuous) AS
SELECT time_bucket(INTERVAL '1 day', day) AS bucket,
   AVG(temperature),
   MAX(temperature),
   MIN(temperature),
   name
FROM devices JOIN conditions USING (device_id)
GROUP BY name, bucket;

复制

注意

有关使用 JOIN 创建连续聚合的更多信息，包括一些额外限制，请参阅关于连续聚合部分。

创建连续聚合并设置刷新策略后，您可以使用 SELECT 查询视图。您只能在 FROM 子句中指定一个超表。在 SELECT 查询中包含更多超表、表、视图或子查询是不受支持的。此外，请确保您查询的超表未启用 行级安全策略 启用。

可变函数在连续聚合查询定义中获得实验性支持。可变函数默认启用。但是，如果您在物化查询中使用它们，则会返回警告。

当使用非不可变函数时，您必须确保这些函数在连续聚合刷新运行时产生一致的结果。例如，如果函数依赖于当前时区，您必须确保所有连续聚合刷新都以一致的时区设置运行。

窗口函数在连续聚合查询定义中获得实验性支持。窗口函数默认禁用。要启用它们，请将 timescaledb.enable_cagg_window_functions 设置为 true。

在连续聚合中使用窗口函数

1. 创建一张简单表，用于在特定时间存储值

   CREATE TABLE example (
     time       TIMESTAMPZ        NOT NULL,
     value      TEXT              NOT NULL,
   );

   复制

2. 启用窗口函数。

   由于窗口函数是实验性的，为了创建带有窗口函数的连续聚合，您必须 enable_cagg_window_functions。

   SET timescaledb.enable_cagg_window_functions TO TRUE;

   复制

3. 按 time 将数据分桶，并使用 lag 窗口函数计算时间桶之间的增量

   窗口函数必须保持在时间桶内。任何试图查看当前时间桶之外的查询都将在刷新边界附近产生不正确的结果。

   CREATE MATERIALIZED VIEW example_aggregate
     WITH (timescaledb.continuous) AS
       SELECT
         time_bucket('1d', time),
         customer_id,
         sum(amount) AS amount,
         sum(amount) - LAG(sum(amount),1,NULL) OVER (PARTITION BY time_bucket('1d', time) ORDER BY sum(amount) DESC) AS amount_diff,
         ROW_NUMBER() OVER (PARTITION BY time_bucket('1d', time) ORDER BY sum(amount) DESC)
       FROM sales GROUP BY 1,2;

   复制

   按 time_bucket 分区的窗口函数即使与 LAG()/LEAD() 一起使用也应该是安全的

对于 TimescaleDB v2.19.3 及更早版本，连续聚合不支持窗口函数。要解决此问题

1. 创建一张简单表，用于在特定时间存储值

   CREATE TABLE example (
     time       TIMESTAMPZ        NOT NULL,
     value      TEXT              NOT NULL,
   );

   复制

2. 创建不使用窗口函数的连续聚合

   CREATE MATERIALIZED VIEW example_aggregate
     WITH (timescaledb.continuous) AS
       SELECT
         time_bucket('10 minutes', time) AS bucket,
         first(value, time) AS value
       FROM example GROUP BY bucket;

   复制

3. 在查询时对您的连续聚合使用 lag 窗口函数

   这通过提前计算聚合来加速您的查询。增量在查询时计算。

   SELECT
     bucket,
     value - lag(value, 1) OVER (ORDER BY bucket) AS delta
   FROM example_aggregate;

   复制