设置数据库

psql -x "<SERVICE_URL>"
Password for user tsdbadmin:

复制

psql (13.3, server 12.8 (Ubuntu 12.8-1.pgdg21.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.
tsdb=>

复制

CREATE TABLE "metrics"(
    created timestamp with time zone default now() not null,
    type_id integer                                not null,
    value   double precision                       not null
);

复制

SELECT create_hypertable('metrics', by_range('created'));

复制

\COPY metrics FROM metrics.csv CSV;

复制

SELECT * FROM metrics LIMIT 5;

复制

created            | type_id | value 
-------------------------------+---------+-------
 2023-05-31 23:59:59.043264+00 |      13 |  1.78
 2023-05-31 23:59:59.042673+00 |       2 |   126
 2023-05-31 23:59:59.042667+00 |      11 |  1.79
 2023-05-31 23:59:59.042623+00 |      23 | 0.408
 2023-05-31 23:59:59.042603+00 |      12 |  0.96

复制

CREATE MATERIALIZED VIEW kwh_day_by_day(time, value)
    with (timescaledb.continuous) as
SELECT time_bucket('1 day', created, 'Europe/Berlin') AS "time",
        round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;

复制

SELECT add_continuous_aggregate_policy('kwh_day_by_day',
   start_offset => NULL,
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

复制

CREATE MATERIALIZED VIEW kwh_hour_by_hour(time, value)
  with (timescaledb.continuous) as
SELECT time_bucket('01:00:00', metrics.created, 'Europe/Berlin') AS "time",
       round((last(value, created) - first(value, created)) * 100.) / 100. AS value
FROM metrics
WHERE type_id = 5
GROUP BY 1;

复制

SELECT add_continuous_aggregate_policy('kwh_hour_by_hour',
   start_offset => NULL,
   end_offset => INTERVAL '1 hour',
   schedule_interval => INTERVAL '1 hour');

复制

SELECT view_name, format('%I.%I', materialization_hypertable_schema,materialization_hypertable_name) AS materialization_hypertable
FROM timescaledb_information.continuous_aggregates;

复制

view_name     |            materialization_hypertable
------------------+--------------------------------------------------
 kwh_day_by_day   | _timescaledb_internal._materialized_hypertable_2
 kwh_hour_by_hour | _timescaledb_internal._materialized_hypertable_3

复制