300
bus_data,complaints=9 passengers=8 1472371200
bus_data,complaints=7 passengers=15 1472372100
bus_data,complaints=7 passengers=15 1472373000
bus_data,complaints=7 passengers=17 1472373900
bus_data,complaints=7 passengers=20 1472374800
导入数据,命令如下:
root@f216e9be15bf:/# influx -import -path=bus_data.txt -precision=s
root@f216e9be15bf:/# influx -precision=rfc3339 -database=transportation
Connected to http://localhost:8086 version 1.3.5InfluxDB shell version: 1.3.5
> select * from bus_data
name: bus_data
time complaints passengers
---- ---------- ----------
2016-08-28T07:00:00Z 9 5
2016-08-28T07:15:00Z 9 8
2016-08-28T07:30:00Z 9 8
2016-08-28T07:45:00Z 9 7
2016-08-28T08:00:00Z 9 8
2016-08-28T08:15:00Z 7 15
2016-08-28T08:30:00Z 7 15
2016-08-28T08:45:00Z 7 17
2016-08-28T09:00:00Z 7 20
示例1 自动缩小取样存储到新的measurement中
对单个字段自动缩小取样并存储到新的measurement中。
CREATE CONTINUOUS QUERY "cq_basic" ON "transportation"
BEGIN
SELECT mean("passengers") INTO "average_passengers" FROM "bus_data" GROUP BY time(1h)
END
这个CQ的意思就是对bus_data
每小时自动计算取样数据的平均乘客数并存储到 average_passengers
中。那么在2016-08-28这天早上会执行如下流程:
At 8:00 cq_basic 执行查询,查询时间范围 time >= '7:00' AND time < '08:00'.
cq_basic写入一条记录到 average_passengers:
name: average_passengers
------------------------
time mean
2016-08-28T07:00:00Z 7
At 9:00 cq_basic 执行查询,查询时间范围 time >= '8:00' AND time < '9:00'.
cq_basic写入一条记录到 average_passengers:
name: average_passengers
------------------------
time mean
2016-08-28T08:00:00Z 13.75
# Results
> SELECT * FROM "average_passengers"name: average_passengers
------------------------
time mean
2016-08-28T07:00:00Z 7
2016-08-28T08:00:00Z 13.75
示例2 自动缩小取样并存储到新的保留策略(Retention Policy)中
CREATE CONTINUOUS QUERY "cq_basic_rp" ON "transportation"
BEGIN
SELECT mean("passengers") INTO "transportation"."three_weeks"."average_passengers" FROM "bus_data" GROUP BY time(1h)
END
与示例1类似,不同的是保留的策略不是autogen
,而是改成了three_weeks
(创建保留策略语法 CREATE RETENTION POLICY "three_weeks" ON "transportation" DURATION 3w REPLICATION 1
)。
> SELECT * FROM "transportation"."three_weeks"."average_passengers"name: average_passengers
------------------------
time mean
2016-08-28T07:00:00Z 7
2016-08-28T08:00:00Z 13.75
示例3 使用后向引用(backreferencing)自动缩小取样并存储到新的数据库中
CREATE CONTINUOUS QUERY "cq_basic_br" ON "transportation"
BEGIN
SELECT mean(*) INTO "downsampled_transportation"."autogen".:MEASUREMENT FROM /.*/ GROUP BY time(30m),*
END
使用后向引用语法自动缩小取样并存储到新的数据库中。语法 :MEASUREMENT
用来指代后面的表,而 /.*/
则是分别查询所有的表。这句CQ的含义就是每30分钟自动查询transportation
的所有表(这里只有bus_data一个表),并将30分钟内数字字段(passengers和complaints)求平均值存储到新的数据库 downsampled_transportation
中。
最终结果如下:
> SELECT * FROM "downsampled_transportation."autogen"."bus_data"name: bus_data
--------------
time mean_complaints mean_passengers
2016-08-28T07:00:00Z 9 6.5
2016-08-28T07:30:00Z 9 7.5
2016-08-28T08:00:00Z 8 11.5
2016-08-