timescaleDB超表创建

一、创建数据库

在timeSeries数据库上创建TimeScaleDB extension

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

创建数据表

CREATE TABLE conditions (
 time        TIMESTAMPTZ       NOT NULL,
   location    TEXT              NOT NULL,
   temperature NUMERIC  NULL,
   humidity    NUMERIC  NULL
 );

添加复合主键

ALTER TABLE conditions ADD PRIMARY KEY (time,location) ;

将新建的表转换为hypertable

 SELECT create_hypertable('conditions', 'time');

把conditions表的数据块区间设置成24小时，分区键是TIMESTAMP类型

SELECT set_chunk_time_interval('conditions', interval '24 hours');

如“行太大，8610”将字段设置为外部存储

ALTER TABLE conditions ALTER temperature SET STORAGE EXTERNAL

数据查询

 INSERT INTO conditions
  VALUES
    (NOW(), 'office', 70.0, 50.0),
    (NOW(), 'basement', 66.5, 60.0),
    (NOW(), 'garage', 77.0, 65.2);

--过去3小时内，每15分钟采集一次数据，按时间和温度排序。
SELECT time_bucket('15 minutes', time) AS fifteen_min,
    location, COUNT(*),
    MAX(temperature) AS max_temp,
    MAX(humidity) AS max_hum
  FROM conditions
  WHERE time > NOW() - interval '3 hours'
  GROUP BY fifteen_min, location
  ORDER BY fifteen_min DESC, max_temp DESC;
	
--均值查询（Median）
SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions;
	
	SELECT ( to_timestamp('1/8/2020 20:00:00','dd/MM/yyyy hh24:mi:ss')),