timescaledb：创建real-time aggregate

创建hypertable【chz_a】

create table chz_a (
       time timestamp,
       device_id int8, 
       value double precision,
       primary key (time)
);
SELECT create_hypertable(
    'chz_a', 
    by_range('time')
);
1
2
3
4
5
6
7
8
9
10

往表里面写入数据

# 当天的数据
insert into chz_a (time, device_id, value)
values
(now()::timestamp, 1, 1);

# 一天前的数据
insert into chz_a (time, device_id, value)
values
(now()::timestamp - '1 day'::interval, 1, 1);

# 两天前的数据
insert into chz_a (time, device_id, value)
values
(now()::timestamp - '2 day'::interval, 1, 1);
1
2
3
4
5
6
7
8
9
10
11
12
13
14

查下表【chz_a】看下里面的数据，执行【select * from chz_a】

创建cagg【chz_a_cagg】，注意【WITH NO DATA】这个参数很重要

CREATE MATERIALIZED VIEW chz_a_cagg
WITH (timescaledb.continuous) AS
SELECT device_id,
   time_bucket(INTERVAL '1 day', time) AS bucket,
   sum(value)
FROM chz_a
GROUP BY device_id, bucket
WITH NO DATA;
1
2
3
4
5
6
7
8

刷新策略：每天一次，刷新两月前到两天前的数据范围

SELECT add_continuous_aggregate_policy('chz_a_cagg',
  start_offset => INTERVAL '1 month',
  end_offset   => INTERVAL '1 day',
  schedule_interval => INTERVAL '1 day'
);
1
2
3
4
5

手动刷新一下数据两天之前的数据范围

CALL refresh_continuous_aggregate(
       'chz_a_cagg',
       NULL,
       localtimestamp - INTERVAL '1 day'
);
1
2
3
4
5

查看【chz_a_cagg】里面的数据，执行【select * from chz_a_cagg】：

可以看到只有【06-19】的数据被查出来了。

将cagg修改为【real-time】模式

ALTER MATERIALIZED VIEW chz_a_cagg set (timescaledb.materialized_only = false);
1

查看【chz_a_cagg】里面的数据，执行【select * from chz_a_cagg】：

可以看到【06-20、06-21】的数据也被查出来了。