基本环境

操作系统： centOS 7

postGreSQL ： 10

timescaleDB : 1.0 +

postGreSQL安装

timescaleDB 安装

官网安装文档：

timescaleDB 设置

实验

-- 下载测试数据    wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz    wget https://timescaledata.blob.core.windows.net/datasets/weather_big.tar.gz    -- 创建数据库    create database weather;    -- 解压下载文件    tar -zxvf weather_small.tar.gz    -- 创建表结构    psql -U postgres -d weather < weather.sql    -- 导入数据    psql -U postgres -d weather -c "\COPY conditions FROM weather_small_conditions.csv CSV"    psql -U postgres -d weather -c "\COPY locations FROM weather_small_locations.csv CSV"    -- 查询测试    SELECT * FROM conditions c ORDER BY time DESC LIMIT 10;    SELECT time, c.device_id, location,    trunc(temperature, 2) temperature, trunc(humidity, 2) humidity    FROM conditions c    INNER JOIN locations l ON c.device_id = l.device_id    WHERE l.environment = 'outside'    ORDER BY time DESC LIMIT 10;    SELECT date_trunc('hour', time) "hour",    trunc(avg(temperature), 2) avg_temp,    trunc(min(temperature), 2) min_temp,    trunc(max(temperature), 2) max_temp    FROM conditions c    WHERE c.device_id IN (        SELECT device_id FROM locations        WHERE location LIKE 'field-%'    ) GROUP BY "hour" ORDER BY "hour" ASC LIMIT 24;

注释

-- 数据库启动systemctl start postgresql-10-- 连接数据库psql-- 创建数据库CREATE database weather_big;-- 创建时序关系CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;-- 导入数据psql -U postgres -d weather_big < weather.sqlpsql -U postgres -d weather_big -c "\COPY conditions FROM weather_big_conditions.csv CSV"psql -U postgres -d weather_big -c "\COPY locations FROM weather_big_locations.csv CSV"

欢迎大家关注