创建连续聚合

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

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

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

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

创建连续聚合

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

创建连续聚合

在 psql 提示符下，创建物化视图


CREATE MATERIALIZED VIEW conditions_summary_daily
WITH (timescaledb.continuous) AS
SELECT device,
   time_bucket(INTERVAL '1 day', time) AS bucket,
   AVG(temperature),
   MAX(temperature),
   MIN(temperature)
FROM conditions
GROUP BY device, bucket;

创建一个策略，每小时刷新一次视图


SELECT add_continuous_aggregate_policy('conditions_summary_daily',
  start_offset => INTERVAL '1 month',
  end_offset => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 hour');

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

选择合适的时间桶间隔

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

当连续聚合被物化时，物化表存储部分结果，然后用于计算查询的结果。这意味着任何查询都需要一定的处理能力，并且随着间隔变小，所需的能力也变得更大。因此，如果您有非常小的间隔，则在超表中的原始数据上运行聚合查询可能更有效。您应该测试这两种方法，以确定哪种方法最适合您的数据集和所需的时间桶间隔。

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

使用 WITH NO DATA 选项

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

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

使用 WITH NO DATA 选项创建连续聚合

在 psql 提示符下，创建视图


CREATE MATERIALIZED VIEW cagg_rides_view
WITH (timescaledb.continuous) AS
SELECT vendor_id,
time_bucket('1h', pickup_datetime) AS hour,
  count(*) total_rides,
  avg(fare_amount) avg_fare,
  max(trip_distance) as max_trip_distance,
  min(trip_distance) as min_trip_distance
FROM rides
GROUP BY vendor_id, time_bucket('1h', pickup_datetime)
WITH NO DATA;

手动刷新视图


CALL refresh_continuous_aggregate('cagg_rides_view', NULL, localtimestamp - INTERVAL '1 week');

添加策略


SELECT add_continuous_aggregate_policy('cagg_rides_view',
  start_offset => INTERVAL '1 week',
  end_offset   => INTERVAL '1 hour',
  schedule_interval => INTERVAL '30 minutes');

创建带有 JOIN 的连续聚合

在 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 查询中包含更多超表、表、视图或子查询。此外，请确保您正在查询的超表未启用行级安全策略。

查询连续聚合

在 psql 提示符下，查询名为 conditions_summary_hourly 的连续聚合视图，以获取设备 5 记录的 2021 年第一季度的平均温度、最低温度和最高温度
```
SELECT *
  FROM conditions_summary_hourly
  WHERE device = 5
  AND bucket >= '2020-01-01'
  AND bucket < '2020-04-01';
```

或者，查询名为 conditions_summary_hourly 的连续聚合视图，以获取该季度前 20 个最大指标范围


SELECT *
  FROM conditions_summary_hourly
  WHERE max - min > 1800
  AND bucket >= '2020-01-01' AND bucket < '2020-04-01'
  ORDER BY bucket DESC, device DESC LIMIT 20;

将连续聚合与窗口函数一起使用

连续聚合目前不支持窗口函数。您可以通过以下方式解决此问题：

为查询的其他部分创建连续聚合，然后
在查询时在连续聚合上使用窗口函数

例如，假设您有一个名为 example 的超表，其中包含 time 列和 value 列。您按 time 对数据进行分桶，并使用 lag 窗口函数计算时间桶之间的增量


WITH t AS (
  SELECT
    time_bucket('10 minutes', time) as bucket,
    first(value, time) as value
  FROM example GROUP BY bucket
)
SELECT
  bucket,
  value - lag(value, 1) OVER (ORDER BY bucket) delta
  FROM t;

您无法使用此查询创建连续聚合，因为它包含 lag 函数。但是您可以通过排除 lag 函数来创建连续聚合


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;

然后，在查询时，通过在连续聚合上使用 lag 来计算增量


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

这通过提前计算聚合来加快您的查询速度。增量仍然需要在查询时计算。

注意

相关内容